id:sunsunpapa
ポイントあり200ptベストアンサーあり
ウェブ制作

postgresqlについて教えてください。


下記のようなデータがあります。

年月日,売上,税込
------------------
20120101,100,105
20120115,200,210
20120305,300,315
20120603,200,210
20120801,50,52
20121201,100,105
20130101,100,105

下記のような2012年の月別の売上集計を行いたいと思っております
ポイントとしては、売上が存在しない月も出力する。
また、税抜と税込を列ではなく、行を分けて出力する。

年月,売上の集計
------------------
201201,300
201201,315
201202,0
201202,0
201203,300
201203,315
201204,0
201204,0
201205,0
201205,0
201206,200
201206,210
201207,0
201207,0
201208,50
201208,52
201209,0
201209,0
201210,0
201210,0
201211,0
201211,0
201212,100
201212,105

どのように記述すればいいのでしょうか？

回答の条件
  • 1人5回まで
  • 登録：
  • 終了：2013/03/20 04:59:58
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:y-kawaz No.1

回答回数1422ベストアンサー獲得回数226

ポイント100pt

その出力を必要とするシーンにツッコミたくなりますがそこはスルーしてSQLクイズのつもりで答えます。また、カラムの型とかも不明なので日付関連はdate型として話を進めます。

とりあえずテストデータを作って…

create TABLE t ("年月日" date,"売上" int,"税込" int);
INSERT INTO t VALUES('2012-01-01',100,105);
INSERT INTO t VALUES('2012-01-15',200,210);
INSERT INTO t VALUES('2012-03-05',300,315);
INSERT INTO t VALUES('2012-06-03',200,210);
INSERT INTO t VALUES('2012-08-01',50,52);
INSERT INTO t VALUES('2012-12-01',100,105);
INSERT INTO t VALUES('2013-01-01',100,105);

SQLと出力はこんな感じでどうでしょう。

  SELECT
    s.ym "年月",
    COALESCE(sum("売上"), 0) "売上の集計",
    '売上' "売上or税込"
  FROM
    (SELECT ('2012-' || s.m || '-01')::date ym FROM generate_series(1,12) as s(m)) s
  LEFT OUTER JOIN
    (SELECT date_trunc('month', "年月日") ym, "売上" FROM t) t2
  ON s.ym=t2.ym
  GROUP BY s.ym
UNION
  SELECT
    s.ym "年月",
    COALESCE(sum("税込"), 0) "売上の集計",
    '税込' "売上or税込"
  FROM
    (SELECT ('2012-' || s.m || '-01')::date ym FROM generate_series(1,12) as s(m)) s
  LEFT OUTER JOIN
    (SELECT date_trunc('month', "年月日") ym, "税込" FROM t) t2
  ON s.ym=t2.ym
  GROUP BY s.ym
ORDER BY "年月", "売上or税込";

出力

    年月    | 売上の集計 | 売上or税込
------------+------------+------------
 2012-01-01 |        300 | 売上
 2012-01-01 |        315 | 税込
 2012-02-01 |          0 | 売上
 2012-02-01 |          0 | 税込
 2012-03-01 |        300 | 売上
 2012-03-01 |        315 | 税込
 2012-04-01 |          0 | 売上
 2012-04-01 |          0 | 税込
 2012-05-01 |          0 | 売上
 2012-05-01 |          0 | 税込
 2012-06-01 |        200 | 売上
 2012-06-01 |        210 | 税込
 2012-07-01 |          0 | 売上
 2012-07-01 |          0 | 税込
 2012-08-01 |         50 | 売上
 2012-08-01 |         52 | 税込
 2012-09-01 |          0 | 売上
 2012-09-01 |          0 | 税込
 2012-10-01 |          0 | 売上
 2012-10-01 |          0 | 税込
 2012-11-01 |          0 | 売上
 2012-11-01 |          0 | 税込
 2012-12-01 |        100 | 売上
 2012-12-01 |        105 | 税込

  • 存在しない連続した値の生成には generate_series 関数が使えます。
  • 同一行に存在するカラムを2行に分割するとかは出来ないので売上と税込カラムと別のSELECTを実行してUNIONで繋げます。
  • 後は適当に外部結合して集約関数をかますだけです。
  • NULLはゼロにしたいようだったのでCOALESCE関数で0にしてます。
  • あとさすがに別の値である売上と税込を区別せずに一つのカラムにするのは気持ち悪いので"売上or税込"というカラムを追加しました。
他1件のコメントを見る
id:y-kawaz

あ、気づきませんでした。
一応文字列版を別回答に書いておきます。

2013/03/18 18:20:57
id:sunsunpapa

generate_series 便利な関数ですね。参考にさせていただきます。ありがとうございました。

2013/03/20 04:59:46

その他の回答（1件）

id:y-kawaz No.1

回答回数1422ベストアンサー獲得回数226ここでベストアンサー

ポイント100pt

その出力を必要とするシーンにツッコミたくなりますがそこはスルーしてSQLクイズのつもりで答えます。また、カラムの型とかも不明なので日付関連はdate型として話を進めます。

とりあえずテストデータを作って…

create TABLE t ("年月日" date,"売上" int,"税込" int);
INSERT INTO t VALUES('2012-01-01',100,105);
INSERT INTO t VALUES('2012-01-15',200,210);
INSERT INTO t VALUES('2012-03-05',300,315);
INSERT INTO t VALUES('2012-06-03',200,210);
INSERT INTO t VALUES('2012-08-01',50,52);
INSERT INTO t VALUES('2012-12-01',100,105);
INSERT INTO t VALUES('2013-01-01',100,105);

