【sql题 累计当前行与前几行不重复的数据】

题目

有一张表tmp，里面有字段tjzq为字符串类型表示月份，字段num为int类型表示数量。现在需要按照tjzq进行累计求当前月与前11个与的和。
增加难度：若有重复的num。累计求和时该值只算一次。

create table tmp (
     tjzq varchar(6)
    ,num int
);
insert into tmp values ('202201',1);
insert into tmp values ('202202',2);
insert into tmp values ('202203',3);
insert into tmp values ('202204',4);
insert into tmp values ('202205',5);
insert into tmp values ('202206',6);
insert into tmp values ('202207',7);
insert into tmp values ('202208',8);
insert into tmp values ('202209',9);
insert into tmp values ('202210',10);
insert into tmp values ('202211',11);
insert into tmp values ('202212',12);
insert into tmp values ('202301',13);
insert into tmp values ('202302',14);
insert into tmp values ('202303',14);
insert into tmp values ('202304',13);
insert into tmp values ('202305',12);
insert into tmp values ('202306',11);
insert into tmp values ('202307',10);
insert into tmp values ('202308',12);
insert into tmp values ('202309',15);
insert into tmp values ('202310',14);
commit;

• 输入
  
• 输出
  

解题思路

1. 先生成所有周期，比如
   202310 对应 202310 202211
   202309 对应 202309 202210

2. 将生成的周期跟原表进行笛卡尔积，筛选出在周期范围内的数据

3. 根据周期进行去重聚合

Oracle解答

• 递归方式

with tmp_a as (
	select 
	     min(to_date(tjzq||'01','yyyymmdd')) tjzq_min  --最小的日期
	    ,max(to_date(tjzq||'01','yyyymmdd')) tjzq_max  --最大的日期
	from tmp
), tmp_b (start_month) as (
    select 
        tjzq_max
    from tmp_a 
    union all
    select add_months(start_month,-1) from tmp_b, tmp_a
    where to_char(add_months(start_month,-1),'yyyymm') >= to_char(tjzq_min,'yyyymm')
), tmp_c as (
   select start_month,add_months(start_month,-11) end_date from tmp_b
)
select 
     to_char(b.start_month,'yyyymm') tjzq
    ,c.num
    ,sum(distinct a.num) num_z
from tmp a, tmp_c b, tmp c
where to_date(a.tjzq||'01','yyyymmdd') >= b.end_date 
    and to_date(a.tjzq||'01','yyyymmdd') <= b.start_month
    and to_char(b.start_month,'yyyymm') = c.tjzq
group by to_char(b.start_month,'yyyymm')
    ,to_char(b.end_date,'yyyymm')
    ,c.num
order by to_char(b.start_month,'yyyymm')
;

• 非递归方式

with tmp_a as (
	select 
	     to_date(tjzq||'01','yyyymmdd') tjzq_begin
	    ,case when add_months(to_date(tjzq||'01','yyyymmdd'),-11) < tjzq_min then tjzq_min else add_months(to_date(tjzq||'01','yyyymmdd'),-11) end tjzq_end
	from tmp a, (
	    select min(to_date(tjzq||'01','yyyymmdd')) tjzq_min
	    from tmp 
	) b 
)
select 
     to_char(b.tjzq_begin,'yyyymm') tjzq
    ,c.num
    ,sum(distinct a.num) num_z
from tmp a, tmp_a b, tmp c
where to_date(a.tjzq||'01','yyyymmdd') >= b.tjzq_end 
    and to_date(a.tjzq||'01','yyyymmdd') <= b.tjzq_begin
    and to_char(b.tjzq_begin,'yyyymm') = c.tjzq
group by to_char(b.tjzq_begin,'yyyymm')
    ,to_char(b.tjzq_end,'yyyymm')
    ,c.num
order by to_char(b.tjzq_begin,'yyyymm')
;

Postgresql解答

• 去重后的解答方式与oracle类似

with tmp_a as (
	select 
	     to_date(tjzq||'01','yyyymmdd') tjzq_begin
	    ,case when to_date(tjzq||'01','yyyymmdd')-interval '11 month' < tjzq_min then tjzq_min else to_date(tjzq||'01','yyyymmdd')-interval '11 month' end tjzq_end
	from tmp a, (
	    select min(to_date(tjzq||'01','yyyymmdd')) tjzq_min
	    from tmp 
	) b 
)
select 
     to_char(b.tjzq_begin,'yyyymm') tjzq
    ,c.num
    ,sum(distinct a.num) num_z
from tmp a, tmp_a b, tmp c
where to_date(a.tjzq||'01','yyyymmdd') >= b.tjzq_end 
    and to_date(a.tjzq||'01','yyyymmdd') <= b.tjzq_begin
    and to_char(b.tjzq_begin,'yyyymm') = c.tjzq
group by to_char(b.tjzq_begin,'yyyymm')
    ,to_char(b.tjzq_end,'yyyymm')
    ,c.num
order by to_char(b.tjzq_begin,'yyyymm')
;

• 这里使用窗口函数实现不去重累加方式

--这里的求和没有去重累加
select tjzq,num,
    sum(num) over(order by tjzq rows between 11 preceding and 0 preceding)
from tmp;

--pg不支持以下方式去重，但有的数据库支持
--pg中，窗口函数中不支持使用distinct
select tjzq,num,
    sum(distinct num) over(order by tjzq rows between 11 preceding and 0 preceding)
from tmp;

往期题目

上一题：【sql题 巧用自连】

下一题：【sql题 累计当前行与前几行不重复的数据】