I keep getting an error: PARTITION BY expression references cust.cif which is neither grouped nor aggregated at [23:60]

Anyone know what the problem is?

query:

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 1,3,4

CodePudding user response：

The issue is because every column has to be in the group by, including those used in a window function, and right now cust.cif isn't being included. Depending on what you're wanting, maybe you can include it in the group by and still get what you're after.

Another solution that I normally use when I want to combine aggregate and window functions is just to use a window function for my aggregate function, and then you don't have to worry how the group by is affecting your results.

SELECT friend.customer
        , SUM(COALESCE(cbal.total_balance, 0)) over (PARTITION BY friend.customer, full_date, balance_tier) 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