MySQL 8.0股票K线数据两类查询需求求助

需求1：找出最低低价时间早于最高高价时间的股票 #

实现SQL #

WITH stock_extremes AS (
    SELECT
        ticker,
        MIN(low) AS min_low,
        MAX(high) AS max_high
    FROM stock_price
    GROUP BY ticker
),
low_dates AS (
    SELECT
        sp.ticker,
        sp.date_time AS low_datetime
    FROM stock_price sp
    JOIN stock_extremes se ON sp.ticker = se.ticker AND sp.low = se.min_low
),
high_dates AS (
    SELECT
        sp.ticker,
        sp.date_time AS high_datetime
    FROM stock_price sp
    JOIN stock_extremes se ON sp.ticker = se.ticker AND sp.high = se.max_high
)
SELECT DISTINCT ld.ticker
FROM low_dates ld
JOIN high_dates hd ON ld.ticker = hd.ticker
WHERE ld.low_datetime < hd.high_datetime;

逻辑说明 #

1. 先通过stock_extremes计算每只股票的最低低价和最高高价
2. 分别通过low_dates和high_dates找出所有对应极值的时间点
3. 关联两个时间数据集，筛选出存在低价时间早于高价时间的股票，去重后得到结果

需求2：找出每只股票最低低价与最高高价时间差最短的K线组合 #

实现SQL #

WITH stock_extremes AS (
    SELECT
        ticker,
        MIN(low) AS min_low,
        MAX(high) AS max_high
    FROM stock_price
    GROUP BY ticker
),
low_records AS (
    SELECT
        ticker,
        date_time AS low_dt,
        low AS min_low_val
    FROM stock_price sp
    JOIN stock_extremes se ON sp.ticker = se.ticker AND sp.low = se.min_low
),
high_records AS (
    SELECT
        ticker,
        date_time AS high_dt,
        high AS max_high_val
    FROM stock_price sp
    JOIN stock_extremes se ON sp.ticker = se.ticker AND sp.high = se.max_high
),
extreme_pairs AS (
    SELECT
        lr.ticker,
        lr.low_dt,
        hr.high_dt,
        ABS(TIMESTAMPDIFF(MINUTE, lr.low_dt, hr.high_dt)) AS time_diff_minutes,
        lr.min_low_val,
        hr.max_high_val
    FROM low_records lr
    JOIN high_records hr ON lr.ticker = hr.ticker
),
min_time_diff AS (
    SELECT
        ticker,
        MIN(time_diff_minutes) AS min_diff
    FROM extreme_pairs
    GROUP BY ticker
)
SELECT
    ep.ticker,
    ep.low_dt AS 最低低价时间,
    ep.min_low_val AS 最低低价,
    ep.high_dt AS 最高高价时间,
    ep.max_high_val AS 最高高价,
    ep.time_diff_minutes AS 时间差(分钟)
FROM extreme_pairs ep
JOIN min_time_diff mtd ON ep.ticker = mtd.ticker AND ep.time_diff_minutes = mtd.min_diff
ORDER BY ep.ticker;

逻辑说明 #

1. 先获取每只股票的最低低价和最高高价
2. 列出所有对应极值的完整记录（包含时间和价格）
3. 生成所有低价-高价的组合，计算时间差（取绝对值兼容低价在高价之后的情况）
4. 找出每只股票的最小时间差阈值
5. 筛选出符合最小时间差的组合，若有多个组合时间差相同，会全部返回

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