eliafino eliafino - 6 months ago 7
SQL Question

MySQL search range and calculate difference

This is an example, table PM0:

ID DATA V A W PF HZ WH S
1 2016-05-06 08:48:44 228 0.10 19 0.63 50 34020 1
1 2016-05-06 08:48:44 228 0.10 19 0.63 50 34020 1
2 2016-05-06 08:48:47 228 0.10 19 0.64 50 34021 1
3 2016-05-06 08:48:49 228 0.10 19 0.64 50 34023 1
4 2016-05-06 08:48:51 228 0.10 19 0.64 50 34024 1
5 2016-05-06 08:48:53 228 0.10 19 0.63 50 34026 1
6 2016-05-06 08:48:56 228 0.10 19 0.64 50 34027 1
7 2016-05-06 08:48:58 226 1.50 222 0.67 50 34030 1
8 2016-05-06 08:49:00 225 2.30 519 1.00 50 34040 1
9 2016-05-06 08:49:02 225 2.30 517 1.00 50 34050 1
10 2016-05-06 08:49:04 225 2.30 515 1.00 50 34070 1
11 2016-05-06 08:49:07 225 2.30 514 1.00 50 34080 1
12 2016-05-06 08:49:09 225 2.30 514 1.00 50 34090 1
13 2016-05-06 08:49:11 225 2.30 513 1.00 50 34100 1
14 2016-05-06 08:49:13 230 0.10 20 0.62 50 34105 0
15 2016-05-06 08:49:15 230 0.10 20 0.62 50 34106 0
16 2016-05-06 08:49:18 227 0.10 19 0.63 50 34107 0
17 2016-05-06 08:49:20 230 0.10 20 0.62 50 34108 0
18 2016-05-06 08:49:22 231 0.10 20 0.62 50 34109 0
19 2016-05-06 08:49:24 231 0.10 20 0.62 50 34110 0
20 2016-05-06 08:49:26 230 0.90 90 0.45 50 34115 0
21 2016-05-06 08:49:29 225 2.30 515 1.00 50 34120 0
22 2016-05-06 08:49:31 225 2.30 515 1.00 50 34130 0
23 2016-05-06 08:49:33 225 2.30 515 1.00 50 34140 0
24 2016-05-06 08:49:35 225 2.30 514 1.00 50 34150 0
25 2016-05-06 08:49:37 225 2.30 513 1.00 50 34160 0
26 2016-05-06 08:49:40 230 0.10 20 0.61 50 34165 1
27 2016-05-06 08:49:42 231 0.10 20 0.62 50 34166 1
28 2016-05-06 08:49:44 231 0.10 20 0.62 50 34167 1
29 2016-05-06 08:49:46 231 0.10 20 0.62 50 34168 1
30 2016-05-06 08:49:48 231 0.10 20 0.62 50 34169 1
31 2016-05-06 08:49:51 226 0.10 19 0.63 50 34170 1
32 2016-05-06 08:49:53 225 2.10 456 0.94 50 34175 1
33 2016-05-06 08:49:55 225 2.30 517 1.00 50 34185 1
34 2016-05-06 08:49:57 225 2.30 514 1.00 50 34195 1
35 2016-05-06 08:50:00 225 2.30 513 1.00 50 34200 1
36 2016-05-06 08:50:02 225 2.30 513 1.00 50 34210 1
37 2016-05-06 08:50:04 229 1.10 139 0.54 50 34215 1
38 2016-05-06 08:50:06 231 0.10 20 0.62 50 34220 1
39 2016-05-06 08:50:08 231 0.10 20 0.62 50 34221 1
40 2016-05-06 08:50:11 231 0.10 20 0.62 50 34222 1
41 2016-05-06 08:50:13 231 0.10 20 0.62 50 34223 1
42 2016-05-06 08:50:15 231 0.10 20 0.61 50 34224 1
43 2016-05-06 08:50:17 231 0.10 20 0.62 50 34225 1
44 2016-05-06 08:50:19 225 2.30 519 1.00 50 34230 0
45 2016-05-06 08:50:22 225 2.30 516 1.00 50 34240 0
46 2016-05-06 08:50:24 225 2.30 515 1.00 50 34250 0
47 2016-05-06 08:50:26 225 2.30 514 1.00 50 34260 0
48 2016-05-06 08:50:28 228 1.60 262 0.72 50 34265 0
49 2016-05-06 08:50:30 231 0.10 20 0.61 50 34266 0
50 2016-05-06 08:50:33 231 0.10 20 0.62 50 34267 0


My problem:
SELECT * FROM PM0 WHERE S=1

