如何为每个用户获得第一个订阅(假设每次自动更新时订阅id都会更改)

Question

好吧，我现在有点麻烦了。我有user_id, subscription_id, plan, subscription_start_date, subscription_end_date．我正在寻找每个用户购买的所有不同的计划和相应的subscription_ids，每个计划只有一个id(第一个)。需要注意的是，每次用户更新订阅时，订阅id都会更改。假设用户A只有一个订阅，并且续订了3次，因此有3个不同的订阅id，用户B有2个计划，并且续订了两次，因此他们有4个订阅id。

我正在寻找用户A有1个sub_id和1个计划和用户B有2个子id和2个不同的计划

这是我到目前为止的查询

SELECT H.plan, H.user_id
FROM my_table H
INNER JOIN
(SELECT user_id, plan, MIN(subscription_purchase_date) As first_sub_date
FROM my_table
GROUP BY user_id, plan) X
ON H.user_id= X.user_id AND H.subscription_purchase_date = X.first_sub

<表类>user_idsubscription_idstart_dateend_date计划tbody><<tr>1232021-01-019999-01-01溢价B1222021-02-039999-03-04常规1442021-02-019999-01-01溢价1552021-03-019999-01-01溢价B1672021-03-039999-03-04常规B1112020-05-182021-12-18审判B1872020-06-182021-12-18审判

使用row_number()和filter

演示你的数据示例:

with my_table as (--data example, use your table instead of this CTE            
select 'A' user_id, 123 subscription_id, '2021-01-01' start_date, '9999-01-01' end_date, 'Premium' plan union all
select 'B', 122, '2021-02-03', '9999-03-04', 'Regular' union all
select 'A', 144, '2021-02-01', '9999-01-01', 'Premium' union all
select 'A', 155, '2021-03-01', '9999-01-01', 'Premium' union all
select 'B', 167, '2021-03-03', '9999-03-04', 'Regular' union all
select 'B', 111, '2020-05-18', '2021-12-18', 'Trial' union all
select 'B', 187, '2020-06-18', '2021-12-18', 'Trial'
)
select user_id, subscription_id, start_date, end_date, plan
from 
(
select user_id, subscription_id, start_date, end_date, plan,
--Row with min start_date will be assigned rn=1
row_number() over(partition by user_id, plan order by start_date) rn
from my_table
)s where rn=1

结果:

user_id subscription_id start_date  end_date    plan
A       123             2021-01-01  9999-01-01  Premium
B       122             2021-02-03  9999-03-04  Regular
B       111             2020-05-18  2021-12-18  Trial