読者です 読者をやめる 読者になる 読者になる

じぶんメモ

プログラミングのメモ、日常のメモとか。

相関サブクエリを使って、各キーごとの最大値を取得する

相関サブクエリとは

SQLのサブクエリ内で、サブクエリ外部のテーブルを参照するサブクエリのこと。
以下の例では、相関サブクエリで、
companyテーブルのareaと同じ値を持つdeptテーブルのレコードを抽出している。

SELECT
  dept.person
FROM
  dept
WHERE
dept.area IN (
  SELECT
    compay.area
  FROM
    compay
)

複数条件に合致する最大値の取得

銀行テーブルから、取引日が2016/01/25の過去直近の各口座の残高を抽出する例を記載する。

  • 銀行テーブル
acc_notrans_datebalance
12016/01/2530000
12016/01/2420000
22016/01/235000
22016/01/224500
SELECT
  ACC1.balance
FROM
  ACCOUNT AS ACC1
WHERE
  ACC1.tran_date = (SELECT MAX(tran_date)
                   FROM   ACCOUNT AS ACC2
                   WHERE   ACC1.acc_no = ACC2.acc_no
                   AND     ACC2.trans_date <= '2016-01-25 00:00:00')

上記SQLを実行すると、以下の情報が取得できる。

acc_notrans_datebalance
12016/01/2530000
22016/01/235000
  • 仕組み
    相関サブクエリで、ACC1.acc_no = ACC2.acc_noとacc_no
    サブクエリの外側のテーブルと紐付けている。
    これによって、各口座番号ごとに、日付が最大かどうかを判断する
    線引きをしている。
SELECT '1', '2016-01-25',   30000 FROM ACCOUNT WHERE '2016-01-25' = '2016-01-25';
SELECT '1', '2016-01-24',   20000 FROM ACCOUNT WHERE '2016-01-24' = '2016-01-25';
―――――――――――――――――――――――――――――――――――― ←口座番号を分ける境界線
SELECT '2', '2016-01-23',   5000   FROM ACCOUNT WHERE '2016-01-23' = '2016-01-23';
SELECT '2', '2016-01-22',   4500   FROM ACCOUNT WHERE '2016-01-22' = '2016-01-22';

以下の記事が参考になりました。

gihyo.jp