ID DATA V A W PF HZ WH S
1 2016-05-06 08:48:44 228 0.10 19 0.63 50 34020 1
2 2016-05-06 08:48:47 228 0.10 19 0.64 50 34021 1
3 2016-05-06 08:48:49 228 0.10 19 0.64 50 34023 1
4 2016-05-06 08:48:51 228 0.10 19 0.64 50 34024 1
5 2016-05-06 08:48:53 228 0.10 19 0.63 50 34026 1
6 2016-05-06 08:48:56 228 0.10 19 0.64 50 34027 1
7 2016-05-06 08:48:58 226 1.50 222 0.67 50 34030 1
8 2016-05-06 08:49:00 225 2.30 519 1.00 50 34040 1
9 2016-05-06 08:49:02 225 2.30 517 1.00 50 34050 1
10 2016-05-06 08:49:04 225 2.30 515 1.00 50 34070 1
11 2016-05-06 08:49:07 225 2.30 514 1.00 50 34080 1
12 2016-05-06 08:49:09 225 2.30 514 1.00 50 34090 1
13 2016-05-06 08:49:11 225 2.30 513 1.00 50 34100 1
26 2016-05-06 08:49:40 230 0.10 20 0.61 50 34165 1
27 2016-05-06 08:49:42 231 0.10 20 0.62 50 34166 1
28 2016-05-06 08:49:44 231 0.10 20 0.62 50 34167 1
29 2016-05-06 08:49:46 231 0.10 20 0.62 50 34168 1
30 2016-05-06 08:49:48 231 0.10 20 0.62 50 34169 1
31 2016-05-06 08:49:51 226 0.10 19 0.63 50 34170 1
32 2016-05-06 08:49:53 225 2.10 456 0.94 50 34175 1
33 2016-05-06 08:49:55 225 2.30 517 1.00 50 34185 1
34 2016-05-06 08:49:57 225 2.30 514 1.00 50 34195 1
35 2016-05-06 08:50:00 225 2.30 513 1.00 50 34200 1
36 2016-05-06 08:50:02 225 2.30 513 1.00 50 34210 1
37 2016-05-06 08:50:04 229 1.10 139 0.54 50 34215 1
38 2016-05-06 08:50:06 231 0.10 20 0.62 50 34220 1
39 2016-05-06 08:50:08 231 0.10 20 0.62 50 34221 1
40 2016-05-06 08:50:11 231 0.10 20 0.62 50 34222 1
41 2016-05-06 08:50:13 231 0.10 20 0.62 50 34223 1
42 2016-05-06 08:50:15 231 0.10 20 0.61 50 34224 1
43 2016-05-06 08:50:17 231 0.10 20 0.62 50 34225 1


Now, from ID 1 to 13 are sequential, I need difference between WH from 1 34020 and 13 34100, so 34100-34020=80, from ID 26 to 43, 34225-34165=60 and total 80+60=140.
140 result for S=1.
For S=0, from ID 14-25 -> 34160-34105=55 and ID 44-50 -> 34267-34230=37. 55+37=92.
92 result for S=0.

Please, I go crazy -_-

Answer

I don't think @Quicker's answer is right if you check this sqlfiddle.

And this will help you;)

select
    t1.s, sum(t2.wh - t1.wh) as diff
from pm0 t1
left join pm0 t2 on t1.id + 1 = t2.id and t1.s = t2.s and t2.data = date(now())
where t1.data = date(now())
group by t1.s
order by t1.id

SQL Fiddle

MySQL 5.6 Schema:

CREATE TABLE PM0
    (`ID` int, `DATA` datetime, `V` int, `A` int, `W` int, `PF` numeric, `HZ` int, `WH` int, `S` int)
;

INSERT INTO PM0
    (`ID`, `DATA`, `V`, `A`, `W`, `PF`, `HZ`, `WH`, `S`)
