How to get first, max, min, last records in a group by with mysql

Question

can anyone help me to know first record from open column, max of from high, min of from low, and last record from close column with 5 minutes interval format group

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

I tried the below query for interval wise group by

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

Expected Output

Answer 1

One of the tricks to get the first & last values in MySql 5.x is to combine GROUP_CONCAT (with an order by) & SUBSTRING_INDEX.

Example:

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);

Result:

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

In MySql 8, you could also use the window function FIRST_VALUE for this.

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;

A test on db<>fiddle here

Answer 2

Use a subquery to get the minimum and maximum timestamps for each each period and symbol. Then use conditional aggregation to capture the values at those timestamp.

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

Answer 3

The posted dataset appears to be different. Anyway, does this help:

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 |
+---------------------+