我目前正在對包含有關用戶訂閱狀態的每月資料的資料集進行一些作業，其中給定的行顯示用戶是否在給定月份（顯示為截斷日期）以以下格式訂閱：
------------ ------ ---------------------
| month | user | subscription_status |
------------ ------ ---------------------
| 2022-02-01 | 2345 | true |
| 2022-01-01 | 2345 | false |
| 2021-12-01 | 2345 | false |
| 2022-02-01 | 4578 | true |
| 2022-02-01 | 9548 | false |
| 2022-02-01 | 1212 | true |
|...... |
------------ ------ ---------------------
我想做的是查詢這些資料，但還會生成一些額外的布爾欄位，其值取決于給定用戶的前一個月訂閱資料，例如：
never_subscribed_ever（用戶每個月的訂閱狀態為假）never_subscribed_last_6_months（subscription_status 僅在過去 6 個月內為假）first_subscribed（subscription_status 第一次為真）resubscribed（subscription_status 上個月為假后為真）
給出一個表格，例如：
------------ ------ --------------------- ----------------------- -------------------------------- ------------------ --------------
| month | user | subscription_status | never_subscribed_ever | never_subscribed_last_6_months | first_subscribed | resubscribed |
------------ ------ --------------------- ----------------------- -------------------------------- ------------------ --------------
但是，恐怕我對如何最好地實作我計劃使用該lag函式以及在需要時if/之類的條件執行的邏輯感到非常迷茫。case
任何人都可以幫助我開始在用戶級別上查看每個欄位過去的各種時間框架來實作滯后邏輯的最佳方式嗎？
uj5u.com熱心網友回復：
其實這是一個很好的關于分析函式使用的小作業。
它們中的大多數都很簡單，只是因為never_subscribed_last_6_months 您需要應用視窗條款才能獲得最后 6 個月。
詢問
select
month,
user_id,
subscription_status,
case when
count(case when SUBSCRIPTION_STATUS = 'true' then 1 end)
over (partition by user_id order by month) = 0 then 'Y' end as never_subscribed_ever,
case when
count(case when SUBSCRIPTION_STATUS = 'true' then 1 end)
over (partition by user_id order by month
range between INTERVAL '6' MONTH preceding and current row) = 0 then 'Y' end as never_subscribed_last_6_months,
min(case when SUBSCRIPTION_STATUS = 'true' then month end) over (partition by user_id) as first_subscribed,
case when SUBSCRIPTION_STATUS = 'true' and
lag(SUBSCRIPTION_STATUS) over (partition by user_id order by MONTH) = 'false' then 'Y' end as resubscribed
from user_status
order by user_id, month desc;
樣本資料
create table user_status as
select date'2022-02-01' month, 2345 user_id, 'true' subscription_status from dual union all
select date'2022-01-01' month, 2345 user_id, 'false' subscription_status from dual union all
select date'2021-12-01' month, 2345 user_id, 'false' subscription_status from dual union all
select date'2022-02-01' month, 4578 user_id, 'true' subscription_status from dual union all
select date'2022-02-01' month, 9548 user_id, 'false' subscription_status from dual union all
select date'2022-02-01' month, 1212 user_id, 'true' subscription_status from dual;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432923.html