如何将MySQL每日价格统计改为按月统计最小、最大等指标？

Alright, I see you want to adjust your query from daily price breakdowns to showing the overall monthly performance (min, max, start, and last prices per month). Let's refactor that query step by step:

Modified Query (Window Function Version) #

This works for databases that support window functions like MySQL 8+, PostgreSQL, etc.:

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  FIRST_VALUE(price) OVER (
    PARTITION BY DATE_FORMAT(created_at, '%Y-%m') 
    ORDER BY created_at ASC
  ) AS start_price,
  LAST_VALUE(price) OVER (
    PARTITION BY DATE_FORMAT(created_at, '%Y-%m') 
    ORDER BY created_at ASC
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_price
FROM your_table_name
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

Key Adjustments Explained #

• Group by Month: We use DATE_FORMAT(created_at, '%Y-%m') to group all records into year-month buckets (e.g., 2024-05 for May 2024), replacing the daily grouping.
• Min/Max Prices: MIN(price) and MAX(price) now calculate across all days in the month instead of a single day—no changes to the functions themselves, just the grouping scope.
• Start Price: FIRST_VALUE(price) with a partition by month and ordered by created_at ascending pulls the earliest recorded price of the month.
• Last Price: LAST_VALUE(price) needs the RANGE BETWEEN clause to ensure it captures the final price of the entire month (by default, it only considers rows up to the current position in the window).

Fallback for Older Databases (No Window Functions) #

If you're using an older MySQL version (pre-8.0) that doesn't support window functions, use subqueries to fetch start and last prices:

SELECT
  month,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  -- Get first price of the month
  (SELECT price 
   FROM your_table_name t2 
   WHERE DATE_FORMAT(t2.created_at, '%Y-%m') = t1.month 
   ORDER BY created_at ASC LIMIT 1) AS start_price,
  -- Get last price of the month
  (SELECT price 
   FROM your_table_name t2 
   WHERE DATE_FORMAT(t2.created_at, '%Y-%m') = t1.month 
   ORDER BY created_at DESC LIMIT 1) AS last_price
FROM (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, price
  FROM your_table_name
) t1
GROUP BY month
ORDER BY month;

This subquery approach directly fetches the first and last price records for each month by sorting and limiting results to 1.

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