英文:
How can we use CTE to handle cumulative date?
问题
我想在Postgres中编写一个递归的通用表达式（CTE），用于计算累积日期，是否可能以及如何实现？<br/>
非常感谢。
这里有一个示例
:
| 项目 | 记录日期 | 宽限期（天数） | 暂停日期 |
| ---- | ------------------------ | ------------------- | --------------- |
| A | 2022-12-14 00:00:00.000 | 30 | 2023-01-13 |
| B | 2022-12-29 00:00:00.000 | 30 | 2023-02-12 |
| C | 2023-06-02 08:40:14.933 | 30 | 2023-07-01 |
| D | 2023-06-02 08:54:48.080 | 30 | 2023-07-31 |
| E | 2023-06-03 06:42:42.077 | 30 | 2023-08-30 |
要求：
当前的暂停日期是由记录日期或上一条记录的暂停日期计算的
- 对于项目A，暂停日期是在记录日期之后的30天，即2023-01-13
- 对于项目B，由于项目A的最后暂停日期是2023-01-13，大于项目B的记录日期（项目A的暂停日期 > 项目B的记录日期），因此项目B的暂停日期将在项目A的暂停日期之后的30天计算，即2023-02-12
- 由于项目C的记录日期大于项目B的暂停日期，因此项目C的暂停日期将在项目C的记录日期之后的30天计算，即2023-07-01
英文:
I want to write a recursive common table expression (CTE) in postgres to calculate a cumulative date, is it possible and how? <br/>
Many thanks.
Here's an example
:
| Item | Recorded Date | Grace period (days) | Suspension Date |
| ---- | ------------------------ | ------------------- | --------------- |
| A | 2022-12-14 00:00:00.000 | 30 | 2023-01-13 |
| B | 2022-12-29 00:00:00.000 | 30 | 2023-02-12 |
| C | 2023-06-02 08:40:14.933 | 30 | 2023-07-01 |
| D | 2023-06-02 08:54:48.080 | 30 | 2023-07-31 |
| E | 2023-06-03 06:42:42.077 | 30 | 2023-08-30 |
Requirements:
The current suspension date is to calculated by Recorded Date or last record's suspension date
- For item A, suspension date is calculated by 30 days after Recorded Date is 2023-01-13
- For Item B, since last suspension date of Item A is 2023-01-13 which is larger than recorded date of Item B (ItemA’s suspension date > ItemB’s recorded date), then itemB’s suspension date would be calculated by 30 days after ItemA’s suspension date is 2023-02-12
- Since ItemC’s recorded date is larger than ItemB’s suspension date, then ItemC’s suspension date would be calculated by 30 days after ItemC’s recorded date is 2023-07-01
答案1
得分: 1
使用row_number()来识别按Recorded_Date排序的行的顺序。
递归CTE将如下所示：
with
cte as
(
select Item, Recorded_Date, Grace_Period,
rn = row_number() over (order by Recorded_Date)
from your_table
),
rcte as
(
-- 锚定成员
select Item, Recorded_Date, Grace_Period,
rn, Suspension_Date = dateadd(day, Grace_Period, Recorded_Date)
from cte
where rn = 1
union all
-- 递归成员
select c.Item, c.Recorded_Date, c.Grace_Period, c.rn,
Suspension_Date = case when r.Suspension_Date > c.Recorded_Date
then dateadd(day, c.Grace_Period, r.Suspension_Date)
else dateadd(day, c.Grace_Period, c.Recorded_Date)
end
from rcte r
inner join cte c on r.rn + 1 = c.rn
)
select *
from rcte
希望这对您有所帮助。
英文:
Use a row_number() to identify the sequence of rows in the ordering of Recorded_Date
The recursive CTE will looks like
with
cte as
(
select Item, Recorded_Date, Grace_Period,
rn = row_number() over (order by Recorded_Date)
from your_table
),
rcte as
(
-- anchor member
select Item, Recorded_Date, Grace_Period,
rn, Suspension_Date = dateadd(day, Grace_Period, Recorded_Date)
from cte
where rn = 1
union all
-- recursive member
select c.Item, c.Recorded_Date, c.Grace_Period, c.rn,
Suspension_Date = case when r.Suspension_Date > c.Recorded_Date
then dateadd(day, c.Grace_Period, r.Suspension_Date)
else dateadd(day, c.Grace_Period, c.Recorded_Date)
end
from rcte r
inner join cte c on r.rn + 1 = c.rn
)
select *
from rcte
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论