[pgsql-jp: 28828] 表を作成するのに最適な構造とSQLについて
Chie.M
gontakun @ fish.co.jp
2003年 1月 29日 (水) 14:50:00 JST
お世話になっております。Chieと申します。
担当者ID、年月、商品A個数、商品A金額、商品B個数、商品B金額、・・・
と言うようなテーブルを、担当者ごとに年月を横展開した表にしたいです。
Excelでありがちな表です。
このような場合に適した構造とSQL文についてのご相談です。
今の所、テーブル構造は以下のようになってます。
CREATE TABLE t_sales (
eid INT4 DEFAULT 0 NOT NULL, --担当ID
sales_month VARCHAR(6) NOT NULL, --年月(YYYYMM)
sales_ct_a INT4, --商品A個数
sales_co_a DECIMAL(8,2), --商品A金額
sales_ct_b INT4, --商品B個数
sales_co_b DECIMAL(8,2) --商品B金額
,CONSTRAINT eid_sales_pri PRIMARY KEY(sales_month,eid));
表のイメージは
担当者、200207-A数、200207-A額 、200208-A数、200208-A額 、・・・
と言う感じで、月ごとに、個数と金額を並列させて、担当者が月に
何個でいくらの売上げがあったかを表示させる物です。
# ここでは商品Aとしているものの値段は、固定されている額ではありません。
# 例えば携帯電話の使用料のようなイメージで、変動があります。
ユーザからの注文は、画面表示をした際に、
・デフォルトで1年分の表示
・画面に「<<」「<」「 >」「>>」のような矢印ボタンをつけて、
一月つづずらしたり、半年毎にずらして表示ができるように
・重たい画面はキライ
という事です。
この商品データは現在の所、3年分あります。
この為には元になるビューを作成しておく必要があると思うのですが
1年分表示させる為だけで、下記のようなものになってしまいました。
毎月データは増えますので、月データの追加があったら、
月に一度だけビューを書き換えるというような処理しか思いつきませんでした。
このSQLでは年数が経てば経つほど膨らんでいってしまうので
あまり気持ちが良くないです。
テーブル構造の変更も含めて、もっとマシな構造やSQLを
ご教授いただければと思います。
宜しくお願いします。
画面表示用のビューイメージ(1年分のみ)
SELECT
eid,
MAX(CASE WHEN sales_month='200207' THEN sales_ct_a END) AS sales_ct_a_07,
MAX(CASE WHEN sales_month='200207' THEN sales_co_a END) AS sales_co_a_07,
MAX(CASE WHEN sales_month='200208' THEN sales_ct_a END) AS sales_ct_a_08,
MAX(CASE WHEN sales_month='200208' THEN sales_co_a END) AS sales_co_a_08,
MAX(CASE WHEN sales_month='200209' THEN sales_ct_a END) AS sales_ct_a_09,
MAX(CASE WHEN sales_month='200209' THEN sales_co_a END) AS sales_co_a_09,
MAX(CASE WHEN sales_month='200210' THEN sales_ct_a END) AS sales_ct_a_10,
MAX(CASE WHEN sales_month='200210' THEN sales_co_a END) AS sales_co_a_10,
MAX(CASE WHEN sales_month='200211' THEN sales_ct_a END) AS sales_ct_a_11,
MAX(CASE WHEN sales_month='200211' THEN sales_co_a END) AS sales_co_a_11,
MAX(CASE WHEN sales_month='200212' THEN sales_ct_a END) AS sales_ct_a_12,
MAX(CASE WHEN sales_month='200212' THEN sales_co_a END) AS sales_co_a_12,
MAX(CASE WHEN sales_month='200301' THEN sales_ct_a END) AS sales_ct_a_01,
MAX(CASE WHEN sales_month='200301' THEN sales_co_a END) AS sales_co_a_01,
MAX(CASE WHEN sales_month='200302' THEN sales_ct_a END) AS sales_ct_a_02,
MAX(CASE WHEN sales_month='200302' THEN sales_co_a END) AS sales_co_a_02,
MAX(CASE WHEN sales_month='200303' THEN sales_ct_a END) AS sales_ct_a_03,
MAX(CASE WHEN sales_month='200303' THEN sales_co_a END) AS sales_co_a_03,
MAX(CASE WHEN sales_month='200304' THEN sales_ct_a END) AS sales_ct_a_04,
MAX(CASE WHEN sales_month='200304' THEN sales_co_a END) AS sales_co_a_04,
MAX(CASE WHEN sales_month='200305' THEN sales_ct_a END) AS sales_ct_a_05,
MAX(CASE WHEN sales_month='200305' THEN sales_co_a END) AS sales_co_a_05,
MAX(CASE WHEN sales_month='200306' THEN sales_ct_a END) AS sales_ct_a_06,
MAX(CASE WHEN sales_month='200306' THEN sales_co_a END) AS sales_co_a_06
FROM
t_sales
GROUP BY
eid,
HAVING
(MAX(CASE WHEN sales_month='200207' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200208' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200209' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200210' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200211' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200212' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200301' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200302' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200303' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200304' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200305' THEN sales_ct_a END))Is Not Null OR
(MAX(CASE WHEN sales_month='200306' THEN sales_ct_a END))Is Not Null
;
--環境-------
サーバ
RedHat 7.3
PostgreSQL 7.2.3
Apache 1.3.27
PHP 4.2.3
クライアント
Windows XP/Access 2002
PostgreSQL ODBC Driver 07.01.0006 日本語版
--------------------
----------------------------
Chie.M <gontakun @ fish.co.jp>
※メールアドレス変わりました。
pgsql-jp メーリングリストの案内