複雑なSQL文について

質問

SQLite　バージョン3.7.10　を使用しています。

以下のサイトにソースがあります。
http://ideone.com/7FPtHL

どう説明するのが適切なのかあまり分かりませんが、
よろしくお願い致します。

t5テーブルにおいて
t2テーブルのt1_key1が't1_key_1'　かつ　t5テーブルのt3_keyに対応するt3テーブルのt1_keyに対応するt2テーブルのt1_key2の
例えば2012年1月の
（t5テーブルのprofit　×　その日（t5テーブルのdateの日の部分）に対応した2テーブルのrate）
を
t4テーブルのkeyごとにグループ化してその合計値を高い順に出力したいのですが、

のですがどのようなSQL文を書けば良いのでしょうか？

※
ただし、もしt5テーブルのdateに対応する日付がt2テーブルのdateにない場合は、
t5テーブルのdateに対応する月（年も含めて）のt2テーブルのrateの平均値、
それもない場合は、t5テーブルのdateに対応する年のt2テーブルのrateの平均値、
それもない場合は、t5テーブルの全てのrateの平均値、
を取得してt5テーブルのprofitにかけたいのです。。
※

例えばこのデータなら
2012年1月は

+----------+------+
|　t4_name　　|　sum　|
+----------+------+
|　t4_name_1　|500000|
+----------+------+
|　t4_name_2　|150000|
+----------+------+

という結果が得たいです。
t5テーブルのdateの'2012-01-03 00:00:02'の日付に対応するt2テーブルのdateは存在しないので、
t2テーブルの1月（2012-01）のrateの平均値（(100＋200＋300)/3＝200）を
profitにかけています。
複雑すぎるというか自分でもうまく説明できないので、
せめて※の部分のSQLの書き方だけでもアドバイス頂けないでしょうか？

回答No.2

OrangeCup150

select t4.name as t4_name, sum(t5.profit * coalesce(t2.rate, avg(case when date(t5.date, 'start of month') = date(t2.date, 'start of month') then t2.rate else null end), avg(case when date(t5.date, 'start of year') = date(t2.date, 'start of year') then t2.rate else null end), avg(t2.rate)) as sum
from t5 inner join t4 on (t5.t4_key = t4.key) inner outer join t3 on (t5.t3_key = t3.key) left outer join t2 on (t3.t1_key = t2.t1_key2 and date(t5.date) = date(t2.date))
where t2.t1_key1 = 't1_key1' and date(t5.date, 'start of month') = '2012-01-01'
group by t4.key, t4.name
order by sum desc

環境がないのでテストしていませんが、上記のような感じになるとかとおもいます。
（あまり自信はないですが）

あと、何でわかりにくく説明しているの？（T1 とか T2 key とか date とか）
具体的に説明してもらったほうが質問を理解しやすいです。（質問を理解するのに無駄に時間がかかりましたので・・・）

お礼

ご回答ありがとうございます。
すいません、たしかに具体的に書かないと説明が分りづらいですね・・・
自分としてはカラムの名前の付け方などがおかしいと思っているので
かえって混乱させることになると思って、抽象的なt1とかに変換しました。

なるほど、coalesceという関数を利用すれば「もし存在しなければ」が実現できそうですね（ですか？どうだろう）

ただ、実際にそのコードを動かしてみたのですが、
sumの閉じ括弧とinner outerとavgの箇所でエラーがでましたので
sumの閉じ括弧を増やし、inner outerはleft outerに変えたのですが
これは変えても良いのでしょうか？
それとavgなんですが、どうやらsumの中で使うとエラーがでるみたいです・・・

select sum(avg(profit)) from t5;

だけで試してみると、
「13SQLite error misuse of aggregate function arg()」
というエラーがでますので。

これはどのように記述を変えれば良いのでしょうか？

次のコード（sumの閉じ括弧とinner outerの修正済み）だとargでエラーが出る・・・

select
t4.name as t4_name,
sum(t5.profit *
coalesce(
t2.rate,
avg(case when date(t5.date, 'start of month') = date(t2.date, 'start of month') then t2.rate else null end),
avg(case when date(t5.date, 'start of year') = date(t2.date, 'start of year') then t2.rate else null end),
avg(t2.rate)
)
) as sum
from t5
inner join t4 on (t5.t4_key = t4.key)
left outer join t3 on (t5.t3_key = t3.key)
left outer join t2 on (t3.t1_key = t2.t1_key2 and date(t5.date) = date(t2.date))
where t2.t1_key1 = 't1_key1' and date(t5.date, 'start of month') = '2012-01-01'
group by t4.key, t4.name
order by sum desc

補足

すいません、訂正です

＞「13SQLite error misuse of aggregate function arg()」
「13SQLite error misuse of aggregate function avg()」

＞argでエラー
avgでエラー

回答No.1

lupin-333333

http://search.yahoo.co.jp/search?p=%22SQL%22+%E5%A4%96%E9%83%A8%E7%B5%90%E5%90%88&aq=-1&oq=&ei=UTF-8&fr=ie8sc&n=10&x=wrt

図を描いて、じっくり取り組んでください

お礼

ご回答ありがとうございます。
すいません、
自分としては
＞
＞※
＞ただし、もしt5テーブルのdateに対応する日付がt2テーブルのdateにない場合は、
＞t5テーブルのdateに対応する月（年も含めて）のt2テーブルのrateの平均値、
＞それもない場合は、t5テーブルのdateに対応する年のt2テーブルのrateの平均値、
＞それもない場合は、t5テーブルの全てのrateの平均値、
＞を取得してt5テーブルのprofitにかけたいのです。。
＞※

これをSQLで書くときに外部結合なのか
それともwhere句でのORで繋ぐことなのかがわからないのです・・・
どちらか一方が真なら取ってくるということなんでしょうけど、
その拾い方がわからないというか、、、
そこだけでもアドバイス頂けないでしょうか？