作者
digoal
日期
2018-02-26
标签
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 无会话、有会话模式 - 客服平均响应速度（RT）实时计算实践》
《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 Oracle 兼容性之 - WITH 递归 ( connect by )》
《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》
《PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type》
《PostgreSQL 递归应用实践 - 非“传销”的高并发实时藤、树状佣金分配体系》
《PostgreSQL 图式搜索(graph search)实践 - 百亿级图谱，毫秒响应》
6、skip locked，跳过被锁的记录
《PostgreSQL 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率》
《PostgreSQL skip locked与CTE妙用 - 解决并发批量更新锁冲突带来的锁等待，提高处理吞吐》
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 的实现与优化》
《PostgreSQL DISTINCT 和 DISTINCT ON 语法的使用》
《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》
《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》
8、秒杀
《PostgreSQL 秒杀4种方法 - 增加 批量流式加减库存 方法》
9、批量SQL
《Greenplum insert的性能(单步\批量\copy) - 暨推荐使用gpfdist、阿里云oss外部表并行导入》
《PostgreSQL jdbc batch insert》
10、数据分析
《PostgreSQL count-min sketch top-n 概率计算插件 cms_topn (结合窗口实现同比、环比、滑窗分析等) - 流计算核心功能之一》
《PostgreSQL 时序数据案例 - 时间流逝, 自动压缩(时间粒度老化), 同比\环比》
11、流计算
《PostgreSQL 流计算插件 - pipelinedb 1.x 参数配置介绍》
《PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合》
12、基于rule , 异步消息，实现异常数据异步通知。
《PostgreSQL 流式处理应用实践 - 二手商品实时归类(异步消息notify/listen、阅后即焚)》
《PostgreSQL 异步消息实践 - Feed系统实时监测与响应(如 电商主动服务) - 分钟级到毫秒级的实现》
13、线性回归，预测数据。
《PostgreSQL KPI 预测例子 - 线性回归, 斜率, 截距, 相关性, regr_slope, regr_intercept, regr_r2》
《PostgreSQL 统计信息之 - 逻辑与物理存储的线性相关性》
《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测》
《在PostgreSQL中用线性回归分析linear regression做预测 - 例子2, 预测未来数日某股收盘价》
《PostgreSQL 多元线性回归 - 1 MADLib Installed in PostgreSQL 9.2》
14、化学分析。
《PostgreSQL 化学分析 - RDKit Cartridge 1 - 环境部署》
15、机器学习。
16、update delte limit
《在PostgreSQL中实现update | delete limit - CTID扫描实践 (高效阅后即焚)》
17、function prepared statement
《PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量-DB端prepare statement)》
参考
https://www.postgresql.org/docs/devel/static/sql-select.html
https://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-LATERAL
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
