按日期创建产品基表

Question

我想创建一个查询，根据筛选器日期返回活动产品的数量。数据库中没有日历dim表。

当前表

Product_name|prod_id|start_date|end_date
P1|1234|02/01/2020|30/05/2020
P1|2345|02/01/2020|31/12/9999
P1|3456|03/01/2020|31/12/9999

预期的结果

Calander_date|product_name|active_base
01/01/2020|P1|0
02/01/2020|P1|2
03/01/2020|P1|3
01/06/2020|P1|2

Answer 1

创建您自己的日历，然后-要么在数据库中，要么作为一个"real"表(行生成器技术在这里有帮助)，或者作为CTE(如下例所示):

SQL> with
2  test (product_name, prod_id, start_date, end_date) as
3    -- you have that table; don't type that
4    (select 'P1', 1234, date '2020-01-02', date '2020-05-30' from dual union all
5     select 'P1', 2345, date '2020-01-02', date '9999-12-31' from dual union all
6     select 'P1', 3456, date '2020-01-03', date '9999-12-31' from dual
7    ),
8  calendar (datum) as
9    -- create your own calendar table
10    (select date '2020-01-01' + level - 1
11     from dual
12     connect by level <= 10000   --> number of days you want in calendar
13    )
14  -- final query - join!
15  select c.datum,
16    t.product_name,
17    count(*) active_base
18  from calendar c join test t on c.datum between t.start_date and t.end_date
19  group by c.datum, t.product_name
20  order by c.datum;
DATUM      PR ACTIVE_BASE
---------- -- -----------
02/01/2020 P1           2
03/01/2020 P1           3
04/01/2020 P1           3
05/01/2020 P1           3
06/01/2020 P1           3
<snip>
28/05/2020 P1           3
29/05/2020 P1           3
30/05/2020 P1           3
31/05/2020 P1           2
01/06/2020 P1           2
02/06/2020 P1           2
<snip>