KES数据库视图封装与业务报表查询实践让复杂 SQL 成为稳定接口本文是本系列第 9 篇。上一篇围绕索引和执行计划分析了查询优化的基本方法本文继续沿着查询场景推进讲解如何用视图封装订单、客户和商品报表。引言上一篇咱们聊的是 SQL 到底是怎么跑的索引到底有没有生效还有怎么用EXPLAIN去看查询计划这些事。但是呢在真实的业务里面其实还有一个特别常见的情况就是很多那种复杂的查询往往会被翻来覆去地用。如果你每个页面、每个脚本都去重复写一大堆 JOIN 和聚合的 SQL那维护起来真的是头疼。而且很容易出现一个问题那就是统计口径对不上的情况。视图这东西其实它就能把复杂的查询给包起来变成一个能反复用的数据库对象。用的人呢就把它当成普通表去查就行了。但是视图的背后往往仅仅只是藏着多表关联啊、字段翻译啊还有聚合统计这些逻辑而已。那么这篇呢咱们就基于kb_shop来建几个特别典型的视图订单列表视图。订单明细视图。客户消费汇总视图。商品销售排行视图。文章目录KES数据库视图封装与业务报表查询实践让复杂 SQL 成为稳定接口引言视图的价值复用、隔离与表达一、连接 kb_shop二、创建订单列表视图三、创建订单明细视图四、创建客户消费汇总视图五、创建商品销售排行视图六、查看视图定义七、视图和表的区别八、常见问题排查问题 1创建视图时报表不存在问题 2视图查询结果为空问题 3修改底层表字段后视图失效九、本文小结视图的价值复用、隔离与表达视图它不是那种用来存数据的普通表。也就是说它里面存的是查询的定义而已。每次你去查视图的时候数据库就会按照这个定义临时给你把结果跑出来。视图常见的价值通常来说有下面这些价值说明复用 SQL把复杂的 JOIN 给包起来这样的话重复的代码就少了统一口径报表统计的逻辑可以集中在一起维护了简化访问用的人只管视图里有什么字段就行没必要去搞懂底层所有的表权限隔离你可以授权让用户去访问视图而不是直接让他们碰底层的表在kb_shop这个库里头订单报表起码得用到客户表、订单主表、订单明细表还有商品表这几张表。视图就能把这些关联关系给固定下来那么后面再查的话其实就清楚多了。从实际做产品的角度来看的话KingbaseES 作为一个企业层级里的关系型数据库它其实不光是用来存表数据的往往还要干一些稳定数据接口的活儿。应用系统啊、报表平台啊还有运维脚本往往都可能要去访问同一批业务数据。如果每个入口都各写一套 SQL 的话那后续你要是想改个口径真的是非常麻烦的。所以把那些稳定的报表口径包成视图就能让数据库这边提供一个统一的入口接着应用那边就只要按视图的字段去拿结果就行了。不过有一点得注意普通视图它可不是什么“自动加速器”。它干的活往往是仅仅解决复用啊、隔离啊还有表达这些情况。性能这东西它还是得看底层的 SQL 写得咋样、索引有没有、统计信息准不准还有数据量到底有多大。所以呢这篇讲视图的文章我把它放在了第八篇索引和执行计划的后头。逻辑是这样的你得先搞明白 SQL 到底是怎么执行的接着再来学怎么把复杂的 SQL 包成一个能长期维护的对象。一、连接 kb_shopcd /d D:\Tools\Kingbase\ES\Server\bin ksql -U system -d kb_shop -h localhost -p 54321先确认一下咱们之前建的业务表还在不在\dt sales.*\dt inventory.*视图这东西通常来说最好把它放到一个单独的模式里面去这样的话跟基础表区分起来就方便多了。那么这里咱们就建一个report模式CREATESCHEMAIFNOTEXISTSreport;COMMENTONSCHEMAreportISReport views for kb_shop sample database;这么搞完之后后续你一看到report.v_order_list这种名字其实心里就有数了。它绝对不是啥基础业务表往往仅仅只是个报表查询视图而已。你看截图里面sales还有inventory这些基础表它已经在了。接着咱们又建了个report模式也就是说咱们这篇文章并没有把前面弄好的业务分层给搞乱。sales还是管客户和订单inventory还是管商品和库存那么report里面就只放报表视图。这个边界划分清楚的话对后面做授权其实也是挺有帮助的。为啥呢因为报表用户你优先把report模式的查询权限给他就行了没必要把所有的基础表都直接暴露出去。二、创建订单列表视图说到订单列表通常来说咱们都需要看到些啥呢订单号肯定得有接着客户名称、订单状态、金额还有创建时间这些也是少不了的。CREATEORREPLACEVIEWreport.v_order_listASSELECTo.order_id,o.order_no,c.customer_id,c.customer_name,c.customer_level,o.order_status,CASEo.order_statusWHENcreatedTHEN已创建WHENpaidTHEN已支付WHENcancelledTHEN已取消ELSE未知ENDASorder_status_name,o.total_amount,o.created_at,o.paid_atFROMsales.customer_order oJOINsales.customer cONo.customer_idc.customer_id;这里咱们用的是CREATE OR REPLACE VIEW用这玩意儿有啥好处呢其实好处就是后面你要是想改视图的定义就不用先去手工删它了很省事。另外你看字段里面那个CASE它其实就是把英文的状态给转成了中文的意思这样的话看起来报表就轻松多了不用再去猜字母啥意思。接着咱们来查一下这个视图SELECTorder_no,customer_name,order_status_name,total_amount,created_atFROMreport.v_order_listORDERBYcreated_atDESC;你看这张结果的截图重点在哪呢其实就是这些字段已经很接近咱们报表要展示的口径了。你看里面既有订单号、客户名称、金额和时间同时还有中文的状态名称。也就是说后面你要是导出订单报表或者说是给报表用户做授权的话直接拿report.v_order_list去用就行了。再也不需要每次都去重新解释那个订单状态的编码是啥意思了。三、创建订单明细视图订单明细这东西它往往需要把订单表、客户表、商品表还有明细表给关联起来。CREATEORREPLACEVIEWreport.v_order_detailASSELECTo.order_no,c.customer_name,p.product_code,p.product_name,p.category,i.quantity,i.unit_price,i.line_amount,o.order_status,o.created_atFROMsales.customer_order oJOINsales.customer cONo.customer_idc.customer_idJOINsales.order_item iONo.order_idi.order_idJOINinventory.product pONi.product_idp.product_id;咱们来查一下某个订单的明细看看SELECTorder_no,product_code,product_name,quantity,unit_price,line_amountFROMreport.v_order_detailWHEREorder_noSO202605270001ORDERBYproduct_code;你看这条查询你根本不需要再去手写那4个表的 JOIN 了。也就是说只要搞懂视图里面有啥字段你就能把报表明细给查出来。其实写文章也是一样的道理这一节主要就是想体现出一种从基础 SQL 到业务接口的进阶过程。咱们前面第六篇的时候多表 JOIN 已经写过很多了。所以这篇文章重点就不再放在 JOIN 语法本身上了。那重点是啥呢其实就是怎么把那些验证过没问题的 JOIN 给固定下来。这样大家就能明白视图这东西它真不是啥新语法的堆叠往往仅仅只是对前面查询成果的复用而已。四、创建客户消费汇总视图客户消费汇总这东西其实在日常运营报表里面是非常常见的。CREATEORREPLACEVIEWreport.v_customer_amountASSELECTc.customer_id,c.customer_name,c.customer_level,COUNT(o.order_id)ASpaid_order_count,COALESCE(SUM(o.total_amount),0)ASpaid_amountFROMsales.customer cLEFTJOINsales.customer_order oONc.customer_ido.customer_idANDo.order_statuspaidGROUPBYc.customer_id,c.customer_name,c.customer_level;那么你想查消费排行的话像这样写就行SELECTcustomer_name,customer_level,paid_order_count,paid_amountFROMreport.v_customer_amountORDERBYpaid_amountDESC;这里为啥要用LEFT JOIN呢原因在于有些客户可能没有已经支付的订单我们想让这些客户也能在报表里面出现。那么COALESCE这个函数的话其实就是把空的金额给你转成 0。为啥要转呢因为报表里面要是出现空值看着很不友好。这个客户消费汇总视图其实还能体现出报表口径是怎么设计的。你看啊它仅仅只统计paid状态的订单但是所有的客户它都保留了。也就是说你既能够看到已经花了钱的客户也能看到一毛钱还没花的客户。那么如果说业务这边仅仅只关心已经消费过的客户的情况你在查视图的时候直接加上WHERE paid_amount 0就可以了。不用去改底层那个视图的定义。五、创建商品销售排行视图CREATEORREPLACEVIEWreport.v_product_sales_rankASSELECTp.product_id,p.product_code,p.product_name,p.category,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_id,p.product_code,p.product_name,p.category;接着你要查排行的话就这样搞SELECTproduct_code,product_name,category,sold_qty,sold_amountFROMreport.v_product_sales_rankORDERBYsold_qtyDESC,sold_amountDESC;这条视图的话它其实就把“只统计已支付订单”这个规则给固定下来了。这是一个好习惯。那为啥要这样搞呢因为可以避免你每次去写销量排行的时候往往容易忘记去过滤那个订单状态。从截图的结果来看的话商品销量排行已经是按照商品的维度把销售数量和金额都给你聚合好了。这个结果后续还能接着用。比如在第十一篇里面讲窗口函数的时候可以拿它来做排名分析。还有第十三篇把它导出成 CSV 也是可以的。那么视图的价值其实就在这里了前面咱们创建的那些数据库对象后面章节会一直拿来复用咱们这实战的逻辑也就是这么一步步串起来的。六、查看视图定义在ksql中查看视图\dv report.*查看某个视图结构\dreport.v_order_list也可以通过系统视图查询SELECTtable_schema,table_nameFROMinformation_schema.viewsWHEREtable_schemareportORDERBYtable_name;七、视图和表的区别对比项表普通视图是否保存数据保存不直接保存是否可直接插入通常可以取决于视图定义主要用途存储业务数据封装查询逻辑维护重点字段、约束、索引查询定义、权限、口径普通视图的话其实它更像是一个 SQL 接口。也就是说它能帮你把查询搞得简单点但是它并不能天然地就去提升性能。如果视图背后的 SQL 本身就挺复杂的情况你去查询这个视图的时候底层还是得去执行对应的逻辑的。那么视图解决的到底是啥问题呢其实往往仅仅只是复用还有口径的问题。性能这块的话你还是得结合索引和执行计划去分析的。八、常见问题排查问题 1创建视图时报表不存在遇到创建视图的时候报表不存在的情况那么先去确认一下底层表到底有没有。你可以像这样去查一下\dt sales.*\dt inventory.*也就是说如果前面几篇你没有执行那个建表的脚本的话那就得先去把基础表给补齐了才行。问题 2视图查询结果为空视图查出来结果是空的这不一定就是视图写错了。那为什么会这样呢原因可能是底层表里面本来就没数据或者是说过滤条件没满足的情况。你可以去检查一下订单数据像这样SELECTorder_no,order_status,total_amountFROMsales.customer_orderORDERBYorder_id;问题 3修改底层表字段后视图失效如果说你把底层表的字段给改了那么视图往往就会失效了。比如说你删了字段或者改了字段名视图依赖的字段找不到了那肯定就没法正常查询了呀。遇到这种情况的话处理方式就是要重新去调整一下视图的定义。你可以回到本文的第二节去按照现在的表结构重新去跑一遍完整的那个CREATE OR REPLACE VIEW report.v_order_list AS ...语句就行了。九、本文小结这篇文章接着第八篇的那个查询优化主题咱们接着聊把复杂的查询进一步封装成了视图。通过report模式里面的好几个视图咱们其实就把订单列表、订单明细、客户消费汇总还有商品销售排行这些常见的报表查询给固定下来了。这篇文章咱们掌握了这些个东西CREATE SCHEMA report CREATE OR REPLACE VIEW CASE 状态翻译 LEFT JOIN 汇总 GROUP BY 聚合 \dv 查看视图 information_schema.views 查询视图第八篇咱们关注的是 SQL 它是怎么去执行的那么到了第九篇咱们关注的就是复杂的 SQL 该怎么去复用了。接着下一篇的话咱们会继续去扩展 SQL 的能力。到时候会围绕字符串、日期、数值还有空值处理和条件表达式这些来讲讲业务 SQL 里面常用的函数和表达式。