(データベース)SQL 上で、売り上げに動的に重みを加味しながら集計することはできるでしょうか?

初投稿です。現在、次のようなテーブルが既に存在している状態だとします。(便宜上非正規形にしています)

<注文>
注文番号 (PK), 担当者, 売り上げ
1, 田中, ¥2,000
2, 山田, ¥3,000

ここで、¥4,000 の佐藤さんと山田さんで担当を行った注文3が入り、売り上げを2人で半分ずつ分ける必要が生じたとします。
(注文を分割してあたかも佐藤さんと山田さんそれぞれに注文があったように見せかけるのはできないものとします)
同じ注文に対して2人以上の担当をつけられないため、次のように構成を拡張しました。

<注文>
注文番号 (PK), 売り上げ
1, ¥2,000
2, ¥3,000
3, ¥4,000

<売り上げ>
注文番号 (PK), 担当者 (PK), 重み
1, 田中, 100%
2, 山田, 100%
3, 佐藤, 50%
3, 山田, 50%

ですが、今度は SQL の SUM 句で担当者ごとの売り上げ集計ができなくなってしまいました。
例えば山田さんは注文2の ¥3,000 と注文3の ¥4,000 × 50% の ¥2,000 を合計して
売り上げ ¥5,000 と出したいのですが、このような重み付けを加味した集計を SQL クエリだけで行うことは可能でしょうか?

回答の条件
  • 1人3回まで
  • 13歳以上
  • 登録:2011/01/12 18:00:58
  • 終了:2011/01/12 18:55:33

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/01/12 18:44:00

ポイント40pt

比較的環境を選ばない方法を書きますが、環境によって全角文字はそのままでは使えない場合(`や"で囲む必要があったりします)もあります

SELECT 売り上げ.担当者, SUM(注文.売り上げ * 売り上げ.重み) AS 売り上げ
FROM 売り上げ
LEFT JOIN 注文 ON 注文.注文番号 = 売り上げ.注文番号
GROUP BY 売り上げ.担当者

売り上げテーブルの重みフィールドは50%などの文字列ではなく0.5といった数値が入っているものとしています

 

SQLは完全な共通語ではなくRDBMSによって大きな違いがあったりもしますので、SQLとせずに必ずRDBMS名(MySQL?PostgreSQL?Access?SQLServer?ORACLE?・・・?)とバージョンを記述するクセをつけておくようにしましょう

 

また、人力検索で質問する際には「回答受付中もコメント・トラックバックを表示する」の欄にチェックを入れておくと細かなやりとりが出来て便利なので、出来ればチェックを入れておいてください

id:quesit

SUM 句で乗算は利用できたんですね。なるほど。

SQL 環境の記述については、個人的に使っている MySQL と仕事の SQL Server が使えるため、

できるだけ環境に依存しない方法が知りたかったので、あえて曖昧にしたのですが、だったらそう書いておくべきでしたね。

あと、今からでも設定を変更できたので、「回答受付中もコメント・トラックバックを表示する」のチェックを入れておきました。

まだ人力はてな初心者でよくわからないことも多いですので、調べてみることにします!

親切なアドバイス・回答ありがとうございました!

2011/01/12 18:54:30

その他の回答(1件)

id:y-kawaz No.1

y-kawaz回答回数1421ベストアンサー獲得回数2262011/01/12 18:43:41

ポイント35pt

↓こんなテーブルがある感じですよね

# SELECT * FROM 注文;
 注文番号 | 売り上げ
----------+----------
        1 |     2000
        2 |     3000
        3 |     4000
(3 rows)

# SELECT * FROM 売り上げ;
 注文番号 | 担当者 | 重み
----------+--------+------
        1 | 田中   |    1
        2 | 山田   |    1
        3 | 佐藤   |  0.5
        3 | 山田   |  0.5
(4 rows)

↓これでどうでしょう?

# SELECT 担当者, SUM(売り上げ * 重み) FROM 売り上げ, 注文 WHERE 売り上げ.注文番号 = 注文.注文番号 GROUP BY 担当者;
 担当者 | sum
--------+------
 山田   | 5000
 佐藤   | 2000
 田中   | 2000
(3 rows)
id:quesit

SUM 句内で乗算を使用することができたんですね…参考になります。

SQL は他の言語に比べて「そんな書き方できるんだ!」っていうことが多くて大変です。。

回答ありがとうございました!

2011/01/12 18:54:27
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/01/12 18:44:00ここでベストアンサー

ポイント40pt

比較的環境を選ばない方法を書きますが、環境によって全角文字はそのままでは使えない場合(`や"で囲む必要があったりします)もあります

SELECT 売り上げ.担当者, SUM(注文.売り上げ * 売り上げ.重み) AS 売り上げ
FROM 売り上げ
LEFT JOIN 注文 ON 注文.注文番号 = 売り上げ.注文番号
GROUP BY 売り上げ.担当者

売り上げテーブルの重みフィールドは50%などの文字列ではなく0.5といった数値が入っているものとしています

 

SQLは完全な共通語ではなくRDBMSによって大きな違いがあったりもしますので、SQLとせずに必ずRDBMS名(MySQL?PostgreSQL?Access?SQLServer?ORACLE?・・・?)とバージョンを記述するクセをつけておくようにしましょう

 

また、人力検索で質問する際には「回答受付中もコメント・トラックバックを表示する」の欄にチェックを入れておくと細かなやりとりが出来て便利なので、出来ればチェックを入れておいてください

id:quesit

SUM 句で乗算は利用できたんですね。なるほど。

SQL 環境の記述については、個人的に使っている MySQL と仕事の SQL Server が使えるため、

できるだけ環境に依存しない方法が知りたかったので、あえて曖昧にしたのですが、だったらそう書いておくべきでしたね。

あと、今からでも設定を変更できたので、「回答受付中もコメント・トラックバックを表示する」のチェックを入れておきました。

まだ人力はてな初心者でよくわからないことも多いですので、調べてみることにします!

親切なアドバイス・回答ありがとうございました!

2011/01/12 18:54:30
  • id:windofjuly
    うぃんど 2011/01/12 19:38:09
    ご質問の趣旨としては終了ということになるとは思うのですが「割り切れない場合は誤差が出る」という点が心残りです
     
    例えば下記のような具合に<売り上げ>のほうに金額を入れてしまって(面倒ですが)毎回計算するという方法のほうが良いような気がしますし、
    <売り上げ>
    注文番号 (PK), 担当者 (PK), 売り上げ
    1, 田中, ¥2,000
    2, 山田, ¥4,000
    3, 佐藤, ¥2,000
    3, 山田, ¥2,000
    非正規形としては下記のように両方に持たせてしまうという手もあると思います
    <注文>
    注文番号 (PK), 売り上げ
    1, ¥2,000
    2, ¥3,000
    3, ¥4,000
    <売り上げ>
    注文番号 (PK), 担当者 (PK), 売り上げ
    1, 田中, ¥2,000
    2, 山田, ¥4,000
    3, 佐藤, ¥2,000
    3, 山田, ¥2,000
     
    以上、蛇足です
  • id:quesit
    windofjuly さん
    確かに乗算で1円単位の誤差が出るのは見過ごすことはできないですね…
    なるほど、重みというパラメータを無くすという考え方で解決するわけですね。
    非正規形の実装も確かに考慮するべきですね。
    色々なパターンを教えていただいてありがとうございます。
    本当に参考になります…

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

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません