对一列不连续的日期,如何对连续的部分生成区间
分类:SQL
数据如下:
('2023-07-01'), ('2023-07-02'), ('2023-07-03'), ('2023-07-06'), ('2023-07-07'), ('2023-07-10'), ('2023-07-11')
如何生成:
start_date | end_date
2023-07-01 | 2023-07-03
2023-07-06 | 2023-07-07
2023-07-10 | 2023-07-11
测试数据:
CREATE TABLE your_table (
date DATE
);
INSERT INTO your_table (date) VALUES
('2023-07-01'),
('2023-07-02'),
('2023-07-03'),
('2023-07-06'),
('2023-07-07'),
('2023-07-10'),
('2023-07-11');
示例代码:
SELECT
MIN(date) AS start_date,
MAX(date) AS end_date
FROM (
SELECT
date,
DATE_SUB(date, INTERVAL ROW_NUMBER() OVER (ORDER BY date) DAY) AS diff
FROM your_table
) t
GROUP BY diff;