MySQL分钟K线数据转换为5分钟K线汇总查询需求

Got it, let's tackle converting your 1-minute candlestick data into 5-minute bars. Your table stores per-minute market data, so we'll need to group rows into 5-minute intervals and calculate the standard candlestick metrics for each group. Here's a complete SQL query tailored to your table structure:

SELECT
    market,
    -- Get the first open price in the 5-minute interval
    FIRST_VALUE(open) OVER (PARTITION BY market, time_interval ORDER BY time) AS open,
    -- Highest price in the interval
    MAX(high) AS high,
    -- Lowest price in the interval
    MIN(low) AS low,
    -- Get the last close price in the interval
    LAST_VALUE(close) OVER (PARTITION BY market, time_interval ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close,
    -- Sum all volume in the interval
    SUM(volume) AS volume,
    -- Use the start time of the 5-minute interval as the bar's time
    time_interval AS time
FROM (
    SELECT
        market,
        open,
        high,
        low,
        close,
        volume,
        time,
        -- Group time into 5-minute intervals (adjust based on your time column type)
        -- If `time` is a DATETIME/TIMESTAMP:
        FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(time) / 300) * 300) AS time_interval
        -- If `time` is a UNIX timestamp (integer):
        -- FLOOR(time / 300) * 300 AS time_interval
    FROM your_table_name
) AS minute_data
GROUP BY market, time_interval
ORDER BY market, time_interval;

How This Query Works #

Let's break down each component:

• Time Interval Grouping: The subquery takes each 1-minute timestamp and rounds it down to the nearest 5-minute mark (300 seconds). This ensures all rows in the same 5-minute window are grouped together. We handle both DATETIME and UNIX timestamp formats—just comment/uncomment the line that matches your time column type.

• Open Price: We use FIRST_VALUE(open) with a window partition to grab the first open price in each 5-minute interval (ordered by the original 1-minute time). This gives us the opening price of the 5-minute bar.

• High/Low Prices: Simple MAX(high) and MIN(low) aggregations give the highest and lowest prices during the 5-minute window—straightforward candlestick logic.

• Close Price: LAST_VALUE(close) needs a full window range (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to ensure we get the final close price of the interval (without this, the default window only includes rows up to the current row in the group).

• Volume: SUM(volume) adds up all the 1-minute volume values to get the total volume for the 5-minute bar.

• Grouping & Ordering: We group by both market and time_interval to make sure we get separate 5-minute bars for each market, then order the results to keep them chronological.

Additional Tips #

• If your time column has timezone-aware data, make sure UNIX_TIMESTAMP() handles it correctly (MySQL uses the session timezone by default—you can set it explicitly with SET time_zone = 'UTC'; if needed).
• If you want the interval to be labeled by the end time instead of the start, just add 300 seconds to time_interval (e.g., time_interval + INTERVAL 5 MINUTE for DATETIME, or time_interval + 300 for timestamps).
• For large datasets, adding an index on (market, time) will speed up the grouping and window functions significantly.

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