SQLのFROM句で副問い合わせの組み合わせ

Question

前提・実現したいこと

次の口座について、入金と出金の両方が発生した日付を抽出する。
また、これまでの入金と出金それぞれの最大額も併せて抽出する。
ただし、FROM句で副問い合わせを用いること。
・口座番号：3104451

発生している問題・エラーメッセージ

下記コードまでは書いたのですが、日付を入金額と出金額の両方があった日で抽出すると、
これまでの入金と出金それぞれの最大額を抽出するとができません。逆に入金額と出金額の最大額で抽出すると、日付が入金額と出金額の両方があった日で抽出できないです。両方の条件で抽出することは不可能なんでしょうか？
２週間やっても全然わかりませんでした。。。ご教授よろしくお願いします。

該当のソースコード

SELECT T.`口座番号`,T.`日付`,MAX(T.`入金額`),MAX(T.`出金額`) FROM (SELECT * FROM `取引` WHERE `口座番号`= '3104451')as T where T.`口座番号`= '3104451' GROUP BY `日付` HAVING sum(`出金額`) >0 and SUM(`入金額`)>0

補足情報（FW/ツールのバージョンなど）

データベース：phpMyAdmin
サーバのバージョン： 10.4.14-MariaDB - Source distribution
バージョン情報： 5.0.2, 最終安定バージョン: 5.0.4
サーバの文字セット: UTF-8 Unicode (utf8mb4)
上記で操作しています。

ここにより詳細な情報を記載してください。

テーブルの定義　
テーブル名　取引
列名	　　　　型	　　　制約	　　　　　　備考
取引番号	　integer　　　PKEY	　　　　取引の連番
取引事由ID　　integer　　　FKEY	　　　取引の内容コード値
日付	　　　　date　　　not null	　　取引のあった日付
口座番号　　　　varchar　　not null　　　　取引のあった口座
入金額	　　integer		　　　　　　　預け入れの金額
出金額	　　integer		　　　　　　　引き出しの金額

Comments

[Orlofsky]

テーブル定義は CREATE TABLE文に変更して https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。
入出金額は integer で桁あふれしませんか？
SELECT文も上記の [コード] に修正してください。

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。

[toku37636566]

アドバイス有難うございます。
変更してみました。

[Orlofsky]

CREATE TABLE文を書けるようになった方が良いです。
2つ目の指摘は完全に無視しているから、すぐにしっぺ返しにあいますよ。

Answer 1

日付を入金額と出金額の両方があった日で抽出すると、これまでの入金と出金それぞれの最大額を抽出するとができません。
逆に入金額と出金額の最大額で抽出すると、日付が入金額と出金額の両方があった日で抽出できないです。

これらは同時に成り立たない事象です。
事象が異なる場合は、別な問合せにする必要があります。
別な問合せを合わせて抽出する方法としては、サブクエリーやインラインビューがありますが、
普通はどちらが効率が良いかは実行計画などで確認して選択します。

ただし、FROM句で副問い合わせを用いること。

MySQL系はサブクエリーが遅い事が多いので、そういう決めなのでしょうね。

・インラインビュー

SELECT  T1.日付 
     ,  T2.`最大出金額`
     ,  T2.`最大入金額`
FROM `取引` as T1 
     inner join (
        select `口座番号`
             , max(`出金額`) as `最大出金額`
             , max(`入金額`) as `最大入金額`
        FROM `取引`
        group by `口座番号`
     ) T2
     on   T1.`口座番号`=T2.`口座番号`
where T1.`口座番号`= '3104451' 
  and `出金額` > 0 and `入金額` > 0

Answer 2

「入金と出金の両方が発生した日付（日付と口座番号でGROUP BY）」と、「入金と出金それぞれの最大額（口座番号GROUP BY）」とでは、お互いにGROUP BYする粒度が違うため、「発生している問題」に記載されている通り、現状のソースコードでは同時に取得することができないのだと思います。

FROM句での副問い合わせを上手く使えば、違うGROUP BYの粒度の情報を一度に取得できると思いますよ。

Answer 3

SQLに限らず、見易いコードを書けるとコードの信頼性も仕事の効率も全然違います。

SQLの掟として、グループ関数(今回はMAX)を使う場合は、他のSELECTの並びにある列でGROUP BYする必要があります。

SELECT
    口座番号
  , 日付
  , MAX(入金額) AS MAX_入金額
  , MAX(出金額) AS MAX_出金額
FROM 取引
WHERE
    口座番号 = '3104451'
GROUP BY
    口座番号
  , 日付
HAVING
    sum(出金額) > 0
and SUM(入金額) > 0

Comments

[sazi]

質問の意図と全然違うので、流石にこれは回答になっていませんよ。