关于计算每天月累计的SQL语句的写法---开窗函数(窗口函数)的应用

韩灏凛ceaser

已于 2022-09-06 17:16:09 修改

阅读量5.6k

点赞数 1

分类专栏： DB2SQL 文章标签： sql 数据库

于 2020-07-10 11:11:53 首次发布

本文链接：https://blog.youkuaiyun.com/qq_32389231/article/details/107246527

版权

DB2SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何使用SQL的开窗函数（窗口函数）来计算每年每天的月累计值。通过举例展示了一种表结构，并提供了两种不同的SQL语句实现方式，以获取每个机构在特定日期的当月累计值。

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

关于计算当年每天月累计的SQL语句的写法
例如有一个表表结构如下

维度    当日数值      日期    
A	    100.0000	2020-06-01
A	    200.0000	2020-06-02
A	    30.0000	    2020-06-03
A	    40.0000	    2020-07-01
A	    5.0000	    2020-07-02
A	    50.0000	    2020-07-03
B	    30.0000	    2020-06-01
B	    500.0000    2020-06-02
B	    50.0000	    2020-06-03
B	    100.0000    2020-07-01
B	    20.0000	    2020-07-02
B	    100.0000    2020-07-03
C	    10.0000	    2020-06-01
C	    20.0000	    2020-06-02
C	    10.0000	    2020-06-03
C	    30.0000	    2020-07-01
C	    50.0000	    2020-07-02
C	    20.0000	    2020-07-03
C	    66.0000	    2020-07-05

如果想获得每个机构每天的当月累计值，即想获得的结果为：

维度       当日数值      日期     截止当日的月累计
A	    100.0000	2020-06-01	 100.0000
A	    200.0000	2020-06-02	 300.0000
A	    30.0000	    2020-06-03	 330.0000
A	    40.0000	    2020-07-01	 40.0000
A	    5.0000	    2020-07-02	 45.0000
A	    50.0000	    2020-07-03	 95.0000
B	    30.0000	    2020-06-01	 30.0000
B	    500.0000    2020-06-02	 530.0000
B	    50.0000	    2020-06-03	 580.0000
B	    100.0000    2020-07-01	 100.0000
B	    20.0000	    2020-07-02	 120.0000
B	    100.0000    2020-07-03	 220.0000
C	    10.0000	    2020-06-01	 10.0000
C	    20.0000	    2020-06-02	 30.0000
C	    10.0000	    2020-06-03	 40.0000
C	    30.0000	    2020-07-01	 30.0000
C	    50.0000	    2020-07-02	 80.0000
C	    20.0000	    2020-07-03	 100.0000
C	    66.0000	    2020-07-05	 166.0000

--建表语句
CREATE TABLE
    DSV.TABLE_TEST       --定义模式名&表名
(
DIANPU VARCHAR(10),
XIAOSHOUE DECIMAL(31,4),
RIQI DATE
) 
COMPRESS YES --压缩表空间
DISTRIBUTE BY HASH(DIANPU)
IN DTA;--指定表空间（不指定为默认）
COMMENT ON TABLE "DSV"."TABLE_TEST" IS '测试表';  --表注释
COMMENT ON COLUMN DSV.TABLE_TEST.DIANPU IS '店铺';--字段注释
COMMENT ON COLUMN DSV.TABLE_TEST.XIAOSHOUE IS '销售额';
COMMENT ON COLUMN DSV.TABLE_TEST.RIQI IS '日期';



--插入数据
insert into DSV.TABLE_TEST  values ('A',100,'2020-06-01');
insert into DSV.TABLE_TEST  values ('A',200,'2020-06-02');
insert into DSV.TABLE_TEST  values ('A',30,'2020-06-03');
insert into DSV.TABLE_TEST  values ('A',40,'2020-07-01');
insert into DSV.TABLE_TEST  values ('A',5,'2020-07-02');
insert into DSV.TABLE_TEST  values ('A',50,'2020-07-03');
insert into DSV.TABLE_TEST  values ('B',30,'2020-06-01');
insert into DSV.TABLE_TEST  values ('B',500,'2020-06-02');
insert into DSV.TABLE_TEST  values ('B',50,'2020-06-03');
insert into DSV.TABLE_TEST  values ('B',100,'2020-07-01');
insert into DSV.TABLE_TEST  values ('B',20,'2020-07-02');
insert into DSV.TABLE_TEST  values ('B',100,'2020-07-03');
insert into DSV.TABLE_TEST  values ('C',10,'2020-06-01');
insert into DSV.TABLE_TEST  values ('C',20,'2020-06-02');
insert into DSV.TABLE_TEST  values ('C',10,'2020-06-03');
insert into DSV.TABLE_TEST  values ('C',30,'2020-07-01');
insert into DSV.TABLE_TEST  values ('C',50,'2020-07-02');
insert into DSV.TABLE_TEST  values ('C',20,'2020-07-03');
insert into DSV.TABLE_TEST  values ('C',66,'2020-07-05');

可以使用开窗函数：

select a.*,sum(XIAOSHOUE)over(partition by dianpu,int(RIQI)/100 order by RIQI ) from DSV.TABLE_TEST a

也可以这么写：

select 
A.DIANPU,
A.XIAOSHOUE,
A.RIQI,
sum(b.XIAOSHOUE) AS LEIJIXIAOSHOUE
from 
DSV.TABLE_TEST A join DSV.TABLE_TEST B on A.DIANPU=b.DIANPU and MONTH(A.RIQI)=MONTH(b.RIQI) and A.RIQI>=b.RIQI 
group by 
A.DIANPU,
A.XIAOSHOUE,
A.RIQI
order by
A.DIANPU,
A.RIQI