PostgreSQL 窗口函数内部distinct的支持与用法 - count(distinct x) over (partition by ...)

作者

digoal

日期

2019-08-19

标签

PostgreSQL , window , distinct

背景

窗口函数内支持distinct，暂时语法上不支持，需要使用子查询支持，或者窗口函数本身支持排重。

《PostgreSQL 11 preview - SQL:2011 window frame clause全面支持 及 窗口、帧用法和业务场景介绍》

《PostgreSQL 窗口函数 - 帧、窗口的应用例子》

例子来自

https://www.cybertec-postgresql.com/en/timeseries-exclude-ties-current-row-and-group/

```
test=# CREATE TABLE t_demo AS
SELECT ordinality, day, date_part('week', day) AS week
FROM generate_series('2020-01-02', '2020-01-15', '1 day'::interval)
WITH ORDINALITY AS day;
SELECT 14

test=# SELECT * FROM t_demo;
ordinality | day | week
------------+------------------------+------
1 | 2020-01-02 00:00:00+01 | 1
2 | 2020-01-03 00:00:00+01 | 1
3 | 2020-01-04 00:00:00+01 | 1
4 | 2020-01-05 00:00:00+01 | 1
5 | 2020-01-06 00:00:00+01 | 2
6 | 2020-01-07 00:00:00+01 | 2
7 | 2020-01-08 00:00:00+01 | 2
8 | 2020-01-09 00:00:00+01 | 2
9 | 2020-01-10 00:00:00+01 | 2
10 | 2020-01-11 00:00:00+01 | 2
11 | 2020-01-12 00:00:00+01 | 2
12 | 2020-01-13 00:00:00+01 | 3
13 | 2020-01-14 00:00:00+01 | 3
14 | 2020-01-15 00:00:00+01 | 3
(14 rows)

test=# SELECT *,
array_agg(DISTINCT week) OVER (ORDER BY day ROWS
BETWEEN 2 PRECEDING AND 2 FOLLOWING)
FROM t_demo;
ERROR: DISTINCT is not implemented for window functions
LINE 2: array_agg(DISTINCT week) OVER (ORDER BY day ROWS

test=# SELECT , (SELECT array_agg(DISTINCT unnest) FROM unnest(x)) AS b
FROM
(
SELECT ,
array_agg(week) OVER (ORDER BY day ROWS
BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS x
FROM t_demo
) AS a;
ordinality | day | week | x | b
------------+------------------------+------+-------------+-------
1 | 2020-01-02 00:00:00+01 | 1 | {1,1,1} | {1}
2 | 2020-01-03 00:00:00+01 | 1 | {1,1,1,1} | {1}
3 | 2020-01-04 00:00:00+01 | 1 | {1,1,1,1,2} | {1,2}
4 | 2020-01-05 00:00:00+01 | 1 | {1,1,1,2,2} | {1,2}
5 | 2020-01-06 00:00:00+01 | 2 | {1,1,2,2,2} | {1,2}
6 | 2020-01-07 00:00:00+01 | 2 | {1,2,2,2,2} | {1,2}
7 | 2020-01-08 00:00:00+01 | 2 | {2,2,2,2,2} | {2}
8 | 2020-01-09 00:00:00+01 | 2 | {2,2,2,2,2} | {2}
9 | 2020-01-10 00:00:00+01 | 2 | {2,2,2,2,2} | {2}
10 | 2020-01-11 00:00:00+01 | 2 | {2,2,2,2,3} | {2,3}
11 | 2020-01-12 00:00:00+01 | 2 | {2,2,2,3,3} | {2,3}
12 | 2020-01-13 00:00:00+01 | 3 | {2,2,3,3,3} | {2,3}
13 | 2020-01-14 00:00:00+01 | 3 | {2,3,3,3} | {2,3}
14 | 2020-01-15 00:00:00+01 | 3 | {3,3,3} | {3}
(14 rows)
```

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.