MySQL二级聚合求平均：按周-日维度计算设备数值的均值

Alright, let's work through this. Your requirement calls for a two-level average calculation—first we need to get the daily average value per device, then average those daily values across the entire week for each device. Here's how to implement this in MySQL:

Using a CTE (Cleaner Approach) #

Common Table Expressions make this logic easy to read and maintain:

WITH daily_device_averages AS (
    SELECT
        id,
        week,
        day,
        AVG(value) AS daily_avg
    FROM
        your_table_name  -- Replace with your actual table name
    GROUP BY
        id, week, day
)
SELECT
    id,
    week,
    AVG(daily_avg) AS average_week_day_value
FROM
    daily_device_averages
GROUP BY
    id, week;

Breakdown: #

1. Inner CTE (daily_device_averages): This first step groups the data by device (id), week, and day, then calculates the average value for each of those groups. This gives us the daily average per device, which is exactly the intermediate step you need.
2. Outer Query: We take those daily averages and group them again by device and week, then compute the average of those daily values. This gives us the final "average of daily averages" per device per week—exactly what your requirement specifies.

Alternative: Nested Subquery (For Older MySQL Versions) #

If you're working with a MySQL version that doesn't support CTEs (pre-8.0), you can use a nested subquery instead:

SELECT
    id,
    week,
    AVG(daily_avg) AS average_week_day_value
FROM (
    SELECT
        id,
        week,
        day,
        AVG(value) AS daily_avg
    FROM
        your_table_name  -- Replace with your actual table name
    GROUP BY
        id, week, day
) AS sub
GROUP BY
    id, week;

Notes: #

• This approach ensures that days with more data points don't skew the weekly average (since each day's average is treated as a single value in the final calculation).
• If a device has no data for a specific day in the week, that day is simply excluded from the weekly average calculation (which aligns with standard averaging logic for missing data).

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