sql 面试题02_sum over的经典面试题-CSDN博客

本文链接：https://blog.csdn.net/qq_44769220/article/details/127797704

本文介绍了如何使用SQL的窗口函数sum() over()来统计每个用户每月的访问量，并演示了如何计算累计次数和按特定时间段（如近三个月）的累计值。通过实例展示了如何在数据库中实现这些功能，适用于业务数据分析和用户行为追踪。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

统计每个人每个月访问量和累计月访问量

数据准备：

用户id 月份 小计 累积
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3

假如想实现每个用户从刚开始一直到最新时间的累计次数？

对于客户u01而言，到2017-01位置一共11次，到2017-02月次数为11+12=23，也就是累计效果。

考察点为开窗函数sum() over()

数据准备：

--创建表
create table test1(
userid varchar(20),
visitDate varchar(20),
vistcount int
)

--插入数据
insert into test1 values('u01','2017-01',11)
,('u01','2017-02',12)
,('u02','2017-01',12)
,('u03','2017-01',8)
,('u04','2017-01',3);

sql实现：

select t1.userid
,t1.visitDate
,t1.c
,sum(c) over(partition by t1.userid order by t1.visitDate) leji from(
select userid ----这一步子查询为了排除同一个客户
,visitDate
,sum(vistcount) as c
from test1 group by userid,visitDate
)t1

结果展示：

拓展

求出某个客户近三个月的累计值，或者这个客户近N个月的累计值？

这个同样是通过开窗函数sum()over() 来做的，只是添加了个可以定义上边界和下边界的语法。

数据准备：

用户id  月份     小计    累积
u01    2017-01    11      11
u01    2017-02    12      23
u01    2017-03    15      38
u01    2017-04    10      37
u02    2017-01    5       5
u02    2017-02    10      15
u02    2017-03    11      26
u02    2017-04    14      35
u03    2017-01    1       1
u03    2017-02    2       3
u03    2017-03    3       6
u03    2017-04    7       12
u03    2017-05    9       19
u03    2017-06    10      26
u03    2017-07    15      34

创建表并插入数据：


create table test2(
userid varchar(20),
visitDate varchar(20),
vistcount int
)

insert into test2 values('u01','2017-01',11)
,('u01','2017-02',12)
,('u01','2017-03',15)
,('u01','2017-04',10)
,('u02','2017-01',5)
,('u02','2017-02',10)
,('u02','2017-03',11)
,('u02','2017-04',14)
,('u03','2017-01',1)
,('u03','2017-02',2)
,('u03','2017-03',3)
,('u03','2017-04',7)
,('u03','2017-05',9)
,('u03','2017-06',10)
,('u03','2017-07',15);

sql实现：

select t1.userid
      ,t1.visitDate
      ,t1.c
      ,sum(c) over(partition by userid order by visitDate rows between 2 preceding and current row ) leji from(
select userid
      ,visitDate
      ,sum(vistcount) as c 
      from test2 group by userid,visitDate
)t1

在这里我们通过在over中 rows between ...定义起始....and ..定义结束.....

求累计3个月，基于当前月再往前推两个月就是3个月，所以累计3个月就是， rows between 2 preceding and current row

常见的还有

---rows between unbounded preceding and current row --从第一行到当前行（用这个语法也可以实现第一个需求）

---rows between unbounded preceding and unbounded following--从第一行截止到最后一行。

累计3个月的实现展示。