使用date_trunc按小时分组后，统计连续3小时内各篮子的最多水果种类数

假设你的主表名为your_main_table，包含字段basket_id（篮子ID）、event_time（事件时间）、fruit_type（水果种类）。以下是针对不同场景的实现方案：

基础场景（篮子小时记录较完整） #

如果每个篮子的大部分小时都有水果记录，可直接使用滑动窗口统计连续3小时内的水果种类数，再取最大值：

WITH unique_hourly_fruits AS (
    -- 先去重：同一小时同一篮子的同一种水果只保留一条
    SELECT DISTINCT
        basket_id,
        date_trunc('hour', event_time) AS hour_start,
        fruit_type
    FROM your_main_table
),
rolling_counts AS (
    SELECT
        basket_id,
        -- 统计当前小时及前2小时的不同水果数量
        count(DISTINCT fruit_type) OVER (
            PARTITION BY basket_id
            ORDER BY hour_start
            RANGE BETWEEN INTERVAL '2 hours' PRECEDING AND CURRENT ROW
        ) AS rolling_3h_fruit_types
    FROM unique_hourly_fruits
)
SELECT
    basket_id,
    max(rolling_3h_fruit_types) AS max_3h_fruit_types
FROM rolling_counts
GROUP BY basket_id;

兼容缺失小时的场景 #

如果部分篮子存在无水果的小时（比如某小时没有任何水果记录），需要先生成每个篮子的连续小时序列，再关联数据统计，确保窗口是严格的连续3小时：

-- 1. 统计每个篮子的时间范围
WITH basket_time_ranges AS (
    SELECT
        basket_id,
        min(date_trunc('hour', event_time)) AS min_hour,
        max(date_trunc('hour', event_time)) AS max_hour
    FROM your_main_table
    GROUP BY basket_id
),
-- 2. 生成每个篮子的连续小时序列
basket_hours AS (
    SELECT
        btr.basket_id,
        generate_series(btr.min_hour, btr.max_hour, INTERVAL '1 hour') AS hour_start
    FROM basket_time_ranges btr
),
-- 3. 关联原始数据，填充水果种类（无水果则为NULL）
basket_hour_fruits AS (
    SELECT
        bh.basket_id,
        bh.hour_start,
        uf.fruit_type
    FROM basket_hours bh
    LEFT JOIN (
        SELECT DISTINCT basket_id, date_trunc('hour', event_time) AS hour_start, fruit_type
        FROM your_main_table
    ) uf 
        ON bh.basket_id = uf.basket_id 
        AND bh.hour_start = uf.hour_start
),
-- 4. 滑动窗口统计连续3小时的水果种类数
rolling_counts AS (
    SELECT
        basket_id,
        count(DISTINCT fruit_type) FILTER (WHERE fruit_type IS NOT NULL) OVER (
            PARTITION BY basket_id
            ORDER BY hour_start
            RANGE BETWEEN INTERVAL '2 hours' PRECEDING AND CURRENT ROW
        ) AS rolling_3h_fruit_types
    FROM basket_hour_fruits
)
-- 5. 取每个篮子的最大值
SELECT
    basket_id,
    max(rolling_3h_fruit_types) AS max_3h_fruit_types
FROM rolling_counts
GROUP BY basket_id;

针对不支持窗口内DISTINCT的数据库（如MySQL） #

如果使用MySQL这类不支持窗口函数中count(DISTINCT)的数据库，可通过时间戳转换+关联查询的方式实现：

WITH unique_hourly_fruits AS (
    SELECT DISTINCT
        basket_id,
        DATE_FORMAT(event_time, '%Y-%m-%d %H:00:00') AS hour_start,
        UNIX_TIMESTAMP(DATE_FORMAT(event_time, '%Y-%m-%d %H:00:00')) AS hour_unix,
        fruit_type
    FROM your_main_table
),
rolling_counts AS (
    SELECT
        uhf1.basket_id,
        uhf1.hour_start,
        -- 关联当前小时及前2小时的所有水果，去重后计数
        (SELECT COUNT(DISTINCT uhf2.fruit_type)
         FROM unique_hourly_fruits uhf2
         WHERE uhf2.basket_id = uhf1.basket_id
           AND uhf2.hour_unix BETWEEN uhf1.hour_unix - 7200 AND uhf1.hour_unix) AS rolling_3h_fruit_types
    FROM unique_hourly_fruits uhf1
)
SELECT
    basket_id,
    max(rolling_3h_fruit_types) AS max_3h_fruit_types
FROM rolling_counts
GROUP BY basket_id;

为什么lag/lead不适用？ #

lag()/lead()只能获取窗口中固定偏移的行，但连续3小时对应的行数不固定（比如某小时有多个水果记录），且如果存在缺失小时，偏移行数和实际小时数无法对应，因此滑动窗口的RANGE方式更适合这类时间区间统计需求。

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