KingbaseES 复杂查询、CTE 与窗口函数实践从统计结果到分析结果这篇文章是咱们这个系列的第 11 篇了。上一篇的话咱们聊了常用函数和表达式那么今天这篇呢咱们就要进一步去看看复杂查询了。也就是说咱们要用 CTE 还有窗口函数去把客户排名、订单分层还有商品销量分析这些东西给搞出来。引言咱们上一篇解决的问题其实就是“单条查询怎么才能表达出更多的业务含义”。比如说状态翻译啦日期统计啦还有金额计算和空值处理这些。但是业务问题一旦变复杂了你光用单纯的JOIN GROUP BY的话那个 SQL 往往就会写得特别长读起来也很费劲。你比如说是下面这些情况每个客户消费金额排名是多少 每个客户最近一笔订单是哪一笔 商品销量排名如何计算 如何把订单按金额分层这些问题出现了。那为啥头疼呢原因在于你不仅要统计还得在统计出来的结果上接着去分析。那么这篇文章的话咱们就会去用 CTE 还有窗口函数让你的 SQL 能够有更强的数据分析能力。文章目录KingbaseES 复杂查询、CTE 与窗口函数实践从统计结果到分析结果引言CTE 与窗口函数解决什么问题一、连接 kb_shop二、用 CTE 拆分客户消费统计三、客户消费排名四、每个客户最近一笔订单五、商品销量排名六、订单金额分层七、复杂查询的可读性原则八、常见问题排查问题 1窗口函数和 GROUP BY 分不清问题 2CTE 写得太多反而难读问题 3排名结果并列时不符合预期九、本文小结CTE 与窗口函数解决什么问题CTE 这个东西其实也就是WITH子句。它能把复杂的 SQL 给拆成好几个带名字的步骤。那么窗口函数呢它就可以在不折叠明细行的情况下面去把排名、累计值还有分组序号给算出来。能力解决的问题CTE拆分复杂查询提高可读性子查询在查询中嵌套临时结果窗口函数保留明细行同时计算排名、序号、累计值ROW_NUMBER分组内排序取第一条RANK排名分析普通的聚合往往会把多行给你压成一行。但是窗口函数不一样它是可以保留原始行的。也就是说这是它跟GROUP BY最大的一个区别了。在实际用 KingbaseES 的时候复杂查询往往仅仅只是出现在报表、运营分析还有迁移校验和数据核对这些场景里面。单纯的聚合呢它只能告诉你“总共有多少、合计多少”。那窗口函数能干嘛呢它就可以进一步告诉你“排第几、每组最新一条是哪条、同类数据怎么分层”。有了这些能力数据库就能直接输出更接近分析结果的数据了。这样的话应用侧再去二次加工的工作量也就少了。不过话又说回来复杂的 SQL 它也更需要可读性。咱们这篇文章用 CTE 的目的其实并不是为了增加语法上的复杂度而是要把业务步骤给拆开。也就是说你先过滤出已经支付的订单接着再去聚合消费金额然后再去做排名或者分层。截图里面的每个结果那都是对应着一个明确的业务问题的。大家看的时候可以从结果反推回去看 SQL 逻辑而不是只看到一大段根本没人敢去维护的语句。一、连接 kb_shopcd /d D:\Tools\Kingbase\ES\Server\bin ksql -U system -d kb_shop -h localhost -p 54321咱们先确认一下视图是不是存在\dv report.*这篇文章的话咱们会去复用前面建好的那个report.v_customer_amount还有report.v_order_detail。二、用 CTE 拆分客户消费统计咱们先来统计一下已经支付的订单WITHpaid_ordersAS(SELECTcustomer_id,order_id,total_amountFROMsales.customer_orderWHEREorder_statuspaid)SELECTc.customer_id,c.customer_name,COUNT(p.order_id)ASpaid_order_count,COALESCE(SUM(p.total_amount),0)ASpaid_amountFROMsales.customer cLEFTJOINpaid_orders pONc.customer_idp.customer_idGROUPBYc.customer_id,c.customer_nameORDERBYpaid_amountDESC;这里面的paid_orders呢其实就像是一个临时起了名字的结果集。有了它主查询就不用去反复写WHERE order_status paid了也就是说可读性会变得更好。从执行的结果去看客户维度的订单数还有消费金额都已经给你整理出来了。这个结果的话跟咱们第九篇那个report.v_customer_amount视图的口径是能相互印证的。也就是说仅仅只统计已支付订单同时还要保留客户信息。在文章里面多次去复用同一个业务口径往往仅仅只是为了减少大家对数据结果的疑惑而已。三、客户消费排名咱们用窗口函数来给客户的消费金额排个名SELECTcustomer_id,customer_name,paid_amount,RANK()OVER(ORDERBYpaid_amountDESC)ASamount_rankFROMreport.v_customer_amountORDERBYamount_rank;RANK() OVER (...)这个东西它是不会把数据分组压缩的。它是在每行上面给你加一个排名的值。这种能力的话拿来做报表那是相当合适的。那如果咱们希望排名不跳号的话就可以这样写SELECTcustomer_id,customer_name,paid_amount,DENSE_RANK()OVER(ORDERBYpaid_amountDESC)ASdense_rank_noFROMreport.v_customer_amountORDERBYdense_rank_no;这里的话你得根据业务上的解释去选择排名函数。销售榜单上面通常来说并列名次很常见那用RANK就可以体现出并列之后跳号的情况。那如果说业务这边更关心连续的排序你就可以改成用DENSE_RANK。要是必须给每一行都给个唯一的序号那就得用ROW_NUMBER了。这些函数其实没有绝对的好坏之分关键就是你的排名规则得跟业务含义对得上才行。四、每个客户最近一笔订单这个问题其实非常常见每个客户最近一次下单到底是哪一笔呢WITHorder_rankAS(SELECTo.order_id,o.order_no,o.customer_id,c.customer_name,o.order_status,o.total_amount,o.created_at,ROW_NUMBER()OVER(PARTITIONBYo.customer_idORDERBYo.created_atDESC,o.order_idDESC)ASrnFROMsales.customer_order oJOINsales.customer cONo.customer_idc.customer_id)SELECTcustomer_id,customer_name,order_no,order_status,total_amount,created_atFROMorder_rankWHERErn1ORDERBYcustomer_id;这里的关键点就在于PARTITIONBYo.customer_idORDERBYo.created_atDESC这表示啥呢也就是说在每个客户的内部按照订单时间去排序接着再用ROW_NUMBER把第一条给取出来。像这种“每组取最新一条”的需求在做迁移校验的时候也是经常碰到的。你比如说要取每个用户最后一次登录的时间或者说每个商品最近一次库存变更还有每个订单最后一条状态记录的情况。用窗口函数去实现的话比你在应用层去写循环查询要清晰得多而且也更容易去做统一的验证。五、商品销量排名WITHproduct_salesAS(SELECTp.product_code,p.product_name,SUM(i.quantity)ASsold_qty,SUM(i.line_amount)ASsold_amountFROMsales.order_item iJOINsales.customer_order oONi.order_ido.order_idJOINinventory.product pONi.product_idp.product_idWHEREo.order_statuspaidGROUPBYp.product_code,p.product_name)SELECTproduct_code,product_name,sold_qty,sold_amount,RANK()OVER(ORDERBYsold_qtyDESC,sold_amountDESC)ASsales_rankFROMproduct_salesORDERBYsales_rank;这个 SQL 咱们是分了两步去走的第一步的话在 CTE 里面先按照商品去把销量给聚合了。接着第二步在外层再去对聚合出来的结果做排名。这样搞的话比你把所有的逻辑都塞在一层查询里面要清楚得多。截图里面的这个排行结果其实还可以拿来做后续索引和报表优化的参考。那如果说商品销量排行会频繁出现在运营看板里面的话你就可以把它封装成一个视图或者结合着数据量再去分析一下执行计划。也就是说复杂查询它并不是孤立的章节它跟咱们第八篇讲的执行计划还有第九篇的视图封装以及第十三篇的报表导出这些都是有连续关系的。六、订单金额分层咱们还可以把订单按照金额给分成不同的层级SELECTorder_no,total_amount,CASEWHENtotal_amount1000THEN高金额订单WHENtotal_amount300THEN中金额订单ELSE普通订单ENDASamount_levelFROMsales.customer_orderORDERBYtotal_amountDESC;那如果咱们要去统计每个层级的数量呢WITHorder_levelAS(SELECTorder_no,total_amount,CASEWHENtotal_amount1000THEN高金额订单WHENtotal_amount300THEN中金额订单ELSE普通订单ENDASamount_levelFROMsales.customer_order)SELECTamount_level,COUNT(*)ASorder_count,SUM(total_amount)ASamount_sumFROMorder_levelGROUPBYamount_levelORDERBYamount_sumDESC;CTE 这么一搞分层逻辑你只要写一次就行了外层仅仅只负责统计。订单金额分层这东西看着好像仅仅只是一个CASE WHEN罢了。但其实它体现出了分析 SQL 的一种常见写法。那是什么呢也就是你先把明细数据转换成业务层级接着再在这个层级上去做统计。在真实的项目里面你可以把金额分层替换成客户等级、风险等级、库存等级或者账龄区间这些。只要分层的规则你写清楚了CTE 就能让后面的统计保持一致。七、复杂查询的可读性原则复杂查询最怕啥最怕的就是“能跑但没人敢改”。CTE 还有窗口函数虽然说很强大但要是你命名搞得乱七八糟层次也太多的话后续要去维护还是很困难的。所以写复杂 SQL 的时候通常来说建议遵循三条原则原则说明CTE 名称表达业务含义例如paid_orders比t1更清楚每层只做一类事情先过滤、再聚合、再排名层次分明关键统计口径写在注释或文章说明中例如“只统计已支付订单”就拿商品销量排行来说product_sales这个 CTE 的职责其实就是先形成商品粒度的销售统计外层再去搞排名。这样一来的话读代码的人就不用在一层 SQL 里面同时去理解过滤、关联、聚合还有排名这些逻辑了。在写文章的时候复杂的 SQL 更应该拆开来讲。你先说明业务问题是啥接着再说明 SQL 分成了几步去解决最后再把完整的代码给出来。这样搞的话既能让文章解释得更有深度也能让看文章的人理解门槛变低。从咱们这篇文章的截图也能看出来每个复杂查询返回的都是有限而且可读的结果集。这一点对教学文章来说很重要。为啥呢因为复杂的 SQL 不应该仅仅只追求语法完整还得让读者能通过结果确认自己到底执行到了哪一步。所以建议大家投稿的时候一定要保留关键结果的截图特别是排名、最近订单还有金额分层这三种结果。为啥要留它们呢原因在于它们最能体现出窗口函数和 CTE 在实战中的价值。八、常见问题排查问题 1窗口函数和 GROUP BY 分不清咱们简单来理解一下写法结果GROUP BY多行压缩成一行窗口函数保留原始行额外增加分析结果那如果咱们既要保留明细又要去搞排名的话就优先去考虑用窗口函数好了。问题 2CTE 写得太多反而难读CTE 这东西是为了提高可读性才用的可不是为了把 SQL 给拆得稀碎的。通常来说建议每个 CTE 都得有明确的业务含义就像paid_orders、product_sales、order_rank这样。问题 3排名结果并列时不符合预期RANK的话是并列会跳号DENSE_RANK是并列但不跳号ROW_NUMBER则是每行都有唯一的序号。这个的话根据业务需求去选就行了。九、本文小结这篇文章接着第十篇的函数表达式咱们进一步进入了复杂查询分析的地盘。通过 CTE 还有窗口函数咱们也就把 SQL 从基础查询给提升到了业务分析的层面了。这篇文章咱们重点掌握了WITH CTE ROW_NUMBER RANK DENSE_RANK PARTITION BY ORDER BY in window 分层统计 最近一笔订单查询到了下一篇的话咱们就会从查询分析切换到运行状态诊断上面去了。到时候会讲会话、锁等待还有长事务排查这些内容。那为什么要讲这些呢原因在于复杂的 SQL 还有事务一旦到了真实环境里面你不仅得能写得出来还得能诊断出运行的时候到底发生了啥问题。