如何合并MariaDB中52周与20天的聚合查询？

嗨，我来帮你搞定这个合并查询的问题～首先得先纠正下原查询里的小bug：你原来的子查询直接SELECT DATE是拿不到最值对应日期的，它只会返回符合条件的任意一条日期，不是我们想要的最高/最低价对应的那一天。先把这个问题修正，再合并两个查询就顺理成章啦！

方案1：用交叉连接合并独立聚合查询（直观易懂） #

把修正后的52周、20天聚合结果分别作为两个派生表，通过CROSS JOIN合并成一行输出——因为两个查询都是针对同一个ticker，结果都是单行，交叉连接刚好能把两组指标拼在一起：

SELECT 
    w52.*,
    d20.*
FROM
    (
        -- 修正后的52周聚合：准确获取最值及对应日期
        SELECT 
            MAX(p.close) AS week_52_High,
            SUBSTRING_INDEX(GROUP_CONCAT(p.date ORDER BY p.close DESC), ',', 1) AS week_52_High_date,
            MIN(p.close) AS week_52_Low,
            SUBSTRING_INDEX(GROUP_CONCAT(p.date ORDER BY p.close ASC), ',', 1) AS week_52_Low_date,
            AVG(p.close) AS week_52_Avg
        FROM prices p
        WHERE p.ticker = 'AAPL' 
          AND p.date >= DATE(NOW()) - INTERVAL 52 WEEK
    ) w52
CROSS JOIN
    (
        -- 修正后的20天聚合：准确获取最值及对应日期
        SELECT 
            MAX(p.close) AS day_20_High,
            SUBSTRING_INDEX(GROUP_CONCAT(p.date ORDER BY p.close DESC), ',', 1) AS day_20_High_date,
            MIN(p.close) AS day_20_Low,
            SUBSTRING_INDEX(GROUP_CONCAT(p.date ORDER BY p.close ASC), ',', 1) AS day_20_Low_date,
            AVG(p.close) AS day_20_Avg
        FROM prices p
        WHERE p.ticker = 'AAPL' 
          AND p.date >= DATE(NOW()) - INTERVAL 20 DAY
    ) d20;

方案2：用窗口函数一次性计算（性能更优） #

如果想减少对prices表的扫描次数，用窗口函数可以在单次查询里搞定所有指标，效率更高：

SELECT DISTINCT
    -- 52周指标组
    MAX(p.close) OVER (w52) AS week_52_High,
    FIRST_VALUE(p.date) OVER (w52 ORDER BY p.close DESC) AS week_52_High_date,
    MIN(p.close) OVER (w52) AS week_52_Low,
    FIRST_VALUE(p.date) OVER (w52 ORDER BY p.close ASC) AS week_52_Low_date,
    AVG(p.close) OVER (w52) AS week_52_Avg,
    -- 20天指标组
    MAX(p.close) OVER (d20) AS day_20_High,
    FIRST_VALUE(p.date) OVER (d20 ORDER BY p.close DESC) AS day_20_High_date,
    MIN(p.close) OVER (d20) AS day_20_Low,
    FIRST_VALUE(p.date) OVER (d20 ORDER BY p.close ASC) AS day_20_Low_date,
    AVG(p.close) OVER (d20) AS day_20_Avg
FROM prices p
WHERE p.ticker = 'AAPL'
  -- 取覆盖两个时间段的范围，确保窗口能计算到所有数据
  AND p.date >= DATE(NOW()) - INTERVAL 52 WEEK
WINDOW
    w52 AS (PARTITION BY p.ticker WHERE p.date >= DATE(NOW()) - INTERVAL 52 WEEK),
    d20 AS (PARTITION BY p.ticker WHERE p.date >= DATE(NOW()) - INTERVAL 20 DAY);

补充说明 #

• 你原来的LEFT JOIN其实是多余的，我们不需要再关联原表，通过聚合函数或窗口函数就能直接拿到最值和对应日期。
• 如果遇到同一天有多个相同最值的情况，上面的方法会取排序后的第一个日期，你可以根据需求调整ORDER BY的规则（比如加个DESC取最晚的日期）。

内容的提问来源于stack exchange，提问作者Carol.Kar