在拉链表中取用户每个月最后一天的状态

 

with data as (
    select 
        t1.*
        ,row_number() over(partition by id order by end_date desc) as rn
    from (
        select 1 as id,20 as status,'2020-03-28' as start_date,'2020-04-05' as end_date union all
        select 1 as id,10 as status,'2020-04-05' as start_date,'2020-04-20' as end_date union all
        select 1 as id,30 as status,'2020-04-21' as start_date,'2020-05-02' as end_date union all
        select 2 as id,20 as status,'2020-01-28' as start_date,'2020-04-05' as end_date
    ) t1
) -- 1. 测试数据
-- select * from data;
,cal as (
    select 
        month
        ,last_day(month) as month_last_day
        ,date_add(month, interval - day(month) + 1 day) as month_first_day
    from (
        select '2020-01-01' as month union all
        select '2020-02-01' as month union all
        select '2020-03-01' as month union all
        select '2020-04-01' as month union all
        select '2020-05-01' as month union all
        select '2020-06-01' as month union all
        select '2020-07-01' as month union all
        select '2020-08-01' as month union all
        select '2020-09-01' as month union all
        select '2020-10-01' as month union all
        select '2020-11-01' as month union all
        select '2020-12-01' as month
    ) t1
) -- 2. 日历
-- select * from cal;
,id_max_day as (
    select 
         id
        ,min(start_date) as start_date
        ,max(end_date) as end_date
    from data
    group by 
        id
) -- 3. 取出id的最大值和最小值
-- select * from id_max_day;
,id_month as (
    select
        t1.*,t2.month,t2.month_last_day,t2.month_first_day
    from id_max_day t1
    inner join cal t2
        on 1 = 1
    where t2.month >= DATE_ADD(t1.start_date,interval -day(t1.start_date)+1 day) -- 开始时间的当月第一天
        and t2.month <=  t1.end_date
) -- 4. 获取所有id的取数范围
-- select * from id_month order by id,month;
select
    -- t1.*,t2.*
     t1.id
    ,t1.status
    ,t2.month
from data t1
inner join id_month t2
    on t1.id = t2.id
where t1.start_date <= t2.month_last_day
    and 
        case 
        when t1.rn = 1 then last_day(t1.end_date) >= t2.month_last_day
        else t1.end_date >= t2.month_last_day
        end
order by t1.id,t2.month 
;

+----+--------+------------+------------+----+------------+------------+------------+----------------+-----------------+
| id | status | start_date | end_date   | id | start_date | end_date   | month      | month_last_day | month_first_day |
+----+--------+------------+------------+----+------------+------------+------------+----------------+-----------------+
|  1 |     20 | 2020-03-28 | 2020-04-05 |  1 | 2020-03-28 | 2020-05-02 | 2020-03-01 | 2020-03-31     | 2020-03-01      |
|  1 |     30 | 2020-04-21 | 2020-05-02 |  1 | 2020-03-28 | 2020-05-02 | 2020-04-01 | 2020-04-30     | 2020-04-01      |
|  2 |     20 | 2020-01-28 | 2020-04-05 |  2 | 2020-01-28 | 2020-04-05 | 2020-01-01 | 2020-01-31     | 2020-01-01      |
|  2 |     20 | 2020-01-28 | 2020-04-05 |  2 | 2020-01-28 | 2020-04-05 | 2020-02-01 | 2020-02-29     | 2020-02-01      |
|  2 |     20 | 2020-01-28 | 2020-04-05 |  2 | 2020-01-28 | 2020-04-05 | 2020-03-01 | 2020-03-31     | 2020-03-01      |
+----+--------+------------+------------+----+------------+------------+------------+----------------+-----------------+

-- 1. 测试数据
+----+--------+------------+------------+
| id | status | start_date | end_date   |
+----+--------+------------+------------+
|  1 |     20 | 2020-03-28 | 2020-04-05 | 2020-03-01
|  1 |     10 | 2020-04-05 | 2020-04-20 |
|  1 |     30 | 2020-04-21 | 2020-05-02 |
|  2 |     20 | 2020-01-28 | 2020-04-05 |
+----+--------+------------+------------+

+----+--------+------------+
| id | status | month      |
+----+--------+------------+
|  1 |     20 | 2020-03-01 |
|  1 |     30 | 2020-04-01 |
|  1 |     30 | 2020-05-01 |
|  2 |     20 | 2020-01-01 |
|  2 |     20 | 2020-02-01 |
|  2 |     20 | 2020-03-01 |
|  2 |     20 | 2020-04-01 |
+----+--------+------------+



-- 3. 取出id的最大值和最小值
+----+------------+------------+
| id | start_date | end_date   |
+----+------------+------------+
|  1 | 2020-03-28 | 2020-05-02 |
|  2 | 2020-01-28 | 2020-04-05 |
+----+------------+------------+

-- 4. 获取所有id的取数范围
+----+------------+------------+------------+
| id | start_date | end_date   | month      |
+----+------------+------------+------------+
|  1 | 2020-03-28 | 2020-05-02 | 2020-03-01 |
|  1 | 2020-03-28 | 2020-05-02 | 2020-04-01 |
|  1 | 2020-03-28 | 2020-05-02 | 2020-05-01 |
|  2 | 2020-01-28 | 2020-04-05 | 2020-01-01 |
|  2 | 2020-01-28 | 2020-04-05 | 2020-02-01 |
|  2 | 2020-01-28 | 2020-04-05 | 2020-03-01 |
|  2 | 2020-01-28 | 2020-04-05 | 2020-04-01 |
+----+------------+------------+------------+

-- 最终结果
+----+--------+------------+------------+----+------------+------------+------------+
| id | status | start_date | end_date   | id | start_date | end_date   | month      |
+----+--------+------------+------------+----+------------+------------+------------+
|  1 |     20 | 2020-03-28 | 2020-04-05 |  1 | 2020-03-28 | 2020-05-02 | 2020-04-01 |
|  1 |     30 | 2020-04-21 | 2020-05-02 |  1 | 2020-03-28 | 2020-05-02 | 2020-05-01 |
|  2 |     20 | 2020-01-28 | 2020-04-05 |  2 | 2020-01-28 | 2020-04-05 | 2020-02-01 |
|  2 |     20 | 2020-01-28 | 2020-04-05 |  2 | 2020-01-28 | 2020-04-05 | 2020-03-01 |
|  2 |     20 | 2020-01-28 | 2020-04-05 |  2 | 2020-01-28 | 2020-04-05 | 2020-04-01 |
+----+--------+------------+------------+----+------------+------------+------------+