SQL查询2021年1月1日-10月31日连续3天温度低于0的城市明细

我们可以通过SQL窗口函数实现需求，以下是主流数据库通用的写法，假设你的表名为city_temperature，字段分别为City（城市名）、temperature（温度值）、day（日期类型）：

方法1：LAG/LEAD偏移法（适合固定连续3天的场景，写法简单） #

WITH filtered_data AS (
    -- 第一步：筛选指定日期范围内的所有数据
    SELECT 
        City,
        temperature,
        day
    FROM city_temperature
    WHERE day BETWEEN '2021-01-01' AND '2021-10-31'
),
flag_data AS (
    -- 第二步：对每个城市按日期排序，标记当前行是否属于连续3天低于0的区间
    SELECT 
        *,
        CASE WHEN 
            -- 当前行为连续3天的中间行
            temperature < 0 
            AND LAG(temperature,1) OVER (PARTITION BY City ORDER BY day) <0
            AND LEAD(temperature,1) OVER (PARTITION BY City ORDER BY day) <0
            THEN 1
            -- 当前行为连续3天的第一天
            WHEN 
                temperature <0
                AND LEAD(temperature,1) OVER (PARTITION BY City ORDER BY day) <0
                AND LEAD(temperature,2) OVER (PARTITION BY City ORDER BY day) <0
                THEN 1
            -- 当前行为连续3天的最后一天
            WHEN 
                temperature <0
                AND LAG(temperature,1) OVER (PARTITION BY City ORDER BY day) <0
                AND LAG(temperature,2) OVER (PARTITION BY City ORDER BY day) <0
                THEN 1
            ELSE 0 
        END AS is_valid
    FROM filtered_data
)
-- 第三步：提取所有符合条件的记录，按城市、日期排序
SELECT City, temperature, day
FROM flag_data
WHERE is_valid = 1
ORDER BY City, day;

方法2：岛屿分组法（支持任意连续天数扩展，性能更优） #

WITH filtered_data AS (
    SELECT 
        City,
        temperature,
        day,
        -- 对每个城市累计温度≥0的记录数，作为连续低温区间的分组ID
        SUM(CASE WHEN temperature >=0 THEN 1 ELSE 0 END) OVER (PARTITION BY City ORDER BY day) AS group_id
    FROM city_temperature
    WHERE day BETWEEN '2021-01-01' AND '2021-10-31'
),
group_stats AS (
    -- 统计每个低温连续区间的天数
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY City, group_id) AS consecutive_days
    FROM filtered_data
    WHERE temperature < 0
)
-- 筛选出连续天数≥3的区间的所有记录
SELECT City, temperature, day
FROM group_stats
WHERE consecutive_days >=3
ORDER BY City, day;

注意事项 #

• 如果你的day字段存储的是字符串格式，需要先转换为日期类型再做比较和排序，例如MySQL用STR_TO_DATE(day, '%d/%m/%Y')，PostgreSQL用TO_DATE(day, 'DD/MM/YYYY')
• 如果存在同一个城市同一天有多条记录的情况，需要先按城市、日期做去重聚合，取当日平均/最低温度再做计算

内容的提问来源于stack exchange，提问作者Vetri N