効率のいい集計SQL文

質問

プログラム勉強中の者です・・・
効率のいいSQL文が書けずに困ってます・・。
みなさまどうかお助け下さい。m(_ _)m

以下のテーブルにおいて、

TABLE：SAMPLE　※主キー：CODE＋PM

|　CODE|　PM　|PROFIT|
―――――――――――
|　　101 |　　+　|　　200　|
|　　102 |　　+　|　　300　|
|　　103 |　　+　|　　400　|
|　　101 |　　-　|　　100　|
|　　102 |　　-　|　　200　|
|　　103 |　　-　|　　300　|

「PROFIT」を「CODE」で集計する。
但し、「PM」が「+」の場合は加算「-」の場合は減算する。

で以下の様な結果を得たい、という要件です。

| CODE | PROFIT |
―――――――――
|　　101 |　　　100　|
|　　102 |　　　100　|
|　　103 |　　　100　|

私が考えた方法は

SELECT CODE,SUM(PROFIT) FROM SAMPLE
GROUP BY CODE

として抽出したレコードをループさせて

SELECT PROFIT FROM SAMPLE
WHERE CODE = (ループ処理中のCODE)
AND　　　PM = "-"

の結果の2倍の値を減算していく、というモノなんですが、
これがとっても効率が悪く、データ量が増えると加速度的に
遅くなるのです。

１回のSQLで処理する方法、または
もっと効率の良いロジックは無いでしょうか・・・

ちなみに環境はoracle9i＋VB6です。

たぶん初歩的な事だと思うんですが、学習書やWebで調べる限りは
うまい解決方が見つかりませんでした・・・。

回答No.2

chukenkenkou · ベストアンサー

#1です。

group byが抜けていました。

＜訂正版＞
select
code,
sum(case PM when '+' then PROFIT
when '-' then -PROFIT
else null
end) as SumPROFIT
from SAMPLE
group by CODE

お礼

ご回答ありがとうございます！！

教えて頂いた方法で解決しました！！

このロジックで全体を書き直したところ、データ量はそのままで処理時間が約1/100になりました。

回答No.8

noname#140971

正確な実行速度を補足しておきます。

１万レコード集計して、かつ、リッチテキストボックスに表示を１０回繰り返した場合。

[イミディエイト]
70591.07 --- 70591.39

0.32秒

ですから　１万レコードの１回集計であれば 0.032 秒程度ということです。

Access だからフロントエンドに使えない訳じゃないです。
「Access は遅い」というのは、もはや、昔の話かと思います。

お礼

みなさま回答ありがとうございます！！

ただいまご回答を1件づつ読んでますです・・・・。

回答No.7

noname#140971

select CODE,SUM(Convert(integer, PM+'1')*PROFIT) AS PROFIT_SUM from sample GROUP BY CODE;

サーバーに10000万件を登録。
Access からサーバーにアクセスして上記コードを実行し結果を取得。
Timer では計測不可。
つまり、0.1秒以下でしたが・・・。

回答No.6

noname#182251

＃５です。実測してみました。但し条件がかなり違います
DB:Access2000
CODE+PMにインデックスはかかっていない
その他環境：CeleronD 3.06GH + 1GB RAM + XP Home SP2 + Delph6 UP2
約一万件（CODEは三種類だけ）

viewを使用した場合
688ミリ秒

SELECT SAMPLE.CODE, Sum([PROFIT]*IIf([PM]="+",1,-1)) AS s
FROM SAMPLE
GROUP BY SAMPLE.CODE;
の場合
984ミリ秒

convert関数を使用した場合は、上記環境では試験できませんでしたが、直接Accessで実行すると数分かかりました。

回答No.5

noname#182251

「効率（高速化）」が眼目であればVIEW
SELECT SAMPLE.CODE, SAMPLE.PM, Sum(SAMPLE.PROFIT) AS PROFITの合計
FROM SAMPLE
GROUP BY SAMPLE.CODE, SAMPLE.PM
HAVING ((([SUMPLE].[PM])="+"));

と
SELECT SAMPLE.CODE, SAMPLE.PM, Sum(SAMPLE.PROFIT) AS PROFITの合計
FROM SAMPLE
GROUP BY SAMPLE.CODE, SAMPLE.PM
HAVING ((([SUMPLE].[PM])="-"));
を作りCODEで連結して差を取るのが、関数を使うなどより二桁か三桁速いでしょう。

お礼

ご回答ありがとうございます！！

VIEWを使う方法もちょっと考えてみましたが・・・
今回はVIEWを使わない方法を探してました。

説明不足でした。すみません。m(_ _)m

回答No.4

noname#140971

SQL Server 2000 で書いてみました。

select CODE,SUM(Convert(integer, PM+'1')*PROFIT) AS PROFIT_SUM from sample GROUP BY CODE;

PM の + を +1、-を -1 に変換後に数値に変換しPROFIT に乗じた値の合計値を求める手法。
多分、オラクルでも可能なんじゃないですかね。

なお、得た結果は次のようです。

___|CODE|PROFIT_SUM|
---|----------------
__1|_101|_______100|
__2|_102|_______100|
__3|_103|_______100|

お礼

ご回答ありがとうございます！！

SQLServerとoracleではConvertの使い方が若干異なる？
のでしょうか・・・

なぜかうまく動きませんでした。

Replaceなら似たようなことができそうですが・・・

すみません。詳細な時間計測までしていただいたのに、私の勉強不足で検証できませんした・・・。

回答No.3

toshih2000

高速化だったら、こんな感じではどうでしょう。

SELECT CODE, SUM(SUMPROFIT) FROM
(select CODE, SUM(PROFIT) as SUMPROFIT FROM SAMPLE WHERE PM='+'
union
select CODE, SUM(-1 * PROFIT) as SUMPROFIT FROM SAMPLE WHERE PM='-') a
※多少の文法上の不備は勘弁してください。

出題意図からははずれるかもしれませんが、
高速化を考えるのであれば、PM を数値にして 数値の 1, -1 を入れておけば、そのまま掛け算できますので、速くなるのでは無いかと思います。それよりも、最初から FROFIT に符号をつける。

お礼

ご回答ありがとうございます！！

UNIONを使う手もあるんですね。
シンプルでわかりやすい方法で参考になります。

今回はテーブルの方は変えられないので・・・
苦しみました。

回答No.1

chukenkenkou

select
code,
sum(case PM when '+' then PROFIT
when '-' then -PROFIT
else null
end) as SumPROFIT
from SAMPLE

ORACLE風にするなら、case式をDECODE関数にしてもいいでしょう。