有關(guān)開窗函數(shù)的基本語法參照 參考地址
1.COUNT
包括類似的SUM慷丽、AVG仿野、MIN、MAX虱痕，都是用于實(shí)現(xiàn)分組內(nèi)的統(tǒng)計
需求案例：一個目的地身冀，用戶可能通過三種路徑到達(dá)靠汁，一天可以到達(dá)多次，統(tǒng)計出只通過A路徑到達(dá)目標(biāo)的人數(shù)闽铐、次數(shù)
image.png
| 用戶 | 路徑 | 目標(biāo) |
|---|---|---|
| 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');
--統(tǒng)計單用戶的目標(biāo)到達(dá)次數(shù)
with push as (
select uid,path,target,count(*) as v1
from log
group by uid,path,target
),
--使用窗口函數(shù)統(tǒng)計單個目標(biāo)到達(dá)的路徑個數(shù)
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'
