只有一个日期列和重复条件的SQL岛
只有一个日期列和重复条件的SQL岛
提问于 2021-08-05 09:55:12
我来寻求帮助，我一直在尝试为一组记录生成孤岛，但我无法做到，我尝试了一些东西，比如滞后函数，在每条记录上生成行，然后生成孤岛，但没有得到正确的结果。
参考我尝试过的解决方案：Show Date Range in Custom Column - Gaps and Islands
我需要生成这个数据结构：
| ChartShiftWeekdayDetailId | IslandStartsOn | IslandEndsOn | NoLinkingReason |
| 3600 | 2021-06-25 | 2021-07-01 | 'Can't be .....'|
| 3600 | 2021-07-02 | 2021-07-14 | null |
| 3600 | 2021-07-15 | 2021-07-31 | 'Can't be .....'|
| 3600 | 2021-08-01 | 2021-08-05 | null |这是一个应该生成预期结果的记录的示例。
我已经达到了这个结果
但正如您所看到的，这不是预期的结果。
这是我尝试过的：
SELECT *,
CONCAT(NoLinkingReason,
CAST(DATEDIFF(DAY,
LAG(DateValue, 1, CAST(ClearedData.DateValue AS datetime) - 1) OVER (PARTITION BY ChartShiftWeekdayDetailId,
NoLinkingReason
ORDER BY DateValue),
DateValue) AS varchar(10))) AS NumDays
FROM ClearedData;我真的很感谢你的帮助。
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-08-05 11:46:18
在查看链接和其他答案后，很明显差距没有被正确定义。这个答案使用了我编造的一些数据。你可以试试这样的东西
with
gaps_cte as (
select *, case when lag(NoLinkingReason, 1, NoLinkingReason)
over (partition by ChartShiftWeekdayDetailId order by DateValue)<>NoLinkingReason then 1
when lag(NoLinkingReason, 1, NoLinkingReason)
over (partition by ChartShiftWeekdayDetailId order by DateValue) is null and NoLinkingReason is not null then 1
when lag(NoLinkingReason, 1, NoLinkingReason)
over (partition by ChartShiftWeekdayDetailId order by DateValue) is not null and NoLinkingReason is null then 1
else 0 end gap
from (values (3600, cast('2021-06-25' as date), 'Something'),
(3600, cast('2021-06-26' as date), 'Something'),
(3600, cast('2021-06-27' as date), 'Nothing'),
(3600, cast('2021-06-28' as date), 'Nothing'),
(3600, cast('2021-06-29' as date), null),
(3600, cast('2021-06-30' as date), Null),
(3600, cast('2021-07-01' as date), 'Something'),
(3600, cast('2021-07-02' as date), 'Something')
) v(ChartShiftWeekdayDetailId, DateValue, NoLinkingReason)),
grps_cte as (
select *, sum(gap) over (partition by ChartShiftWeekdayDetailId order by DateValue) grp
from gaps_cte)
select ChartShiftWeekdayDetailId,
min(DateVAlue) IslandStartsOn,
max(DateVAlue) IslandEndsOn,
max(NoLinkingReason) NoLinkingReason
from grps_cte
group by ChartShiftWeekdayDetailId, grp
order by min(DateVAlue);ChartShiftWeekdayDetailId IslandStartsOn IslandEndsOn NoLinkingReason
3600 2021-06-25 2021-06-26 Something
3600 2021-06-27 2021-06-28 Nothing
3600 2021-06-29 2021-06-30 NULL
3600 2021-07-01 2021-07-02 SomethingStack Overflow用户
发布于 2021-08-05 10:45:55
假设您有每天的数据(如示例数据所示)，您可以从日期中减去枚举值，得到特定组的常量。对于每个链接原因，枚举将是分开的：
select ChartShiftWeekdayDetailId, NoLinkingReason,
min(datevalue), max(datevalue)
from (select cd.*,
row_number() over (partition by ChartShiftWeekdayDetailId, NoLinkingReason order by datevalue) as seqnum
from ClearedData cd
) cd
group by datediff(day, -seqnum, datevalue)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68664405
复制相关文章
相似问题
腾讯云开发者
