hana - 查找间隔 15 分钟的数据集中的最后一个值

Question

   ID Timestamp Value                                                               
   -- --------- -----
   1   11:59.54   10
   1   12.04.00   20
   1   12.12.00   31
   1   12.16.00   10
   1   12.48.00   05

我希望结果集为

   ID Timestamp Value
   -- --------- -----
   1   11:59.54   10
   1   12:00:00   10
   1   12.04.00   20
   1   12.12.00   31
   1   12:15:00   31
   1   12:16.00   10
   1   12:30:00   10
   1   12:45:00   10
   1   12.48.00   05

Answer 1

更多的咖啡可能会导致更简单的解决方案，但请考虑以下...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,timestamp TIMESTAMP
,value INT NOT NULL
);

INSERT INTO my_table VALUES
(1   ,'11:59:54',10),
(2   ,'12:04:00',20),
(3   ,'12:12:00',31),
(4   ,'12:16:00',10),
(5   ,'12:48:00',05);

...此外，我有一个整数表，如下所示:

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

所以...

SELECT a.timestamp
     , b.value
  FROM 
     ( SELECT x.*
            , MIN(y.timestamp) min_timestamp
         FROM 
            ( SELECT timestamp
                FROM my_table
               UNION
              SELECT SEC_TO_TIME((i2.i*10+i1.i)*900)
                FROM ints i1
                   , ints i2 
               WHERE SEC_TO_TIME((i2.i*10+i1.i)*900) 
                     BETWEEN (SELECT MIN(timestamp) FROM my_table) 
                         AND (SELECT MAX(timestamp) FROM my_table) 
               ORDER 
                  BY timestamp
            ) x
         LEFT
         JOIN my_table y
           ON y.timestamp >= x.timestamp
        GROUP 
           BY x.timestamp
    ) a
 JOIN my_table b
   ON b.timestamp = min_timestamp;

+-----------+-------+
| timestamp | value |
+-----------+-------+
| 11:59:54  |    10 |
| 12:00:00  |    20 |
| 12:04:00  |    20 |
| 12:12:00  |    31 |
| 12:15:00  |    10 |
| 12:16:00  |    10 |
| 12:30:00  |     5 |
| 12:45:00  |     5 |
| 12:48:00  |     5 |
+-----------+-------+