SQLと出力はこんな感じでどうでしょう。

  SELECT
    s.ym "年月",
    COALESCE(sum("売上"), 0) "売上の集計",
    '売上' "売上or税込"
  FROM
    (SELECT ('2012-' || s.m || '-01')::date ym FROM generate_series(1,12) as s(m)) s
  LEFT OUTER JOIN
    (SELECT date_trunc('month', "年月日") ym, "売上" FROM t) t2
  ON s.ym=t2.ym
  GROUP BY s.ym
UNION
  SELECT
    s.ym "年月",
    COALESCE(sum("税込"), 0) "売上の集計",
    '税込' "売上or税込"
  FROM
    (SELECT ('2012-' || s.m || '-01')::date ym FROM generate_series(1,12) as s(m)) s
  LEFT OUTER JOIN
    (SELECT date_trunc('month', "年月日") ym, "税込" FROM t) t2
  ON s.ym=t2.ym
  GROUP BY s.ym
ORDER BY "年月", "売上or税込";

出力

    年月    | 売上の集計 | 売上or税込
------------+------------+------------
 2012-01-01 |        300 | 売上
 2012-01-01 |        315 | 税込
 2012-02-01 |          0 | 売上
 2012-02-01 |          0 | 税込
 2012-03-01 |        300 | 売上
 2012-03-01 |        315 | 税込
 2012-04-01 |          0 | 売上
 2012-04-01 |          0 | 税込
 2012-05-01 |          0 | 売上
 2012-05-01 |          0 | 税込
 2012-06-01 |        200 | 売上
 2012-06-01 |        210 | 税込
 2012-07-01 |          0 | 売上
 2012-07-01 |          0 | 税込
 2012-08-01 |         50 | 売上
 2012-08-01 |         52 | 税込
 2012-09-01 |          0 | 売上
 2012-09-01 |          0 | 税込
 2012-10-01 |          0 | 売上
 2012-10-01 |          0 | 税込
 2012-11-01 |          0 | 売上
 2012-11-01 |          0 | 税込
 2012-12-01 |        100 | 売上
 2012-12-01 |        105 | 税込

  • 存在しない連続した値の生成には generate_series 関数が使えます。
  • 同一行に存在するカラムを2行に分割するとかは出来ないので売上と税込カラムと別のSELECTを実行してUNIONで繋げます。
  • 後は適当に外部結合して集約関数をかますだけです。
  • NULLはゼロにしたいようだったのでCOALESCE関数で0にしてます。
  • あとさすがに別の値である売上と税込を区別せずに一つのカラムにするのは気持ち悪いので"売上or税込"というカラムを追加しました。
他1件のコメントを見る
id:y-kawaz

あ、気づきませんでした。
一応文字列版を別回答に書いておきます。

2013/03/18 18:20:57
id:sunsunpapa

generate_series 便利な関数ですね。参考にさせていただきます。ありがとうございました。

2013/03/20 04:59:46
id:y-kawaz No.2

回答回数1422ベストアンサー獲得回数226

ポイント100pt

もう一つの質問見ると年月日はテキスト型のようなので、文字列バージョンも回答しておきます。

まずテストデータを作ります。

CREATE TABLE t ("年月日" text, "売上" int, "税込" int);
INSERT INTO t VALUES('20120101',100,105);
INSERT INTO t VALUES('20120115',200,210);
INSERT INTO t VALUES('20120305',300,315);
INSERT INTO t VALUES('20120603',200,210);
INSERT INTO t VALUES('20120801',50,52);
INSERT INTO t VALUES('20121201',100,105);
INSERT INTO t VALUES('20130101',100,105);

SQLです。

  SELECT
    s.ym "年月",
    COALESCE(sum("売上"), 0) "売上の集計",
    '売上' "売上or税込"
  FROM
    (SELECT ym::text FROM generate_series(201201,201212) as s(ym)) s
  LEFT OUTER JOIN
    (SELECT substr("年月日",1,6) ym, "売上" FROM t) t2
  ON s.ym=t2.ym
  GROUP BY s.ym
UNION
  SELECT
    s.ym "年月",
    COALESCE(sum("税込"), 0) "売上の集計",
    '税込' "売上or税込"
  FROM
    (SELECT ym::text FROM generate_series(201201,201212) as s(ym)) s
  LEFT OUTER JOIN
    (SELECT substr("年月日",1,6) ym, "税込" FROM t) t2
  ON s.ym=t2.ym
  GROUP BY s.ym
ORDER BY "年月", "売上or税込";

出力です。

  年月  | 売上の集計 | 売上or税込
--------+------------+------------
 201201 |        300 | 売上
 201201 |        315 | 税込
 201202 |          0 | 売上
 201202 |          0 | 税込
 201203 |        300 | 売上
 201203 |        315 | 税込
 201204 |          0 | 売上
 201204 |          0 | 税込
 201205 |          0 | 売上
 201205 |          0 | 税込
 201206 |        200 | 売上
 201206 |        210 | 税込
 201207 |          0 | 売上
 201207 |          0 | 税込
 201208 |         50 | 売上
 201208 |         52 | 税込
 201209 |          0 | 売上
 201209 |          0 | 税込
 201210 |          0 | 売上
 201210 |          0 | 税込
 201211 |          0 | 売上
 201211 |          0 | 税込
 201212 |        100 | 売上
 201212 |        105 | 税込
(24 行)

基本は全く変えてません、日付操作を文字列操作にしただけです。

コメント（0件)

コメントはまだありません

この質問への反応（ブックマークコメント）

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません
ユーザー登録をして使いはじめる!無料
すでに登録されている方はこちらからログイン
この質問に含まれるキーワード
知りたいことを検索してみよう
▲ページトップへ