1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| CREATE TABLE `meter`
( `DeviceRowID` integer
, `Value` decimal(7,0)
, `Usage` integer
, `Date` datetime
) engine=INNODB
;
INSERT INTO `meter` (`DeviceRowID`, `Value`, `Usage`, `Date`)
VALUES (7, 971198, 0, '2021-01-25 22:45:15'),
(8, 477042, 0, '2021-01-25 22:45:21'),
(9, 569808, 0, '2021-01-25 22:45:32'),
(7, 972179, 0, '2021-01-25 23:50:00'),
(8, 477524, 0, '2021-01-25 23:50:00'),
(9, 570384, 0, '2021-01-25 23:50:00'),
(7, 972179, 0, '2021-01-26 01:15:10'),
(8, 477524, 0, '2021-01-26 01:15:23'),
(9, 571384, 0, '2021-01-26 01:15:32'),
(7, 973324, 0, '2021-01-26 22:45:15'),
(8, 477948, 0, '2021-01-26 22:45:21'),
(9, 572808, 0, '2021-01-26 22:45:32'),
(7, 973754, 0, '2021-01-28 03:50:30'),
(8, 478848, 0, '2021-01-28 03:51:23'),
(9, 573384, 0, '2021-01-28 03:52:11'),
(7, 974100, 0, '2021-01-28 21:12:10'),
(8, 478848, 0, '2021-01-28 21:12:23'),
(9, 573984, 0, '2021-01-28 21:12:32')
;
select * from `meter`
;
create view MS_EXTR as
(select `DeviceRowID` as MS_Ident
, max(`Value`) as MS_Value
, date(`Date`) as MS_Date
from `meter`
group by `DeviceRowID`
, date(`Date`)
order by `DeviceRowID`
, date(`Date`)
)
; |