mysql - 当天第一个和最后一个记录之间的差异，按天分组

Question

这是我的表格:

+----+---------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----+
| id | time                | gebr_laag_tarief | gebr_hoog_tarief | leve_laag_tarief | leve_hoog_tarief | huidig_verbruik | huidig_levering | gas |
+----+---------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----+
|  1 | 2015-12-22 17:46:33 |                0 |                0 |                0 |                0 |              20 |               0 |   0 |
|  2 | 2015-12-22 17:46:38 |                0 |                0 |                0 |                0 |              30 |               0 |   0 |
|  3 | 2015-12-22 17:46:42 |                0 |                0 |                0 |                0 |              40 |               0 |   0 |
|  4 | 2015-12-22 17:46:45 |                0 |                0 |                0 |                0 |              50 |               0 |   0 |
|  5 | 2015-12-22 17:46:52 |                0 |                0 |                0 |                0 |              60 |               0 |   0 |
|  6 | 2015-12-22 17:46:56 |                0 |                0 |                0 |                0 |              70 |               0 |   0 |
|  7 | 2015-12-22 17:47:00 |                0 |                0 |                0 |                0 |              80 |               0 |   0 |
|  8 | 2015-12-22 17:47:02 |                0 |                0 |                0 |                0 |              90 |               0 |   0 |
|  9 | 2015-12-22 17:47:06 |                0 |                0 |                0 |                0 |             100 |               0 |   0 |
| 10 | 2015-12-22 17:47:11 |                0 |                0 |                0 |                0 |              95 |               0 |   0 |
| 11 | 2015-12-22 17:47:19 |                0 |                0 |                0 |                0 |              85 |               0 |   0 |
| 12 | 2015-12-22 17:47:22 |                0 |                0 |                0 |                0 |              75 |               0 |   0 |
| 13 | 2015-12-24 17:47:24 |                0 |                0 |                0 |                0 |              65 |               0 |   0 |
| 14 | 2015-12-25 17:47:27 |                0 |                0 |                0 |                0 |              55 |               0 |   0 |
| 15 | 2015-12-26 18:08:50 |                0 |                0 |                0 |                0 |              35 |               0 |   0 |
| 16 | 2015-12-26 21:37:57 |               10 |               20 |                0 |                0 |               0 |               0 |   0 |
| 17 | 2015-12-26 21:38:06 |               20 |               40 |                0 |                0 |               0 |               0 |   0 |
| 18 | 2015-12-26 21:38:14 |               25 |               45 |                0 |                0 |               0 |               0 |   0 |
| 19 | 2015-12-26 21:38:22 |               30 |               50 |                0 |                0 |               0 |               0 |   0 |
| 20 | 2015-12-27 14:47:27 |               31 |               52 |                0 |                0 |              10 |               0 |   0 |
| 21 | 2015-12-27 14:47:40 |               36 |               53 |                0 |                0 |              15 |               0 |   0 |
| 22 | 2015-12-27 14:47:49 |               37 |               53 |                0 |                0 |               5 |               0 |   0 |
| 23 | 2015-12-27 14:48:00 |               37 |               54 |                0 |                0 |               6 |               0 |   0 |
| 24 | 2015-12-27 14:48:15 |               40 |               55 |                0 |                0 |              30 |               0 |   0 |
+----+---------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----+

这是我努力实现的目标:

+----------+-----------------------+
| Day      | diff_gebr_laag_tarief |
+----------+-----------------------+
| 25       |                     0 |
+----------+-----------------------+
| 26       |                    30 |
+----------+-----------------------+
| 27       |                    10 |
+----------+-----------------------+

换句话说，我想要每天的数字，在当月(即当月的 26 日)与前一天的最后一条记录之间的差异。在这个解释中，最后一条记录定义为在特定日期最后一次添加记录，但它也应该是该特定日期的最大 ID 号(即 14 | 2015-12-25 17:47: 27 或 19 | 2015-12-26 21:38:22)

我想要 gebr_laag_tarief、gebr_hoog_tarief、leve_laag_tarief、leve_hoog_tarief 和 gas 的差异。可以存在 0 (NULL) 值，但每一行都应包含与前一行相同或更大的数字。此外，每天都会添加记录，并且不会跳过任何一天。我只是添加了一些随机值来启动查询。

我在这条路上:

SELECT Sub1.JustDate, (b.gebr_laag_tarief - a.gebr_laag_tarief) AS Verschil
    -> FROM (
    -> SELECT DATE_FORMAT(FROM_UNIXTIME(time), '%Y-%m-%d') AS JustDate, MIN(time) AS MinTimeStamp, MAX(time) AS MaxTimeStamp
    -> FROM meetwaarden
    -> GROUP BY JustDate) Sub1
    -> INNER JOIN meetwaarden a on Sub1.MinTimeStamp = a.time
    -> INNER JOIN meetwaarden b on Sub1.MaxTimeStamp = b.time
    -> ;

但它并没有完全按照我的想法去做，作为天数的返回，差异是总差异而不是每日差异。

希望我足够清楚!谢谢你和我一起思考。第一次发帖所以对我放轻松。我搜索了 stackoverflow 和网络，但就是找不到答案 - 或者 - 无法让它工作。

Answer 1

您的查询已接近尾声，您只需在加入条件中指定这一天是第二次加入的前一天

SELECT t.my_time,DATE(b.my_time), MAX(a.tar) - MAX(b.tar) as diff
FROM 
(   SELECT MAX(id) as max_id, MAX(my_time) as my_time
    FROM test 
    GROUP BY DATE(my_time)
) t
LEFT JOIN test a ON a.id = t.max_id 
LEFT JOIN test b ON DATE(b.my_time) + INTERVAL 1 DAY = DATE(a.my_time)
WHERE b.my_time is not null
GROUP BY t.my_time

sql fiddle

将其转换为您的表/查询

SELECT DATE(t.my_time), MAX(a.gebr_laag_tarief) - MAX(b.gebr_laag_tarief) as diff_gebr_laag_tarief
FROM 
(   SELECT MAX(id) as max_id, MAX(time) as my_time
    FROM meetwaarden 
    GROUP BY DATE(time)
) t
LEFT JOIN meetwaarden a ON a.id = t.max_id 
LEFT JOIN meetwaarden b ON DATE(b.time) + INTERVAL 1 DAY = DATE(a.time)
WHERE b.time is not null
GROUP BY t.my_time

FIDDLE

输出:

+-----+-----------------------+
| day | diff_gebr_laag_tarief |
+-----+-----------------------+
| 25  | 0                     |
| 26  | 30                    |
| 27  | 10                    |
+-----+-----------------------+