SQL来查找一系列更改的窗口中的总天数的总和

Question

以下是表格：

id

start_date	recorded_date
2021-11-10	2021-11-01	1a
2021-11-08	2021-11-02	1a
2021-11-11	2021-11-03	1a
2021-11-10	2021-11-04	1a
2021-11-10	2021-11-05	1a

您可以使用窗口函数lead来获得行之间的差异，然后按id 进行分组

select id, sum(daydiff) Agg_Change
from (
select id, abs(datediff(day, start_Date, lead(start_date,1,start_date) over (partition by id order by recorded_date))) as daydiff
from tablename
) t group by id 

它实际上是使用LAG()来获取OLAP查询中的前一个日期，而外部查询则获取绝对日期差及其总和，按id:分组

WITH
-- your input - don't use in real query ...
indata(start_date,recorded_date,id) AS (
SELECT DATE '2021-11-10',DATE '2021-11-01','1a'
UNION ALL SELECT DATE '2021-11-08',DATE '2021-11-02','1a'
UNION ALL SELECT DATE '2021-11-11',DATE '2021-11-03','1a'
UNION ALL SELECT DATE '2021-11-10',DATE '2021-11-04','1a'
UNION ALL SELECT DATE '2021-11-10',DATE '2021-11-05','1a'
)
-- real query starts here, replace following comma with "WITH" ...
,
w_lag AS (
SELECT
id
, start_date
, LAG(start_date) OVER w AS prevdt
FROM indata
WINDOW w AS (PARTITION BY id ORDER BY recorded_date)
)
SELECT
id
, SUM(ABS(DATEDIFF(DAY,start_date,prevdt))) AS dtdiff
FROM w_lag
GROUP BY id
-- out  id | dtdiff 
-- out ----+--------
-- out  1a |      6

我原以为滞后函数会给我答案，但它一直给我错误的答案，因为我在一个地方有错误的逻辑。我有我需要的答案：

with cte as(
select id, start_date, recorded_date,
row_number() over(partition by id order by recorded_date asc) as idrank,
lag(start_date,1) over(partition by id order by recorded_date asc) as prev
from table_temp
)
select id, sum(abs(date(start_date) - date(prev))) as Agg_Change
from cte
group by 1

如果有人有更好的解决方案，请告诉我。