英文:
How can I capture date intervals dynamically using recursion or another method in Redshift?
问题
我在Redshift中有一个公共表达式(CTE):
select
cast('2021-03-31' as date) as contract_start_date,
cast('2025-03-30' as date) as contract_end_date,
datediff(month, '2021-03-31', '2025-03-30') as number_of_months,
'abc' as client_id;
我想要生成一个长表格,捕捉从contract_start_date到contract_end_date每六个月一次的日期。代码的输出应该如下所示:
client_id date_interval
abc 9-30-21
abc 3-30-22
abc 9-30-22
abc 3-30-23
abc 9-30-23
abc 3-30-24
abc 9-30-24
abc 3-30-25
我知道可以使用dateadd函数在不同的CTE中硬编码这个,但是合同期限可能会有很大的变化。我更喜欢动态代码,从enddate开始,迭代性地减去6个月,直到剩下0个月,每次迭代都将日期捕捉到单独的行中。这是否可以通过递归来实现?如果在Redshift中太复杂,我可能可以在Python中完成,但那是最后的选择,性能会受到影响。
英文:
I have this in a CTE in Redshift:
select
cast('2021-03-31' as date) as contract_start_date,
cast('2025-03-30' as date) as contract_end_date,
datediff(month, '2021-03-31', '2025-03-30') as number_of_months,
'abc' as client_id;
I want to output a long table that captures the date every six months starting from the contract_start_date to the contract_end_date. The output of the code should look like this:
client_id date_interval
abc 9-30-21
abc 3-30-22
abc 9-30-22
abc 3-30-23
abc 9-30-23
abc 3-30-24
abc 9-30-24
abc 3-30-25
I know that I can hard code this using dateadd functions in separate CTEs, but contract durations can vary widely. I prefer to have dynamic code that, starting with the enddate, subtracts 6 months iteratively until 0 months remain, capturing each date in a separate row with each iteration. Can this be done recursively? If this is too complicated in Redshift, I can probably do it in Python, but that is a last resort and performance will suffer.
答案1
得分: 1
Redshift最近添加了对递归查询的支持: 喜闻乐见!
我认为您想要的逻辑是:
with recursive cte (client_id, date_interval, end_date) as (
select client_id, contract_start_date from mytable
union all
select client_id, dateadd(month, 3, date_interval), end_date
from cte
where date_interval < end_date
)
select client_id, date_interval from cte
如果您喜欢,您可以在查询的锚点部分硬编码值,而不是使用select ... from mytable
(正如您在示例中所示)。
如果合同结束日期与开始日期(模三个月)不完全匹配,您可能需要调整递归成员的where
子句。
英文:
Redshift recently added support for recursive queries: yay!
I think the logic you want is:
with recursive cte (client_id, date_interval, end_date) as (
select client_id, contract_start_date from mytable
union all
select client_id, dateadd(month, 3, date_interval), end_date
from cte
where date_interval < end_date
)
select client_id, date_interval from cte
You can hardcode the values in the anchor of the query rather than select ... from mytable
if you prefer (as shown in your example).
If the contract end dates do not exactly align to the start dates (modulo three months), you might want to adapt the where
clause of the recursive member.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论