窗口函数避坑指南

窗口函数避坑指南：

窗口函数执行顺序：

• FROM
• WHERE
• GROUP BY
• 聚合函数
• HAVING
• 窗口函数
• SELECT
• DISTINCT
• UNION
• ORDER BY
• OFFSET
• LIMIT

不能在WHERE子句中使用窗口函数：

错误版本演示：

SELECT 
  id, 
  final_price 
FROM auction 
WHERE final_price > AVG(final_price) OVER();

报错信息：

SELECT 
  id, 
  final_price 
FROM auction 
WHERE final_price > AVG(final_price) OVER()
> 3593 - You cannot use the window function 'avg' in this context.'
> 时间: 0.005s

正确代码：

SELECT
  id,
  final_price 
FROM (
  SELECT
    id,
    final_price,
    AVG(final_price) OVER() AS avg_final_price
  FROM auction) c
WHERE final_price > avg_final_price

不能在HAVING子句中使用窗口函数：

错误版本展示：

SELECT 
  country, 
  AVG(final_price) 
FROM auction 
GROUP BY country 
HAVING AVG(final_price) > AVG(final_price) OVER ();

正确版本展示：

SELECT
  country,
  AVG(final_price) 
FROM auction 
GROUP BY country 
HAVING AVG(final_price) > (SELECT AVG(final_price) FROM auction);

不能在GROUP BY子句中使用窗口函数:

错误版本演示：

SELECT 
  NTILE(4) OVER(ORDER BY views DESC) AS quartile, 
  MIN(views), 
  MAX(views) 
FROM auction 
GROUP BY NTILE(4) OVER(ORDER BY views DESC);

正确版本演示：

SELECT
  quartile,
  MIN(views),
  MAX(views)
FROM
  (SELECT
    views,
    ntile(4) OVER(ORDER BY views DESC) AS quartile
  FROM auction) c
GROUP BY quartile;

在ORDER BY中使用窗口函数:

SELECT
  id,
  views,
  NTILE(4) OVER(ORDER BY views DESC) AS quartile
FROM auction
ORDER BY NTILE(4) OVER(ORDER BY views DESC);

窗口函数与GROUP BY一起使用:

错误演示：

SELECT 
  category_id,
  final_price, 
  AVG(final_price) OVER() 
FROM auction;

正确演示：

SELECT
  category_id,
  MAX(final_price) AS max_final, 
  AVG(MAX(final_price)) OVER() AS `avg`
FROM auction
GROUP BY category_id;

Rank时使用聚合函数:

SELECT
  country,
  COUNT(id),
  RANK() OVER(ORDER BY COUNT(id) DESC) AS `rank`
FROM auction
GROUP BY country;

利用GROUP BY计算环比:

SELECT
  ended,
  SUM(final_price) AS sum_price,
  LAG(SUM(final_price)) OVER(ORDER BY ended) AS `lag`
FROM auction
GROUP BY ended
ORDER BY ended;

对GROUP BY分组后的数据使用 PARTITION BY:

select country,
    ended,
    sum(views) views_on_day,
    sum(sum(views))over (PARTITION BY country) views_country
from auction group by country,ended;

总结：

• 窗口函数只能出现在SELECT和ORDER BY子句中
• 如果查询的其他部分（WHERE，GROUP BY，HAVING）需要窗口函数，请使用子查询，然后在子查询中在使用窗口函数
• 如果查询使用聚合或GROUP BY，请记住窗口函数只能处理分组后的结果，而不是原始的表数据