sql - Oracle SQL - 按月选择两个日期之间的用户

Question

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

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

所以，按月计算活跃用户，我应该得到如下输出:

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

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

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

如果我想进入 10 年或 120 个月大声笑，则不是很优化和可持续。

欢迎任何帮助。非常感谢!

Answer 1

此查询显示截至月底有效的活跃用户数。

工作原理:

1. 将每个输入行(具有 StartDate 和 EndDate 值)转换为代表时间点的两行活跃用户数递增(在 StartDate)和递减(在 EndDate)。我们需要将 NULL 转换为一个遥远的日期值，因为 NULL 值是在非 NULL 值之前而不是之后排序的:

   这使您的数据看起来像这样:

   OnThisDate   Change
   2018-01-01        1
   2019-01-01       -1
   2018-01-01        1
   9999-12-31       -1
   2019-01-01        1
   2019-06-01       -1
   2017-01-01        1
   2019-03-01       -1
   

2. 然后我们简单地SUM OVER Change 值(排序后)以获得截至该特定日期的活跃用户数:

   首先，按 OnThisDate 排序:

   OnThisDate   Change
   2017-01-01        1
   2018-01-01        1
   2018-01-01        1
   2019-01-01        1
   2019-01-01       -1
   2019-03-01       -1
   2019-06-01       -1
   9999-12-31       -1
   

   然后 SUM OVER:

   OnThisDate   ActiveCount
   2017-01-01             1
   2018-01-01             2
   2018-01-01             3
   2019-01-01             4
   2019-01-01             3
   2019-03-01             2
   2019-06-01             1
   9999-12-31             0
   

3. 然后我们按月PARTITION(不是分组!)行并按日期排序，这样我们就可以识别该月的最后一个 ActiveCount 行(这实际上发生在最外层查询的 WHERE 中，每个月使用 ROW_NUMBER() 和 COUNT() PARTITION):

   OnThisDate   ActiveCount    IsLastInMonth
   2017-01-01             1                1
   2018-01-01             2                0
   2018-01-01             3                1
   2019-01-01             4                0
   2019-01-01             3                1
   2019-03-01             2                1
   2019-06-01             1                1
   9999-12-31             0                1
   

4. 然后过滤 IsLastInMonth = 1(实际上，每个 PARTITION 中的 ROW_COUNT() = COUNT(*) ) 给我们最终的输出数据:

   At-end-of-month     Active-count
   2017-01                        1
   2018-01                        3
   2019-01                        3
   2019-03                        2
   2019-06                        1
   9999-12                        0
   

这确实会导致结果集中出现“间隙”，因为 At-end-of-month 列仅显示 Active-count 值实际发生变化的行而不是包括所有可能的日历月 - 但这是理想的(就我而言)，因为它排除了冗余数据。填补空白可以在您的应用程序代码中完成，只需为每个额外的月份重复输出行，直到它达到下一个 At-end-of-month 值。

这是在 SQL Server 上使用 T-SQL 的查询(我现在无法访问 Oracle)。这是我用来解决问题的 SQLFiddle:http://sqlfiddle.com/#!18/ad68b7/24

SELECT
  OtdYear,
  OtdMonth,
  ActiveCount
FROM
  (

    -- This query adds columns to indicate which row is the last-row-in-month ( where RowInMonth == RowsInMonth )
    SELECT
      OnThisDate,
      OtdYear,
      OtdMonth,
      ROW_NUMBER() OVER ( PARTITION BY OtdYear, OtdMonth ORDER BY OnThisDate ) AS RowInMonth,
      COUNT(*) OVER ( PARTITION BY OtdYear, OtdMonth ) AS RowsInMonth,
      ActiveCount
    FROM
      (
        SELECT
          OnThisDate,
          YEAR( OnThisDate ) AS OtdYear,
          MONTH( OnThisDate ) AS OtdMonth,
          SUM( [Change] ) OVER ( ORDER BY OnThisDate ASC ) AS ActiveCount
        FROM
          (
            SELECT
              StartDate AS [OnThisDate],
              1 AS [Change]
            FROM
              tbl

            UNION ALL

            SELECT
              ISNULL( EndDate, DATEFROMPARTS( 9999, 12, 31 ) ) AS [OnThisDate],
              -1 AS [Change]
            FROM
              tbl
          ) AS sq1
      ) AS sq2
  ) AS sq3
WHERE
  RowInMonth = RowsInMonth
ORDER BY
  OtdYear,
  OtdMonth

通过直接使用聚合函数和窗口函数而不是使用别名(如 OtdYear、ActiveCount、等)，但这会使查询更难理解。