MySQL，最后一个(再次)

Question

我在 Stackoverflow 中找到了大量关于如何选择组中第一行和最后一行的示例，但我无法根据需要调整它们。唉，我对 MySQL 的有限了解无济于事。

一些数据(date_time、val1 和 val2)是随机保存的。我需要将它们分组 15 分钟，然后每 15 分钟计算一次:

• 间隔中的行数(完成)
• val1 分钟(完成)
• val1 平均(完成)
• val1 最大值(完成)
• 小组中的第一个 val2(完成，使用 COALESCE 很容易)
• 组中的最后一个 val2(我需要你的帮助)

这是我的数据、期望的结果和迄今为止的最大努力...

# what I would like to obtain:
#_timeslice              _count     _min_val1   _avg_val1   _max_val1   _first_val2  _last_val2
# 2017-11-01T00:00:00Z     4          90          100         110          200        210
# 2017-11-01T00:15:00Z     3         100          110         120          240        230
# 2017-11-01T00:30:00Z     2         110          120         130          270        265
# 2017-11-01T00:45:00Z     4          80          112.5       150          290        320

CREATE TABLE `test-table`
    (`date_time` datetime, `val1` float, `val2` float);

INSERT INTO `test-table`
    (`date_time`,         `val1`, `val2`)
VALUES

    ('2017-11-01 00:00:00', 100,   200),  # first 15 min
    ('2017-11-01 00:01:00', 110,   190),
    ('2017-11-01 00:02:05',  90,   220),
    ('2017-11-01 00:14:00', 100,   210),

    ('2017-11-01 00:15:00', 100,   240),  # second 15 min
    ('2017-11-01 00:16:00', 110,   250),
    ('2017-11-01 00:28:00', 120,   230),

    ('2017-11-01 00:30:00', 110,   270),  # third 15 min
    ('2017-11-01 00:44:59', 130,   265),

    ('2017-11-01 00:50:00', 120,   290),  # fourth 15 min 
    ('2017-11-01 00:55:00', 150,   300),
    ('2017-11-01 00:57:00', 100,   280),
    ('2017-11-01 00:58:00',  80,   320)
;

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`date_time`)/900)*900) AS _timeslice,
    COUNT(*) AS _count,
    min(`val1`) as _min_val1,
    avg(`val1`) as _avg_val1,
    max(`val1`) as _max_val1,
    coalesce(`val2`) as _first_val2  # returns the first val2 in the group
    # ----> how to add here the last val2 for the group?
FROM `test-table`
GROUP BY _timeslice;

...这是一个相同的 fiddle SQLFiddle_grouping_15_min

有人可以帮助我了解要使用的语法吗？

Answer 1

SELECT a.*
  FROM `test-table` a
  JOIN 
     ( SELECT FLOOR(UNIX_TIMESTAMP(date_time)/900) timeslice
            , MIN(date_time) min_date_time 
            , MAX(date_time) max_date_time 
         FROM `test-table` 
        GROUP 
           BY timeslice
     ) b
    ON b.timeslice = FLOOR(UNIX_TIMESTAMP(a.date_time)/900)
   AND a.date_time IN(b.min_date_time,b.max_date_time);

+---------------------+------+------+
| date_time           | val1 | val2 |
+---------------------+------+------+
| 2017-11-01 00:00:00 |  100 |  200 |
| 2017-11-01 00:14:00 |  100 |  210 |
| 2017-11-01 00:15:00 |  100 |  240 |
| 2017-11-01 00:28:00 |  120 |  230 |
| 2017-11-01 00:30:00 |  110 |  270 |
| 2017-11-01 00:44:59 |  130 |  265 |
| 2017-11-01 00:50:00 |  120 |  290 |
| 2017-11-01 00:58:00 |   80 |  320 |
+---------------------+------+------+