我有一個如下所示的資料集。
| ID | 日期 |
|---|---|
| 100 | 2022-03-01 |
| 100 | 2022-02-10 |
| 100 | 2021-12-15 |
| 100 | 2021-11-30 |
| 200 | 2021-08-05 |
| 200 | 2021-07-10 |
對于特定的 ID，我需要比較前幾行以檢查它們是否存在于前幾個月。所以我的輸出表應該如下所示：
| ID | 日期 | 1個月前 | 2個月前 | 3個月前 |
|---|---|---|---|---|
| 100 | 2022-03-01 | 1 | 0 | 0 |
| 100 | 2022-02-10 | 0 | 1 | 1 |
| 100 | 2021-12-15 | 1 | 0 | 0 |
| 100 | 2021-11-30 | 0 | 0 | 1 |
| 200 | 2021-08-05 | 1 | 0 | 0 |
| 200 | 2021-07-10 | 0 | 0 | 0 |
我可以使用 LAG 功能，但我認為之前只適用于一排。對于一個特定的 ID，如果該 ID 存在與否，我需要回顧 3 個月。不需要做任何天數計算，一個月就可以了。
有什么方法可以使用 sql 實作這一點嗎？感謝您對此的任何幫助。謝謝。
uj5u.com熱心網友回復：
以下僅基于月份計算 -
實際表 -
select * from PREV_MONTH;
----- ------------
| ID | DT_DATE |
|----- ------------|
| 100 | 2022-03-01 |
| 100 | 2022-02-10 |
| 100 | 2021-12-15 |
| 100 | 2021-11-30 |
| 200 | 2021-08-05 |
| 200 | 2021-07-10 |
----- ------------
查詢以繪制所需的結果 -
select id,dt_date,
CASE
when extract(month from add_months(dt_date,-1))
IN
(select extract (month from dt_date) FROM
prev_month pm1 where pm1.id=prev_month.id)
then 1
else 0 end as month_1,
CASE
when extract(month from add_months(dt_date,-2))
IN
(select extract (month from dt_date) FROM
prev_month pm1 where pm1.id=prev_month.id)
then 1
else 0 end as month_2,
CASE
when extract(month from add_months(dt_date,-3))
IN
(select extract (month from dt_date) FROM
prev_month pm1 where pm1.id=prev_month.id)
then 1
else 0 end as month_3
from prev_month;
----- ------------ --------- --------- ---------
| ID | DT_DATE | MONTH_1 | MONTH_2 | MONTH_3 |
|----- ------------ --------- --------- ---------|
| 100 | 2022-03-01 | 1 | 0 | 1 |
| 100 | 2022-02-10 | 0 | 1 | 1 |
| 100 | 2021-12-15 | 1 | 0 | 0 |
| 100 | 2021-11-30 | 0 | 0 | 0 |
| 200 | 2021-08-05 | 1 | 0 | 0 |
| 200 | 2021-07-10 | 0 | 0 | 0 |
----- ------------ --------- --------- ---------
uj5u.com熱心網友回復：
因此，使用此 CTE 資料：
with data(ID, DT_DATE) as (
SELECT * FROM VALUES
(100, '2022-03-01'),
(100, '2022-02-10'),
(100, '2021-12-15'),
(100, '2021-11-30'),
(200, '2021-08-05'),
(200, '2021-07-10')
)
每個日期的上個月視窗：
如果您想知道“從該日期開始的 1、2、3 個月的視窗中有 1 個以上的值”，則此超級非高性能代碼有效。
SELECT
a.id
,a.dt_date
,count_if(b.dt_date >= dateadd('month',-1, a.dt_date) and b.dt_date < dateadd('month', 0, a.dt_date))::int as "1monthago"
,count_if(b.dt_date >= dateadd('month',-2, a.dt_date) and b.dt_date < dateadd('month', -1, a.dt_date))::int as "2monthago"
,count_if(b.dt_date >= dateadd('month',-3, a.dt_date) and b.dt_date < dateadd('month', -2, a.dt_date))::int as "3monthago"
FROM data AS a
LEFT JOIN data AS b
ON a.id = b.id AND b.dt_date < a.dt_date
GROUP BY 1,2
ORDER BY 1,2 desc;
| ID | DT_DATE | 1個月前 | 2個月前 | 3個月前 |
|---|---|---|---|---|
| 100 | 2022-03-01 | 1 | 0 | 1 |
| 100 | 2022-02-10 | 0 | 1 | 1 |
| 100 | 2021-12-15 | 1 | 0 | 0 |
| 100 | 2021-11-30 | 0 | 0 | 0 |
| 200 | 2021-08-05 | 1 | 0 | 0 |
| 200 | 2021-07-10 | 0 | 0 | 0 |
先前命名的月份：
SELECT
a.id
,a.dt_date
,count_if(date_trunc('month', dateadd('month',-1, a.dt_date)) = date_trunc('month', b.dt_date))::int as "1monthago"
,count_if(date_trunc('month', dateadd('month',-2, a.dt_date)) = date_trunc('month', b.dt_date))::int as "2monthago"
,count_if(date_trunc('month', dateadd('month',-3, a.dt_date)) = date_trunc('month', b.dt_date))::int as "3monthago"
FROM data AS a
LEFT JOIN data AS b
ON a.id = b.id AND b.dt_date < a.dt_date
GROUP BY 1,2
ORDER BY 1,2 desc;
| ID | DT_DATE | 1個月前 | 2個月前 | 3個月前 |
|---|---|---|---|---|
| 100 | 2022-03-01 | 1 | 0 | 1 |
| 100 | 2022-02-10 | 0 | 1 | 1 |
| 100 | 2021-12-15 | 1 | 0 | 0 |
| 100 | 2021-11-30 | 0 | 0 | 0 |
| 200 | 2021-08-05 | 1 | 0 | 0 |
| 200 | 2021-07-10 | 0 | 0 | 0 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448968.html
上一篇：將最新日期添加到資料透視查詢