WITH -- Sample data
tbl AS
( Select 1 "ID", To_Date('12.01.2023', 'dd.mm.yyyy') "ACT_DATE", 'ACTIVATION' "ACT_TYPE" From Dual UNION ALL
Select 2, To_Date('19.01.2023', 'dd.mm.yyyy'), 'ACTIVATION' From Dual UNION ALL
Select 3, To_Date('19.01.2023', 'dd.mm.yyyy'), 'ACTIVATION' From Dual UNION ALL
Select 4, To_Date('19.01.2023', 'dd.mm.yyyy'), 'ACTIVATION' From Dual UNION ALL
Select 5, To_Date('19.01.2023', 'dd.mm.yyyy'), 'ACTIVATION' From Dual UNION ALL
Select 1, To_Date('12.02.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 1, To_Date('31.01.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 3, To_Date('13.02.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 2, To_Date('17.02.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 6, To_Date('17.02.2023', 'dd.mm.yyyy'), 'ACTIVATION' From Dual UNION ALL
Select 7, To_Date('19.02.2023', 'dd.mm.yyyy'), 'ACTIVATION' From Dual UNION ALL
Select 1, To_Date('10.03.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 2, To_Date('12.03.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 6, To_Date('27.03.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual UNION ALL
Select 6, To_Date('10.04.2023', 'dd.mm.yyyy'), 'ACTIVITY' From Dual
),
ID ACT_DATE ACT_TYPE
---------- --------- ----------
1 12-JAN-23 ACTIVATION
2 19-JAN-23 ACTIVATION
3 19-JAN-23 ACTIVATION
4 19-JAN-23 ACTIVATION
5 19-JAN-23 ACTIVATION
1 12-FEB-23 ACTIVITY
1 31-JAN-23 ACTIVITY
3 13-FEB-23 ACTIVITY
2 17-FEB-23 ACTIVITY
6 17-FEB-23 ACTIVATION
7 19-FEB-23 ACTIVATION
1 10-MAR-23 ACTIVITY
2 12-MAR-23 ACTIVITY
6 27-MAR-23 ACTIVITY
6 10-APR-23 ACTIVITY
...然后你可以创建一个cte（命名为grid）来反映你预期结果的结构：
grid AS
( SELECT t1.ACTIVATION_MONTH, t1.RN_1, t2.ACT_MONTH, t2.RN_2
FROM ( Select To_Char(t.ACT_DATE, 'MON-yy') "ACTIVATION_MONTH",
To_Char(t.ACT_DATE, 'yyyymm') "RN_1"
From tbl t
Where ACT_TYPE = 'ACTIVATION'
Group By To_Char(t.ACT_DATE, 'MON-yy'), To_Char(t.ACT_DATE, 'yyyymm')
Order By To_Char(t.ACT_DATE, 'yyyymm')
) t1
LEFT JOIN ( Select To_Char(t.ACT_DATE, 'MON-yy') "ACT_MONTH",
To_Char(t.ACT_DATE, 'yyyymm') "RN_2"
From tbl t
Group By To_Char(t.ACT_DATE, 'MON-yy'), To_Char(t.ACT_DATE, 'yyyymm')
Order By To_Char(t.ACT_DATE, 'yyyymm')
) t2 ON(t2.RN_2 >= t1.RN_1)
)
--
-- grid result
ACTIVATION_MONTH RN_1 ACT_MONTH RN_2
---------------- ------ --------- ------
JAN-23 202301 JAN-23 202301
JAN-23 202301 FEB-23 202302
JAN-23 202301 MAR-23 202303
JAN-23 202301 APR-23 202304
FEB-23 202302 FEB-23 202302
FEB-23 202302 MAR-23 202303
FEB-23 202302 APR-23 202304
SELECT g.ACTIVATION_MONTH, g.ACT_MONTH,
CASE WHEN g.RN_1 = g.RN_2
THEN ( Select Count(Distinct ID)
From tbl
Where ACT_TYPE = 'ACTIVATION' And To_Char(ACT_DATE, 'yyyymm') = g.RN_1)
ELSE ( Select Count(Distinct ID)
From tbl
Where ACT_TYPE = 'ACTIVITY' And
To_Char(ACT_DATE, 'yyyymm') = g.RN_2 And
ID IN(SELECT ID FROM TBL WHERE ACT_TYPE = 'ACTIVATION' And To_Char(ACT_DATE, 'yyyymm') = g.RN_1))
END "ACTIVE_SUBS"
FROM grid g
--
-- R e s u l t :
ACTIVATION_MONTH ACT_MONTH ACTIVE_SUBS
---------------- --------- -----------
JAN-23 JAN-23 5
JAN-23 FEB-23 3
JAN-23 MAR-23 2
JAN-23 APR-23 0
FEB-23 FEB-23 2
FEB-23 MAR-23 1
FEB-23 APR-23 1
SELECT g.ACTIVATION_MONTH,
( Select Distinct LISTAGG(Distinct ID, ', ') WITHIN GROUP (Order By ID) OVER() "ID_LIST_ACTIVATED"
From tbl
Where ACT_TYPE = 'ACTIVATION' And To_Char(ACT_DATE, 'yyyymm') = g.RN_1) "ACTIVATED_IDS",
--
g.ACT_MONTH,
CASE WHEN g.RN_1 = g.RN_2
THEN ( Select Count(Distinct ID)
From tbl
Where ACT_TYPE = 'ACTIVATION' And To_Char(ACT_DATE, 'yyyymm') = g.RN_1)
ELSE ( Select Count(Distinct ID)
From tbl
Where ACT_TYPE = 'ACTIVITY' And
To_Char(ACT_DATE, 'yyyymm') = g.RN_2 And
ID IN(SELECT ID FROM TBL WHERE ACT_TYPE = 'ACTIVATION' And To_Char(ACT_DATE, 'yyyymm') = g.RN_1))
END || ' IDs' "ACTIVE_SUBS",
--
(Select Distinct LISTAGG(Distinct ID, ', ') WITHIN GROUP (Order By ID) OVER() "ID_LIST_ACTIVE"
From tbl
Where ACT_TYPE = 'ACTIVITY' And
To_Char(ACT_DATE, 'yyyymm') = g.RN_2 And
ID IN(SELECT ID FROM TBL WHERE ACT_TYPE = 'ACTIVATION' And To_Char(ACT_DATE, 'yyyymm') = g.RN_1)) "ACTIVE_IDS"
FROM grid g
--
-- R e s u l t :
ACTIVATION_MONTH ACTIVATED_IDS ACT_MONTH ACTIVE_SUBS ACTIVE_IDS
---------------- ------------- --------- ----------- --------------------
JAN-23 1, 2, 3, 4, 5 JAN-23 5 IDs 1
JAN-23 1, 2, 3, 4, 5 FEB-23 3 IDs 1, 2, 3
JAN-23 1, 2, 3, 4, 5 MAR-23 2 IDs 1, 2
JAN-23 1, 2, 3, 4, 5 APR-23 0 IDs
FEB-23 6, 7 FEB-23 2 IDs
FEB-23 6, 7 MAR-23 1 IDs 6
FEB-23 6, 7 APR-23 1 IDs 6
更新： 为了避免使用IN子句，您可以尝试创建另外两个cte-s，如下所示：
activations AS
( Select To_Char(ACT_DATE, 'yyyymm') "ACT_DATE", ID From tbl Where ACT_TYPE = 'ACTIVATION' ),
activities AS
( Select a.ACT_DATE "ACT_DATE_1", To_Char(a1.ACT_DATE, 'yyyymm') "ACT_DATE_2", a1.ID
From tbl a1
Left Join activations a ON(a.ID = a1.ID And a.ACT_DATE != To_Char(a1.ACT_DATE, 'yyyymm') )
Where a1.ACT_TYPE = 'ACTIVITY' )
...并将主SQL更改为：
SELECT g.ACTIVATION_MONTH, g.ACT_MONTH,
CASE WHEN g.RN_1 = g.RN_2
THEN (Select Count(ID) From activations Where ACT_DATE = g.RN_1)
ELSE (Select Count(Distinct ID) From activities Where ACT_DATE_1 = g.RN_1 And ACT_DATE_2 = g.RN_2)
END "ACTIVE_SUBS"
FROM grid g
Order By g.RN_1, g.RN_2
1条答案
按热度按时间kcrjzv8t1#
如果我得到了正确的，如果你的数据是（或可能是）类似于：
...然后你可以创建一个cte（命名为grid）来反映你预期结果的结构：
...现在你可以使用RN_1作为ACTIVATIONS条件生成器，使用RN_2（或两者）作为ACTIVITIES条件生成器。
主SQL
附加说明：
网格cte生成激活月份（JAN和FEB）的结构，然后是随后的月份，直到数据中的最后一个月份。
主SQL遵循网格结构，统计在月份内激活的ID，其中（RN_1 = RN_2 ===〉JAN = JAN或FEB = FEB），因此列ACTIVE_SUBS中的这些行显示激活的数量。其余的动作月份（具有相同的激活月份）显示在激活月份内激活的仍然有效的ID的数量。
也许一个激活和仍然激活的ID列表可以帮助。下面是相同的主SQL，其中新添加了两个列来显示列表。
更新：
为了避免使用IN子句，您可以尝试创建另外两个cte-s，如下所示：
...并将主SQL更改为：