在 SQL 中重用多个日期的值

Question

>我有一个看起来像这样的表格

ID            Type               Change_Date               
1              t1                2015-10-08
1              t2                2016-01-03
1              t3                2016-03-07
2              t1                2017-12-13
2              t2                2018-02-01

它显示客户是否更改了帐户类型以及更改了时间。但是，我想要一个可以给我以下输出的查询

ID            Type               Change_Date               
1              t1                2015-10
1              t1                2015-11
1              t1                2015-12
1              t2                2016-01
1              t2                2016-02
1              t3                2016-03
1              t3                2016-04
...            ...               ...
1              t3                2018-10

对于每个ID.输出显示客户在当月之前每个月拥有的帐户类型。我的问题是填写"空"月份。在某些情况下，帐户更改之间的间隔可能超过一年。

我希望这是有道理的。

提前谢谢。

Answer 1

基于 Presto SQL(因为你的起源问题是关于 Presto/SQL(

---

2018-11-01 更新：使用lead()简化 SQL

---

准备数据

表mytable与您相同

id  type  update_date
1   t1    2015-10-08
1   t2    2016-01-03
1   t3    2016-03-07
2   t1    2017-12-13
2   t2    2018-02-01

表t_month是一个字典表，其中包含从2015-01到2019-12的所有月份数据。这种字典表很有用。

ym
2015-01
2015-02
2015-03
2015-04
2015-05
2015-06
2015-07
2015-08
2015-09
...
2019-12

---

延长mytable的使用寿命

通常，您应该"管理"您的数据，就像它们的生命周期一样。所以mytable应该喜欢

id  type   start_date      end_date
1   t1     2015-10-08      2016-01-03
1   t2     2016-01-03      2016-03-07
1   t3     2016-03-07      null
2   t1     2017-12-13      2018-02-01
2   t2     2018-02-01      null

但在这种情况下，你没有。所以下一步是"创建"一个。使用lead()窗口功能。

select 
id,
type, 
date_format(update_date, '%Y-%m') as start_month,
lead(
date_format(update_date, '%Y-%m'), 
1, -- next one
date_format(current_date+interval '1' month, '%Y-%m') -- if null return next month
) over(partition by id order by update_date) as end_month
from mytable

输出

id  type  start_month  end_month
1   t1    2015-10     2016-01
1   t2    2016-01     2016-03
1   t3    2016-03     2018-11
2   t1    2017-12     2018-02
2   t2    2018-02     2018-11

---

交叉联接id和month

很简单

with id_month as (
select * from t_month 
cross join (select distinct id from mytable)
)
select * from id_month

输出

ym      id
2015-01 1
2015-02 1
2015-03 1
...
2019-12 1
2015-01 2
2015-02 2
2015-03 2
...
2019-12 2

---

最后

现在，您可以在select子句中使用subquery

select 
id,
type,
ym
from (
select
t1.id,
t1.ym,
(select type from mytable2 where t1.id = id and t1.ym >= start_month and t1.ym < end_month) as type
from id_month t1
)
where type is not null
-- order by id, ym

---

完整 sql

with mytable2 as (
select 
id,
type, 
date_format(update_date, '%Y-%m') as start_month,
lead(
date_format(update_date, '%Y-%m'), 
1, -- next one
date_format(current_date+interval '1' month, '%Y-%m') -- if null return next month
) over(partition by id order by update_date) as end_month
from mytable
)
, id_month as (
select * from t_month 
cross join (select distinct id from mytable)
)
select 
id,
type,
ym
from (
select
t1.id,
t1.ym,
(select type from mytable2 where t1.id = id and t1.ym >= start_month and t1.ym < end_month) as type
from id_month t1
)
where type is not null
--order by id, ym

输出

id  type  ym
1   t1    2015-10
1   t1    2015-11
1   t1    2015-12
1   t2    2016-01
1   t2    2016-02
1   t3    2016-03
1   t3    2016-04
...
1   t3    2018-10
2   t1    2017-12
2   t1    2018-01
2   t2    2018-02
...
2   t2    2018-10