【最全最经典SQL题】四 窗口大小控制

一、前言

从事数仓，数据开发的同学应该知道，日常工作中主要要用到SQL去实现业务各种数据需求。本系列【最全最经典SQL题】将列举日常工作或者笔试面试中频繁、经典的业务场景并通过SQL去实现。欢迎大家收藏起来，遇到对应的场景可以马上用起来。本系列不定期更新，内容如下：

【最全最经典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='门店销售统计日表';

三、数据准备

先准备3家门店，5天的数据

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
	   ,sales_amt
	   ,lag(sales_amt,1,0) over (partition by store_name order by stat_date) as before_sales_amt  -- 前一行
	   ,lead(sales_amt,1,0) over (partition by store_name order by stat_date) as afer_sales_amt  -- 后一行
  from st_store_sales_di

2、前后一行相加

需求描述：统计每个门店前一天和后一天相加的销售额

需求实现：

select store_name
       ,stat_date
	   ,sales_amt
	   ,lag(sales_amt,1,0) over (partition by store_name order by stat_date)
		 +lead(sales_amt,1,0) over (partition by store_name order by stat_date) as add_sales_amt  -- 前后一行相加
  from st_store_sales_di

3、前一行和当前行的均值

需求描述：统计每个门店前一天和当天的平均销售额

需求实现：

select store_name
       ,stat_date
	   ,sales_amt
	   ,case when before_sales_amt is null then sales_amt
			  else (sales_amt+before_sales_amt)/2 end as avg_sales_amt
  from (
				select store_name
					   ,stat_date
					   ,sales_amt
					   ,lag(sales_amt,1) over (partition by store_name order by stat_date) as before_sales_amt  -- 前一行 
					from st_store_sales_di
) a

好了，大家赶紧收藏起来去实现吧~