MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE smartmeter (
  skey INT PRIMARY KEY auto_increment NOT NULL, 
  stimestamp TIMESTAMP NOT NULL, 
  watt varchar(100) NOT NULL ) 
  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci COMMENT='smart Meter log table';

INSERT INTO smartmeter VALUES
(1,"2022-10-04 01:01:01",10),
(2,"2022-10-04 01:02:01",10),
(3,"2022-10-04 01:03:01",101),
(4,"2022-10-04 01:04:01",101),
(5,"2022-10-04 01:05:01",102),
(6,"2022-10-04 01:06:01",101),
(7,"2022-10-04 01:07:01",102),
(8,"2022-10-04 01:08:01",10),
(9,"2022-10-04 01:09:01",10),
(10,"2022-10-04 01:09:01",10),
(11,"2022-10-04 01:09:01",107),
(12,"2022-10-04 01:09:01",101),
(13,"2022-10-04 01:09:01",109),
(14,"2022-10-04 01:09:01",10),
(15,"2022-10-04 01:09:01",10);

SELECT * FROM smartmeter;

Records: 15  Duplicates: 0  Warnings: 0




skey
stimestamp
watt


1
2022-10-04 01:01:01
10

2
2022-10-04 01:02:01
10

3
2022-10-04 01:03:01
101

4
2022-10-04 01:04:01
101

5
2022-10-04 01:05:01
102

6
2022-10-04 01:06:01
101

7
2022-10-04 01:07:01
102

8
2022-10-04 01:08:01
10

9
2022-10-04 01:09:01
10

10
2022-10-04 01:09:01
10

11
2022-10-04 01:09:01
107

12
2022-10-04 01:09:01
101

13
2022-10-04 01:09:01
109

14
2022-10-04 01:09:01
10

15
2022-10-04 01:09:01
10


/*1st query*/
SELECT skey,
       stimestamp,
       watt,
      LENGTH(watt) AS lenwatt,
       LAG(LENGTH(watt)) OVER (ORDER BY skey) llwatt
   FROM smartmeter;


skey
stimestamp
watt
lenwatt
llwatt


1
2022-10-04 01:01:01
10
2
null

2
2022-10-04 01:02:01
10
2
2

3
2022-10-04 01:03:01
101
3
2

4
2022-10-04 01:04:01
101
3
3

5
2022-10-04 01:05:01
102
3
3

6
2022-10-04 01:06:01
101
3
3

7
2022-10-04 01:07:01
102
3
3

8
2022-10-04 01:08:01
10
2
3

9
2022-10-04 01:09:01
10
2
2

10
2022-10-04 01:09:01
10
2
2

11
2022-10-04 01:09:01
107
3
2

12
2022-10-04 01:09:01
101
3
3

13
2022-10-04 01:09:01
109
3
3

14
2022-10-04 01:09:01
10
2
3

15
2022-10-04 01:09:01
10
2
2


/*2nd query*/
  SELECT s1.*,
         @n := COALESCE(IF(s1.skey=1,1,s2.skey), @n) As newGroup
   FROM smartmeter s1
  LEFT JOIN (
  SELECT skey,
       stimestamp,
       watt,
      LENGTH(watt) AS lenwatt,
       LAG(LENGTH(watt)) OVER (ORDER BY skey) llwatt
   FROM smartmeter) s2 ON s1.skey=s2.skey
  AND lenwatt != llwatt


skey
stimestamp
watt
newGroup


1
2022-10-04 01:01:01
10
1

2
2022-10-04 01:02:01
10
1

3
2022-10-04 01:03:01
101
3

4
2022-10-04 01:04:01
101
3

5
2022-10-04 01:05:01
102
3

6
2022-10-04 01:06:01
101
3

7
2022-10-04 01:07:01
102
3

8
2022-10-04 01:08:01
10
8

9
2022-10-04 01:09:01
10
8

10
2022-10-04 01:09:01
10
8

11
2022-10-04 01:09:01
107
11

12
2022-10-04 01:09:01
101
11

13
2022-10-04 01:09:01
109
11

14
2022-10-04 01:09:01
10
14

15
2022-10-04 01:09:01
10
14


/*final query*/
WITH cte AS (
  SELECT s1.*,
         @n := COALESCE(IF(s1.skey=1,1,s2.skey), @n) As newGroup
   FROM smartmeter s1
  LEFT JOIN (
  SELECT skey,
       stimestamp,
       watt,
      LENGTH(watt) AS lenwatt,
       LAG(LENGTH(watt)) OVER (ORDER BY skey) llwatt
   FROM smartmeter) s2 ON s1.skey=s2.skey
  AND lenwatt != llwatt)
SELECT MONTH(stimestamp) AS Month,
       DAY(stimestamp) AS Day,
       ROW_NUMBER() OVER (ORDER BY MIN(skey)) AS 'numbers of group',
       MIN(skey) AS 'first id',
       MAX(skey) AS 'last id',
       AVG(watt) AS 'Average watt',
       CEIL(AVG(watt)) AS 'Average watt rounded',
       newGroup
   FROM cte
  WHERE watt >= 100
GROUP BY newGroup, MONTH(stimestamp), DAY(stimestamp)


Month
Day
numbers of group
first id
last id
Average watt
Average watt rounded
newGroup


10
4
1
3
7
101.4
102
3

10
4
2
11
13
105.66666666666667
106
11

skey	stimestamp	watt
1	2022-10-04 01:01:01	10
2	2022-10-04 01:02:01	10
3	2022-10-04 01:03:01	101
4	2022-10-04 01:04:01	101
5	2022-10-04 01:05:01	102
6	2022-10-04 01:06:01	101
7	2022-10-04 01:07:01	102
8	2022-10-04 01:08:01	10
9	2022-10-04 01:09:01	10
10	2022-10-04 01:09:01	10
11	2022-10-04 01:09:01	107
12	2022-10-04 01:09:01	101
13	2022-10-04 01:09:01	109
14	2022-10-04 01:09:01	10
15	2022-10-04 01:09:01	10

skey	stimestamp	watt	lenwatt	llwatt
1	2022-10-04 01:01:01	10	2	null
2	2022-10-04 01:02:01	10	2	2
3	2022-10-04 01:03:01	101	3	2
4	2022-10-04 01:04:01	101	3	3
5	2022-10-04 01:05:01	102	3	3
6	2022-10-04 01:06:01	101	3	3
7	2022-10-04 01:07:01	102	3	3
8	2022-10-04 01:08:01	10	2	3
9	2022-10-04 01:09:01	10	2	2
10	2022-10-04 01:09:01	10	2	2
11	2022-10-04 01:09:01	107	3	2
12	2022-10-04 01:09:01	101	3	3
13	2022-10-04 01:09:01	109	3	3
14	2022-10-04 01:09:01	10	2	3
15	2022-10-04 01:09:01	10	2	2