Oracle SQL-按月在两个日期之间选择用户 [英] Oracle SQL - Select users between two date by month

问题描述

我正在学习SQL，我想知道如何根据活跃日期的开始和结束日期(均为timestamp(6))按月选择活跃用户.我的桌子看起来像这样:

I am learning SQL and I was wondering how to select active users by month, depending on their starting and ending date (both timestamp(6)). My table looks like this:

Cust_Num | Start_Date | End_Date
       1 | 2018-01-01 | 2019-01-01
       2 | 2018-01-01 | NULL
       3 | 2019-01-01 | 2019-06-01
       4 | 2017-01-01 | 2019-03-01

因此，按月份统计活跃用户，我应该得到如下输出:

So, counting the active users by month, I should have an output like:

As of.     | Count
2018-06-01 | 3
...
2019-02-01 | 3
2019-07-01 | 1

到目前为止，我通过每月输入一次来进行手动操作:

So far, I do a manual operation by entering each month:

Select
    201906,
    count(distinct a.cust_num)
From
    active_users a
Where
    to_date(‘20190630’,’yyyymmdd) between a.start_date and nvl (a.end_date, ‘31-dec-9999)

union all

Select
    201905,
    count(distinct a.cust_num)
From
    active_users a
Where
    to_date(‘20190531’,’yyyymmdd) between a.start_date and nvl (a.end_date, ‘31-dec-9999)

union all
...

如果我想在120个月内进入10年，则不是非常优化和可持续.

Not very optimized and sustainable if I want to enter 10 years ao 120 months lol.

欢迎任何帮助.非常感谢！

Any help is welcome. Thanks a lot!

推荐答案

我创建了查询，该查询将给出从表中的最小开始日期到最大结束日期的所有月份的结果.

I have created the query which will give the result of all the months starting from the minimum start date in the table till maximum end date.

您可以通过在WHERE子句中添加一个条件来更改它.

You can change it using adding one condition in WHERE clause.

-- table creation

CREATE TABLE ACTIVE_USERS (CUST_NUM NUMBER, START_DATE DATE, END_DATE DATE)

-- data creation

INSERT INTO ACTIVE_USERS
SELECT * FROM 
(
SELECT 1, DATE '2018-01-01', DATE '2019-01-01' FROM DUAL UNION ALL
SELECT 2, DATE '2018-01-01', NULL              FROM DUAL UNION ALL
SELECT 3, DATE '2019-01-01', DATE '2019-06-01' FROM DUAL UNION ALL
SELECT 4, DATE '2017-01-01', DATE '2019-03-01' FROM DUAL
)

-- data in the actual table

SELECT * FROM ACTIVE_USERS ORDER BY CUST_NUM;

  CUST_NUM START_DATE END_DATE  
---------- ---------- ----------
         1 2018-01-01 2019-01-01
         2 2018-01-01           
         3 2019-01-01 2019-06-01
         4 2017-01-01 2019-03-01

查询以获取所需结果

WITH CTE ( START_DATE, END_DATE ) AS 
(
    SELECT
        ADD_MONTHS( START_DATE, LEVEL - 1 ),
        ADD_MONTHS( START_DATE, LEVEL ) - 1
    FROM
    (
        SELECT
            MIN( START_DATE ) AS START_DATE,
            MAX( END_DATE   ) AS END_DATE
        FROM
            ACTIVE_USERS
    )
    CONNECT BY LEVEL <= CEIL( MONTHS_BETWEEN( END_DATE, START_DATE ) ) + 1
) 
--
--
SELECT    
    C.START_DATE,
    COUNT(1) AS CNT
FROM
    CTE C
    JOIN ACTIVE_USERS D ON
    (
        C.END_DATE BETWEEN
            D.START_DATE
            AND
            CASE
                WHEN D.END_DATE IS NOT NULL THEN D.END_DATE
                ELSE C.END_DATE
            END
    )
GROUP BY
    C.START_DATE
ORDER BY
    C.START_DATE;

-输出-

START_DATE        CNT
---------- ----------
2017-01-01          1
2017-02-01          1
2017-03-01          1
2017-04-01          1
2017-05-01          1
2017-06-01          1
2017-07-01          1
2017-08-01          1
2017-09-01          1
2017-10-01          1
2017-11-01          1

START_DATE        CNT
---------- ----------
2017-12-01          1
2018-01-01          3
2018-02-01          3
2018-03-01          3
2018-04-01          3
2018-05-01          3
2018-06-01          3
2018-07-01          3
2018-08-01          3
2018-09-01          3
2018-10-01          3

START_DATE        CNT
---------- ----------
2018-11-01          3
2018-12-01          3
2019-01-01          3
2019-02-01          3
2019-03-01          2
2019-04-01          2
2019-05-01          2
2019-06-01          1

30 rows selected. 

干杯！

这篇关于Oracle SQL-按月在两个日期之间选择用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！