我有一个表A,其中有一个与帐户关联的注册时间,每个id只能有一个条目。
对于表A中存在的所有ID,将在表B中有类似状态的条目,如下所示
预期转换表
对于表A中的每个ID如果有否对应一个ID条目存在于已注册的时候的月表B中-想要将其归类为新的该报告的一个月。同样,如果表B的后续月份中没有对应的ID条目,则希望将其分类为New。
例如:
ID 111 ~ Registered in November 2020 => Table B has no entries for ID 111 => Transformed table has an entry for ID 111 for the november month with state New.
ID 112 ~ Registered in November 2020 => Table B has entries for ID 112 in the month of November => Transformed table has no entry for ID 112
ID 113 ~ Registered in November 2020 => Table B has entries for ID 113 starting December => Transformed table has an entry for ID 113 for the november month with state New.
ID 114 ~ Registered in November 2020 => Table B has entries for ID 114 starting Feb 2021 => Transformed table has an entry for ID 114 for the months of november,December,Jan month with state New.
If I am following this correctly, you can use generate_series()
and a lateral join:
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)
The trick lies in the generation of the argument to generate_series()
: if there is at least one entry available in b
, we generate the date series from the beginning of the month of the registered time of a
until the prior month to the earliest date in b
; in case there is a date in b
in the same month as in a
, this generates an empty range, and the original row is filtered out. Else, we fall back on the registered time as end of range (which generates a range made of a single date).
id | 州| 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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句