BigQuery中QUALIFY与GROUP BY配合使用报错的问题咨询

问题原因 #

你遇到的语法错误是因为BigQuery的SQL子句书写顺序有严格要求，QUALIFY的正确位置是在GROUP BY和HAVING之后、ORDER BY之前。你把QUALIFY放在了GROUP BY前面，不符合语法规范，因此触发报错。
另外你提到的需要加WHERE true的问题，是因为如果没有实际的WHERE过滤条件，又需要衔接QUALIFY子句的话，必须加一个占位的WHERE条件，否则语法校验不通过。

正确写法参考 #

如果你是要先筛选出每个产品的最新季度数据，再对筛选结果做分组聚合，有两种常用写法：

写法1：同查询块内调整子句顺序

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4')
SELECT product, count(*)
FROM Produce
WHERE true 
GROUP BY 1
QUALIFY RANK() OVER (PARTITION BY product ORDER BY max(quarter) DESC) <= 1

注意这种写法中窗口函数里的排序字段因为已经做了GROUP BY，所以需要用聚合函数包裹原始字段。

写法2：子查询先过滤再聚合（更推荐，可读性更高）

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4'),
-- 先筛选每个产品最新季度的数据
latest_produce AS (
  SELECT *
  FROM Produce
  WHERE true QUALIFY RANK() OVER (PARTITION BY product ORDER BY quarter DESC) <= 1
)
SELECT product, count(*)
FROM latest_produce
GROUP BY 1

内容的提问来源于stack exchange，提问作者David Masip