如何构建SQL查询按日期分组筛选符合时间范围的日志极值？

要满足你的需求，核心是先把日志按统计日期和时段拆分，再分别提取对应时段的极值，最后合并结果。直接用GROUP BY DATE(date)取全局min/max会把跨时段的记录混在一起，自然不符合要求。

核心思路 #

1. 定义统计日期：
   • 6:00-18:00的记录，统计日期为记录的自然日期（DATE(date)）
   • 18:00至次日6:00的记录，统计日期为记录的前一天日期（比如2024-05-21 03:00的记录，统计日期归为2024-05-20）
2. 分时段统计：
   • 对每个统计日期，单独提取6:00-18:00的最早记录
   • 对每个统计日期，单独提取18:00-次日6:00的最晚记录
3. 合并结果：将两个时段的统计结果按统计日期关联，得到完整的每日数据

方案一：子查询关联（适合简单场景） #

如果只需要时间字段和少量关联信息，用子查询直接统计即可：

SELECT
    COALESCE(d.stat_date, n.stat_date) AS 统计日期,
    d.最早日间日志时间,
    d.最早日志详情,
    n.最晚夜间日志时间,
    n.最晚日志详情
FROM
    -- 统计每日6:00-18:00的最早记录
    (
        SELECT
            DATE(date) AS stat_date,
            MIN(date) AS 最早日间日志时间,
            -- 这里可以替换成你需要的日志字段，比如用户ID、请求路径等
            (SELECT CONCAT('用户ID:', user_id, ' 请求路径:', request_path) FROM request_logs WHERE date = MIN(r.date)) AS 最早日志详情
        FROM request_logs r
        WHERE TIME(date) BETWEEN '06:00:00' AND '17:59:59'
        GROUP BY stat_date
    ) d
-- 关联夜间时段统计结果，用FULL OUTER JOIN保证某天无日间/夜间记录时仍显示日期
FULL OUTER JOIN
    -- 统计每日18:00至次日6:00的最晚记录
    (
        SELECT
            CASE
                WHEN TIME(date) >= '18:00:00' THEN DATE(date)
                ELSE DATE(date - INTERVAL 1 DAY)
            END AS stat_date,
            MAX(date) AS 最晚夜间日志时间,
            (SELECT CONCAT('用户ID:', user_id, ' 请求路径:', request_path) FROM request_logs WHERE date = MAX(r.date)) AS 最晚日志详情
        FROM request_logs r
        WHERE TIME(date) >= '18:00:00' OR TIME(date) <= '05:59:59'
        GROUP BY stat_date
    ) n
ON d.stat_date = n.stat_date
ORDER BY 统计日期;

方案二：窗口函数（适合需要完整日志字段的场景） #

如果需要获取整条日志的所有字段，用窗口函数给记录排秩，再筛选极值记录更灵活：

WITH 分类日志 AS (
    SELECT
        *,
        -- 计算每条日志归属的统计日期
        CASE
            WHEN TIME(date) BETWEEN '06:00:00' AND '17:59:59' THEN DATE(date)
            ELSE DATE(date - INTERVAL 1 DAY)
        END AS 统计日期,
        -- 标记日志所属时段
        CASE
            WHEN TIME(date) BETWEEN '06:00:00' AND '17:59:59' THEN '日间'
            ELSE '夜间'
        END AS 时段类型
    FROM request_logs
),
日间排秩 AS (
    SELECT
        *,
        -- 按统计日期分组，按时间升序排号，取第1条（最早）
        ROW_NUMBER() OVER (PARTITION BY 统计日期 ORDER BY date ASC) AS 排号
    FROM 分类日志
    WHERE 时段类型 = '日间'
),
夜间排秩 AS (
    SELECT
        *,
        -- 按统计日期分组，按时间降序排号，取第1条（最晚）
        ROW_NUMBER() OVER (PARTITION BY 统计日期 ORDER BY date DESC) AS 排号
    FROM 分类日志
    WHERE 时段类型 = '夜间'
)
SELECT
    COALESCE(d.统计日期, n.统计日期) AS 统计日期,
    -- 日间最早日志的所有字段
    d.date AS 最早日间日志时间,
    d.user_id AS 最早日志用户ID,
    d.request_path AS 最早日志请求路径,
    -- 夜间最晚日志的所有字段
    n.date AS 最晚夜间日志时间,
    n.user_id AS 最晚日志用户ID,
    n.request_path AS 最晚日志请求路径
FROM 日间排秩 d
FULL OUTER JOIN 夜间排秩 n
ON d.统计日期 = n.统计日期 AND d.排号 = 1 AND n.排号 = 1
WHERE d.排号 = 1 OR n.排号 = 1
ORDER BY 统计日期;

关键细节说明 #

• 时段边界处理：用17:59:59和05:59:59作为分界，避免18:00和06:00的记录被重复统计
• 空值处理：用COALESCE和FULL OUTER JOIN保证某天无日间/夜间记录时，统计日期仍会显示，对应字段为NULL
• 适配不同数据库：如果用MySQL，注意FULL OUTER JOIN需要用LEFT JOIN加RIGHT JOIN模拟，或者改用UNION ALL再聚合

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