SQL 中的分组空白填充

Question

我有一些分组的时间序列数据，有间隙。我需要用 null 填充这些空白，如果这些数据中没有组会非常简单(参见下面的示例)。我知道我可以通过左加入日历表来做到这一点，但无法弄清楚具体如何。

我的原始数据示例： 日期 - 可以是日，周，月，随便什么，在这里并不重要

date   | group   | data      |
-------+---------+-----------+
1      | a       | some_data |     
3      | a       | some_data |     
5      | b       | some_data |     
7      | b       | some_data |     
8      | b       | some_data |     
2      | c       | some_data |     
6      | c       | some_data | 

期望的结果：

date   | group   | data      |
-------+---------+-----------+
1      | a       | some_data |    
2      | a       |   null    |      
3      | a       | some_data |     
5      | b       | some_data |
6      | b       |   null    |          
7      | b       | some_data |     
8      | b       | some_data |     
2      | c       | some_data |
3      | c       |   null    |     
4      | c       |   null    |     
5      | c       |   null    |          
6      | c       | some_data | 

附言我知道使用 python 或其他任何东西很容易完成，但我真的需要只用 SQL 来完成

Answer 1

您可以使用一个RECURSIVE CTE来执行此操作，该返回每个group的所有可能的date，然后返回一个LEFT连接到表中：

with cte as (
select min(date) date, max(date) maxdate, `group`
from tablename
group by `group`
union all
select c.date + 1, maxdate, c.`group`
from cte c
where c.date < c.maxdate
)  
select c.date, c.`group`, t.data 
from cte c left join tablename t
on t.date = c.date and t.`group` = c.`group`
order by c.`group`, c.date

请参阅演示。
结果：

> date | group | data     
> ---: | :---- | :--------
>    1 | a     | some_data
>    2 | a     | null     
>    3 | a     | some_data
>    5 | b     | some_data
>    6 | b     | null     
>    7 | b     | some_data
>    8 | b     | some_data
>    2 | c     | some_data
>    3 | c     | null     
>    4 | c     | null     
>    5 | c     | null     
>    6 | c     | some_data