代码之家 › 专栏 › 技术社区 › Manikanta

如何通过mysql获取组中的第一、最大、最小和最后记录

sql mysql

Manikanta · 技术社区 · 7 年前

有谁能帮我知道开放列中的第一条记录,从高到高的最大记录,从低到低的最小记录,以及从封闭列中的最后一条记录,间隔为5分钟格式组

https://drive.google.com/open?id=1HMDECnuReJbnmRj0o_Gevn1ePMdpDoNy

我尝试了以下查询,以查找按间隔分组的用户

SELECT
    timestamp,
    symbol,
    open,
    MAX(high) as high,
    MIN(low) as low,
    close
FROM ohlc_database 
WHERE `symbol` LIKE 'AMBUJACEM' 
GROUP BY 
UNIX_TIMESTAMP(timestamp) DIV 300, symbol

3 回复 | 直到 7 年前

LukStorms 7 年前

获得第一个&MySql 5.x中的最后一个值是组合 GROUP_CONCAT (附命令)及; SUBSTRING_INDEX .

例子:

SELECT 
 symbol,
 FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP (`timestamp`)/300)*300) AS `timestamp`,
 CAST(SUBSTRING_INDEX(GROUP_CONCAT(`open` ORDER BY `timestamp` ASC SEPARATOR '|'),'|',1) AS DECIMAL(10,2)) AS `open`,
 MAX(`high`) AS `high`,
 MIN(`low`) AS `low`,
 CAST(SUBSTRING_INDEX(GROUP_CONCAT(`close` ORDER BY `timestamp` DESC SEPARATOR '|'),'|',1) AS DECIMAL(10,2)) AS `close`
FROM ohlc_database
WHERE symbol LIKE 'AMBUJACEM'
GROUP BY symbol, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP (`timestamp`)/300)*300);

symbol      timestamp           open    high    low     close
---------   ------------------- ------  ------  ------  ------
AMBUJACEM   24.01.2019 03:45:00 213,10  213,5   213,1   213,50
AMBUJACEM   24.01.2019 03:50:00 213,70  213,8   212     212,40
AMBUJACEM   24.01.2019 03:55:00 212,40  212,75  211,85  211,90

在MySql 8中,还可以使用窗口函数 FIRST_VALUE

SELECT 
 symbol, 
 ts05 as `timestamp`, 
 MIN(first_open) as `open`,
 MAX(`high`) as `high`,
 MIN(`low`) as `low`,
 MAX(last_close) as `close`
FROM
(
  SELECT 
   id, symbol, `timestamp`, `low`, `high`, 
   FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP (`timestamp`)/300)*300) AS ts05,
   FIRST_VALUE(`open`) OVER (PARTITION BY symbol, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP (`timestamp`)/300)*300) ORDER BY `timestamp` ASC) as first_open,
   FIRST_VALUE(`close`) OVER (PARTITION BY symbol, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP (`timestamp`)/300)*300) ORDER BY `timestamp` DESC) AS last_close
  FROM ohlc_database
  WHERE symbol LIKE 'AMBUJACEM'
) q
GROUP BY symbol, ts05;

测试 db<&燃气轮机;不停摆弄 here

Gordon Linoff 7 年前

SELECT UNIX_TIMESTAMP(timestamp) DIV 300 as ts,
       symbol,
       MAX(CASE WHEN timestamp = min_ts THEN open END) as open,
       MAX(high) as high,
       MIN(low) as low,
       MAX(CASE WHEN timestamp = max_ts THEN close END) as close
FROM ohlc_database o JOIN
     (SELECT symbol, 
             UNIX_TIMESTAMP(timestamp) DIV 300 as ts,
             MIN(timestamp) as min_ts, MAX(timestamp) as max_ts
      FROM ohlc_database
      WHERE symbol LIKE 'AMBUJACEM' 
      GROUP BY symbol, UNIX_TIMESTAMP(timestamp) DIV 300
     ) o2
     ON o2.ts = UNIX_TIMESTAMP(o.timestamp) DIV 300 AND
        o2.symbol = o.symbol
WHERE symbol LIKE 'AMBUJACEM' 
GROUP BY UNIX_TIMESTAMP(timestamp) DIV 300, symbol

-1

Strawberry 7 年前

发布的数据集似乎不同。不管怎样,这是否有帮助:

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`timestamp`)/300)*300) x FROM ohlc_database;
+---------------------+
| x                   |
+---------------------+
| 2019-01-24 03:45:00 |
| 2019-01-24 03:45:00 |
| 2019-01-24 03:45:00 |
| 2019-01-24 03:45:00 |
| 2019-01-24 03:45:00 |
| 2019-01-24 03:50:00 |
| 2019-01-24 03:50:00 |
| 2019-01-24 03:50:00 |
| 2019-01-24 03:50:00 |
| 2019-01-24 03:50:00 |
| 2019-01-24 03:55:00 |
| 2019-01-24 03:55:00 |
| 2019-01-24 03:55:00 |
| 2019-01-24 03:55:00 |
| 2019-01-24 03:55:00 |
+---------------------+