プログラム勉強中の者です・・・
効率のいい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倍の値を減算していく、というモノなんですが、
これがとっても効率が悪く、データ量が増えると加速度的に
遅くなるのです。
1回のSQLで処理する方法、または
もっと効率の良いロジックは無いでしょうか・・・
ちなみに環境はoracle9i+VB6です。
たぶん初歩的な事だと思うんですが、学習書やWebで調べる限りは
うまい解決方が見つかりませんでした・・・。
No.7
- 回答日時:
select CODE,SUM(Convert(integer, PM+'1')*PROFIT) AS PROFIT_SUM from sample GROUP BY CODE;
サーバーに10000万件を登録。
Access からサーバーにアクセスして上記コードを実行し結果を取得。
Timer では計測不可。
つまり、0.1秒以下でしたが・・・。
No.6
- 回答日時:
#5です。
実測してみました。但し条件がかなり違います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
- 回答日時:
「効率(高速化)」が眼目であれば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
- 回答日時:
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
- 回答日時:
高速化だったら、こんな感じではどうでしょう。
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
- 回答日時:
select
code,
sum(case PM when '+' then PROFIT
when '-' then -PROFIT
else null
end) as SumPROFIT
from SAMPLE
ORACLE風にするなら、case式をDECODE関数にしてもいいでしょう。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- PHP DBのハッシュ化したパスワードをpassword_verifyで戻し照合したのですが上手く行きません 2 2023/02/06 13:24
- PHP $_SESSIONについて教えて下さい。 4 2023/03/13 13:45
- SQL Server [SQLServer] テーブル名からカラム名を取得する 1 2022/08/23 21:20
- その他(プログラミング・Web制作) visual studio code 共有方法 1 2022/04/06 17:55
- その他(プログラミング・Web制作) VS codeを使って、ラズパイ Picoを動かせる簡単な方法 1 2023/04/27 13:49
- Visual Basic(VBA) ローマ字、ハイフン付きの並び替え ローマ字抽出方法 Excelマクロ 4 2022/04/01 14:10
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- PHP PHPのエラーの解消法について教えて下さい。 1 2023/02/06 10:48
- PHP PHP MySql ページング 2 2022/09/20 06:38
- Excel(エクセル) 全角文字「ヴ」の半角文字「ヴ」への変換方法について 3 2022/11/05 12:07
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Oracleのビュー作成時に「指定...
-
抽出結果を1件ずつ次の抽出条件...
-
ROWNUMについて
-
Excel 2019 のピボットテーブル...
-
エクセルVBAで5行目からオート...
-
Oracle 2つのDate型の値の差を...
-
作番ってどういう意味でしょうか?
-
Accessでテーブル名やクエリ名...
-
ACCESSのクエリで集計で、先頭...
-
ACCESSに同時アクセス(編集)を...
-
IT用語について質問です。 以前...
-
Access IF文でテーブルに存在し...
-
Accessでコードを入れると名前...
-
Access テキスト型に対する指定...
-
「直需」の意味を教えてください
-
Vbaでアクセスからエクセルにリ...
-
ACCESSでの改行コード
-
Accessのリンクテーブルのパス...
-
SQLでの変数の扱いについて
-
ACCESSで表が作りたく、そのた...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ORACLEで一番最初の結果だけを...
-
Oracleのビュー作成時に「指定...
-
Oracle SQLにて固定長でデータ...
-
ROWNUMについて
-
DB2でREPLACEによる文字列の置換
-
ある値以上の空き番の最小値を...
-
distinct で抽出したレコード件数
-
WITH句で複数テーブルを定義す...
-
抽出結果を1件ずつ次の抽出条件...
-
sqlplusで日本語入力
-
VBAのRows.Selectについて
-
group by でselect
-
時間の重複を加味した連続時間S...
-
PL/SQL 複数件同じ値で更新す...
-
重複
-
動的SQLでのDECODE
-
sqlの条件文に関して
-
Excel 2019 のピボットテーブル...
-
「直需」の意味を教えてください
-
エクセルVBAで5行目からオート...
おすすめ情報