SQL查询日期合并问题：每日双储罐平均容量统计缺失处理

嘿，我猜你是在统计两个储罐的每日平均容量时，碰到了部分日期其中一个储罐没数据的麻烦——毕竟你给出的查询片段里已经在按日期分组算平均了对吧？别担心，我来帮你把这个查询完善，搞定那些缺失的记录。

首先，先明确我们的核心目标：按日期分组，每个日期都要显示两个储罐的平均容量，哪怕其中一个储罐当天没有任何数据记录。结合你提供的查询开头，我假设你的表结构大概是这样的：

• 表名：比如tank_data（你可以换成实际表名）
• event_datetime：记录的时间戳
• event_param_2：储罐编号（比如R1、R2）
• event_param_3：储罐的容量值

第一步：基础统计（但有缺失问题） #

先写最基础的分组查询，计算每个储罐每天的平均容量：

SELECT 
    CAST(event_datetime AS DATE) AS day,
    event_param_2 AS tank_id,
    AVG(event_param_3) AS avg_content
FROM tank_data
GROUP BY CAST(event_datetime AS DATE), event_param_2

但这个查询的问题是：如果某天R2没有任何记录，结果里就不会出现该日期的R2行，导致数据不完整。

第二步：补全缺失的日期-储罐组合 #

要解决这个问题，我们需要先生成所有可能的日期-储罐组合，再把统计结果左连接上去，确保每个组合都有记录。具体分三步：

1. 生成所有存在数据的日期列表
2. 生成所有需要统计的储罐列表（固定两个的话直接用UNION ALL）
3. 把两者做笛卡尔积，再左连接统计结果

完整查询如下：

WITH all_dates AS (
    -- 获取所有有数据的日期
    SELECT DISTINCT CAST(event_datetime AS DATE) AS day FROM tank_data
),
all_tanks AS (
    -- 定义要统计的两个储罐，也可以从数据里动态获取
    SELECT 'R1' AS tank_id UNION ALL SELECT 'R2' AS tank_id
),
daily_tank_stats AS (
    -- 基础的每日储罐平均统计
    SELECT 
        CAST(event_datetime AS DATE) AS day,
        event_param_2 AS tank_id,
        AVG(event_param_3) AS avg_content
    FROM tank_data
    GROUP BY CAST(event_datetime AS DATE), event_param_2
)
SELECT 
    ad.day,
    at.tank_id,
    -- 如果需要把无数据的情况显示为0，就用COALESCE，否则直接用dts.avg_content（显示NULL）
    COALESCE(dts.avg_content, 0) AS avg_content
FROM all_dates ad
-- 笛卡尔积生成所有日期-储罐组合
CROSS JOIN all_tanks at
-- 左连接确保所有组合都保留
LEFT JOIN daily_tank_stats dts
    ON ad.day = dts.day AND at.tank_id = dts.tank_id
ORDER BY ad.day, at.tank_id;

可选：行转列显示（同一日期两个储罐在一行） #

如果你希望把两个储罐的平均容量放在同一行显示（比如R1和R2各占一列），可以调整成这个版本：

WITH all_dates AS (
    SELECT DISTINCT CAST(event_datetime AS DATE) AS day FROM tank_data
),
daily_tank_stats AS (
    SELECT 
        CAST(event_datetime AS DATE) AS day,
        event_param_2 AS tank_id,
        AVG(event_param_3) AS avg_content
    FROM tank_data
    GROUP BY CAST(event_datetime AS DATE), event_param_2
)
SELECT 
    ad.day,
    -- 获取R1的平均容量，无数据则显示NULL（可以加COALESCE换成0）
    MAX(CASE WHEN dts.tank_id = 'R1' THEN dts.avg_content END) AS R1_avg_content,
    -- 获取R2的平均容量
    MAX(CASE WHEN dts.tank_id = 'R2' THEN dts.avg_content END) AS R2_avg_content
FROM all_dates ad
LEFT JOIN daily_tank_stats dts ON ad.day = dts.day
GROUP BY ad.day
ORDER BY ad.day;

关键注意点 #

• 如果你的储罐编号不是固定的R1/R2，而是从数据里动态生成的，可以把all_tanks换成SELECT DISTINCT event_param_2 AS tank_id FROM tank_data
• COALESCE函数用来把NULL替换成你需要的默认值（比如0），如果业务允许显示NULL，可以直接去掉这个函数
• 确保event_param_3是数值类型，否则AVG函数会报错，如果是字符串类型需要先转换（比如AVG(CAST(event_param_3 AS DECIMAL(10,2)))）

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