-- SAMPLE DATA
WITH
tbl AS
(
Select 'A' "A_TYPE", 1 "SUBSCRIPTION_ID", To_Date('10.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", Null "INACTIVE_DATE" From Dual Union All
Select 'A' "A_TYPE", 2 "SUBSCRIPTION_ID", To_Date('11.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", To_Date('14.10.2022', 'dd.mm.yyyy') "INACTIVE_DATE" From Dual Union All
Select 'A' "A_TYPE", 3 "SUBSCRIPTION_ID", To_Date('12.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", To_Date('14.10.2022', 'dd.mm.yyyy') "INACTIVE_DATE" From Dual Union All
Select 'B' "A_TYPE", 4 "SUBSCRIPTION_ID", To_Date('13.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", Null "INACTIVE_DATE" From Dual Union All
Select 'B' "A_TYPE", 5 "SUBSCRIPTION_ID", To_Date('14.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", To_Date('18.10.2022', 'dd.mm.yyyy') "INACTIVE_DATE" From Dual Union All
Select 'B' "A_TYPE", 6 "SUBSCRIPTION_ID", To_Date('15.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", Null "INACTIVE_DATE" From Dual
),
dates AS
(
Select Distinct
t.A_TYPE "A_TYPE",
d.RANGE_DATE "RANGE_DATE"
From
(Select To_Date('09.10.2022', 'dd.mm.yyyy') + LEVEL - 1 "RANGE_DATE" From dual Connect By LEVEL <= 11) d
Left Join
tbl t ON(1 = 1)
Order By
t.A_TYPE,
d.RANGE_DATE
)
和主SQL:
SELECT
d.A_TYPE "A_TYPE",
d.RANGE_DATE "RANGE_DATE",
(Select Count(*) From tbl Where A_TYPE = d.A_TYPE And ACTIVE_DATE <= d.RANGE_DATE And Nvl(INACTIVE_DATE, To_Date('11.10.2062', 'dd.mm.yyyy')) > d.RANGE_DATE) "ACTIVE_COUNT"
FROM
dates d
这就是结果:
/*
A_TYPE RANGE_DATE ACTIVE_COUNT
------ ---------- ------------
A 09-OCT-22 0
A 10-OCT-22 1
A 11-OCT-22 2
A 12-OCT-22 3
A 13-OCT-22 3
A 14-OCT-22 1
A 15-OCT-22 1
A 16-OCT-22 1
A 17-OCT-22 1
A 18-OCT-22 1
A 19-OCT-22 1
B 09-OCT-22 0
B 10-OCT-22 0
B 11-OCT-22 0
B 12-OCT-22 0
B 13-OCT-22 1
B 14-OCT-22 2
B 15-OCT-22 3
B 16-OCT-22 3
B 17-OCT-22 3
B 18-OCT-22 2
B 19-OCT-22 2
* /
WITH data (type, subscription_id, is_active, dt) AS (
SELECT type, subscription_id, is_active, dt
FROM table_name
UNPIVOT (
dt FOR is_active IN (
active_date AS 1,
inactive_date AS 0
)
)
)
SELECT d.type,
c.day,
COUNT(CASE d.is_active WHEN 1 THEN 1 END) AS count_active,
COUNT(CASE d.is_active WHEN 0 THEN 1 END) AS count_inactive
FROM (
SELECT TRUNC(min_dt) + LEVEL - 1 AS day
FROM (
SELECT MIN(dt) AS min_dt,
MAX(dt) AS max_dt
FROM data
)
CONNECT BY TRUNC(min_dt) + LEVEL - 1 < max_dt
) c
LEFT OUTER JOIN data d
PARTITION BY (d.type)
ON (c.day <= d.dt AND d.dt < c.day + 1)
GROUP BY
d.type,
c.day
ORDER BY
d.type,
c.day
其中,对于示例数据:
CREATE TABLE table_name (type, subscription_id, active_date, inactive_date) AS
SELECT 'A', 1, DATE '2022-11-01', NULL FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2022-11-02', DATE '2022-11-10' FROM DUAL UNION ALL
SELECT 'A', 3, DATE '2022-11-03', NULL FROM DUAL UNION ALL
SELECT 'A', 4, DATE '2022-11-04', NULL FROM DUAL UNION ALL
SELECT 'B', 5, DATE '2022-11-05', NULL FROM DUAL UNION ALL
SELECT 'B', 6, DATE '2022-11-06', NULL FROM DUAL UNION ALL
SELECT 'B', 7, DATE '2022-11-07', DATE '2022-11-09' FROM DUAL UNION ALL
SELECT 'B', 8, DATE '2022-11-08', NULL FROM DUAL;
3条答案
按热度按时间wfauudbj1#
您可以这样做:
CTE若要产生A_TYPE的日期,请执行下列步骤:您可以在此定义范围起始日期(09.10.2022)以及您希望范围持续的天数(LEVEL〈= 11)
和主SQL:
这就是结果:
此致。
xmjla07d2#
你可以
UNPIVOT
你的两个日期列,然后使用一个PARTITION
艾德OUTER JOIN
来一个日历:其中,对于示例数据:
输出:
| 型号|日间|活动计数|计数_非活动|
| - -|- -|- -|- -|
| A级|2022年11月1日|一个|第0页|
| A级|2022年11月2日|一个|第0页|
| A级|2022年11月3日|一个|第0页|
| A级|2022年11月4日|一个|第0页|
| A级|2022年11月5日|第0页|第0页|
| A级|2022年11月6日|第0页|第0页|
| A级|2022年11月7日|第0页|第0页|
| A级|2022年11月8日|第0页|第0页|
| A级|2022年11月9日|第0页|第0页|
| B| 2022年11月1日|第0页|第0页|
| B| 2022年11月2日|第0页|第0页|
| B| 2022年11月3日|第0页|第0页|
| B| 2022年11月4日|第0页|第0页|
| B| 2022年11月5日|一个|第0页|
| B| 2022年11月6日|一个|第0页|
| B| 2022年11月7日|一个|第0页|
| B| 2022年11月8日|一个|第0页|
| B| 2022年11月9日|第0页|一个|
fiddle
jbose2ul3#