SQL 窗口函数

 窗口函数用于在不改变查询结果集行数的情况下，对每一行执行聚合计算或其他复杂的计算，可以跨行计算。

随着窗口函数的出现，无须再使用关联子查询。窗口函数的可读性很好，代码也很简洁。

1 实践

1.1 营业额年度变化

需求：查找与上一年相比营业额没有变化的年份。

图 年度营业额t_year_sales 表

-- 关联子查询
SELECT *
FROM t_year_sales y1 
WHERE sale = (SELECT sale FROM t_year_sales WHERE `year` = y1.year - 1);
-- 左连接
SELECT *
FROM (
	SELECT y1.*,y2.sale AS preSale
	FROM t_year_sales y1 
	LEFT JOIN t_year_sales y2 ON y2.`year` = y1.`year` - 1
) temp 
WHERE sale = preSale;
-- 窗口函数
SELECT *
FROM (
  SELECT *,
  MAX(sale) OVER ( ORDER BY `year` RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS preSale
  FROM t_year_sales 
) temp 
WHERE sale = preSale;

1.1.1 时间轴有间断时：和过去最临近的时间进行比较

图 有间断的年度营业额t_year_sales2 表

需求：查询与过去最临近的年份营业额相同的年份。

-- 关联子查询 
SELECT *
FROM t_year_sales2 y
WHERE sale = (SELECT sale FROM t_year_sales2 WHERE `year` = (SELECT MAX(`year`) FROM t_year_sales2 WHERE `year` < y.year));
-- 窗口函数
SELECT *
FROM 
(
  SELECT *,MAX(`sale`) OVER(ORDER BY `year` ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS preSale
	FROM t_year_sales2 y
) temp 
WHERE sale = preSale;

1.2 窗口函数与关联子查询

图 商品信息t_product_info 表

需求：按照商品种类查询销售单价高于平均销售单价的商品。

-- 关联子查询
SELECT *,(SELECT AVG(price) FROM t_product_info WHERE `type` = p.type) AS avgPrice
FROM t_product_info p 
WHERE price > (SELECT AVG(price) FROM t_product_info WHERE `type` = p.type);
-- 关联子查询
SELECT *
FROM (
	SELECT *,AVG(price) OVER (PARTITION BY `type`) AS avgPrice
	FROM t_product_info
) temp
WHERE price > avgPrice;

1.3 查询重叠的时间区间

图 酒店预约t_reservation2表

需求：查找出重叠时间的预定

-- 关联子查询
SELECT *
FROM t_reservation2 r 
WHERE EXISTS 
(
	SELECT *
	FROM t_reservation2 r1
	WHERE r1.reserver != r.reserver AND 
	(r.start_date BETWEEN r1.start_date AND r1.end_date
	 OR r.end_date BETWEEN r1.start_date AND r1.end_date	
	 OR (r.start_date <= r1.start_date AND r.end_date >= r1.end_date)
	)
);

这个需求窗口函数较难实现。

1.4 移动平均值

图 银行账户存取款记录t_account表

需求：求当前取款额及最近三次平均取款额（当前行+前两行。当前两行记录不全时，根据既有数据求平均值）。

-- 窗口函数
SELECT *,AVG(amount) OVER (ORDER BY `date` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avgAmount
FROM t_account;
-- 关联子查询
SELECT *,(SELECT AVG(amount) FROM t_account WHERE `date` <= a.date LIMIT 3) AS avgAmount
FROM t_account a; 

需求：上面需求改为，当记录少于3行，平均值为NULL。

-- 窗口函数
SELECT *,
CASE WHEN COUNT(*) OVER (ORDER BY `date` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) < 3 THEN NULL 
ELSE AVG(amount) OVER (ORDER BY `date` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  END AS avgAmount
FROM t_account;
-- 关联子查询
SELECT *,
CASE WHEN (SELECT COUNT(*) FROM t_account WHERE `date` <= a.date) < 3 THEN NULL 
ELSE (SELECT AVG(amount) FROM t_account WHERE `date` <= a.date LIMIT 3) END AS avgAmount
FROM t_account a;