-- 统计指定日期的订单日志数据（UTC+0 转换为 UTC-5）
SELECT 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) AS LocalDate,
    ol.OperationType,
    ol.OperationResult,
    COUNT(*) AS RecordCount
FROM 
    order_logs ol
WHERE 
    -- 将UTC+0时间转换为UTC-5后，筛选指定日期范围
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) IN ('2026-04-24', '2026-05-11', '2026-05-12', '2026-05-13')
GROUP BY 
    LocalDate,
    ol.OperationType,
    ol.OperationResult
ORDER BY 
    LocalDate ASC,
    ol.OperationType ASC,
    ol.OperationResult ASC;

-- 如果需要按日期汇总总数，可以使用以下查询
SELECT 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) AS LocalDate,
    COUNT(*) AS TotalCount,
    SUM(CASE WHEN ol.OperationResult = '成功' THEN 1 ELSE 0 END) AS SuccessCount,
    SUM(CASE WHEN ol.OperationResult = '失败' THEN 1 ELSE 0 END) AS FailedCount
FROM 
    order_logs ol
WHERE 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) IN ('2026-04-24', '2026-05-11', '2026-05-12', '2026-05-13')
GROUP BY 
    LocalDate
ORDER BY 
    LocalDate ASC;

-- 按日期和小时时段（0-12、12-24）统计 - 透视表格式，便于对比
SELECT 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) AS LocalDate,
    ol.OperationType,
    -- 0-12小时时段统计
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 0 AND 11 THEN 1 ELSE 0 END) AS Period0_12_Total,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 0 AND 11 AND ol.OperationResult = '成功' THEN 1 ELSE 0 END) AS Period0_12_Success,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 0 AND 11 AND ol.OperationResult = '失败' THEN 1 ELSE 0 END) AS Period0_12_Failed,
    -- 12-24小时时段统计
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 12 AND 23 THEN 1 ELSE 0 END) AS Period12_24_Total,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 12 AND 23 AND ol.OperationResult = '成功' THEN 1 ELSE 0 END) AS Period12_24_Success,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 12 AND 23 AND ol.OperationResult = '失败' THEN 1 ELSE 0 END) AS Period12_24_Failed
FROM 
    order_logs ol
WHERE 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) IN ('2026-04-24', '2026-05-11', '2026-05-12', '2026-05-13')
GROUP BY 
    LocalDate,
    ol.OperationType
ORDER BY 
    LocalDate ASC,
    ol.OperationType ASC;

-- 按日期汇总时段统计（不含操作类型）
SELECT 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) AS LocalDate,
    -- 0-12小时时段统计
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 0 AND 11 THEN 1 ELSE 0 END) AS Period0_12_Total,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 0 AND 11 AND ol.OperationResult = '成功' THEN 1 ELSE 0 END) AS Period0_12_Success,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 0 AND 11 AND ol.OperationResult = '失败' THEN 1 ELSE 0 END) AS Period0_12_Failed,
    -- 12-24小时时段统计
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 12 AND 23 THEN 1 ELSE 0 END) AS Period12_24_Total,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 12 AND 23 AND ol.OperationResult = '成功' THEN 1 ELSE 0 END) AS Period12_24_Success,
    SUM(CASE WHEN HOUR(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) BETWEEN 12 AND 23 AND ol.OperationResult = '失败' THEN 1 ELSE 0 END) AS Period12_24_Failed,
    -- 合计
    COUNT(*) AS TotalAllPeriods
FROM 
    order_logs ol
WHERE 
    DATE(CONVERT_TZ(ol.CreatedAt, '+00:00', '-05:00')) IN ('2026-04-24', '2026-05-11', '2026-05-12', '2026-05-13')
GROUP BY 
    LocalDate
ORDER BY 
    LocalDate ASC;