SQL报错：PARTITION BY引用cust.cif未分组或聚合的问题排查

报错原因 #

报错提示PARTITION BY expression references cust.cif which is neither grouped nor aggregated，核心问题是：

• 你的查询使用GROUP BY做聚合时，分组字段仅包含friend.customer、full_date、balance_tier，但窗口函数dense_rank()的PARTITION BY子句里引用了cust.cif。
• SQL语法强制要求：聚合查询中，SELECT里的字段要么出现在GROUP BY中，要么被聚合函数（如SUM、MAX）处理。cust.cif既不在GROUP BY里，也没有被聚合，引擎无法确定分组后该用哪个cust.cif值来做窗口分区。

解决办法 #

根据friend.customer与cust.cif的对应关系，分两种情况处理：

情况1：一个customer对应唯一的cif #

如果friend.customer和cust.customer_id是一对一关联（即每个客户仅对应一个cif），直接把cust.cif加入GROUP BY即可：

WITH friend AS (
  SELECT ID
          , lpad(cast(ID as string), 10, '0') AS customer
  FROM `data-sandbox.WorksNew.friendversary`
),cust AS (
  SELECT customer_id,
        cif,
        customer_start_date
  FROM `data-production.dashboard_views.customer_registration`
),cbal AS (
  SELECT customer_id
          , total_balance
          , full_date
          , balance_tier
  FROM `data-production.data_analytics.customer_record`
  WHERE full_date = "2022-07-22"
  GROUP BY 1,2,3,4
)
SELECT friend.customer
        , SUM(COALESCE(cbal.total_balance, 0)) AS total_balance
        , full_date
        , balance_tier
        , dense_rank() OVER (PARTITION BY friend.customer, cust.cif ORDER BY cbal.full_date DESC) rn_desc
FROM friend
LEFT JOIN cbal
  ON friend.customer = cbal.customer_id
LEFT JOIN cust
  ON friend.customer = cust.customer_id
GROUP BY friend.customer, full_date, balance_tier, cust.cif

情况2：一个customer对应多个cif #

如果一个客户存在多条cif记录，需要先对cust表的cif做聚合处理（比如取最新/最早的cif，或用MAX/MIN），再参与查询。示例如下：

WITH friend AS (
  SELECT ID
          , lpad(cast(ID as string), 10, '0') AS customer
  FROM `data-sandbox.WorksNew.friendversary`
),cust AS (
  -- 先聚合每个customer对应的cif，这里用MAX示例，可根据业务逻辑调整
  SELECT customer_id,
        MAX(cif) AS cif,
        MAX(customer_start_date) AS customer_start_date
  FROM `data-production.dashboard_views.customer_registration`
  GROUP BY customer_id
),cbal AS (
  SELECT customer_id
          , total_balance
          , full_date
          , balance_tier
  FROM `data-production.data_analytics.customer_record`
  WHERE full_date = "2022-07-22"
  GROUP BY 1,2,3,4
)
SELECT friend.customer
        , SUM(COALESCE(cbal.total_balance, 0)) AS total_balance
        , full_date
        , balance_tier
        , dense_rank() OVER (PARTITION BY friend.customer, cust.cif ORDER BY cbal.full_date DESC) rn_desc
FROM friend
LEFT JOIN cbal
  ON friend.customer = cbal.customer_id
LEFT JOIN cust
  ON friend.customer = cust.customer_id
GROUP BY friend.customer, full_date, balance_tier, cust.cif

内容的提问来源于stack exchange，提问作者Mario Prabawa