17,086
社区成员
发帖
与我相关
我的任务
分享
WITH t (id,S,E) AS (
--借用#1的拆分
)
,t1 AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY S) rn
FROM t
)
,t2 AS (-- 用下一段的 S 剪切前一段的 E,去除重叠段落
SELECT ta.id,
ta.S,
CASE WHEN tb.S IS NULL THEN
ta.E
WHEN tb.S > ta.E THEN
ta.E
ELSE
tb.S-1
END E
FROM t1 ta
LEFT JOIN t1 tb
ON tb.id = ta.id
AND tb.rn = ta.rn+1
)
SELECT id,
SUM(E-S+1) days
FROM t2
GROUP BY id
with T as (
select id,
TO_DATE(SUBSTR(日期,1,INSTR(日期,'-')-1),'YYYY.MM.DD') AS S,
TO_DATE(SUBSTR(日期,INSTR(日期,'-')+1),'YYYY.MM.DD') AS E
FROM T
)
SELECT ID,SUM(greatest(E-greatest(S,PRE+1)+1,0)) 天数
FROM
(SELECT ID,S,E,LAG(E,1,S-1)OVER(PARTITION BY ID ORDER BY E) PRE
FROM T)
GROUP BY ID
ORDER BY ID