SQLの相関サブクエリの使い方

ファンコミュニケーションズ(東証一部上場)

今回は,SQLの相関サブクエリの応用について説明します.

相関サブクエリは,プログラミングでいう「繰り返し」や「逐次処理」です.

今回の記事は,下記の記事をもとに進める少し発展的な内容です.

下記の投稿記事をご覧ください.

今回の記事は,下記のような方に参考になると思います

✔ ビッグデータを扱おうとしている人

✔ 相関サブクエリの応用方法を知りたい人

相関サブクエリを用いて,任意の期間における医薬品の種類数を求めます.

SQLの相関サブクエリの使い方【医薬品データの例】

材料は,以前使用した「drugテーブル」「drug_conテーブル」です.

このdrug_conテーブルに,「cont」というカラムを追加します

drug テーブル
drug_con テーブル

相関サブクエリによる指定期間内の医薬品数をもとめる

下記のSQLのポイントは2個です.

相関サブクエリを使用している

行番号をグループ化している(group by)

「drug_conテーブル」 を基本としつつ, 「drugテーブル」 を探索していく

というような処理を記述することができます.

また,行番号をグループ化することで,

「1行ごとの結果を算出できるようにしています」

--相関サブクエリを使った指定期間内の医薬品数の算出
drop table if exists drug_numincam cascade;
create table drug_numincam as
select *
from drug_con con
left outer join
(
select
--行番号
re.rownum as rownum_subq,
--医薬品数
count(distinct dg.who_atc_name)
from
drug_con re
inner join
drug dg
on re.pat_id = dg.pat_id
where
--医薬品の処方日と失効日を期間にしていする(相関サブクエリ)
(dg.dispensing_date between
(select re2.dispensing_date from drug_con re2 where re.rownum = re2.rownum)
and
(select re3.expire_date from drug_con re3 where re.rownum = re3.rownum))

--グループ化は行を指定する(1行ごとに結果を求めるための工夫)
group by re.rownum

)dsb
on con.rownum = dsb.rownum_subq

上記のSQLを適用すると,下記のテーブルが得られます.

「count」というカラムに,各レコードに関して「dispensing date」から「expire_date」の期間において,いくつの医薬品が使われていたかが記載されています.


drug_numincam テーブル

今回の記事は相関サブクエリを使いましたが,わかりましたでしょうか?ネットや本などでは相関サブクエリは,同一のテーブルを持ちていることが多いのですが,実際には異なるテーブル同士を用いることが一般的です.

このような処理は,ある程度データ量が多くなると(目安として1千万レコードを超えたあたり),膨大な実行時間がかかります.

今後ビッグデータを扱っていこうとしている方には,参考になったのではないかと思います. 今回のような相関サブクエリの使い方はかなり応用範囲が広いと思うので,是非覚えておいてください.

今回は,下記の関連記事をベースにしていますので,参考にしてください.

関連記事


コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

ABOUTこの記事をかいた人

某メーカーで数年間エンジニアとして勤務していました.研究,開発,品質管理とたらい回しの刑を満了し,現在はパッケージソフトウェア開発者として個人で活動しています.