回答編集履歴
6
追記
test
CHANGED
@@ -20,4 +20,32 @@
|
|
20
20
|
|
21
21
|
記述を簡潔にすると以下の様になります。~~
|
22
22
|
|
23
|
-
実行時の時間を軸にしているようなので、取り下げ
|
23
|
+
~~実行時の時間を軸にしているようなので、取り下げ~~
|
24
|
+
|
25
|
+
ついでに、元のSQLを簡潔にすると
|
26
|
+
|
27
|
+
```SQL
|
28
|
+
|
29
|
+
select dt.datetime
|
30
|
+
|
31
|
+
, count(his.id)
|
32
|
+
|
33
|
+
from generate_series(
|
34
|
+
|
35
|
+
TO_CHAR(NOW(), 'YYYY/MM/DD HH24:00:00'):: timestamp + INTERVAL '-3 day',
|
36
|
+
|
37
|
+
TO_CHAR(NOW(), 'YYYY/MM/DD HH24:00:00'):: timestamp,
|
38
|
+
|
39
|
+
'1 hour'
|
40
|
+
|
41
|
+
) as dt(datetime)
|
42
|
+
|
43
|
+
left join t_history as his
|
44
|
+
|
45
|
+
on his.end_timestamp>= dt.datetime and his.end_timestamp< (dt.datetime + INTERVAL '1 hour')
|
46
|
+
|
47
|
+
group by dt.datetime
|
48
|
+
|
49
|
+
|
50
|
+
|
51
|
+
```
|
5
修正
test
CHANGED
@@ -4,7 +4,7 @@
|
|
4
4
|
|
5
5
|
select dt.datetime
|
6
6
|
|
7
|
-
, count(his.
|
7
|
+
, count(his.id)
|
8
8
|
|
9
9
|
from generate_series('2018/12/12'::timestamp, '2018/12/12'::timestamp + INTERVAL '1 day', '5 minute') as dt(datetime)
|
10
10
|
|
4
修正
test
CHANGED
@@ -6,17 +6,11 @@
|
|
6
6
|
|
7
7
|
, count(his.regist_timestamp)
|
8
8
|
|
9
|
-
from (
|
10
|
-
|
11
|
-
select datetime, datetime + interval '5 minute' as dtend
|
12
|
-
|
13
|
-
|
9
|
+
from generate_series('2018/12/12'::timestamp, '2018/12/12'::timestamp + INTERVAL '1 day', '5 minute') as dt(datetime)
|
14
|
-
|
15
|
-
) dt
|
16
10
|
|
17
11
|
left join t_history as his
|
18
12
|
|
19
|
-
on his.regist_timestamp >= dt.datetime and his.regist_timestamp < dten
|
13
|
+
on his.regist_timestamp >= dt.datetime and his.regist_timestamp < (datetime + INTERVAL '5 minute')
|
20
14
|
|
21
15
|
group by dt.datetime
|
22
16
|
|
3
修正
test
CHANGED
@@ -6,11 +6,17 @@
|
|
6
6
|
|
7
7
|
, count(his.regist_timestamp)
|
8
8
|
|
9
|
+
from (
|
10
|
+
|
11
|
+
select datetime, datetime + interval '5 minute' as dtend
|
12
|
+
|
9
|
-
from generate_series('2018/12/12'::timestamp, '2018/12/12'::timestamp + INTERVAL '1 day', '5 minute') as dt(datetime)
|
13
|
+
from generate_series('2018/12/12'::timestamp, '2018/12/12'::timestamp + INTERVAL '1 day', '5 minute') as dt(datetime)
|
14
|
+
|
15
|
+
) dt
|
10
16
|
|
11
17
|
left join t_history as his
|
12
18
|
|
13
|
-
on his.regist_timestamp >= dt.datetime and his.regist_timestamp <
|
19
|
+
on his.regist_timestamp >= dt.datetime and his.regist_timestamp < dtend
|
14
20
|
|
15
21
|
group by dt.datetime
|
16
22
|
|
2
修正
test
CHANGED
@@ -10,28 +10,14 @@
|
|
10
10
|
|
11
11
|
left join t_history as his
|
12
12
|
|
13
|
-
|
13
|
+
on his.regist_timestamp >= dt.datetime and his.regist_timestamp < (dt.datetime + INTERVAL '6 minute')
|
14
14
|
|
15
15
|
group by dt.datetime
|
16
16
|
|
17
17
|
```
|
18
18
|
|
19
|
-
因みに、参考にしているSQLですが、NOW()を正時に変更していますが、Date型だとそれは不要です。
|
19
|
+
~~因みに、参考にしているSQLですが、NOW()を正時に変更していますが、Date型だとそれは不要です。
|
20
20
|
|
21
|
-
記述を簡潔にすると以下の様になります。
|
21
|
+
記述を簡潔にすると以下の様になります。~~
|
22
22
|
|
23
|
-
```SQL
|
24
|
-
|
25
|
-
select dt.datetime
|
26
|
-
|
27
|
-
|
23
|
+
実行時の時間を軸にしているようなので、取り下げ
|
28
|
-
|
29
|
-
from generate_series(current_date + INTERVAL '-3 day', current_date, '1 hour') as dt(datetime)
|
30
|
-
|
31
|
-
left join t_history as his
|
32
|
-
|
33
|
-
on his.end_timestamp between (dt.datetime + INTERVAL '-3 day') and dt.datetime
|
34
|
-
|
35
|
-
group by dt.datetime
|
36
|
-
|
37
|
-
```
|
1
修正
test
CHANGED
@@ -1,16 +1,16 @@
|
|
1
|
-
指定した日付の5分間隔のタイムテーブルを基準に、`t_history`の`e
|
1
|
+
指定した日付の5分間隔のタイムテーブルを基準に、`t_history`の`regist_timestamp`と結合してカウントします。
|
2
2
|
|
3
3
|
```SQL
|
4
4
|
|
5
5
|
select dt.datetime
|
6
6
|
|
7
|
-
, count(his.e
|
7
|
+
, count(his.regist_timestamp)
|
8
8
|
|
9
9
|
from generate_series('2018/12/12'::timestamp, '2018/12/12'::timestamp + INTERVAL '1 day', '5 minute') as dt(datetime)
|
10
10
|
|
11
11
|
left join t_history as his
|
12
12
|
|
13
|
-
on his.e
|
13
|
+
on his.regist_timestamp between dt.datetime and (dt.datetime + INTERVAL '5 minute')
|
14
14
|
|
15
15
|
group by dt.datetime
|
16
16
|
|