我如何使用递归或其他方法在Redshift中动态捕获日期间隔?

huangapple go评论54阅读模式
英文:

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 &lt; 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.

huangapple
  • 本文由 发表于 2023年6月16日 03:49:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76485088.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定