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

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

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ログインして回答する

ベストアンサー

y-kawaz2013/03/18 16:56:10
満足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税込"というカラムを追加しました。

スター

3件のコメントを見る
うぃんど 2013/03/18 17:19:28

横からごめんですが、
もう1つの質問を見るとsubstr(年月日,1,6)という記述があるので、
日付はテキストのように思います。

スター
y-kawaz 2013/03/18 18:20:57

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

スター
sunsunpapa 2013/03/20 04:59:46

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

スター

その他の回答

y-kawaz2013/03/18 18:23:51
満足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 行)

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

スター

この質問へのコメント

コメントはありません

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

質問の情報

登録日時: 2013-03-18 11:36:11
終了日時: 2013-03-20 04:59:58
回答条件: 1人5回まで

この質問のカテゴリ

ウェブ制作

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

ベストアンサー

y-kawaz2013/03/18 16:56:10
満足100pt

うぃんど 2013/03/18 17:19:28

y-kawaz 2013/03/18 18:20:57

sunsunpapa 2013/03/20 04:59:46

その他の回答

y-kawaz2013/03/18 18:23:51
満足100pt

この質問へのコメント

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

質問の情報

この質問のカテゴリ

この質問に含まれるキーワード

人気の質問

メニュー

PC版

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

ベストアンサー

y-kawaz2013/03/18 16:56:10 満足100pt

うぃんど 2013/03/18 17:19:28

y-kawaz 2013/03/18 18:20:57

sunsunpapa 2013/03/20 04:59:46

その他の回答

y-kawaz2013/03/18 18:23:51 満足100pt

この質問へのコメント

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

質問の情報

この質問のカテゴリ

この質問に含まれるキーワード

人気の質問

メニュー

PC版

y-kawaz2013/03/18 16:56:10
満足100pt

y-kawaz2013/03/18 18:23:51
満足100pt