回答編集履歴

4

加筆修正

2018/09/10 02:06

投稿

退会済みユーザー
test CHANGED
@@ -26,7 +26,7 @@
26
26
 
27
27
  ```sql
28
28
 
29
- select d.tm, coalesce(r.count, 0) as count
29
+ select d.ym, coalesce(r.count, 0) as count
30
30
 
31
31
  from (
32
32
 
@@ -57,3 +57,7 @@
57
57
 
58
58
 
59
59
  で動きますでしょうか。(動作テストできてませんけど。)
60
+
61
+ 2018年のデータだけ集計したいように見えたので、
62
+
63
+ [date_part(text, timestamp)](https://www.postgresql.jp/document/9.6/html/functions-datetime.html)()を使えば良さそうかなと。

3

見直し

2018/09/10 02:06

投稿

退会済みユーザー
test CHANGED
@@ -10,7 +10,7 @@
10
10
 
11
11
  from (
12
12
 
13
- select * from generate_series('2018-01-01'::timestamp, '2018-09-01'::timestamp, '+1 month'::interval)
13
+ select * from generate_series('2018-01-01'::timestamp, '2018-12-01'::timestamp, '+1 month'::interval)
14
14
 
15
15
  ) as s(a)
16
16
 
@@ -34,7 +34,7 @@
34
34
 
35
35
  from (
36
36
 
37
- select * from generate_series('2018-01-01'::timestamp, '2018-09-01'::timestamp, '+1 month'::interval)
37
+ select * from generate_series('2018-01-01'::timestamp, '2018-12-01'::timestamp, '+1 month'::interval)
38
38
 
39
39
  ) as s(a)
40
40
 

2

加筆修正

2018/09/10 01:48

投稿

退会済みユーザー
test CHANGED
@@ -42,11 +42,13 @@
42
42
 
43
43
  left join (
44
44
 
45
- SELECT to_char(date, 'YYYYMM') as ym, SUM(insert_count) as count
45
+ SELECT to_char(orders.date, 'YYYYMM') as ym, SUM(orders.insert_count) as count
46
46
 
47
47
  FROM orders
48
48
 
49
+ WHERE date_part('year', orders.date) = 2018
50
+
49
- WHERE to_char(date, '%Y') = to_char(DATE '2018-01-01', '%Y') GROUP BY to_char(date, 'YYYYMM')
51
+ GROUP BY to_char(orders.date, 'YYYYMM')
50
52
 
51
53
  ) as r on d.ym = r.ym
52
54
 

1

見直し

2018/09/10 01:48

投稿

退会済みユーザー
test CHANGED
@@ -10,7 +10,7 @@
10
10
 
11
11
  from (
12
12
 
13
- select * from generate_series('2018-01-01'::timestamp, '2018-12-01'::timestamp, '+1 month')
13
+ select * from generate_series('2018-01-01'::timestamp, '2018-09-01'::timestamp, '+1 month'::interval)
14
14
 
15
15
  ) as s(a)
16
16
 
@@ -34,7 +34,7 @@
34
34
 
35
35
  from (
36
36
 
37
- select * from generate_series('2018-01-01'::timestamp, '2018-09-01'::timestamp, '+1 month')
37
+ select * from generate_series('2018-01-01'::timestamp, '2018-09-01'::timestamp, '+1 month'::interval)
38
38
 
39
39
  ) as s(a)
40
40