如何计算使用同一源表的两个不同临时表的Id的累计计数

Question

我的表类似于：

1-11-2020021年1月1日//tr>1-06-2021//tr>1-03-2021//tr>1-04-2021//tr>021年4月1日1-04-2021//tr>1-06-2021//tr>

ID	类型	month
100	激活
100	付费
100	付费
101	激活
102	激活
102	付费
103	激活
103	付费

您实际上并不需要2个CTE
一个自联接的聚合就足够了。

您可以看到，窗口函数(例如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;

>020-11-01<1>

id	type	activate_month
100	激活	2021-03-01
101	激活	2021-03-01		102	激活		103	激活

如果我理解正确，这可以使用outer apply的分析函数来完成。

Select Tbl.ID, Tbl.Type, Min(Tbl.[month]) As Amonth, Min(T.Pmonth) As Pmonth, 
Count(Tbl.ID) Over (Order by Min(Tbl.[month]) Rows Unbounded Preceding) As countA, 
Case When T.ID Is Not Null 
Then Count(T.ID) Over (Order by Min(T.Pmonth) 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 Min(Tbl.[month])

数据输出：

ID
100		101		102		103