问SQL创建一个新的日期列，以根据日期时间字段计算计数。

EN

-- 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
        )

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
*/

SELECT 
type,
TO_DATE(date, 'DD-MM-YYYY') as date,
count(date) AS count_of_dates
FROM table
GROUP BY type, date
ORDER BY type