查詢以查找客戶在指定間隔之間擁有有效卡的“天數”

我有一個具有以下結構的表：

create table test_table
(
 customer_num      NUMBER,
 card_number       char(2),
 card_issue_date   DATE,
 card_expire_date  DATE
)

範例數據是：

customer_num    |  card_number   |  card_issue_date | card_expire_date
--------------------------------------------------------------------------
 1             |  x             |  1/1/2019        | 1/4/2020
 1             |  y             |  1/1/2020        | 1/4/2020
 1             |  z             |  1/11/2020       | 1/15/2020 
 1             |  b             |  1/11/2020       | 1/20/2020
 1             |  w             |  1/12/2020       | 1/17/2020
 1             |  a             |  1/18/2020       | 2/12/2020

我有一個具有以下結構的日曆表：

create table dimdate
(
 datekey   DATE,
 <other columns related to date>
)

上表包含所有日期。

對於每一個custoemr_num ，我們需要找到total number of days他在“2020 年 1 月 1 日”和“2020 年 1 月 31 日”之間有一張活動卡。我們需要在這裡考慮一些要點：

1）我們需要注意的是，對於某些卡，例如卡號x和aissue_date 可能小於 ‘1/1/2020’ 或大於 ‘1/31/2020’。

2）我們要注意overlaps。不同卡 (b,w,z,a) 的 issue_date 和 expire_date 之間可能存在重疊。

首先我們認為我們可以計算每張卡的活躍天數，然後從最終結果中減去重疊，

到目前為止我們寫的是這樣的：

select t.customer_num,
      t.card_number,
      t.card_issue_date,
      t.card_expire_date,
      lead(t.card_issue_date) over(partition by t.customer_num order by t.card_issue_date) next_card_issue_date,
      case
        when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') and
             t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
         to_date('1/31/2020', 'mm/dd/yyyy') -
         to_date('1/1/2020', 'mm/dd/yyyy')
      
        when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') then
         t.card_expire_date - to_date('1/1/2020', 'mm/dd/yyyy')
      
        when t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
         to_date('1/31/2020', 'mm/dd/yyyy') - t.card_issue_date
      
        else
         t.card_expire_date - t.card_issue_date
      
      end as card_active_days

 from test_table t
where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
  and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')

但我們無法處理overlaps。我想知道你是否可以在這裡幫助我。

提前致謝

我想我已經找到了一個很好的解決方案。你能告訴我除了這個之外還有沒有更好的方法？

select a.customer_num, count(distinct b.datekey) num_of_days
 from (select t.customer_num,
              t.card_number,
              t.card_issue_date,
              t.card_expire_date
         from test_table t
        where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
          and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')) a
inner join dimdate b
   on b.datekey between a.card_issue_date and a.card_expire_date
  and b.datekey between to_date('1/1/2020', 'mm/dd/yyyy') and
      to_date('1/31/2020', 'mm/dd/yyyy')
group by a.customer_num

引用自：https://dba.stackexchange.com/questions/285027