MySQL查询优化需求：按优先级获取每日指定时段valor值（禁用聚合函数）

Got it, let's solve this problem by replacing aggregate functions with window functions to prioritize the desired time slots, while keeping your original query structure fully intact.

Modified Query #

SELECT
    month(fecha) as mes,
    day(fecha) as dia,
    DATE_FORMAT(fecha, '%m-%d') as mesdia1,
    DATE_FORMAT(fecha,'%b %d') mesdia,
    cast(case when year(fecha) = 2022 then valor end as decimal(10,4)) as 'a2022',
    cast(case when year(fecha) = 2021 then valor end as decimal(10,4)) as 'a2021',
    cast(case when year(fecha) = 2020 then valor end as decimal(10,4)) as 'a2020',
    cast(case when year(fecha) = 2019 then valor end as decimal(10,4)) as 'a2019',
    cast(case when year(fecha) = 2018 then valor end as decimal(10,4)) as 'a2018',
    cast(case when year(fecha) = 2017 then valor end as decimal(10,4)) as 'a2017',
    cast(case when year(fecha) = 2016 then valor end as decimal(10,4)) as 'a2016'
FROM (
    SELECT
        d.*,
        -- Assign priority: lower number = higher priority
        CASE hora
            WHEN '08:00:00' THEN 1
            WHEN '08:15:00' THEN 2
            WHEN '08:30:00' THEN 3
            WHEN '08:45:00' THEN 4 -- Lowest priority, only selected if first three slots have no data
        END as priority,
        -- Rank rows per day by priority
        ROW_NUMBER() OVER (
            PARTITION BY id_estacion, month(fecha), day(fecha)
            ORDER BY priority ASC
        ) as rn
    FROM datos d
    WHERE id_estacion=1 
      AND tipo_sensor=3 
      AND year(fecha) in (2022,2021,2020,2019,2018,2017,2016)
      AND hora IN ('08:00:00', '08:15:00', '08:30:00', '08:45:00')
) ranked
-- Only keep the highest priority row per day
WHERE rn = 1
ORDER BY mes, dia;

Key Explanations #

• Priority Assignment: We use a CASE statement to assign numerical priorities to each time slot. 08:00 gets the highest priority (1), followed by 08:15 (2), 08:30 (3), and 08:45 (4, which acts as a fallback only if the first three slots have no data).
• Row Ranking: The ROW_NUMBER() window function groups rows by station, month, and day, then sorts them by priority. This marks the highest-priority row for each day as rn=1.
• No Aggregates Needed: Since we're only selecting one row per day (the highest-priority one), we can directly reference valor in the outer query's CASE statements without needing MAX or other aggregates.
• Structure Preservation: All original fields, year-specific columns, grouping logic, and sorting order are kept exactly as in your initial query.

Adjustment for Edge Cases #

If you don't want to include days where only 08:45 data exists (since it's not in your core priority list), simply remove '08:45:00' from the IN clause in the subquery's WHERE condition. This will exclude those days from the results entirely.

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