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 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, FROM_UNIXTIME(data_timestamp - MOD(data_timestamp, 300));

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 hq_kline
GROUP BY floor(data_timestamp / 300); data_timestamp/5*60,得到的值向下取整，得到相同值的为一组 （ceil()向上取整，floor()向下取整） 

 

 

//每五个记录为一组，分组统计，以每组最大时间为新记录的时间戳，计算每组的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;

 

SQL时间戳相关转换：

1.SELECT FROM_UNIXTIME(1570219906)
运行以上命令将返回结果：'2019-10-04 07:45:06'，即该时间戳对应的标准时间格式。

2.将时间戳转换为DATE类型
SELECT DATE(FROM_UNIXTIME(1570219906))
该命令将返回结果：'2019-10-04'，即该时间戳所对应的日期。

3.将时间戳转换为TIME类型
SELECT TIME(FROM_UNIXTIME(1570219906))
该命令将返回结果：'07:45:06'，即该时间戳所对应的时间。

4.将时间戳转换为DATETIME类型
SELECT DATE(FROM_UNIXTIME(1570219906)), TIME(FROM_UNIXTIME(1570219906))
该命令将返回结果：'2019-10-04'和'07:45:06'，即该时间戳所对应的日期和时间。

5.获取当前时间的时间戳
SELECT UNIX_TIMESTAMP() --> 1676600862

6.获取指定时间的时间戳
SELECT UNIX_TIMESTAMP('2023-2-17 20:05:05')  --> 1676635505

7.获取指定日期的时间戳
SELECT UNIX_TIMESTAMP('2023-2-17')  -->1676563200