Postgresql基础实践教程（九）

⭐️⭐️⭐️⭐️⭐️完整数据详见 练习数据·免费⭐️⭐️⭐️⭐️⭐️七十二、WITH查询公用表表达式CTE1. SELECT 中的 WITH2. 递归查询3. 公用表表达式的物化4. WITH中的数据修改语句WITH提供了一种在主查询中写辅助语句的方法。这些语句通常被称为公用表表达式CTE可以理解为定义了一些临时表它们只为当前查询而存在。WITH子句中的每个辅助语句可以是SELECT、INSERT、UPDATE、DELETE或MERGEWITH子句本身会附加到一个主语句上主语句也可以是SELECT、INSERT、UPDATE、DELETE或MERGE。1. SELECT中的WITHSELECT在WITH中的主要作用是把复杂的查询拆分成更简单的部分。举个例子WITHregional_salesAS(SELECTregion,SUM(amount)AStotal_salesFROMordersGROUPBYregion),top_regionsAS(SELECTregionFROMregional_salesWHEREtotal_sales(SELECTSUM(total_sales)/10FROMregional_sales))SELECTregion,product,SUM(quantity)ASproduct_units,SUM(amount)ASproduct_salesFROMordersWHEREregionIN(SELECTregionFROMtop_regions)GROUPBYregion,product;这个查询只显示销售冠军地区的每种产品销售总额。WITH子句定义了两个辅助语句regional_sales和top_regions其中regional_sales的输出被top_regions使用而top_regions的输出则被主SELECT查询使用。这个例子不用WITH也能写但就需要两层嵌套的子SELECT了。用WITH的方式更容易理解。2. 递归查询可选的RECURSIVE修饰符让WITH从一个简单的语法糖变成了标准SQL中无法实现的功能。有了RECURSIVEWITH查询可以引用自身的输出。一个很简单的例子是计算1到100的整数和WITHRECURSIVE t(n)AS(VALUES(1)UNIONALLSELECTn1FROMtWHEREn100)SELECTsum(n)FROMt;递归WITH查询的一般形式总是这样一个非递归项然后是UNION或UNION ALL然后是一个递归项只有递归项可以包含对自身输出的引用。这种查询的执行过程如下递归查询求值步骤先求非递归项的值。对于UNION但不是UNION ALL会去重。把剩下的行加入递归查询的结果中同时放到一个临时的工作表里。只要工作表不为空就重复以下步骤求递归项的值用工作表的当前内容替换递归自引用。对于UNION但不是UNION ALL会去重并去掉之前结果中已经出现过的行。把剩下的行加入递归查询的结果中同时放到一个临时的中间表里。用中间表的内容替换工作表的内容然后清空中间表。注意虽然RECURSI#VE允许递归地指定查询但内部实现其实是迭代执行的。在上面的例子中工作表每一步只有一行依次取1到100的值。第100步时因为WHERE子句的条件不满足没有输出查询就终止了。递归查询通常用来处理层次结构或树状数据。举一个有用的例子假设有一张表记录了直接包含关系用这个查询可以找出某个产品的所有直接和间接部件WITHRECURSIVE included_parts(sub_part,part,quantity)AS(SELECTsub_part,part,quantityFROMpartsWHEREpartour_productUNIONALLSELECTp.sub_part,p.part,p.quantity*pr.quantityFROMincluded_parts pr,parts pWHEREp.partpr.sub_part)SELECTsub_part,SUM(quantity)astotal_quantityFROMincluded_partsGROUPBYsub_part2.1. 搜索顺序用递归查询来遍历树的时候可能需要按深度优先或广度优先的顺序排列结果。这可以通过在查询中计算一个排序列来实现最后用这个列对结果排序。需要注意的是这并不能真正控制查询求值时访问行的顺序——SQL中这一步的实现取决于具体系统。这里说的方法只是在最后给结果排个序而已。要创建深度优先顺序需要为每一行计算一个数组用来记录到目前为止访问过的行。比如这个用link字段搜索tree表的查询WITHRECURSIVE search_tree(id,link,data)AS(SELECTt.id,t.link,t.dataFROMtree tUNIONALLSELECTt.id,t.link,t.dataFROMtree t,search_tree stWHEREt.idst.link)SELECT*FROMsearch_tree;加上深度优先的排序信息可以这样写WITHRECURSIVE search_tree(id,link,data,path)AS(SELECTt.id,t.link,t.data,ARRAY[t.id]FROMtree tUNIONALLSELECTt.id,t.link,t.data,path||t.idFROMtree t,search_tree stWHEREt.idst.link)SELECT*FROMsearch_treeORDERBYpath;如果需要用多个字段才能唯一确定一行就用行的数组。比如要追踪f1和f2两个字段WITHRECURSIVE search_tree(id,link,data,path)AS(SELECTt.id,t.link,t.data,ARRAY[ROW(t.f1,t.f2)]FROMtree tUNIONALLSELECTt.id,t.link,t.data,path||ROW(t.f1,t.f2)FROMtree t,search_tree stWHEREt.idst.link)SELECT*FROMsearch_treeORDERBYpath;提示如果只需要追踪一个字段可以省略ROW()语法直接用简单数组就行效率更高。要创建广度优先顺序可以加一个列来记录搜索深度比如WITHRECURSIVE search_tree(id,link,data,depth)AS(SELECTt.id,t.link,t.data,0FROMtree tUNIONALLSELECTt.id,t.link,t.data,depth1FROMtree t,search_tree stWHEREt.idst.link)SELECT*FROMsearch_treeORDERBYdepth;如果想要稳定的排序可以把数据列作为第二排序列。提示递归查询求值算法实际上是按广度优先顺序产生输出的。但这只是个实现细节最好不要依赖它。每层内行的顺序是不确定的所以有时候还是需要显式排序。PostgreSQL内置了计算深度优先或广度优先排序列的语法比如WITHRECURSIVE search_tree(id,link,data)AS(SELECTt.id,t.link,t.dataFROMtree tUNIONALLSELECTt.id,t.link,t.dataFROMtree t,search_tree stWHEREt.idst.link)SEARCH DEPTHFIRSTBYidSETordercolSELECT*FROMsearch_treeORDERBYordercol;WITHRECURSIVE search_tree(id,link,data)AS(SELECTt.id,t.link,t.dataFROMtree tUNIONALLSELECTt.id,t.link,t.dataFROMtree t,search_tree stWHEREt.idst.link)SEARCH BREADTHFIRSTBYidSETordercolSELECT*FROMsearch_treeORDERBYordercol;这些语法内部会被展开成类似上面手写的样子。SEARCH子句指定是要深度优先还是广度优先搜索要追踪哪些列用于排序以及一个列名来存放排序结果数据。这个列会隐式地加到CTE的输出行中。2.2. 循环检测写递归查询的时候要确保递归部分最终会没有元组返回否则查询会无限循环。有时候用UNION替代UNION ALL就能解决这个问题——它会去掉和之前输出重复的行。但有时候循环并不意味着输出行完全相同可能只需要检查一两个字段就知道有没有回到同一个点。处理这种情况的标准方法是维护一个数组来记录已经访问过的值。再来看用link字段搜索graph表的查询WITHRECURSIVE search_graph(id,link,data,depth)AS(SELECTg.id,g.link,g.data,0FROMgraph gUNIONALLSELECTg.id,g.link,g.data,sg.depth1FROMgraph g,search_graph sgWHEREg.idsg.link)SELECT*FROMsearch_graph;如果link关系里有环这个查询就会无限循环。因为需要输出depth所以改成UNION ALL也没用。需要的是识别出在沿着某条链路走下去的时候是不是又回到了同一行。加两个列is_cycle和pathWITHRECURSIVE search_graph(id,link,data,depth,is_cycle,path)AS(SELECTg.id,g.link,g.data,0,FALSE,ARRAY[g.id]FROMgraph gUNIONALLSELECTg.id,g.link,g.data,sg.depth1,g.idANY(path),path||g.idFROMgraph g,search_graph sgWHEREg.idsg.linkANDNOTis_cycle)SELECT*FROMsearch_graph;除了防止循环这个数组本身也很有用——它记录了到达任意特定行所经过的路径。如果需要用多个字段才能识别循环要用行的数组。比如要比较f1和f2两个字段WITHRECURSIVE search_graph(id,link,data,depth,is_cycle,path)AS(SELECTg.id,g.link,g.data,0,FALSE,ARRAY[ROW(g.f1,g.f2)]FROMgraph gUNIONALLSELECTg.id,g.link,g.data,sg.depth1,ROW(g.f1,g.f2)ANY(path),path||ROW(g.f1,g.f2)FROMgraph g,search_graph sgWHEREg.idsg.linkANDNOTis_cycle)SELECT*FROMsearch_graph;提示如果只需要用一个字段来识别循环可以省略ROW()语法直接用简单数组就行效率更高。PostgreSQL内置了简化循环检测的语法。上面那个查询也可以这样写WITHRECURSIVE search_graph(id,link,data,depth)AS(SELECTg.id,g.link,g.data,1FROMgraph gUNIONALLSELECTg.id,g.link,g.data,sg.depth1FROMgraph g,search_graph sgWHEREg.idsg.link)CYCLEidSETis_cycleUSINGpathSELECT*FROMsearch_graph;内部会被转换成上面那种形式。CYCLE子句先指定要追踪哪些列用于循环检测然后是一个列名用来表示是否检测到循环最后是另一个列名用来追踪路径。循环和路径列会隐式地加到CTE的输出行中。提示循环路径列的计算方式和上一节讲的深度优先排序列是一样的。一个查询可以同时有SEARCH和CYCLE子句但深度优先搜索规范和循环检测规范会做重复的计算所以直接用CYCLE子句然后按path排序列排序会更高效。如果需要广度优先排序同时指定SEARCH和CYCLE才会有用。不太确定查询会不会卡死的话有个很有用的技巧在父查询里加个LIMIT。比如下面这个查询没有LIMIT的话就会永远循环下去WITHRECURSIVE t(n)AS(SELECT1UNIONALLSELECTn1FROMt)SELECTnFROMtLIMIT100;这招管用是因为PostgreSQL只会求值父查询实际取到的那么多行。不过生产环境中不推荐这么干因为其他系统可能有不同的行为。而且如果外层查询要对递归结果排序或join其他表这招通常就不灵了——因为那样的话外层查询往往会尝试取WITH查询的全部输出。3. 公用表表达式的物化WITH查询有一个很有用的特性就算主查询多次引用它们也通常只在主查询执行过程中求值一次。这样就能把需要在多处使用的昂贵计算放到WITH查询里避免重复工作。另一个用途是防止有副作用的函数被多次求值。但另一方面优化器无法把主查询的限制条件下推到被多次引用的WITH查询中因为那可能会影响到所有使用WITH查询输出的地方即使本应该只影响其中一部分。被多次引用的WITH查询会按写的样子求值不会预先剔除主查询后来可能会丢弃的行。但就像前面说的如果引用只要求取少量行可能会提前停止求值。不过如果WITH查询是非递归的且没有副作用即是一个不包含易变函数的SELECT那它可以被合并到父查询中让两个查询层级一起优化。默认情况下如果父查询只引用WITH查询一次就会合并如果引用多次就不会合并。可以用MATERIALIZED强制WITH查询单独计算或者用NOT MATERIALIZED强制合并到父查询。后者有重复计算的风险但如果每次使用WITH查询只需要其完整输出的一小部分还是划算的。举个例子说明这些规则WITHwAS(SELECT*FROMbig_table)SELECT*FROMwWHEREkey123;这个WITH查询会被合并执行计划和下面这个一样SELECT*FROMbig_tableWHEREkey123;特别是如果key上有索引可能会用来只取key123的那些行。但是WITHwAS(SELECT*FROMbig_table)SELECT*FROMwASw1JOINwASw2ONw1.keyw2.refWHEREw2.key123;这个查询里WITH查询会被物化生成big_table的临时副本然后和自己join用不上索引。如果改成这样WITHwASNOTMATERIALIZED(SELECT*FROMbig_table)SELECT*FROMwASw1JOINwASw2ONw1.keyw2.refWHEREw2.key123;就能让父查询的限制条件直接应用到big_table的扫描上。NOT MATERIALIZED不合适的情况比如WITHwAS(SELECTkey,very_expensive_function(val)asfFROMsome_table)SELECT*FROMwASw1JOINwASw2ONw1.fw2.f;这里WITH查询被物化可以确保very_expensive_function每个表行只求值一次而不是两次。上面的例子都是WITH和SELECT一起用但WITH同样可以附加到INSERT、UPDATE、DELETE或MERGE上。效果都是提供临时的表可以在主命令中引用。4. WITH中的数据修改语句可以在WITH中使用数据修改语句INSERT、UPDATE、DELETE或MERGE。这样可以在同一条查询里执行多个不同的操作。举个例子WITHmoved_rowsAS(DELETEFROMproductsWHEREdate2010-10-01ANDdate2010-11-01RETURNING*)INSERTINTOproducts_logSELECT*FROMmoved_rows;这个查询把products表的记录搬到了products_log表。WITH中的DELETE从products删除符合条件的记录通过RETURNING子句返回被删除的内容然后主查询读取这些输出并插入products_log。上面例子有个细节WITH子句是附加在INSERT上的不是附加在INSERT里面的子SELECT上的。这是因为数据修改语句只能出现在附加在顶层语句的WITH子句中。不过普通的WITH可见性规则还是适用的所以子SELECT里可以引用WITH语句的输出。WITH中的数据修改语句通常都有RETURNING子句见6.4节就像上面例子那样。真正形成临时表供查询其他部分引用的是RETURNING子句的输出不是数据修改语句的目标表。如果WITH中的数据修改语句没有RETURNING子句那就不会形成临时表查询的其他部分也引用不了。不过该语句还是会执行的。举一个没什么用的例子WITHtAS(DELETEFROMfoo)DELETEFROMbar;这个例子会删除foo表和bar表的全部记录。返回给客户端的影响行数只包含从bar删除的行数。数据修改语句不支持递归自引用。有些情况下可以绕开这个限制比如引用递归WITH的输出WITHRECURSIVE included_parts(sub_part,part)AS(SELECTsub_part,partFROMpartsWHEREpartour_productUNIONALLSELECTp.sub_part,p.partFROMincluded_parts pr,parts pWHEREp.partpr.sub_part)DELETEFROMpartsWHEREpartIN(SELECTpartFROMincluded_parts);这个查询会删除产品的所有直接和间接部件。WITH中的数据修改语句会执行一次而且总是执行完不管主查询是否读取了全部甚至任何输出。注意这和WITH中SELECT的规则不同正如上一节说的SELECT的求值程度取决于主查询对它的需求。WITH中的子语句彼此之间、以及和主查询之间是并发执行的。因此使用WITH中的数据修改语句时实际执行更新的顺序是不可预测的。所有语句都用同一个快照执行见第13章所以彼此看不到对方对目标表的影响。这缓解了更新顺序不可预测带来的问题也意味着不同WITH子语句和主查询之间只能通过RETURNING数据来传递变化。举个例子WITHtAS(UPDATEproductsSETpriceprice*1.05RETURNING*)SELECT*FROMproducts;这个外层SELECT返回的是UPDATE之前的原价而WITHtAS(UPDATEproductsSETpriceprice*1.05RETURNING*)SELECT*FROMt;这个外层SELECT返回的是更新后的数据。