DuckDB 聚合函数用法举例

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、应用等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

背景

https://duckdb.org/docs/sql/aggregates

DuckDB和PostgreSQL聚合用法一样, 都支持: 聚合内容前置排序、表达式聚合、聚合内容前置过滤器

select agg ([distinct] express [order by xx]) [filter (where ...)]

同时支持很多聚合函数, 本文列举一些在统计中可能比较有用但是比较冷门的:

1、线性相关性的一些函数, 常用于预测、计算相关性. regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy等

《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测 - 股票预测例子》

《在PostgreSQL中用线性回归分析linear regression做预测 - 例子2, 预测未来数日某股收盘价》

《PostgreSQL 线性回归 - 股价预测 1》

《PostgreSQL 多元线性回归 - 2 股票预测》

2、分位数计算

quantile_cont(x,pos)  
quantile_disc(x,pos)

3、高频词

mode(x)

4、柱状图统计

histogram(arg)	: Returns a LIST of STRUCTs with the fields bucket and count.	  

5、近似计算

近似count distinct (HLL算法): approx_count_distinct(A)  
近似分位数(T-Digest算法): approx_quantile(A,0.5)  
近似分位数(采样法): reservoir_quantile(A,0.5,1024)

更多聚合函数详见:
https://duckdb.org/docs/sql/aggregates

例子

create table test (c1 int, c2 int);  
insert into test select generate_series, random()*100 from generate_series(1,1000000);  
insert into test select random()*100, -1 from generate_series(1,1000000);  
      D select histogram(c1), histogram(c2) from test;   
┌────────────────────────────────────────────────────────────────────────────────────┬────────────────────────────────────────────────────────────────────────────────────┐  
│                                   histogram(c1)                                    │                                   histogram(c2)                                    │  
├────────────────────────────────────────────────────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────────┤  
│ {0=5065, 1=9966, 2=9916, 3=10087, 4=9890, 5=9979, 6=10207, 7=10067, 8=10002, 9=... │ {-1=1000000, 0=4958, 1=10069, 2=10127, 3=10184, 4=9959, 5=10003, 6=9937, 7=1001... │  
└────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────┘  
Run Time: real 1.548 user 1.699476 sys 0.361806  
D select count(distinct c1), count(distinct c2), approx_count_distinct(c1), approx_count_distinct(c2) from test;  
┌────────────────────┬────────────────────┬───────────────────────────┬───────────────────────────┐  
│ count(DISTINCT c1) │ count(DISTINCT c2) │ approx_count_distinct(c1) │ approx_count_distinct(c2) │  
├────────────────────┼────────────────────┼───────────────────────────┼───────────────────────────┤  
│ 1000001            │ 102                │ 1007982                   │ 99                        │  
└────────────────────┴────────────────────┴───────────────────────────┴───────────────────────────┘  
Run Time: real 0.250 user 0.188888 sys 0.071540  
    D select mode(c2) from test;   
┌──────────┐  
│ mode(c2) │  
├──────────┤  
│ -1       │  
└──────────┘  
Run Time: real 0.009 user 0.044197 sys 0.000779  
  D select reservoir_quantile(c1,0.5,10000000) from test;   
┌───────────────────────────────────────┐  
│ reservoir_quantile(c1, 0.5, 10000000) │  
├───────────────────────────────────────┤  
│ 100                                   │  
└───────────────────────────────────────┘  
Run Time: real 0.037 user 0.060452 sys 0.019617  
D select reservoir_quantile(c1,0.5,1001000) from test;   
┌──────────────────────────────────────┐  
│ reservoir_quantile(c1, 0.5, 1001000) │  
├──────────────────────────────────────┤  
│ 8980                                 │  
└──────────────────────────────────────┘  
Run Time: real 0.067 user 0.074727 sys 0.024510  
D select reservoir_quantile(c1,0.5,1009000) from test;   
┌──────────────────────────────────────┐  
│ reservoir_quantile(c1, 0.5, 1009000) │  
├──────────────────────────────────────┤  
│ 258740                               │  
└──────────────────────────────────────┘  
  D select approx_quantile(c1,0.5) from test;   
┌──────────────────────────┐  
│ approx_quantile(c1, 0.5) │  
├──────────────────────────┤  
│ 17177                    │  
└──────────────────────────┘  
Run Time: real 0.024 user 0.162472 sys 0.000718  
    D select quantile_disc(c1,0.5) from test;   
┌────────────────────────┐  
│ quantile_disc(c1, 0.5) │  
├────────────────────────┤  
│ 100                    │  
└────────────────────────┘  
Run Time: real 0.026 user 0.052480 sys 0.017376  
D select quantile_cont(c1,0.5) from test;   
┌────────────────────────┐  
│ quantile_cont(c1, 0.5) │  
├────────────────────────┤  
│ 100.0                  │  
└────────────────────────┘  
Run Time: real 0.038 user 0.059058 sys 0.027527

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.