sql 集計結果の抽出方法を教えてください

質問

金額テーブル（会社名、金額）と詳細テーブル（会社名、日付、金額）があり

金額テーブル
A社　100円

詳細テーブル
A社　2011/1/1　10円
A社　2011/1/2　10円
A社　2011/1/3　50円
A社　2011/1/4　20円
A社　2011/1/5　10円
A社　2011/1/6　10円

詳細テーブルの金額を一件づつ合計していき、金額テーブルの金額を超えた時の
詳細テーブルの日付を抽出するSQLを教えてください。

上記データの2011/1/5を抽出したいのです。

宜しくお願いします。

回答No.4

root139

> 使用してたDBはDB2です。
> エラーになったのではなく、別の結果が出ていました。

補足、ありがとうございます。
DB2は手元に無いので確認は出来ないのですが、エラーになるならともかく、別の結果になったのはちょっと不思議です。

実行されたSQLの内容とその時のテーブルのデータを補足して頂ければ、何か分かるかもしれません。

回答No.3

root139

まず、使用しているDB製品とそのバージョンを補足してもらえますでしょうか？
(Oracle, SQLServer, MySQL, Access, etc・・・)
DBMSの種類によっても動作が違ってきますので。

> すみません　どうもうまく動きませんでした。
> SQL文SUMの実行途中の値とうまく比べていないような感じです。

エラーになったのでしょうか？
それとも、期待される結果と違うものが取得されたのでしょうか？

> 教えていただいたSQLは　SUMの集計途中の値と
> 比べているのですか？
> データの動きがわかりませんでした。
> よろしければ、SQL文の説明をして頂けないでしょうか。

No.2 の方だとちょっと複雑なので、No.1 の方のSQL(下記)を説明します。
(No.2 にも書きましたが、このSQLは複数の会社が在るとうまくいきませんが)

------------------------------------------------
SELECT
日付
FROM 詳細 d1 INNER JOIN 金額 m ON(d1.会社名 = m.会社名)
WHERE
(SELECT SUM(金額) FROM 詳細 d2 WHERE d2.日付 <= d1.日付) >=
m.金額
ORDER BY 日付 LIMIT 1;
------------------------------------------------

外側のSELECT、つまり詳細テーブルと金額テーブルの会社名が同じものを結合したもの
　　⇒ 「詳細 d1 INNER JOIN 金額 m ON(d1.会社名 = m.会社名)」

の一行一行について、それよりも前の日付の詳細テーブルの金額の合計を求めて、
　　⇒ 「SELECT SUM(金額) FROM 詳細 d2 WHERE d2.日付 <= d1.日付」

それが、外側のSELECTの金額テーブルの金額よりも大きなものだけを取出して、
日付順で最初の行だけを取得しています。
　　⇒ 「ORDER BY 日付 LIMIT 1」

この様に外側のクエリの値を参照する内側のクエリを相関サブクエリと言います。
詳しくは下記のページなどを参考にして下さい。

http://gihyo.jp/dev/serial/01/sql_academy2/000901
http://gihyo.jp/dev/serial/01/sql_academy2/000902
http://gihyo.jp/dev/serial/01/sql_academy2/000903
http://gihyo.jp/dev/serial/01/sql_academy2/000904
https://codezine.jp/article/detail/460?p=1
http://www.techscore.com/tech/sql/07_01.html#sql75

お礼

返信がおそくなりすみませんでした。

説明ありがとうございました。

使用してたDBはDB2です。

エラーになったのではなく、別の結果が出ていました。

回答No.2

root139

訂正です。

#1 のSQLでは、複数の会社が在る場合にうまくいきませんね。
会社ごとにグループ化してやる必要が有りました。

----------------------------------------------------
SELECT
d1.company, MIN(date)
FROM detail d1 INNER JOIN money m ON(d1.company = m.company)
WHERE
(SELECT SUM(amount) FROM detail d2
　 WHERE d1.company = d2.company AND d2.date <= d1.date)
>= m.amount
GROUP BY d1.company;
----------------------------------------------------

お礼

ありがとうございました。

このSQLを参考にして実行してみます。

お世話になりました。

補足

すみません　どうもうまく動きませんでした。

SQL文SUMの実行途中の値とうまく比べていないような感じです。

SQL初心者なのですが、データを一件づつ合計していくイメージがわかりません。

教えていただいたSQLは　SUMの集計途中の値と比べているのですか？

データの動きがわかりませんでした。

よろしければ、SQL文の説明をして頂けないでしょうか。

回答No.1

root139

2011/1/5は金額テーブルの金額と同じになった時ですよね？
金額テーブルの金額を超えた時だと2011/1/6になるかと。

抽出は相関サブクエリを使えば可能です。

詳細テーブルの金額の合計が金額テーブルの金額以上になった日とすると、下記の様になるかと。
どのDB製品が対象か分かりませんので、なるべく標準SQLに沿うように書きました。

-------------------------------------------------
SELECT
日付
FROM 詳細 d1 INNER JOIN 金額 m ON(d1.会社名 = m.会社名)
WHERE
(SELECT SUM(金額) FROM 詳細 d2 WHERE d2.日付 <= d1.日付) >=
m.金額
ORDER BY 日付 LIMIT 1;
-------------------------------------------------

WHERE 句は詳細テーブルのある日付の以前の金額の合計が金額テーブルの金額以上である条件を表しています。
金額テーブルの金額も結合を使わずにWHERE句内の相関サブクエリで取得することも出来ます。

金額テーブルの金額を超えた時を抽出する場合は「>=」の部分を「>」にして下さい。

参考URL：

http://codezine.jp/article/detail/907