回答編集履歴
5
推敲
test
CHANGED
|
@@ -92,4 +92,4 @@
|
|
|
92
92
|
|
|
93
93
|
|
|
94
94
|
|
|
95
|
-
という事なので、桁数的に不明な部分がありますので、２０桁が’YYYYMMDDHH24MISSUS’だとして、必要な桁のみをsubstrでピックアップする
|
|
95
|
+
という事なので、桁数的に不明な部分がありますので、２０桁が’YYYYMMDDHH24MISSUS’だとして、必要な桁のみをsubstrでピックアップする事にしています。
|
4
推敲
test
CHANGED
|
@@ -32,7 +32,7 @@
|
|
|
32
32
|
|
|
33
33
|
```SQL
|
|
34
34
|
|
|
35
|
-
select active_date, active_day, active_time
|
|
35
|
+
select active_date, active_day, active_timezone, count(*) as cnt
|
|
36
36
|
|
|
37
37
|
from (
|
|
38
38
|
|
|
@@ -54,7 +54,7 @@
|
|
|
54
54
|
|
|
55
55
|
when 3 then '18-24'
|
|
56
56
|
|
|
57
|
-
end AS active_time
|
|
57
|
+
end AS active_timezone -- 時間帯
|
|
58
58
|
|
|
59
59
|
, to_timestamp(substr(active, 1, 20), 'YYYYMMDDHH24MISSUS') AS active_timestamp -- タイムスタンプ
|
|
60
60
|
|
|
@@ -82,9 +82,9 @@
|
|
|
82
82
|
|
|
83
83
|
*/
|
|
84
84
|
|
|
85
|
-
group by active_date, active_day, active_time
|
|
85
|
+
group by active_date, active_day, active_timezone
|
|
86
86
|
|
|
87
|
-
order by active_date, active_time
|
|
87
|
+
order by active_date, active_timezone
|
|
88
88
|
|
|
89
89
|
```
|
|
90
90
|
|
3
推敲
test
CHANGED
|
@@ -84,6 +84,8 @@
|
|
|
84
84
|
|
|
85
85
|
group by active_date, active_day, active_timerange
|
|
86
86
|
|
|
87
|
+
order by active_date, active_timerange
|
|
88
|
+
|
|
87
89
|
```
|
|
88
90
|
|
|
89
91
|
> 元の文字列は「2021040112000012345678」のような22桁の文字列です。
|
2
推敲
test
CHANGED
|
@@ -46,9 +46,9 @@
|
|
|
46
46
|
|
|
47
47
|
, case substr(active, 9, 2)::integer / 6
|
|
48
48
|
|
|
49
|
-
when 0 then '0-6'
|
|
49
|
+
when 0 then '00-06'
|
|
50
50
|
|
|
51
|
-
when 1 then '6-12'
|
|
51
|
+
when 1 then '06-12'
|
|
52
52
|
|
|
53
53
|
when 2 then '12-18'
|
|
54
54
|
|
1
推敲
test
CHANGED
|
@@ -72,11 +72,15 @@
|
|
|
72
72
|
|
|
73
73
|
) edit
|
|
74
74
|
|
|
75
|
+
/*
|
|
76
|
+
|
|
75
77
|
where active_date between '2021/04/01' and '2021/04/05'
|
|
76
78
|
|
|
77
79
|
and active_day in ('月','木','土')
|
|
78
80
|
|
|
79
81
|
and active_time between '12:00:00' AND '17:59:59'
|
|
82
|
+
|
|
83
|
+
*/
|
|
80
84
|
|
|
81
85
|
group by active_date, active_day, active_timerange
|
|
82
86
|
|