海量Samples表按小时/天分组查询AVG、最值及对应日期的高性能实现

Hey there! Let's work through this query for your large Samples table—we'll get the grouped stats you need while keeping performance as snappy as possible.

First: Critical Performance Prep (Indexing) #

For a massive table, the right index is non-negotiable. Create this covering index to avoid expensive table lookups and speed up grouping/filtering:

CREATE NONCLUSTERED INDEX IX_Samples_SampleDate 
ON Samples (SampleDate) 
INCLUDE (AvgValue, MaxValue, DT_MaxValue, MinValue, DT_MinValue);

This index includes every column your query needs, so SQL can pull all data directly from the index without hitting the main table.

Query 1: Group by Day #

This query groups data by calendar day, returning the daily average, max value (with its date), and min value (with its date):

WITH DailyGroups AS (
    SELECT
        CAST(SampleDate AS DATE) AS GroupDate,
        AvgValue,
        MaxValue,
        DT_MaxValue,
        MinValue,
        DT_MinValue,
        -- Mark the row with the highest MaxValue per day
        ROW_NUMBER() OVER (PARTITION BY CAST(SampleDate AS DATE) ORDER BY MaxValue DESC) AS rn_max,
        -- Mark the row with the lowest MinValue per day
        ROW_NUMBER() OVER (PARTITION BY CAST(SampleDate AS DATE) ORDER BY MinValue ASC) AS rn_min
    FROM Samples
    -- Add a date filter here if you don't need all historical data!
    -- WHERE SampleDate BETWEEN '2024-01-01' AND '2024-01-31'
)
SELECT
    GroupDate,
    AVG(AvgValue) AS DailyAverage,
    MAX(CASE WHEN rn_max = 1 THEN MaxValue END) AS DailyMaxValue,
    MAX(CASE WHEN rn_max = 1 THEN DT_MaxValue END) AS DailyMaxDate,
    MAX(CASE WHEN rn_min = 1 THEN MinValue END) AS DailyMinValue,
    MAX(CASE WHEN rn_min = 1 THEN DT_MinValue END) AS DailyMinDate
FROM DailyGroups
GROUP BY GroupDate
ORDER BY GroupDate;

Query 2: Group by Hour #

Swap out the grouping logic to aggregate by hour instead. The rest of the pattern stays the same for consistency and performance:

WITH HourlyGroups AS (
    SELECT
        DATEADD(HOUR, DATEDIFF(HOUR, 0, SampleDate), 0) AS GroupHour,
        AvgValue,
        MaxValue,
        DT_MaxValue,
        MinValue,
        DT_MinValue,
        ROW_NUMBER() OVER (PARTITION BY DATEADD(HOUR, DATEDIFF(HOUR, 0, SampleDate), 0) ORDER BY MaxValue DESC) AS rn_max,
        ROW_NUMBER() OVER (PARTITION BY DATEADD(HOUR, DATEDIFF(HOUR, 0, SampleDate), 0) ORDER BY MinValue ASC) AS rn_min
    FROM Samples
    -- Again, filter by date range if possible!
    -- WHERE SampleDate BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'
)
SELECT
    GroupHour,
    AVG(AvgValue) AS HourlyAverage,
    MAX(CASE WHEN rn_max = 1 THEN MaxValue END) AS HourlyMaxValue,
    MAX(CASE WHEN rn_max = 1 THEN DT_MaxValue END) AS HourlyMaxDate,
    MAX(CASE WHEN rn_min = 1 THEN MinValue END) AS HourlyMinValue,
    MAX(CASE WHEN rn_min = 1 THEN DT_MinValue END) AS HourlyMinDate
FROM HourlyGroups
GROUP BY GroupHour
ORDER BY GroupHour;

Key Performance & Logic Notes #

• Sargable Expressions: We use CAST(SampleDate AS DATE) and DATEADD(HOUR, DATEDIFF(HOUR, 0, SampleDate), 0) because these are index-friendly (they don't apply functions directly to the indexed column in a way that breaks index usage).
• Single Table Scan: The CTE with window functions scans the table only once, which is way more efficient than multiple subqueries or joins that would re-scan data.
• Handling Ties: If multiple rows have the same max/min value in a group, ROW_NUMBER() will pick one arbitrarily. If you need all tied rows, replace ROW_NUMBER() with RANK() and adjust the final select to handle multiple entries (but that will change the output structure).
• Always Filter: Adding a WHERE clause for SampleDate will drastically reduce the amount of data processed—never query the entire table unless you absolutely need to.

Hope this works smoothly for your dataset! If you run into edge cases (like NULL values affecting aggregates) or need further tuning, feel free to ask.

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