目录

• 题目
• 解题思路
• Oracle解答
• Postgresql解答
• 往期题目

题目

有一张表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_maxfrom tmp_a union allselect add_months(start_month,-1) from tmp_b, tmp_awhere 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_monthand 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_endfrom tmp a, (select min(to_date(tjzq||'01','yyyymmdd')) tjzq_minfrom 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_beginand 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_endfrom tmp a, (select min(to_date(tjzq||'01','yyyymmdd')) tjzq_minfrom 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_beginand 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题 累计当前行与前几行不重复的数据】