SQL窗口函数
SQL窗口函数
你我皆温柔SQL窗口函数
什么是窗口函数?
OLAP,对数据库数据进行实时分析处理
select 窗口函数() over (partition by xx ordder by xx) from 表名;
- 聚合:
- partition by:
- order by:
窗口函数不改变数据的行数,有时需要去重(在使用聚合函数时)
聚合函数是对当前记录及以上记录求和、最大、最小值等
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51drop table test1;
CREATE TABLE test1 (
userId varchar(50),
visitDate varchar(50),
visitCount INT );
INSERT INTO test1
VALUES
( 'u01', '2017/1/21', 5 ),
( 'u02', '2017/1/23', 6 ),
( 'u03', '2017/1/22', 8 ),
( 'u04', '2017/1/20', 3 ),
( 'u01', '2017/1/23', 6 ),
( 'u01', '2017/2/21', 8 ),
( 'u02', '2017/1/23', 6 ),
( 'u01', '2017/2/22', 4 );
select t1.userId, t1.visitDate, t1.小计
from (select userId,visitDate, (sum(visitCount) over(partition by userId,visitDate)) 小计
from test1) as t1;
# 使用SQL统计出每个用户的累积访问次数
# 法一
select userId, 月份, 小计1 ,sum(小计1) over(partition by userId order by 月份) 累计
from (
select distinct userId, 月份 , sum(小计) over(partition by userId,月份 order by 月份) 小计1
from (
select t.userId, t.月份, 小计
from (
select distinct userId, date_format(visitDate,'%Y-%m') 月份 , sum(visitCount) over(partition by userId,visitDate order by visitDate) 小计 from test1 order by userId
) t) t1) t2;
-- 法2
SELECT t2.userid, t2.visitmonth, subtotal_visit_cnt, sum(subtotal_visit_cnt) over (partition BY userid ORDER BY visitmonth) AS total_visit_cnt
FROM (SELECT userid, visitmonth,sum(visitcount) AS subtotal_visit_cnt
FROM (SELECT userid, date_format(visitdate,'%Y-%m') AS visitmonth,visitcount FROM test1) t1
GROUP BY userid, visitmonth) t2
-- 法3
select *, sum(小计) over(partition by userId order by vis_m) 累计
from (
select userId,vis_m, sum(visitCount) 小计
from (
SELECT userId,DATE_FORMAT(visitDate,'%Y-%m') vis_m, visitCount FROM test1 ORDER BY userId) t1
group by userId,vis_m) t2
-- 法4
select distinct userId, 月份 , sum(visitCount) over(partition by userId,月份) 小计, sum(visitCount) over(partition by userId order by 月份) 累计
from (
select userId,date_format(visitDate,'%Y-%m') 月份, visitCount
from test1 order by userId,月份) as t