我想知道这是否可能,但我想我的措辞可能是我很难找到它的原因。
这是我的场景。
请参阅下面的选择结果?
| Column A | Column B | Column C |
| 001 | 09-10-20 | 09-11-20 |
| 001 | 09-11-20 | 09-16-20 |
| 001 | 09-16-20 | 10-20-20 |
| 001 | 10-20-20 | 11-11-20 |
| 001 | 11-12-20 | 11-13-20 |
| 001 | 11-14-20 | 11-16-20 |
| 001 | 11-16-20 | 11-20-20 |
| 001 | 11-20-20 | 11-21-20 |
有没有办法把它输出为v?
| Column A | Column B | Column C |
| 001 | 09-10-20 | 11-11-20 |
| 001 | 11-12-20 | 11-13-20 |
| 001 | 11-14-20 | 11-21-20 |
老实说,到目前为止,我认为这似乎不太可能只使用SQL,但我想我还是会问它
我已经看了,也确实看到了识别差距和重叠的方法,但我仍然无法理解如何用SQL来做到这一点。
通常情况下,我只需要SQL之外的另一段代码来筛选输出,并从表A中给我表B。但这需要额外的开销,我宁愿只学习如何在SQL中执行。
您可以使用lag()
和累积和来定义组。然后聚合:
select a, min(b), max(c)
from (select t.*,
sum(case when prev_c >= c then 0 else 1 end) over (partition by a order by b) as grp
from (select t.*,
lag(c) over (partition by a order by b) as prev_c
from t
) t
) t
group by a, grp;
这个问题是一个缺口和岛屿问题的例子。使用CCD_ 2和累积和来识别";"岛";是这些类型问题的一种解决方案。
SNODGRASS实现这一点的经典方法是:
WITH T
AS (SELECT F.[Column B], L.[Column C], F.[Column A]
FROM T_INTERVAL_ITV AS F
JOIN T_INTERVAL_ITV AS L
ON F.[Column C] <= L.[Column C]
AND F.[Column A] = L.[Column A]
INNER JOIN T_INTERVAL_ITV AS E
ON F.[Column A] = E.[Column A]
GROUP BY F.[Column B], L.[Column C], F.[Column A]
HAVING COUNT(CASE
WHEN (E.[Column B] < F.[Column B] AND F.[Column B] <= E.[Column C])
OR (E.[Column B] <= L.[Column C] AND L.[Column C] < E.[Column C])
THEN 1
END) = 0)
SELECT [Column A], [Column B], MIN([Column C]) AS [Column C]
FROM T
GROUP BY [Column A], [Column B];