count(distinct ) over(partition by order by)替换成size(collect_set() over(partition by order by))

这个函数的大致意思是：在分组内求去重后的数量

为什么不用count(distinct )，不支持原因

例子：

数据准备：

SELECT '1' as id ,'201808' as m,'a' as k
union all
SELECT '2' as id ,'201808' as m,'a' as k
union all
SELECT '1' as id ,'201809' as m,'a' as k
union all
SELECT '1' as id ,'201808' as m,'b' as k
union all
SELECT '2' as id ,'201809' as m,'b' as k

id代表人编号,m代表月份，k代表其他key键。

id	m	k
1	201808	a
2	201808	a
1	201809	a
1	201808	b
2	201809	b

需求：本月累计人数（即9月份的客户要包含9月以前的客户数）

预计结果

k          m            n

a        201808     2

a        201809     2

b        201808    1

b        201809    2

第一步：

SELECT
k,
m,
size(collect_set(id) OVER(PARTITION BY k ORDER BY m asc rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )) as n
from
(
SELECT '1' as id ,'201808' as m,'a' as k
union all
SELECT '2' as id ,'201808' as m,'a' as k
union all
SELECT '1' as id ,'201809' as m,'a' as k
union all
SELECT '1' as id ,'201808' as m,'b' as k
union all
SELECT '2' as id ,'201809' as m,'b' as k
)t
order by k,m

 

k	m	n
a	201808	1
a	201808	2
a	201809	2
b	201808	1
b	201809	2

第二步：取出分组内最后一条即可

SELECT
k,
m,
n,
row_number() over(PARTITION BY k,m ORDER BY n desc)rk
from
(
SELECT
k,
m,
size(collect_set(id) OVER(PARTITION BY k ORDER BY m asc rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )) as n
from
(
SELECT '1' as id ,'201808' as m,'a' as k
union all
SELECT '2' as id ,'201808' as m,'a' as k
union all
SELECT '1' as id ,'201809' as m,'a' as k
union all
SELECT '1' as id ,'201808' as m,'b' as k
union all
SELECT '2' as id ,'201809' as m,'b' as k
)t
order by k,m
)having rk = 1
 

结果为：

k	m	n	rk
a	201808	2	1
a	201809	2	1
b	201808	1	1
b	201809	2	1

 

若需要了解rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，请参考https://mp.csdn.net/postedit/82909903