不使用子查询过滤重复数据：按分钟分组统计优化方案

Hey there! Let’s fix that query to ditch the unnecessary subquery and boost performance, while keeping exactly the results you want.

Your original approach works, but grouping twice (once in the subquery, once in the outer query) is redundant. The core goal here is to deduplicate records by minute (since your cronjob might run multiple times in the same minute) and then calculate your stats per unique minute—we can do this in a single pass.

Optimized Query (No Subqueries) #

SELECT
    1 AS record_count,
    ANY_VALUE(`amount`) AS total_amount,
    ANY_VALUE(`amount`) AS max_amount,
    ANY_VALUE(`amount`) AS min_amount,
    DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i') AS minute_window
FROM `stats`
GROUP BY DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i')

Why This Works #

• Direct Grouping: The GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') clause groups all records into unique minute buckets, handling the deduplication in one step (each group represents one unique minute).
• ANY_VALUE() Function: This picks an arbitrary amount from each minute group, which matches the behavior of your original subquery (where MySQL returns one random record per group when you SELECT * with GROUP BY).
• Hardcoded Count: Since each group is a deduplicated minute, we can just use 1 AS record_count to match your original result where COUNT(*) always returned 1.

If You Need Control Over Which Record Is Picked #

If you want to explicitly choose the first/last record per minute (instead of a random one), use a window function. This still uses a subquery, but it’s far more efficient than your original and gives you predictability:

SELECT
    1 AS record_count,
    `amount` AS total_amount,
    `amount` AS max_amount,
    `amount` AS min_amount,
    DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i') AS minute_window
FROM (
    SELECT
        `amount`,
        `created_at`,
        -- Assigns a row number to each record per minute, ordered by creation time
        ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i') ORDER BY `created_at`) AS rn
    FROM `stats`
) AS tmp
WHERE rn = 1 -- Keep only the first record per minute

Performance Boost Tip #

To make either query run even faster, add an index on created_at—this will speed up the grouping/partitioning operation, especially as your table grows:

CREATE INDEX idx_stats_created_at ON `stats`(`created_at`);

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