题目来源:https://www.jianshu.com/p/0f165dcf9525
1. 题目(分组累加)
1)对A表的fdate列添加索引
2)通过SQL语句,将A表数据进行计算后得到B表结果,并描述执行过程
建表语句:
create table A2 (
FDATE datetime,
value int
);
insert into a2 values
("2018/11/23",10),
("2018/12/31",3),
("2019/2/9",53),
("2019/3/31",23),
("2019/7/8",11),
("2019/7/31",10);
2. 解题
2.1 对A表的fdate列添加索引
-- 添加索引 给 FDATE;
create index id_FDATE on a2(FDATE);
show index from a2;
2.2 通过SQL语句,将A表数据进行计算后得到B表结果,并描述执行过程
# 中间表:(标序号,累加前必须的步骤!)
select year(FDATE) FYEAR,
month(FDATE) FMONTH,
value,
row_number() over(partition by year(FDATE)) as rn_ysum, -- 根据年份为value标序号,为后面根据年份累加做准备
row_number() over () as rn_sum -- 为value标序号,为后面累加做准备
from A2;
select t.FYEAR,
t.FMONTH,
t.value,
sum(value) over (partition by FYEAR order by rn_ysum) as YSUM,
sum(value) over (order by rn_sum) as SUM
from(
select year(FDATE) FYEAR,
month(FDATE) FMONTH,
value,
row_number() over(partition by year(FDATE)) as rn_ysum,
row_number() over () as rn_sum
from A2
) t;
补充(索引)
# 添加索引
create index id_FDATE2 on a2(FDATE);
# 删除索引
alter table a2 drop index id_FDATE;
# 查看索引
show index from a2;