SQLのウィンドウ関数の使い方【lead関数】

今回はSQLのウィンドウ関数であるlead関数の使い方について説明します．

ビッグデータを扱う場合，pythonやRなどのプログラミング言語は適していません．ほぼSQLの一択です．

例として，医薬品情報が記載されている「drug」というテーブルを扱います．

薬の場合は「処方日」や「処方された薬が切れる日」などがありますが，

例えばコンビニの場合だとお客さんの「購入日」や「再度訪れた日」などに読み替えてもらうといろいろな場面で応用可能だと思います．

それでは操作対象である「drug」というテーブルについて説明します．

下記の通り，患者名（pat_id)ごとに医薬品の処方が記載さています．

SQLによる医薬品投与期間の算出

上記の drug テーブルに対して，下記のSQLを実行してみます．

SQLコードのポイントは３個です．

・レコードに行番号を付与している（row_number関数）

・lead関数を用いて，前回処方された日から何日遅れて薬をもらったかを計算している

・pat_id（患者ID），who_atc_name（医薬品名），dispensing_date（処方日）の順でソートしている

drop table if exists drug_con;
create table drug_con as
select 
 --行番号の付与
row_number() over( order by dg.pat_id, dg.who_atc_name, dg.dispensing_date ) as rownum,
 --患者ID
 dg.pat_id, 
 --薬の一般名
 dg.who_atc_name, 
  --薬の処方日
 dg.dispensing_date,
 --薬の処方日数
 dg.medication_days, 
 --薬がなくなった日付
 (dg.dispensing_date + cast(dg.medication_days as integer)) as expire_date,

 --薬がなくなった日から次に処方されるまでの日数（lead関数を使う）
 (( lead(dg.dispensing_date,1) over(partition by dg.pat_id, dg.who_atc_name order by dg.who_atc_name,dg.dispensing_date) ) 
 (dg.dispensing_date + cast(dg.medication_days as integer))) as delay,

 --薬の処方量(mg,mL)
 dg.medication_volume, 
 --薬の処方数
 dg.drug_volume,
 --薬の合計処方量(mg,mL)
 dg.drug_mg_sum 
 from drug　dg
where 
 --処方日が記載されていないレコードは除く
 dg.dispensing_date is not null
 --患者ID, 一般名，処方日の順に並べる
  order by dg.pat_id, dg.who_atc_name, dg.dispensing_date

SQLを実行すると，下記のようなデータが出力されます．

追加されたカラムを「赤字」に変えています．

delayのカラムを見てください．

「薬が切れた日から何日経過したときに，次の薬をもらったか」を示しています．

この値がマイナスの時は，「薬がきれる前に病院を訪れた」わけですね．

このカラムはlead関数を使って計算しています．

リード関数は，現在の行からのオフセットを指定することで，その位置から指定したオフセット分「後」にある指定されたレコードにアクセスすることができます ．

この例では，１レコード後のデータを見たいので，オフセットは「1」です．

このカラムが空白の時が，とある薬に関して処方が終わったレコードです．

患者（pat_id）に関して，「最初の処方日」と「delayが空白の処方日」を選ぶと任意の薬の処方期間を求めることができます．

また，「delayが30を超えた場合は終了とする」というような処理も簡単に実現できます．

PostgreSQLのインストール方法に関しては下記のページで説明していますので参考にしてください．

関連記事

■PostgreSQLのインストール方法