我想创建一个SQlite查询来查找特定日期的条纹.
我有一张桌子:
| id | dt |
|---|---|
| 1 | 10/12/2023 |
| 2 | 11/12/2023 |
| 3 | 12/12/2023 |
| 4 | 16/12/2023 |
| 5 | 17/12/2023 |
| 6 | 25/12/2023 |
| 7 | 26/12/2023 |
例如，如果我查询以下数据:
09/12/2023-结果将是0
12/12/2023-结果将是3
11/12/2023-结果将是2
17/12/2023 -结果将是2
24/12/2023-结果将是0
26/12/2023 -结果将是2
你有一个gaps and islands分的问题.为了解决这个问题，我们需要通过唯一的id来枚举每一组连续的行.
我们可以使用窗口函数LAG()来获取前一行并计算连续日期之间的差值，然后对所产生的差值应用连续总和:
WITH cte AS (
SELECT *,
CASE WHEN julianday(dt)-julianday(LAG(dt, 1, dt) OVER (ORDER BY dt)) <= 1 THEN 0 ELSE 1 END AS date_diff
FROM mytable
WHERE dt <= '2023-12-26'
),
cte2 AS (
SELECT *, SUM(date_diff) OVER (ORDER BY dt) as grp
FROM cte
)
SELECT COUNT(*)
FROM cte2 c
INNER JOIN (
SELECT MAX(grp) as latest_grp, MAX(dt) as latest_dt
FROM cte2
) AS s ON s.latest_grp = c.grp AND s.latest_dt = '2023-12-26'