一、前言
从事数仓,数据开发的同学应该知道,日常工作中主要要用到SQL去实现业务各种数据需求。本系列【最全最经典SQL题】将列举日常工作或者笔试面试中频繁、经典的业务场景并通过SQL去实现。欢迎大家收藏起来,遇到对应的场景可以马上用起来。本系列不定期更新,内容如下:
【最全最经典SQL题】四 窗口大小控制
【最全最经典SQL题】五 数据合并与拆分
【最全最经典SQL题】六 数据扩充与收缩
【最全最经典SQL题】七 容器
【最全最经典SQL题】八 时间序列
【最全最经典SQL题】九 非等值连接
更新中........
本系列将提供建表语句、数据、解题SQL代码,大家动动小手指就能看到效果以便理解,为了方便大部分同学实操,默认采用MySQL的SQL,如Hive有区别的会注明一下。
二、表结构
现有一张门店销售统计日表,具体字段和注释如下:
CREATE TABLE `st_store_sales_di` (
`stat_date` date NOT NULL COMMENT '日期',
`store_id` int NOT NULL COMMENT '门店ID',
`store_name` varchar(10) COMMENT '门店名称',
`sales_amt` decimal(38,2) COMMENT '销售金额'
) ENGINE=InnoDB COMMENT='门店销售统计日表';
三、数据准备
INSERT INTO `st_store_sales_di` VALUES ('2024-03-01', 1001, '广州店', 380.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-01', 1002, '北京店', 220.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-01', 1003, '上海店', 360.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-02', 1001, '广州店', 350.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-02', 1002, '北京店', 380.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-02', 1003, '上海店', 180.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-03', 1001, '广州店', 260.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-03', 1002, '北京店', 280.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-03', 1003, '上海店', 230.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-04', 1001, '广州店', 310.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-04', 1002, '北京店', 290.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-04', 1003, '上海店', 420.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-05', 1001, '广州店', 460.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-05', 1002, '北京店', 490.00);
INSERT INTO `st_store_sales_di` VALUES ('2024-03-05', 1003, '上海店', 480.00);
四、数据需求
1、取最小值
需求描述:取各个门店销售额最低的日期
需求实现:
select store_name
,stat_date as min_amt_date
from (
select stat_date
,store_name
,sales_amt
,row_number() over(partition by store_name order by sales_amt asc) as rn
from st_store_sales_di
) a
where rn = 1
2、 取最大值
需求描述:取各个门店销售额最高的日期
需求实现:
select store_name
,stat_date
from (
select stat_date
,store_name
,sales_amt
,row_number() over(partition by store_name order by sales_amt desc) as rn
from st_store_sales_di
) a
where rn = 1
3、取第二大值
需求描述:取各个门店销售额第二高的日期
需求实现:
select store_name
,stat_date
from (
select stat_date
,store_name
,sales_amt
,row_number() over(partition by store_name order by sales_amt desc) as rn
from st_store_sales_di
) a
where rn = 2
4、取最大和最小值
需求描述:取各个门店销售额最高和最低的日期
需求实现:
select store_name
,max(case when desc_rn = 1 then stat_date end) as max_sales_date
,max(case when asc_rn = 1 then stat_date end ) as min_sales_date
from (
select stat_date
,store_name
,sales_amt
,row_number() over(partition by store_name order by sales_amt desc) as desc_rn
,row_number() over(partition by store_name order by sales_amt asc) as asc_rn
from st_store_sales_di
) a
where desc_rn = 1
or asc_rn = 1
group by store_name
5、取最大前2值和最小前2值
需求描述:按门店分组,取销售最好的2天日期,和最差的2天日期
需求实现:
select table_max.store_name
,table_max.max_stat_date
,table_min.min_stat_date
from
(
select store_name
,concat_ws(',' , group_concat(stat_date) ) as max_stat_date
from (
select stat_date
,store_name
,sales_amt
,row_number() over(partition by store_name order by sales_amt desc) as rn
from st_store_sales_di
) a
where rn <= 2
group by store_name
) table_max
join
(
select store_name
,concat_ws(',' , group_concat(stat_date) ) as min_stat_date
from (
select stat_date
,store_name
,sales_amt
,row_number() over(partition by store_name order by sales_amt asc) as rn
from st_store_sales_di
) a
where rn <= 2
group by store_name
) table_min
on table_max.store_name = table_min.store_name
好了,大家赶紧收藏起来去实现吧~