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，输出函数返回记录的每行行号

<a href="https://github.com/digoal/blog/blob/master/201307/20130730_01.md">《PostgreSQL 9.4 Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)》</a>

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

<a href="https://github.com/digoal/blog/blob/master/201210/20121008_01.md">《PostgreSQL 9.3 add LATERAL support》</a>

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

<a href="https://github.com/digoal/blog/blob/master/201703/20170330_06.md">《PostgreSQL 10.0 preview 性能增强 - hashed aggregation with grouping sets(多维分析)更快,更省内存》</a>

<a href="https://github.com/digoal/blog/blob/master/201505/20150526_02.md">《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》</a>

<a href="https://github.com/digoal/blog/blob/master/201212/20121218_03.md">《Greenplum 最佳实践 - 多维分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》</a>

4、window, 窗口查询

<a href="https://github.com/digoal/blog/blob/master/201802/20180224_01.md">《PostgreSQL 11 preview - SQL:2011 window frame clause全面支持 及 窗口、帧用法和业务场景介绍》</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170722_02.md">《车联网案例，轨迹清洗 - 阿里云RDS PostgreSQL最佳实践 - 窗口函数》</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170705_01.md">《PostgreSQL 海量时序数据(任意滑动窗口实时统计分析) - 传感器、人群、物体等对象跟踪》</a>

<a href="https://github.com/digoal/blog/blob/master/201705/20170504_04.md">《PostgreSQL 聚合表达式 FILTER , order , within group, over window 用法》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161203_01.md">《用PostgreSQL描绘人生的高潮、尿点、低谷 - 窗口/帧 or 斜率/导数/曲率/微积分?》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161128_01.md">《时序数据合并场景加速分析和实现 - 复合索引，窗口分组查询加速，变态递归加速》</a>

5、CTE，复杂SQL逻辑

<a href="https://github.com/digoal/blog/blob/master/201712/20171204_05.md">《PostgreSQL 用 CTE语法 + 继承 实现拆分大表》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171107_28.md">《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》</a>

<a href="https://github.com/digoal/blog/blob/master/201705/20170519_01.md">《PostgrSQL 递归SQL的几个应用 - 极客与正常人的思维》</a>

<a href="https://github.com/digoal/blog/blob/master/201703/20170324_01.md">《PostgreSQL 递归查询CASE - 树型路径分组输出》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161201_01.md">《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161128_02.md">《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》</a>

<a href="https://github.com/digoal/blog/blob/master/201607/20160725_01.md">《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》</a>

<a href="https://github.com/digoal/blog/blob/master/201607/20160723_01.md">《PostgreSQL 递归死循环案例及解法》</a>

<a href="https://github.com/digoal/blog/blob/master/201604/20160405_01.md">《PostgreSQL 递归查询一例 - 资金累加链》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151221_02.md">《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》</a>

<a href="https://github.com/digoal/blog/blob/master/201210/20121009_01.md">《递归优化CASE - group by &amp; distinct tuning case : use WITH RECURSIVE and min() function》</a>

<a href="https://github.com/digoal/blog/blob/master/201209/20120914_01.md">《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》</a>

<a href="https://github.com/digoal/blog/blob/master/201105/20110527_01.md">《PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type》</a>

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

<a href="https://github.com/digoal/blog/blob/master/201610/20161018_01.md">《PostgreSQL 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率》</a>

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

<a href="https://github.com/digoal/blog/blob/master/201711/20171123_02.md">《PostgreSQL 流式统计 - insert on conflict 实现 流式 UV(distinct), min, max, avg, sum, count ...》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171123_01.md">《HybridDB PostgreSQL "Sort、Group、distinct 聚合、JOIN" 不惧怕数据倾斜的黑科技和原理 - 多阶段聚合》</a>

<a href="https://github.com/digoal/blog/blob/master/201711/20171122_01.md">《PostgreSQL distinct 与 Greenplum distinct 的实现与优化》</a>

<a href="https://github.com/digoal/blog/blob/master/201710/20171024_01.md">《[转] 快速计算Distinct Count》</a>

<a href="https://github.com/digoal/blog/blob/master/201710/20171017_02.md">《PostgreSQL DISTINCT 和 DISTINCT ON 语法的使用》</a>

<a href="https://www.postgresql.org/docs/devel/static/sql-select.html">https://www.postgresql.org/docs/devel/static/sql-select.html</a>

<a href="https://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-LATERAL">https://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-LATERAL</a>