mysql - 合并两个使用不同时间范围的最小/最大/平均值查询

Question

我正在使用 10.1.39-MariaDB - mariadb.org 二进制文件，我有下表:

| id    | date                | ticker | close     |
|-------|---------------------|--------|-----------|
| 39869 | 2019-09-18 00:00:00 | AAPL   | 221.96160 |
| 39870 | 2019-09-17 00:00:00 | AAPL   | 220.70000 |
| 39871 | 2019-09-16 00:00:00 | AAPL   | 219.90000 |
| 39872 | 2019-09-13 00:00:00 | AAPL   | 218.75000 |
| 39873 | 2019-09-12 00:00:00 | AAPL   | 223.09000 |
| 39874 | 2019-09-11 00:00:00 | AAPL   | 223.59000 |
| 39875 | 2019-09-10 00:00:00 | AAPL   | 216.70000 |

我有两个查询，其中计算 52 周 的指标，第二个查询计算 20 天 的聚合指标:

52 周:

SELECT
    Y.*
FROM
    (
    SELECT
        MAX(CLOSE) AS week_52_High,
        DATE AS week_52_High_date,
        MIN(CLOSE) AS week_52_Low,
        DATE AS week_52_Low_date,
        AVG(CLOSE) AS week_52_Avg
    FROM
        `prices`
    WHERE
        DATE >= DATE(NOW()) - INTERVAL 52 WEEK AND ticker = "AAPL") Y
    LEFT JOIN prices tmax52 ON
        tmax52.date = Y.week_52_High_date AND tmax52.close = week_52_High
    LEFT JOIN prices tmin52 ON
        tmin52.date = Y.week_52_Low_date AND tmin52.close = week_52_Low
    LEFT JOIN prices tavg52 ON
        tavg52.close = week_52_Avg

20 天

SELECT
    Y.*
FROM
    (
    SELECT
        MAX(CLOSE) AS day_20_High,
        DATE AS day_20_High_date,
        MIN(CLOSE) AS day_20_Low,
        DATE AS day_20_Low_date,
        AVG(CLOSE) AS day_20_Avg
    FROM
        `prices`
    WHERE
        DATE >= DATE(NOW()) - INTERVAL 20 DAY AND ticker = "AAPL") Y
    LEFT JOIN prices tmax20 ON
        tmax20.date = Y.day_20_High_date AND tmax20.close = day_20_High
    LEFT JOIN prices tmin20 ON
        tmin20.date = Y.day_20_Low_date AND tmin20.close = day_20_Low
    LEFT JOIN prices tavg20 ON
        tavg20.close = day_20_Avg

两个查询都会计算每个收盘价的最小/最大/平均值，并附上发生这种情况的日期。

有什么建议如何合并这两个查询以将所有内容合并到 1 个输出中吗？

感谢您的回复!

Answer 1

您的第一个问题是您的查询实际上不起作用。获取high和low值的日期的正确方法如下。请注意，尝试获取平均收盘价是没有意义的，因为股票不太可能以该价格收盘。

SELECT
    Y.day_20_High,
    tmax20.DATE AS day_20_High_date,
    Y.day_20_Low,
    tmin20.DATE AS day_20_Low_date,
    Y.day_20_Avg
FROM
    (
    SELECT
        MAX(CLOSE) AS day_20_High,
        MIN(CLOSE) AS day_20_Low,
        ROUND(AVG(CLOSE),2) AS day_20_Avg
    FROM
        `prices`
    WHERE
        DATE >= CURDATE() - INTERVAL 20 DAY AND ticker = "AAPL") Y
    LEFT JOIN prices tmax20 ON tmax20.close = Y.day_20_High
    LEFT JOIN prices tmin20 ON tmin20.close = Y.day_20_Low

输出(对于我的示例数据)

day_20_High     day_20_High_date    day_20_Low  day_20_Low_date     day_20_Avg
107.50          2019-09-20          101.10      2019-09-10          104.05

Demo on dbfiddle

更正查询后，您现在只需 JOIN 到 52 周数据的同一查询即可:

SELECT
    Y20.day_20_High,
    tmax20.DATE AS day_20_High_date,
    Y20.day_20_Low,
    tmin20.DATE AS day_20_Low_date,
    Y20.day_20_Avg,
    Y52.week_52_High,
    tmax52.DATE AS week_52_High_date,
    Y52.week_52_Low,
    tmin52.DATE AS week_52_Low_date,
    Y52.week_52_Avg
FROM ((
    SELECT
        MAX(CLOSE) AS day_20_High,
        MIN(CLOSE) AS day_20_Low,
        ROUND(AVG(CLOSE),2) AS day_20_Avg
    FROM
        `prices`
    WHERE
        DATE >= CURDATE() - INTERVAL 20 DAY AND ticker = "AAPL") Y20
    LEFT JOIN prices tmax20 ON tmax20.close = Y20.day_20_High
    LEFT JOIN prices tmin20 ON tmin20.close = Y20.day_20_Low)
JOIN ((
    SELECT
        MAX(CLOSE) AS week_52_High,
        MIN(CLOSE) AS week_52_Low,
        ROUND(AVG(CLOSE),2) AS week_52_Avg
    FROM
        `prices`
    WHERE
        DATE >= DATE(NOW()) - INTERVAL 52 WEEK AND ticker = "AAPL") Y52
    LEFT JOIN prices tmax52 ON tmax52.close = Y52.week_52_High
    LEFT JOIN prices tmin52 ON tmin52.close = Y52.week_52_Low)

输出(对于我的示例数据)

day_20_High     day_20_High_date    day_20_Low  day_20_Low_date     day_20_Avg  week_52_High    week_52_High_date   week_52_Low     week_52_Low_date    week_52_Avg
107.50          2019-09-20          101.10      2019-09-10          104.05      109.70          2019-08-24          100.00          2019-08-21          104.19

Demo on dbfiddle