我正在使用 BigQuery 將用戶的年度支出附加到交易 ID 上。我不知道為什么我會得到SELECT list expression references column price which is neither grouped nor aggregated at [4:7]價格列是聚合目標，我為什么要按它分組？
SELECT
trans_id,
user_id,
SUM(price) OVER (PARTITION BY user_id, EXTRACT(year FROM date)) as user_annual_spend
FROM sample_sales_table
GROUP BY trans_id, user_id
trans_is [STRING NULLABLE]，user_id [STRING NULLABLE]，日期 [DATE NULLABLE] yyyy-mm-dd 格式，價格 [NUMERIC NULLABLE]
trans_id | user_id | date | price
AAAAAAA | user1 |2022-01-09| 20
AAAAAAC | user1 |2022-05-01| 50
BAAAAAB | user1 |2021-01-05| 10
BAAAACB | user1 |2020-01-01| 40
BAAAAAC | user2 |2021-02-01| 30
理想輸出
trans_id | user_id |user_annual_spend
AAAAAAA | user1 | 70
AAAAAAC | user1 | 70
BAAAAAB | user1 | 10
BAAAACB | user1 | 40
BAAAAAC | user2 | 30
uj5u.com熱心網友回復：
修改您的查詢如下：
SELECT trans_id, user_id,
SUM(user_annual_spend) AS user_annual_spend
FROM
(
SELECT trans_id, user_id,
SUM(price) OVER (PARTITION BY user_id, EXTRACT(year FROM date)) as user_annual_spend
FROM sample_sales_table
) T
GROUP BY trans_id, user_id
SUM(price) OVER (PARTITION BY user_id, EXTRACT(year FROM date))是視窗函式而不是聚合函式。
或者簡單地通過以下方式洗掉組：
SELECT trans_id, user_id,
SUM(price) OVER (PARTITION BY user_id, EXTRACT(year FROM date)) as user_annual_spend
FROM sample_sales_table
ORDER BY user_id, trans_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/522179.html
標籤：sql