sql - 考虑到报告月份另一个表中的条目，如何根据注册时间对记录进行分组？

Question

我有一个表 A 与一个帐户关联的注册时间，每个 id 只能有一个条目。

对于表 A 中存在的所有 Id，表 B 中将有如下所示的条目和状态

预期的转换表

对于表 A 中的每个 ID 如果表 B 中对于注册时间的月份没有对应的 ID 条目 - 希望将其分类为该报告月份的新条目。同样，如果在表 B 中的后续月份中没有对应的条目，则 ID 希望将它们分类为新的。

例如：

ID 111 ~ 2020 年 11 月注册 => 表 B 没有 ID 111 的条目 => 转换后的表在 11 月有一个 ID 111 的条目，状态为 New。
ID 112 ~ 2020 年 11 月注册 => 表 B 在 11 月有 ID 112 的条目 => 转换后的表没有 ID 112 的条目
ID 113 ~ 2020 年 11 月注册 => 表 B 从 12 月开始有 ID 113 的条目 = > 转换后的表在 11 月有一个 ID 为 113 的条目，状态为 New。
ID 114 ~ 于 2020 年 11 月注册 => 表 B 从 2021 年 2 月开始具有 ID 114 的条目 => 转换后的表在 11 月、12 月、1 月的月份具有 ID 114 的条目，状态为 New。

标签： sqlpostgresqldatetimedate-arithmeticlateral-join

Answer 1

如果我正确地遵循这一点，您可以使用generate_series()横向连接：

select a.id, 'new' state, s.dt
from tablea a
cross join lateral (
    select generate_series(
        date_trunc('month', a.registered_time), 
        coalesce(
            date_trunc('month', min(b.time)) - interval '1 month', 
            date_trunc('month', a.registered_time)
        ),
        '1 month'
    )
    from tableb b
    where b.id = a.id
) s(dt)

诀窍在于参数的生成generate_series()：如果至少有一个条目可用b，我们生成从注册时间的a月初到上个月到最早日期的日期序列b；如果有一个日期 inb与 in 同月a，则会生成一个空范围，并过滤掉原始行。否则，我们将注册时间作为范围结束（生成由单个日期组成的范围）。

DB Fiddle 上的演示：

编号 | 状态 | dt                 
--: | :---- | :-----------------
111 | 新 | 2020-11-01 00:00:00
113 | 新 | 2020-11-01 00:00:00
114 | 新 | 2020-11-01 00:00:00
114 | 新 | 2020-12-01 00:00:00
114 | 新 | 2021-01-01 00:00:00