MySQL分组聚合

-- 创建hq_kline表
delete from hq_kline
CREATE TABLE hq_kline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    finance_mic VARCHAR(16) NOT NULL,
    prod_code VARCHAR(64) NOT NULL,
    trade_date INT NOT NULL,
    data_timestamp INT NOT NULL,
    preclose_px DECIMAL(9,3) NOT NULL,
    open_px DECIMAL(9,3) NOT NULL,
    high_px DECIMAL(9,3) NOT NULL,
    low_px DECIMAL(9,3) NOT NULL,
    close_px DECIMAL(9,3) NOT NULL,
    business_count INT NOT NULL,
    business_amount BIGINT NOT NULL,
    business_balance DECIMAL(18,3) NOT NULL
);

-- 插入数据
INSERT INTO hq_kline (finance_mic, prod_code, trade_date, data_timestamp, preclose_px, open_px, high_px, low_px, close_px, business_count, business_amount, business_balance)
VALUES
    -- 第一组数据
    ('MIC1', 'CODE1', 20231014, 1634131240, 100.000, 105.000, 110.000, 95.000, 100.500, 1000, 50000, 25000.000),
    ('MIC1', 'CODE1', 20231014, 1634131300, 101.000, 106.000, 111.000, 96.000, 101.500, 1100, 55000, 28000.000),
    ('MIC1', 'CODE1', 20231014, 1634131350, 102.000, 107.000, 112.000, 97.000, 102.500, 1200, 60000, 30000.000),
    ('MIC1', 'CODE1', 20231014, 1634131410, 103.000, 108.000, 113.000, 98.000, 103.500, 1300, 65000, 32000.000),
    ('MIC1', 'CODE1', 20231014, 1634131470, 104.000, 109.000, 114.000, 99.000, 104.500, 1400, 70000, 34000.000),
    -- 第二组数据
    ('MIC1', 'CODE1', 20231015, 1634131550, 105.000, 110.000, 115.000, 100.000, 105.500, 1500, 75000, 36000.000),
    ('MIC1', 'CODE1', 20231015, 1634131610, 106.000, 111.000, 116.000, 101.000, 106.500, 1600, 80000, 38000.000),
    ('MIC1', 'CODE1', 20231015, 1634131670, 107.000, 112.000, 117.000, 102.000, 107.500, 1700, 85000, 40000.000),
    ('MIC1', 'CODE1', 20231015, 1634131730, 108.000, 113.000, 118.000, 103.000, 108.500, 1800, 90000, 42000.000),
    ('MIC1', 'CODE1', 20231015, 1634131790, 109.000, 114.000, 119.000, 104.000, 109.500, 1900, 95000, 44000.000),
    -- 第三组数据
    ('MIC1', 'CODE1', 20231016, 1634142550, 110.000, 115.000, 120.000, 105.000, 110.500, 2000, 100000, 46000.000),
    ('MIC1', 'CODE1', 20231016, 1634142600, 111.000, 116.000, 121.000, 106.000, 111.500, 2100, 105000, 48000.000),
    ('MIC1', 'CODE1', 20231016, 1634142610, 112.000, 117.000, 122.000, 107.000, 112.500, 2200, 110000, 50000.000),
    ('MIC1', 'CODE1', 20231016, 1634142620, 113.000, 118.000, 123.000, 108.000, 113.500, 2300, 115000, 52000.000),
    ('MIC1', 'CODE1', 20231016, 1634142700, 114.000, 119.000, 124.000, 109.000, 114.500, 2400, 120000, 54000.000);

//按照每五分钟分组统计，以每组最大时间为新记录的时间戳，计算每组的business_count，business_amount，business_balance总和作为对应的结果，其他字段为每组的最大时间的同行数据。
SELECT
    finance_mic,
    prod_code,
    MAX(data_timestamp) AS new_data_timestamp,
    SUM(business_count) AS total_business_count,
    SUM(business_amount) AS total_business_amount,
    SUM(business_balance) AS total_business_balance,
    MAX(open_px) AS last_open_px,
    MAX(high_px) AS last_high_px,
    MAX(low_px) AS last_low_px,
    MAX(close_px) AS last_close_px,
    MAX(preclose_px) AS last_preclose_px
FROM (
    SELECT
        finance_mic,
        prod_code,
        FROM_UNIXTIME(data_timestamp - MOD(data_timestamp, 300)) AS group_timestamp,
        MAX(data_timestamp) AS data_timestamp,
        SUM(business_count) AS business_count,
        SUM(business_amount) AS business_amount,
        SUM(business_balance) AS business_balance,
        MAX(open_px) AS open_px,
        MAX(high_px) AS high_px,
        MAX(low_px) AS low_px,
        MAX(close_px) AS close_px,
        MAX(preclose_px) AS preclose_px
    FROM hq_kline
    GROUP BY finance_mic, prod_code, group_timestamp
) AS subquery
GROUP BY finance_mic, prod_code, group_timestamp;

SELECT
    MAX(data_timestamp) AS new_data_timestamp,
    MAX(finance_mic) AS finance_mic,
    MAX(prod_code) AS prod_code,
    MAX(trade_date) AS trade_date,
    MAX(preclose_px) AS preclose_px,
    MAX(open_px) AS open_px,
    MAX(high_px) AS high_px,
    MAX(low_px) AS low_px,
    MAX(close_px) AS close_px,
    SUM(business_count) AS total_business_count,
    SUM(business_amount) AS total_business_amount,
    SUM(business_balance) AS total_business_balance
FROM (
    SELECT
        *,
        CEIL(data_timestamp / 300) AS group_number
    FROM hq_kline
) AS numbered_data
GROUP BY group_number;


//每五个记录为一组，分组统计，以每组最大时间为新记录的时间戳，计算每组的business_count，business_amount，business_balance总和作为对应的结果，其他字段为每组的最大时间的同行数据。
SELECT
    MAX(data_timestamp) AS new_data_timestamp,
    SUM(business_count) AS total_business_count,
    SUM(business_amount) AS total_business_amount,
    SUM(business_balance) AS total_business_balance,
    MAX(preclose_px) AS last_preclose_px,
    MAX(open_px) AS last_open_px,
    MAX(high_px) AS last_high_px,
    MAX(low_px) AS last_low_px,
    MAX(close_px) AS last_close_px
FROM (
    SELECT
        *,
        CEIL(ROW_NUMBER() OVER (ORDER BY data_timestamp) / 5) AS group_number
    FROM hq_kline
) AS numbered_data
GROUP BY group_number;