目录
- 题目
- 解题思路
- Oracle解答
- Postgresql解答
- 往期题目
题目
有一张表tmp,里面有字段tjzq为字符串类型表示月份,字段num为int类型表示数量。现在需要按照tjzq进行累计求当前月与前11个与的和。
增加难度:若有重复的num。累计求和时该值只算一次。
sql">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;
- 输入
- 输出
解题思路
-
先生成所有周期,比如
202310 对应 202310 202211
202309 对应 202309 202210 -
将生成的周期跟原表进行笛卡尔积,筛选出在周期范围内的数据
-
根据周期进行去重聚合
Oracle解答
- 递归方式
sql">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')
;
- 非递归方式
sql">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')
;
sql_108">Postgresql解答
- 去重后的解答方式与oracle类似
sql">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')
;
- 这里使用窗口函数实现不去重累加方式
sql">--这里的求和没有去重累加
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题 累计当前行与前几行不重复的数据】