窗口函数示例1-count：

有关开窗函数的基本语法参照 参考地址

1.COUNT

包括类似的SUM、AVG、MIN、MAX，都是用于实现分组内的统计

需求案例：一个目的地，用户可能通过三种路径到达，一天可以到达多次，统计出只通过A路径到达目标的人数、次数

image.png

用户	路径	目标
uid1	A	Target1
uid1	B	Target1
uid2	A	Target1
uid2	A	Target2
uid3	A	Target2
uid3	B	Target2
uid3	A	Target2

--建表
create table log (uid string,path string,target string);
insert into log values('uid1','A','Target1');
insert into log values('uid1','B','Target1');
insert into log values('uid2','A','Target1');
insert into log values('uid2','A','Target2');
insert into log values('uid3','A','Target2');
insert into log values('uid3','B','Target2');
insert into log values('uid3','A','Target2');


--统计单用户的目标到达次数
with push as (
select uid,path,target,count(*) as v1
from log
group by uid,path,target
),

--使用窗口函数统计单个目标到达的路径个数
stat as (
  select uid,path,target,v1,count(path) over(partition by uid,target) as v2
  from  push
) 

--筛选+汇总
select count(distinct uid),sum(v1) 
from  stat
where v2 = 1 and path= 'A'