表“用戶”：
| ID | 姓名 | 電子郵件 | created_at |
|---|---|---|---|
| 46 | FSDSD2 | [email protected] | 2022-05-29 14:19:21 |
| 47 | Fxz3 | [email protected] | 2022-05-30 20:12:15 |
| 48 | Fgh3 | [email protected] | 2022-05-31 20:12:15 |
| 49 | Fghxc3 | [email protected] | 2022-06-01 20:12:15 |
| 50 | Fdx3 | [email protected] | 2022-06-02 20:12:15 |
| 51 | FG3q3 | [email protected] | 2022-06-03 20:12:15 |
| 88 | Fbhgt | [email protected] | 2022-05-23 16:38:41 |
| 112 | 飛度 | [email protected] | 2022-05-24 16:19:23 |
| 113 | Y14gss | [email protected] | 2022-05-25 16:42:44 |
| 114 | 順風順水 | [email protected] | 2022-05-26 12:10:40 |
| 115 | A2czu | [email protected] | 2022-05-27 14:00:31 |
| 116 | cc1sn | [email protected] | 2022-05-28 12:04:56 |
表“ oxygen_point_earns ”：
| ID | 用戶身份 | 氧點 | created_at |
|---|---|---|---|
| 2 | 116 | 50.00 | 2022-05-23 17:49:30 |
| 3 | 113 | 10.00 | 2022-05-24 07:49:46 |
| 4 | 114 | 10.00 | 2022-05-25 07:50:42 |
| 5 | 46 | 50.00 | 2022-05-26 07:55:19 |
| 6 | 47 | 40.00 | 2022-05-27 13:28:17 |
| 7 | 48 | 30.00 | 2022-05-28 13:32:19 |
| 8 | 49 | 10.00 | 2022-05-29 13:32:19 |
| 9 | 50 | 5.00 | 2022-05-30 13:32:19 |
| 10 | 51 | 10.00 | 2022-05-31 13:32:19 |
| 11 | 88 | 20.00 | 2022-06-01 13:32:19 |
| 12 | 112 | 50.00 | 2022-06-02 13:32:19 |
| 13 | 115 | 10.00 | 2022-06-03 13:32:19 |
| 14 | 112 | 20.00 | 2022-06-03 16:32:19 |
我有兩張桌子：
- “ users ”，存盤用戶基本資訊
- “ oxygen_point_earns ”，存盤特定用戶獲得的氧分
“ users ”表有12行，而“ oxygen_point_earns ”表包含13條記錄，這意味著一個用戶甚至可以多次獲得積分。
我試圖在這些表之間進行一些計算（例如，將每周獲得的總積分除以每個用戶的每周用戶累積總和）。當我嘗試獲取用戶累積總和時，就會出現問題。
SELECT STR_TO_DATE(CONCAT(YEARWEEK(op.created_at), ' Sunday'), '%X%V %W') AS week,
SUM(COUNT(*)) OVER(ORDER BY MIN(op.created_at)) AS user_count,
SUM(op.oxygen_point) AS op_weekly
FROM users us
LEFT JOIN oxygen_point_earns op
ON us.id = op.user_id
GROUP BY week
ORDER BY week
此查詢為我提供以下輸出：
如您所見，即使正確計算了點數，第二行的總用戶數也是錯誤的：它應該是 12 而不是 13（第一周我有6 個用戶，然后下周又有 6 個用戶注冊。所以我的總用戶計數是 12。在第二行我應該得到 12。）
我試過DISTINCT，GROUP_CONCAT但沒有用。如何修復此查詢以獲得用戶計數的真實結果？
uj5u.com熱心網友回復：
一個直接的選擇是使用子查詢/cte 將兩個操作（聚合和視窗化）分開：
WITH cte AS (
SELECT STR_TO_DATE(CONCAT(YEARWEEK(op.created_at), ' Sunday'), '%X%V %W') AS week,
COUNT(DISTINCT user_id) AS cnt,
SUM(op.oxygen_point) AS op_weekly
FROM users us
LEFT JOIN oxygen_point_earns op ON us.id = op.user_id
GROUP BY week
)
SELECT week,
SUM(cnt) OVER(ORDER BY week) AS user_count,
op_weekly
FROM cte
ORDER BY week
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/497107.html
上一篇：在MYSQL中計算有條件的用戶