我有一個包含 3 列 Serviceid、Date、User_id 的“purchases”表
一些資料看起來像這樣：
20 2-Jan-18 40709217
20 2-Jan-18 40709217
40 2-Jan-18 40709217
40 2-Jan-20 40709217
50 2-Jan-21 40709217
984 22-Mar-18 18246539
269 22-Mar-18 18246539
666 1-Apr-18 18246539
我的查詢請求是：
對于每個“user_id”，獲取以下資訊：
- 用戶購買的前 2 個最早的 ServiceId 和日期
- 用戶購買的最新 ServiceId 和 Date
- 用戶購買的服務數量
結果表的列必須遵循以下順序：User_id、FirstServiceid、SecondServiceid、FirstServiceDate、SecondServiceDate、LastServiceid、LastServiceDate、TotalService。
預期輸出：
| 用戶身份 | 第一服務id | 第二服務id | 第一次約會 | 第二次約會 | 上次服務ID | 最后日期 | 總服務 |
|---|---|---|---|---|---|---|---|
| 40709217 | 20 | 40 | 18 年 1 月 2 日 | 2-1-20 | 50 | 2 月 21 日 | 5 |
| 18246539 | 984 | 666 | 18 年 3 月 22 日 | 1-4-18 | 666 | 1-4-18 | 3 |
我的想法是進行聚合，然后將它們連接在一起，但我遇到了這個錯誤
“列 'purchase.Serviceid' 在選擇串列中無效，因為它不包含在聚合函式或 GROUP BY 子句中”
呼叫最新的 Serviceid 和 Date 時：
select user_id, max(date), serviceid
from purchases
group by user_id
如何克服這個問題，是否有更好的方法來聚合大量資訊而不必使用 JOIN？
uj5u.com熱心網友回復：
您可以嘗試使用以下使用視窗函式的方法，例如row_number和dense_rank識別所需的記錄，然后再聚合結果，user_id并在MAX函式中使用 case 運算式提取所需列中的資料
SELECT
User_id,
MAX(CASE WHEN earliest_order=1 and rp=1 THEN Serviceid END) as FirstServiceid,
MAX(CASE WHEN earliest_order=2 and rp=1 THEN Serviceid END) as SecondServiceid,
MAX(CASE WHEN earliest_order=1 and rp=1 THEN [Date] END) as FirstServiceDate,
MAX(CASE WHEN earliest_order=2 and rp=1 THEN [Date] END) as SecondServiceDate,
MAX(CASE WHEN latest_order=1 THEN Serviceid END) as LastServiceid,
MAX(CASE WHEN latest_order=1 THEN [Date] END) as LastServiceDate,
COUNT(1) as TotalService
FROM (
SELECT
*,
DENSE_RANK() OVER (
PARTITION BY User_id
ORDER BY [Date]
) as earliest_order,
ROW_NUMBER() OVER (
PARTITION BY User_id
ORDER BY [Date] DESC, Serviceid
) as latest_order,
ROW_NUMBER() OVER (
PARTITION BY User_id,[Date]
ORDER BY Serviceid DESC
) rp
FROM
purchases
) t
GROUP BY
User_id
ORDER BY
User_id DESC
上面使用的子查詢的結果也包含在下面供您閱讀。
SELECT
*,
DENSE_RANK() OVER (
PARTITION BY User_id
ORDER BY [Date]
) as earliest_order,
ROW_NUMBER() OVER (
PARTITION BY User_id
ORDER BY [Date] DESC, Serviceid
) as latest_order,
ROW_NUMBER() OVER (
PARTITION BY User_id,[Date]
ORDER BY Serviceid DESC
) rank_priority
FROM
purchases
ORDER BY User_id, [Date], Serviceid DESC
| 服務號 | 日期 | 用戶身份 | 最早的順序 | 最新訂單 | rank_priority |
|---|---|---|---|---|---|
| 984 | 2018-03-22 | 18246539 | 1 | 3 | 1 |
| 269 | 2018-03-22 | 18246539 | 1 | 2 | 2 |
| 666 | 2018-04-01 | 18246539 | 2 | 1 | 1 |
| 40 | 2018-01-02 | 40709217 | 1 | 5 | 1 |
| 20 | 2018-01-02 | 40709217 | 1 | 3 | 2 |
| 20 | 2018-01-02 | 40709217 | 1 | 4 | 3 |
| 40 | 2020-01-02 | 40709217 | 2 | 2 | 1 |
| 50 | 2021-01-02 | 40709217 | 3 | 1 | 1 |
在此處查看作業演示資料庫小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346767.html
標籤：sql
下一篇：按日期列出未連接的用戶