回答編集履歴

2

調整

2023/11/29 06:12

投稿

yambejp
yambejp

スコア117755

test CHANGED
@@ -6,7 +6,7 @@
6
6
  StartDT datetime,
7
7
  dist int,
8
8
  d date as( StartDT - interval 8 hour),
9
- index (dist,StartDT)
9
+ index (dist,d)
10
10
  );
11
11
 
12
12
  insert into tbl(StartDT,dist) values

1

調整

2023/11/29 05:57

投稿

yambejp
yambejp

スコア117755

test CHANGED
@@ -1 +1,31 @@
1
1
  datetimeから8時間引いた日付で集計すればよいでしょう
2
+ 集計用の生成列を予め用意するとより効率的です
3
+
4
+ ```SQL
5
+ create table tbl (id int primary key auto_increment,
6
+ StartDT datetime,
7
+ dist int,
8
+ d date as( StartDT - interval 8 hour),
9
+ index (dist,StartDT)
10
+ );
11
+
12
+ insert into tbl(StartDT,dist) values
13
+ ('2023-11-01 08:30',10),
14
+ ('2023-11-01 09:00',5),
15
+ ('2023-11-01 10:00',15),
16
+ ('2023-11-02 08:30',20),
17
+ ('2023-11-02 11:00',5),
18
+ ('2023-11-02 14:00',30),
19
+ ('2023-11-02 16:00',10),
20
+ ('2023-11-03 02:00',30),
21
+ ('2023-11-03 04:00',10),
22
+ ('2023-11-03 14:00',10),
23
+ ('2023-11-04 06:00',30),
24
+ ('2023-11-04 08:00',10),
25
+ ('2023-11-04 10:00',30),
26
+ ('2023-11-04 15:00',10);
27
+
28
+ select d,sum(dist) from tbl
29
+ where d between '2023-11-02' and '2023-11-04'
30
+ group by d
31
+ ```