在几个月中找到天数，并计算一个月的活跃天 - 甲骨文

Question

我有一个表活动_calc。

  CREATE TABLE Activity_Calc ( 
  person_id   INTEGER NOT NULL PRIMARY KEY, 
  Activity_start_date DATE, 
  Activity_End_Date DATE
);
INSERT INTO Activity_Calc 
VALUES (123,TO_DATE( '05-23-2020', 'MM-DD-YYYY' ), TO_DATE( '11-19-2020', 'MM-DD-YYYY' ) );

INSERT INTO Activity_Calc 
VALUES (890,TO_DATE( '01-06-2021', 'MM-DD-YYYY' ), TO_DATE( '07-05-2021', 'MM-DD-YYYY' ) );

INSERT INTO Activity_Calc 
VALUES (231,TO_DATE( '02-14-2020', 'MM-DD-YYYY' ), TO_DATE( '08-12-2020', 'MM-DD-YYYY' ) );

Activity_start_date和Activity_end_date之间的差异始终为180天。 我需要创建一个类似于下面的表：

在这里T1表示最近一个月。例如，对于Person_ID 123，T1是11月，他在11月活跃了19天。因此，对于Person_ID 123，T1获得了价值19。依此类推。从11月开始的上个月，他活跃了8天。因此T7需要8。 对于很少的客户来说，这可能会发生，T7甚至不会出现。只有T1至T6就足够了。在那种情况下，T7取0。

Answer 1

您可以使用子句的Connect生成12个数字，然后将其视为几个月。然后加入您的表格并生成所需的行 -

WITH MONTHS AS(SELECT level months
                 FROM DUAL
              CONNECT BY LEVEL <= 12)
SELECT A.person_id,
       'T' || ROW_NUMBER() OVER(PARTITION BY person_id ORDER BY M.months) Period,
        CASE WHEN M.months = EXTRACT(MONTH FROM A.Activity_start_date)
                  THEN LAST_DAY(Activity_start_date) - Activity_start_date
             WHEN M.months = EXTRACT(MONTH FROM A.Activity_End_Date)
                  THEN EXTRACT(DAY FROM Activity_End_Date)
             ELSE EXTRACT(DAY FROM LAST_DAY(TO_DATE(M.months, 'MM'))) 
        END no_of_active_days, Activity_start_date
  FROM MONTHS M
  JOIN Activity_Calc A ON M.months BETWEEN EXTRACT(MONTH FROM A.Activity_start_date) 
                                       AND EXTRACT(MONTH FROM A.Activity_End_Date)
 ORDER BY person_id, M.months DESC;

Answer 2

您可以在此期间使用递归查询进行迭代，并找到每个月的开始和结束，然后计算天数：

WITH months (person_id, month_id, activity_start_date, start_date, end_date) AS (
  SELECT person_id,
         1,
         CAST(activity_start_date + 1 AS DATE),
         GREATEST(activity_start_date + 1, TRUNC(activity_end_date, 'MM')),
         activity_end_date
  FROM   activity_calc
UNION ALL
  SELECT person_id,
         month_id + 1,
         activity_start_date,
         GREATEST(activity_start_date, TRUNC(start_date - 1, 'MM')),
         start_date - 1
  FROM   months
  WHERE  start_date > activity_start_date
)
SEARCH DEPTH FIRST BY person_id SET order_id
SELECT person_id,
       'T' || month_id AS period,
       TO_CHAR(start_date, 'YYYY-MM') AS month,
       end_date - start_date + 1 AS no_of_active_days
FROM   months

对于示例数据：

CREATE TABLE Activity_Calc ( 
  person_id           NOT NULL PRIMARY KEY, 
  Activity_start_date , 
  Activity_End_Date
) AS
SELECT 890, DATE '2021-01-06', DATE '2021-07-05' FROM DUAL UNION ALL
SELECT 123, DATE '2020-05-23', DATE '2020-11-19' FROM DUAL UNION ALL
SELECT 231, DATE '2020-02-14', DATE '2020-08-12' FROM DUAL UNION ALL
SELECT 321, DATE '2021-12-10', DATE '2021-12-10' + INTERVAL '180' DAY(3) FROM DUAL;

输出：输出：

person_id	ofient	月	no_of_active_days
123	t1	2020-11	19
123	t2	2020-10	31
123	t3	2020-09	30
123	t4	2020-08	31
123	t5	2020-07	31
123	t6	2020-06	30
123	t7	2020-05	8
231	t1	2020-08	12
231	t2	2020-07	31
231	t3	2020-06	30
231	t4	2020-05	31
231	t5	2020-04	30
231	t6	2020-03	31
231	t7	2020-02	15
321	t1	2022-06	8
321	t2	2022-05	31
321	t3	2022-04	30
321	t4	2022-03	31
321	t5	2022-02	28
321	t6	2022-01	31
321	t7	2021-12	21
890	t1	2021-07	5
890	t2	2021-06	30
890	t3	2021-05	31
890	t4	2021-04	30
890	t5	2021-03	31
890	t6	2021-02	28
890	t7	2021-01	25

db＆lt;＆gt; fiddle 在这里

Answer 3

with gen as (
    select level-1 as n 
    from dual 
    connect by
        level<=1+(select max(Activity_End_Date-Activity_start_date) from Activity_Calc)
    )
select 
   person_id
  ,'T'||
    dense_rank()
      over(partition by person_id order by trunc(ac.Activity_start_date+v.n,'mm') desc) x
  ,count(*) no_of_Active_days
from Activity_Calc ac
    join gen v
        on ac.Activity_start_date+v.n<=ac.Activity_End_Date
group by person_id, trunc(ac.Activity_start_date+v.n,'mm')
order by 1,2;

NB：看起来您的T7不正确或您尚未详细解释为什么您不在第一个月的整个天数。

Answer 4

您可以以封闭的形式解决此问题 - 稍微约会算术（纸上的铅笔）可以节省代码中的大量计算。

请注意我对问题的解释（在问题下的评论中详细说明）：如果您包括开始日期和结束日期，则在示例数据中显示的时间为181天。更常见的惯例是，最后一个日期是排除：一个19-Nov-2020的“日期”是指午夜 一天的开始，所以该人是那天不活跃。 11月的活跃日子的计数应为18岁，而不是19岁。同样，如果结束日期为4月1日，那么该人在3月31日午夜不活动 - 因此，“上个月”活动的“上个月”因为那个人必须是三月，而不是四月。

如果“ 180天”是正确的，但是您必须包括结束日期，则必须排除开始日期 - 这是没有意义的。或您的期间是181天，而不是180天，如果您必须在计数中包括开始日期和结束日期。所有这些不同的惯例都将导致不同的结果，但是可以为任何一个调整查询。

因此，无论如何 - 这是可以适用于我的解释的查询。为了更容易遵循输出，我也包括了与您的“周期”相对应的几个月；您可以从中删除该列，如果需要，请选择。

with   g (lvl) as (select level from dual connect by level <= 7)
select person_id, 'T' || lvl as period,
       to_char(add_months(trunc(activity_end_date - 1, 'mm'), 1 - lvl),
                  'Mon-yyyy') as mth,          --  added for debugging
       greatest(0, 
           least   (activity_end_date, 
                    add_months(trunc(activity_end_date - 1, 'mm'), 2 - lvl)
                   )
         - greatest(add_months(trunc(activity_end_date - 1, 'mm'), 1 - lvl),
                    activity_start_date
                   )
       )   as no_of_active_days
from   activity_calc cross join g
order  by person_id, lvl
;

如果您的数据具有有意义的大小，则可能需要尝试不同的解决方案并比较执行速度。