PostgreSQLSELECT的高階用法(CTE,LATERAL,ORDINALITY,WINDOW,SKIPLOCKED,DISTINCT,GROUPINGSETS,…)

德哥發表於2018-02-28

標籤

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 rigger
ecursive 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


相關文章