PostgreSQL SELECT 的高级用法(CTE, LATERAL, ORDINALITY, WINDOW, SKIP LOCKED, DISTINCT, GROUPING SETS, ...)...

标签

PostgreSQL , CTE , LATERAL , ORDINALITY , WINDOW , SKIP LOCKED , DISTINCT , GROUPING SETS , distinct on

---

背景

1、WITH ORDINALITY，输出函数返回记录的每行行号

《PostgreSQL 9.4 Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)》

2、LATERAL，独立子查询内支持JOIN子查询外面的表

《PostgreSQL 9.3 add LATERAL support》

3、GROUPING SETS, CUBE and ROLLUP。多维聚合

《PostgreSQL 10.0 preview 性能增强 - hashed aggregation with grouping sets(多维分析)更快,更省内存》

《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》

《Greenplum 最佳实践 - 多维分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》

4、window, 窗口查询

《PostgreSQL 11 preview - SQL:2011 window frame clause全面支持 及 窗口、帧用法和业务场景介绍》

《车联网案例，轨迹清洗 - 阿里云RDS PostgreSQL最佳实践 - 窗口函数》

《PostgreSQL 海量时序数据(任意滑动窗口实时统计分析) - 传感器、人群、物体等对象跟踪》

《PostgreSQL 聚合表达式 FILTER , order , within group, over window 用法》

《用PostgreSQL描绘人生的高潮、尿点、低谷 - 窗口/帧 or 斜率/导数/曲率/微积分?》

《时序数据合并场景加速分析和实现 - 复合索引，窗口分组查询加速，变态递归加速》

5、CTE，复杂SQL逻辑

《PostgreSQL 用 CTE语法 + 继承 实现拆分大表》

《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》

《PostgrSQL 递归SQL的几个应用 - 极客与正常人的思维》

《PostgreSQL 递归查询CASE - 树型路径分组输出》

《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》

《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》

《时序数据合并场景加速分析和实现 - 复合索引，窗口分组查询加速，变态递归加速》

《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》

《PostgreSQL 递归死循环案例及解法》

《PostgreSQL 递归查询一例 - 资金累加链》

《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》

《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

《PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type》

6、skip locked，跳过被锁的记录

《PostgreSQL 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率》

7、distinct, 唯一，唯一数等。distinct on 某列(s)，随机提取一行。

《PostgreSQL 流式统计 - insert on conflict 实现 流式 UV(distinct), min, max, avg, sum, count ...》

《HybridDB PostgreSQL "Sort、Group、distinct 聚合、JOIN" 不惧怕数据倾斜的黑科技和原理 - 多阶段聚合》

《PostgreSQL distinct 与 Greenplum distinct 的实现与优化》

《[转] 快速计算Distinct Count》

《PostgreSQL DISTINCT 和 DISTINCT ON 语法的使用》

《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》

《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》

参考

https://www.postgresql.org/docs/devel/static/sql-select.html

https://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-LATERAL