VALUES
    (1, '2016-05-06 08:48:44', 228, 0.10, 19, 0.63, 50, 34020, 1),
    (2, '2016-05-06 08:48:47', 228, 0.10, 19, 0.64, 50, 34021, 1),
    (3, '2016-05-06 08:48:49', 228, 0.10, 19, 0.64, 50, 34023, 1),
    (4, '2016-05-06 08:48:51', 228, 0.10, 19, 0.64, 50, 34024, 1),
    (5, '2016-05-06 08:48:53', 228, 0.10, 19, 0.63, 50, 34026, 1),
    (6, '2016-05-06 08:48:56', 228, 0.10, 19, 0.64, 50, 34027, 1),
    (7, '2016-05-06 08:48:58', 226, 1.50, 222, 0.67, 50, 34030, 1),
    (8, '2016-05-06 08:49:00', 225, 2.30, 519, 1.00, 50, 34040, 1),
    (9, '2016-05-06 08:49:02', 225, 2.30, 517, 1.00, 50, 34050, 1),
    (10, '2016-05-06 08:49:04', 225, 2.30, 515, 1.00, 50, 34070, 1),
    (11, '2016-05-06 08:49:07', 225, 2.30, 514, 1.00, 50, 34080, 1),
    (12, '2016-05-06 08:49:09', 225, 2.30, 514, 1.00, 50, 34090, 1),
    (13, '2016-05-06 08:49:11', 225, 2.30, 513, 1.00, 50, 34100, 1),
    (14, '2016-05-06 08:49:13', 230, 0.10, 20, 0.62, 50, 34105, 0),
    (15, '2016-05-06 08:49:15', 230, 0.10, 20, 0.62, 50, 34106, 0),
    (16, '2016-05-06 08:49:18', 227, 0.10, 19, 0.63, 50, 34107, 0),
    (17, '2016-05-06 08:49:20', 230, 0.10, 20, 0.62, 50, 34108, 0),
    (18, '2016-05-06 08:49:22', 231, 0.10, 20, 0.62, 50, 34109, 0),
    (19, '2016-05-06 08:49:24', 231, 0.10, 20, 0.62, 50, 34110, 0),
    (20, '2016-05-06 08:49:26', 230, 0.90, 90, 0.45, 50, 34115, 0),
    (21, '2016-05-06 08:49:29', 225, 2.30, 515, 1.00, 50, 34120, 0),
    (22, '2016-05-06 08:49:31', 225, 2.30, 515, 1.00, 50, 34130, 0),
    (23, '2016-05-06 08:49:33', 225, 2.30, 515, 1.00, 50, 34140, 0),
    (24, '2016-05-06 08:49:35', 225, 2.30, 514, 1.00, 50, 34150, 0),
    (25, '2016-05-06 08:49:37', 225, 2.30, 513, 1.00, 50, 34160, 0),
    (26, '2016-05-06 08:49:40', 230, 0.10, 20, 0.61, 50, 34165, 1),
    (27, '2016-05-06 08:49:42', 231, 0.10, 20, 0.62, 50, 34166, 1),
    (28, '2016-05-06 08:49:44', 231, 0.10, 20, 0.62, 50, 34167, 1),
    (29, '2016-05-06 08:49:46', 231, 0.10, 20, 0.62, 50, 34168, 1),
    (30, '2016-05-06 08:49:48', 231, 0.10, 20, 0.62, 50, 34169, 1),
    (31, '2016-05-06 08:49:51', 226, 0.10, 19, 0.63, 50, 34170, 1),
    (32, '2016-05-06 08:49:53', 225, 2.10, 456, 0.94, 50, 34175, 1),
    (33, '2016-05-06 08:49:55', 225, 2.30, 517, 1.00, 50, 34185, 1),
    (34, '2016-05-06 08:49:57', 225, 2.30, 514, 1.00, 50, 34195, 1),
    (35, '2016-05-06 08:50:00', 225, 2.30, 513, 1.00, 50, 34200, 1),
    (36, '2016-05-06 08:50:02', 225, 2.30, 513, 1.00, 50, 34210, 1),
    (37, '2016-05-06 08:50:04', 229, 1.10, 139, 0.54, 50, 34215, 1),
    (38, '2016-05-06 08:50:06', 231, 0.10, 20, 0.62, 50, 34220, 1),
    (39, '2016-05-06 08:50:08', 231, 0.10, 20, 0.62, 50, 34221, 1),
    (40, '2016-05-06 08:50:11', 231, 0.10, 20, 0.62, 50, 34222, 1),
    (41, '2016-05-06 08:50:13', 231, 0.10, 20, 0.62, 50, 34223, 1),
    (42, '2016-05-06 08:50:15', 231, 0.10, 20, 0.61, 50, 34224, 1),
    (43, '2016-05-06 08:50:17', 231, 0.10, 20, 0.62, 50, 34225, 1),
    (44, '2016-05-06 08:50:19', 225, 2.30, 519, 1.00, 50, 34230, 0),
    (45, '2016-05-06 08:50:22', 225, 2.30, 516, 1.00, 50, 34240, 0),
    (46, '2016-05-06 08:50:24', 225, 2.30, 515, 1.00, 50, 34250, 0),
    (47, '2016-05-06 08:50:26', 225, 2.30, 514, 1.00, 50, 34260, 0),
    (48, '2016-05-06 08:50:28', 228, 1.60, 262, 0.72, 50, 34265, 0),
    (49, '2016-05-06 08:50:30', 231, 0.10, 20, 0.61, 50, 34266, 0),
    (50, '2016-05-06 08:50:33', 231, 0.10, 20, 0.62, 50, 34267, 0)
;

Query 1:

select
 t1.s, sum(t2.wh - t1.wh) as diff
from pm0 t1
left join pm0 t2 on t1.id + 1 = t2.id and t1.s = t2.s
group by t1.s
order by t1.id

Results:

| S | diff |
|---|------|
| 1 |  140 |
| 0 |   92 |