sql - 刚刚在同一 CTE 中创建的访问列

Question

我有这样的数据。基本上是日期和时间。

DECLARE @sample table
(
    _date date,
    _time Time(0)
)

INSERT INTO @sample
VALUES
('2022-06-22', '09:00:00'),
('2022-06-22', '09:30:00'),
('2022-06-22', '10:00:00'),
('2022-06-22', '10:30:00'),
('2022-06-22', '11:00:00'),
('2022-06-23', '09:00:00'),
('2022-06-23', '09:30:00'),
('2022-06-23', '10:00:00');

然后我给它添加了行号。

WITH cte AS(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY _date ORDER BY _time) AS rn     
    FROM @sample    
)
SELECT *
FROM cte

之后的数据是这样的

| _date      | _time    | rn |
|------------|----------|----|
| 2022-06-22 | 09:00:00 | 1  |
| 2022-06-22 | 09:30:00 | 2  |
| 2022-06-22 | 10:00:00 | 3  |
| 2022-06-22 | 10:30:00 | 4  |
| 2022-06-22 | 11:00:00 | 5  |
| 2022-06-23 | 09:00:00 | 1  |
| 2022-06-23 | 09:30:00 | 2  |
| 2022-06-23 | 10:00:00 | 3  |

现在假设我想遍历每一行并修改 rn 列，每个 rn 本身就是最后一行的 rn。

| _date      | _time    | rn |
|------------|----------|----|
| 2022-06-22 | 09:00:00 | 1  |
| 2022-06-22 | 09:30:00 | 3  |
| 2022-06-22 | 10:00:00 | 6  |
| 2022-06-22 | 10:30:00 | 10 |
| 2022-06-22 | 11:00:00 | 15 |
| 2022-06-23 | 09:00:00 | 16 |
| 2022-06-23 | 09:30:00 | 18 |
| 2022-06-23 | 10:00:00 | 21 |

如何添加行号并在相同的 CTE 范围内执行此操作？

我知道我可能可以在之后立即使用另一个 CTE 并使用 LAG() 函数获取上一行并对 rn 列进行我想要的任何修改，但不知何故，我需要在一个 CTE 中执行此操作，解释起来有点太复杂了。

提前感谢您的帮助!

Answer 1

一个可能的选择是一个额外的窗口化 SUM()(我假设，行是按 _date 列排序的):

; WITH cte AS (
   SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY _date ORDER BY _time) AS rn
   FROM @sample
)
SELECT 
   _date, _time, 
   SUM(rn) OVER (ORDER BY _date, rn) AS rn
FROM cte