NULLの問題について(テーブル結合の落とし穴)
最近あった問題点のお話です。
このブログでもDPC出来高対比の出し方は何度か記事にしてきました。
要は「データ識別番号」「入院年月日」の二つをキーにしてEFファイル/Dファイルを集計し、二つのテーブルを結合するという手順を踏むのですが、では日別に集計したらどうなるのでしょうか?
具体的には「データ識別番号」「実施年月日」をキーにして集計し結合すれば、日別の対比が出せます。
応用すればDPC期間別のデータなども作れて便利ですので、実際にやってみたところ、ものの見事に失敗しました。
原因は短期滞在入院料が含まれたデータを、何も考えずに集計したせいです。
何が起こるかというと…
- 当初のイメージ
データ識別番号 | 入院年月日 | 実施年月日 | 出来高点数 | DPC点数 |
---|---|---|---|---|
001 | 2016-07-01 | 2016-07-01 | 9000 | 12000 |
001 | 2016-07-01 | 2016-07-02 | 3000 | 0 |
001 | 2016-07-01 | 2016-07-03 | 3000 | 0 |
- 実際のデータ
データ識別番号 | 入院年月日 | 実施年月日 | 出来高点数 | DPC点数 |
---|---|---|---|---|
001 | 2016-07-01 | 2016-07-01 | 9000 | 12000 |
おや?7月2日と3日のデータが消えていますね?
実際の短期滞在入院料のデータは、Dファイル上では短期滞在入院料のみが記録され、他の日のデータは綺麗さっぱり消えています。Dファイルのデータはレセプトと同一のものになるよう設計されているので、ある意味では当然のことですが…
なので、短期滞在のデータを集計すると、こんな感じになります。
- Dファイル
データ識別番号 | 入院年月日 | 実施年月日 | 出来高点数 | DPC点数 |
---|---|---|---|---|
001 | 2016-07-01 | 2016-07-01 | 9000 | 12000 |
- EFファイル
データ識別番号 | 入院年月日 | 実施年月日 | 出来高点数 |
---|---|---|---|
001 | 2016-07-01 | 2016-07-01 | 9000 |
001 | 2016-07-01 | 2016-07-02 | 3000 |
001 | 2016-07-01 | 2016-07-03 | 3000 |
7月2日、7月3日にも入院はしていますが、データは存在しないので0点にもなりません。
これはとても重要なことなので、集計時に注意しておいて下さい。
さて、この二つを突合するとき、SQLやPandasでは「外部結合」(OUTER JOIN)と「内部結合」(INNER JOIN)を選べます。
「内部結合」では、二つのデータのキーを比べ、「一致するもの」だけが残り、片方にしか存在しないレコードはどこかへ消えます。
冒頭のデータ、
データ識別番号 | 入院年月日 | 実施年月日 | 出来高点数 | DPC点数 |
---|---|---|---|---|
001 | 2016-07-01 | 2016-07-01 | 9000 | 12000 |
は、DファイルとEFファイルの集計結果を「データ識別番号」「実施年月日」をキーに何も考えず結合した結果です。
さて、それではどうすれば正しいデータを得られるでしょうか?
内部結合で失敗したのだから、次は外部結合です。こちらは二つのデータのキーを比べ、存在しないものにはNULLを返します。
結果はどうなるかといいますと…
データ識別番号 | 入院年月日 | 実施年月日 | 出来高点数 | DPC点数 |
---|---|---|---|---|
001 | 2016-07-01 | 2016-07-01 | 9000 | 12000 |
001 | 2016-07-01 | 2016-07-02 | 3000 | NULL |
001 | 2016-07-01 | 2016-07-03 | 3000 | NULL |
本当は0であって欲しいところに、NULL
が入っています。
NULLってなんだよと言うと、レコードが存在しない状態を指すステータスです。
0は数値データであり、きちんと0として存在していますが、NULLはそうではありません。従って、
0 - 3000 = -3000
ですが、
NULL - 3000
はNULLを返します。
つまり、このNULLはどうにかして0に変換しなくてはいけないのです。
方法としては、
COALESCE
関数を使う。COALESCE(DPC点数,0)
とするとDPC点数がNULLになる場合、自動的に0に変換してくれます。SQLでの作業は諦めてスクリプト上で処理する。Pandasなら
fillna
メソッドでNULLレコードを一斉置換出来ます。
どちらを使うかはケース次第ですが、原因がわかってしまえば対処の難しい問題ではありません。