mysql - 根据与前一天数据的差异获得前 5 个结果

Question

我正在运行一个 mysql - 10.1.39-MariaDB - mariadb.org binary- 数据库。

我有下表:

| id   | date                | api_endpoint | ticker | open      | high      | low       | close     | volume    |
|------|---------------------|--------------|--------|-----------|-----------|-----------|-----------|-----------|
| 18   | 2019-08-07 00:00:00 | daily        | AAPL   | 195.41000 | 199.56000 | 193.82000 | 199.04000 | 33364400  |
| 19   | 2019-08-06 00:00:00 | daily        | AAPL   | 196.31000 | 198.07000 | 194.04000 | 197.00000 | 35824800  |
| 20   | 2019-08-05 00:00:00 | daily        | AAPL   | 197.99000 | 198.65000 | 192.58000 | 193.34000 | 52393000  |
| 21   | 2019-08-02 00:00:00 | daily        | AAPL   | 205.53000 | 206.43000 | 201.62470 | 204.02000 | 40862100  |
| 44   | 2019-08-01 00:00:00 | monthly      | AAPL   | 213.90000 | 218.03000 | 206.74000 | 208.43000 | 54017900  |
| 5273 | 1999-09-07 00:00:00 | monthly      | AAPL   | 73.75000  | 77.93800  | 73.50000  | 76.37500  | 246198400 |

我正在使用 mysql 计算返回:

SELECT *
      ,(CLOSE - (SELECT (t2.close)
                 FROM prices t2
                 WHERE t2.date < t1.date
                 ORDER BY t2.date DESC
                 LIMIT 1 ) ) / (SELECT (t2.close)
                                FROM prices t2
                                WHERE t2.date < t1.date
                                ORDER BY t2.date DESC
                                LIMIT 1 ) AS daily_returns
FROM prices

上面的查询添加了一列 daily_returns 到我的表中。

我想获得前 5 个最高的 daily_returns。我尝试使用 ORDER BY，但是，这不适用于计算列。

关于如何获得前 5 名最高 daily_returns 的任何建议？

Answer 1

更新:MySQL 8

SELECT 
    prices.*, 
    prices.close - LAG(prices.close) OVER w AS daily_return
FROM prices
WHERE api_endpoint = 'daily'
WINDOW w AS (ORDER BY prices.`date` ASC)
ORDER BY daily_return DESC
LIMIT 5;

MySQL 5.7 及更低版本

使用MySQL variable存储最后一天的 close 值。将其与当前行的 close 值进行比较以进行计算。

SELECT 
    *
FROM (
    SELECT
        prices.*,
        (`close` - @old_close) / @old_close AS daily_return, -- Use @old_case, currently it has value of old row, next column will set it to current close value.
        @old_close:= `close`    -- Set @old_close to close value of this row, so it can be used in next row
    FROM prices,
    (SELECT @old_close:= 0 as o_c) AS t -- Initialize old_close as 0
    WHERE api_endpoint = 'daily'
    ORDER BY `date` ASC         -- return is calculated based on last day close, so keep it sorted based on ascending order of date
) AS tt
ORDER BY daily_return DESC
LIMIT 5;

引用:How to get diff between two consecutive rows