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>