我的表是這樣的：
| ID | 型別 | 月 |
|---|---|---|
| 100 | 啟用 | 01-11-2020 |
| 100 | 有薪酬的 | 01-03-2021 |
| 100 | 有薪酬的 | 01-06-2021 |
| 101 | 啟用 | 01-03-2021 |
| 102 | 啟用 | 01-04-2021 |
| 102 | 有薪酬的 | 01-04-2021 |
| 103 | 啟用 | 01-04-2021 |
| 103 | 有薪酬的 | 01-06-2021 |
現在我有兩個臨時表：
tbl1 as (
Select *,case when row_number() over(partition by id order by month)=1 then 1 else null End as Cont1 from table where type='activate'),
tbl2 as (select *,,case when row_number() over(partition by id order by month)=1 then 1 else null End as Cont2 from table where type='paid')
select tbl1.id ,
tbl1.type,
tbl1.month as activate_month,
tbl2.month as paid_month,
-- datediff('month',tb1.month,tbl2.month) month_diff,
Sum(Cont1) over (partition by 1 order by tbl1.month rows unbounded preceding) as distA,
Sum(Cont2) over (partition by 1 order by tbl2.month rows unbounded preceding) as distP
from tbl1 ta
left join tbl2 tp on ta.id=tp.id
場景：
對于 type='activate' id 的條目將只存在一次，但對于 type='paid' 可能有多個條目，所以我想以累積順序計算這個 id，這樣我們就可以考慮第一個條目用一個月算一算。
我想要的結果：
| ID | 型別 | 一個月 | 月 | 計數A | 計數 |
|---|---|---|---|---|---|
| 100 | 啟用 | 01-11-2020 | 01-03-2021 | 1 | 1 |
| 101 | 啟用 | 01-03-2021 | 空值 | 2 | 0 |
| 102 | 啟用 | 01-04-2021 | 01-04-2021 | 3 | 2 |
| 103 | 啟用 | 01-04-2021 | 01-06-2021 | 4 | 3 |
uj5u.com熱心網友回復：
如果我理解正確，這可以使用帶有outer apply.
Select Tbl.ID, Tbl.Type, Min(Tbl.month) As Amonth, Min(T.Pmonth) As Pmonth,
Count(Tbl.ID) Over (Order by Tbl.ID Rows Unbounded Preceding) As countA,
Case When T.ID Is Not Null
Then Count(T.ID) Over (Order by T.ID Rows Unbounded Preceding)
Else 0
End As countp
From Tbl Outer Apply (Select ID, Min(month) As Pmonth
From Tbl As T
Where ID=Tbl.ID
And Type='Paid'
Group by ID) As T
Where Tbl.Type='activate'
Group by Tbl.ID, T.ID, Tbl.Type
Order by Tbl.ID
資料輸出：
ID Type Amonth Pmonth countA countp
----------- ---------- ---------- ---------- ----------- -----------
100 activate 2020-11-01 2021-03-01 1 1
101 activate 2021-03-01 NULL 2 0
102 activate 2021-04-01 2021-04-01 3 2
103 activate 2021-04-01 2021-06-01 4 3
uj5u.com熱心網友回復：
為此，您實際上并不需要 2 個 CTE。
自聯接的聚合就足夠了。
你看，窗函式（fe row_number，dense_rank）是在聚合后處理的。因此，您也可以在MIN.
例如：
select activate.id, activate.type , min(activate.month) as activate_month , min(paid.month) as activate_month , row_number() over (order by min(activate.month) asc) as rn_activate , case when min(paid.month) is not null then row_number() over (order by min(paid.month) asc) else 0 end as rn_paid from yourtable as activate left join yourtable as paid on paid.id = activate.id and paid.type = 'paid' where activate.type = 'activate' group by activate.id, activate.type order by min(activate.month) asc;
| ID | 型別 | 激活月 | 激活月 | rn_activate | rn_已付 |
|---|---|---|---|---|---|
| 100 | 啟用 | 2020-11-01 | 2021-03-01 | 1 | 1 |
| 101 | 啟用 | 2021-03-01 | 空值 | 2 | 0 |
| 102 | 啟用 | 2021-04-01 | 2021-04-01 | 3 | 2 |
| 103 | 啟用 | 2021-04-01 | 2021-06-01 | 4 | 3 |
關于db<>fiddle 的演示在這里
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387373.html
標籤：sql sql-server 亚马逊红移
下一篇：從sql得到錯誤的答案