窗口函数用于在不改变查询结果集行数的情况下，对每一行执行聚合计算或其他复杂的计算，可以跨行计算。
随着窗口函数的出现，无须再使用关联子查询。窗口函数的可读性很好，代码也很简洁。
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 preSaleFROM 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 preSaleFROM 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 preSaleFROM 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 avgPriceFROM t_product_info
) temp
WHERE price > avgPrice;1.3 查询重叠的时间区间
图 酒店预约t_reservation2表
需求：查找出重叠时间的预定
-- 关联子查询
SELECT *
FROM t_reservation2 r
WHERE EXISTS
(SELECT *FROM t_reservation2 r1WHERE r1.reserver != r.reserver AND (r.start_date BETWEEN r1.start_date AND r1.end_dateOR 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; 
