目次


赤レンガ倉庫6丁目2番地

第4回 システム構築に有効なソリューション・テンプレート(医療保険事業編)

Comments

コンテンツシリーズ

このコンテンツは全#シリーズのパート#です: 赤レンガ倉庫6丁目2番地

このシリーズの続きに乞うご期待。

このコンテンツはシリーズの一部分です:赤レンガ倉庫6丁目2番地

このシリーズの続きに乞うご期待。

はじめに

第4回目は、前回に引続きテンプレートモデルの紹介をします。前回の紹介をうけて、あるお客様からテンプレートの必要性について、「テンプレートに合わせて業務フローを変える必要がありますか?」という質問を頂きました。同一の業種であっても会社毎の規模や業務スタイル、仕事の進め方で、分析ニーズも異ってくるはずです。しかし、業務の内容がマッチすれば(対象テンプレートが使用できる)、システムを最初から開発する必要はありません。テンプレートをお客様のニーズに合わせてカスタマイズすればよい訳です。システムを一から構築するのに比べて、開発スケジュールが短縮でき、コストの削減にもつながります。テンプレートとは開発工程の作業を補完するものと考ればよいと思います。

今回ご紹介しますテンプレートは、医療保険事業での患者支払請求分析ソリューション用テンプレートです。医療提供者の分析、保険契約者の分析を行う様なアプリケーションを作成する場合に、利用できる内容となっています。それでは、ソリューション・テンプレートとして提供する個々のコンポーネントの紹介から始めたいと思います。

医療保険- 患者の支払請求分析ソリューション・テンプレート

医療保険事業における患者の支払請求分析ソリューション用テンプレートとして、提供している基本コンポーネントを御紹介いたします。今回御紹介いたしますテンプレートは、業種、業態に特化し、かつ支払請求分析に必要な基本的なデータ項目を元に作成されています。 では最初に、ソリューション・テンプレートを構成する各コンポーネントについて、紹介していきます。

ソリューション・テンプレートで提供するコンポーネント

  • ビジネス上の質問セット
    どのような分析業務をシステム化したいかを、質問の形で表現しています。ここで紹介しているビジネス上の質問は、今回の説明で作成するシステムを想定して書かれています。実際には、このような問い合わせをシステム要件から抽出し、例えば、ここで提供していますテンプレートに不足している項目やテーブルの洗い出しを行い、実際のシステムに反映させる形で作成するのも、利用法の一つになります。ここでは、医療保険事業における患者の支払請求分析の基本的な要求を質問する内容になっています。
  • 論理データモデルに含まれる各エンティティと属性
    医療保険事業のシステム構築において、必要と思われるデータ項目についての詳細情報を、このテンプレートでは提供しています。実際には、これらの情報を用いて論理データモデルを構築するのが手順としてありますが、論理データモデルに含まれる各エンティティと属性の各項目を利用して、前述したビジネス上の質問セットに答えるために必要なスキーマ構成の例を、テンプレートとして提供しています。これらの詳細情報の一部が各テーブルのデータ項目としてモデル化されています。
  • スキーマの設計及び実装例
    Claim ItemとProductClaimという2つのFACTテーブルが存在するスター・スキーマ構造が、今回の医療保険事業における患者の支払請求分析用のテンプレート・スキーマになります。 ここでは、ビジネス上の質問を考慮し、2FACTの構成を採用しています。
  • テーブルの作成とスターインデックスの設定
    スキーマの設計及び実装例
    で示したスキーマ構成を実際に作成するDDLの記述例を提供しています。またINDEXの設定、特にスタースキーマ構成に最適化されたインデックスである、スターインデックスの定義例も提供しています。スターインデックスを設定することが、IBM Red Brick Warehouseのパフォーマンスを享受するための、必須条件となります。
  • ビジネス上の質問に答えるための SQL ステートメント
    作成したスタースキーマ構造のデータベースに対して、ビジネス上の質問を行う場合に使用されるSQLステートメントの例を提供しています。SQLの記述については、この例以外にも色々と考えられると思いますが、このDMLを利用して、より最適なクエリーの記述の考察とか、要求以外の処理を実施する場合の元になるSQLとして使用することもできます。

    以上に挙げたような、基本コンポーネントの全部、あるいは一部を利用して、医療保険事業における患者の支払請求分析アプリケーションを作成することが可能となります。それでは、次にソリューションテンプレートを使ったシステムの構築について、構築の手順に沿って見て行きましょう。

構築手順

1 ソリューション・テンプレートをより効果的に使用するためには、以下の手順で作業を行うことを、推奨しています。ここでは、実際のシステム構築手順の流れを示しながら、ソリューションテンプレートの利用ポイントとその使用方法を説明いたします。

Step:1 エンティティーと属性の定義を含む論理データ・モデルについて調べます。

ビジネス上の質問セットの内容から、システムを構築する上で必要なデータ項目を洗い出し、データを検証しながら、論理データモデルを作成していきます。
このステップで提供しますソリューションテンプレートビジネス上の質問セットは、今回構築するシステムのビジネス上の質問になります。 また論理データモデルを構成する各エンティティーと属性についての詳細情報を論理データモデルに含まれる各エンティティと属性で提供します。この情報をベースに論理データモデルを作成することが可能になります。

Step:2 スキーマについて調べます。

論理データモデルを元に、テーブルスキーマの設計を行います。この時、一番の注意点は、スタースキーマ構成を前提として設計を行うため、定義するテーブルをFactとDimensionに分けるように設計します。また、論理データモデルのエンティティや属性が各テーブルのデータ項目に該当するかどうかを判断、考慮しながら設計する必要があります。今回のソリューションテンプレートには、上記手順を踏んで作成したテーブルスキーマ構造が含まれています。

Step:3 データベースに格納するデータのソースを識別する。

設計が完了したテーブルスキーマの各テーブルに対して、必要とするデータの内容についての検証を行います。この場合、予め用意したサンプルデータを利用することで、このステップでの作業効率が図れます。ソリューションテンプレートとしては、システムで使用されるDBの項目定義書を提供しています。

Step:4 電子媒体からサーバーにファイルをインストールします。

IBM Red Brick Warehouse 製品をインストールして、実行可能な状態にします。

Step:5 具体的なデータ要件を正確に表わすよう、スキーマを変更します。

再度、スキーマ定義を検証し、データ要件の正確さを高め、必要であればスキーマ構造の変更を行います。

Step:6 スクリプトを実行し、データベースを作成します。

ソリューションテンプレートで提供しているデータベース作成スクリプトを、現行システム用にカスタマイズし、実行してデータベースを作成します。ここでは、テーブル作成用のスクリプトを提供しています。

Step:7 データベースにデータをロードします。

サンプルのデータ、あるいは実際に作成したデータをデータベースにロードします。

Step:8 選択したクライアント・ツールを利用してビジネス上の質問を実施します。

テンプレートで提供しているSQLステートメントをビジネス上の質問として、クライアントツールから実行します。このステップではクエリーパフォーマンスを考慮するインデックス作成用のスクリプトと、最終目的であるビジネス上の質問に答えるための SQL ステートメントを利用して、実際のビジネス上の質問に答えていきます。
この二つのソョリューション・テンプレート(インデックス作成用のスクリプト、ビジネス上の質問に答えるための SQL ステートメント)を有効に使うことで、最終的に出来上がったシステムのパフォーマンスと製品のクオリティを上げる効果があります。

それでは、最後にソリューション・テンプレートの詳細を示します。構築手順に従って、以下のテンプレートを利用することが、システム開発の過程で有効であることを御確認いただければと思います。

ビジネス上の質問

ソリューション・テンプレートとして提供しているビジネス上の質問ですが、今回構築するシステムの要求仕様であり、システム用件の範囲を定義しています。またこれらのビジネス上の質問に答えるために使用するSQL ステートメントの例を、テンプレートの一部として提供しています。

医療提供者の分析

質問 1: 2001 年の 1 月に一般医療提供者が行った上位 10 種類の処置は?件数別および請求総額別にリストしてください。

質問 2: 2000 年中に治療「X」を最も多く使用した一般医療提供者は?

質問 3: 2000 年中に治療「X」の治療費の請求額が「Y」ドルを超えた医療提供者は?

質問 4: 特定の医療提供者において、2000 年中に請求額が高額だった上位 10 種類の治療は?

質問 5: 2000 年中の治療「X」の平均の請求額は?

質問 6: 2000 年に治療「X」の請求を行った保険契約者のうち、入院治療を受けた契約者と外来治療を受けた契約者との比率は?

質問 7: 特定の医療提供者において、2000 年中に治療「X」について提出された請求の件数は?

質問 8: 2000 年中に治療「X」を使用した医療提供者の割合は?

保険契約者の分析

質問 9: 2001年の第2四半期に提出された請求金額が多かった上位 5 州は?

質問 10: 2000 年の第 4 四半期に受領した、50 歳を超える女性患者についての請求の件数は?

質問 11: 今年の最初の月に、指定ブランドの薬を使用した処方箋を要求した会員の割合は?

質問 12: 2000 年中の、患者の死亡に関係する請求件数は?

次に論理データモデルに含まれる各エンティティと属性の詳細について、記述しています。

エンティティーと属性の説明

この章では、論理データ・モデルに含まれる各エンティティーと属性について説明します。ここで説明されている各エンティや属性がその後に作成されるテーブルスキーマ構成に対応していきます。

Claim Item (支払請求項目)

支払請求で提出される項目の金額が含まれています。

  • Claim_Id - 特定の支払請求を示す固有の ID です。
  • Claim_Date - 支払請求が提出された日付です。
  • Line_Item_Number - 単一の支払請求内で順番に付けられた項目番号です。
  • Rejection_Code - Rejection Reason (拒否理由) から取得されます。
  • Service_Code - Service (サービス) から取得されます。
  • Patient_Id - Patient (患者) から取得されます。
  • Member_Id - Member (保険契約者) から取得されます。
  • Provider_Id - Provider (医療提供者) から取得されます。
  • Drug_Code - Prescription Drug (処方薬) から取得されます。
  • Primary_Diagnosis_Code - Diagnosis (診断) から取得されます。
  • Secondary_Diagnosis_Code - Diagnosis (診断) から取得されます。
  • Service_Date - 患者が治療を受けた日付です。
  • Claim_Item_Amount - 支払請求項目に関連付けられた金額の合計です。

Claim Summary (支払請求の要約)

特定の支払請求についての、支払請求項目の要約が含まれています。

  • Claim_Id - Claim Item (支払請求項目) から取得されます。
  • Claim_Date - 支払請求が提出された日付です。
  • Rejection_Code - Rejection Reason (拒否理由) から取得されます。
  • Member_Id - Member (保険契約者) から取得されます。
  • Provider_Id - Provider (医療提供者) から取得されます。
  • Patient_Id - Patient (患者) から取得されます。
  • Payment_Date - 保険契約者の支払請求に対する支払いが実施された日付です。
  • Disability_Start_Date - 患者の障害者手当ての受給が開始された日付です。
  • Disability_End_Date - 患者の障害者手当ての受給が終了した日付です。
  • Date_Deceased - 患者が死亡した日付です。
  • Claim _Amount - 保険契約者が提出した支払請求金額の合計です。
  • Payment_Amount - 保険契約者の支払請求に対して支払われた金額の合計です。
  • Co-Payment_Amount - 支払請求に関連する治療に対して保険契約者が支払った自己負担額です。

Diagnosis (診断)

特定の診断と診断者を示します。診断は「診察時」に行われるもので、身体の異常と考えられる点について説明したものです。医師による診断は正確性がより高く、患者を診察した後に担当医によって下されます。最終的な診断は最も正確であり、患者の退院後に入力されます。

  • Diagnosis_Code - 特定の診断を示す固有の番号です。
  • Diag_Description - テキストによる診断の説明です。
  • Diag_Source - 診断者 (医師、看護婦、医療記録) を示します。

Member (保険契約者)

医療保険プログラムの保険契約者になっている個人を示します。保険契約者資格は、通常、雇用者によって設定されます。

  • Member_Id - 特定の保険契約者を識別するために使用する固有の ID です。
  • Group_Policy_Number - Member Group (保険契約者グループ) から取得されます。
  • Member_Name - 保険契約者の氏名です。
  • Member_Occupation - 保険契約者の職業です。
  • Member_Employment_Status - 異常発生時の保険契約者の就労状況 (常勤、パート、休暇中など) です。
  • Member_Marital_Status - 保険契約者の婚姻状況です。
  • Member_Gender - 保険契約者の性別です。
  • Member_City - 保険契約者が居住している都市の名前です。
  • Member_County - 保険契約者が居住している郡の名前です。
  • Member_State - 保険契約者が居住している州の名前です。
  • Member_Postal_Code - 保険契約者の居住地の 5 桁の郵便番号です。

Member Group (保険契約者グループ)

保険契約を締結する対象のグループを示します。このグループは、一般的に、保険会社との間で医療保険について契約する雇用者または組織です。
Group_Policy_Number - 雇用者 (グループ) と医療保険会社との間の契約に対して割り当てられた保険証書番号です。

  • Group_Name - 従業員に医療給付を提供している会社の正式名称です。
  • Group_Description - グループの説明です。
  • Effective_Date - 契約が発効した日付です。
  • Cancel_Date - 契約が取り消された日付です。
  • Renewal_Date - 契約が更新される日付です。
  • Group_City - グループがビジネスを行っている都市の名前です。
  • Group_State - グループがビジネスを行っている州の名前です。
  • Group_Postal_Code - グループがビジネスを行っている場所の郵便番号です。
  • Group_Sic_Code - グループに関連付けられている業種の識別に使用する標準産業コードです。
  • Insurance_Carrier - 保険契約を締結した保険会社の名前です。
  • Sales_Rep_Name - 保険契約をグループに販売した営業担当員の名前です。

Patient (患者)

治療を受け、その治療に対する支払請求を行った患者の名前が含まれます。患者は、保険契約者自身、保険契約者の配偶者、または保険契約者の扶養者の場合があります。

  • Patient_Id - 特定の患者を識別する固有の番号です。
  • Patient_Name - 治療を受けた患者の氏名です。
  • Patient_Mbr_Relationship - 患者と保険契約者との続柄 (配偶者、扶養者、保険契約者自身) です。
  • Patient_Occupation - 患者の職業です。
  • Patient_Marital_Status - 患者の婚姻状況です。
  • Patient_Gender - 患者の性別です。
  • Patient_City - 患者が居住している都市の名前です。
  • Patient_County - 患者が居住している郡の名前です。
  • Patient_State - 患者が居住している州の名前です。
  • Patient_Postal_Code - 患者の居住地の 5 桁の郵便番号です。

Period (期間)

その年の日次のレコードが含まれています。

  • Date_Key - 特定の日を示す整数です。
  • Calendar_Day_Num - 暦年中の特定の日を示す整数です。
  • Calendar_Month_Abbr - 暦年中の特定の月を示す、月名の省略名です。
  • Calendar_Month_Name - 暦年中の特定の月を示す名前です。
  • Calendar_Month_Num - 暦年中の特定の月を示す整数です。
  • Calendar_Quarter_Num - 暦年中の、1 月から始まる各 3 か月間を示す整数です (単一の暦年内では、暦四半期は固有です)
  • Calendar_Year_Num - 西暦の年で定義される年 (2000、2001、2002 など) です。
  • Fiscal_Month_Abbr - 単一の会計年度を 12 分割した、企業の時間グループを示す月名の省略名です (会計月は、複数の完全な週 (4 週または 5 週) で構成され、単一の会計年度内で固有です)。
  • Fiscal_Month_Name - 単一の会計年度を 12 分割した、企業の時間グループを示す名前です (会計月は、複数の完全な週 (4 週または 5 週) で構成され、単一の会計年度内で固有です)。
  • Fiscal_Month_Num - 単一の会計年度を 12 分割した、企業の時間グループを示す整数です (会計月は、複数の完全な週 (4 週または 5 週) で構成される必要があり、単一の会計年度内で固有です)。
  • Fiscal_Quarter_Num - 単一の会計年度を 4 分割した、企業の時間グループです。
  • Fiscal_Week_Num - 会計年度中の連続する 7 日をグループ化した、企業の時間グループです。会計年度中の固有の週を表します。
  • Fiscal_Year_Num - 会計報告年に相当する、企業の時間グループです。
  • Full_Date - 西暦の年を基にした、午前 0 時から始まる特定の 24 時間です。
  • Day_Name - 曜日の名前 (Monday (月曜日)、Tuesday (火曜日)、Wednesday (水曜日) など) です。
  • Day_Of_Week_Num - 週内の特定の日を示す整数です。
  • Day_Type - 平日、週末、祭日などの日のタイプです。

Prescription Drug (処方薬)

医師によって処方された処方箋を示します。

  • Drug_Code - 特定の処方薬に割り当てられた固有の番号です。
  • Drug_Description - テキストによる処方薬の説明です。
  • Drug_Generic_Indicator - 記入された処方箋に指定ブランドの薬が含まれるかどうかを示す True/False のフラグです。

Provider (医療提供者)

患者に医療サービスを提供するあらゆる個人 (医師、薬剤師) または組織 (医院、病院) を識別します。医療提供者は、PPO (特約医療機構) や HMO (健康維持組織) の一部である場合、または独立した提供者である場合があります。

  • Provider_Id - 特定の医療提供者を示す固有の整数です。
  • Provider_Name - 医療提供者の正式名称です。
  • Provider_Category - 医療提供者が PPO (特約医療機構) であるか、HMO (健康維持組織) であるか、またはその他であるかを示します。
  • Provider_Type - 医療提供者が、病院、医者、または薬剤師 (「医者」という用語は、資格の範囲内で治療を行う認可された医師、整骨医、指圧師、足病医、足治療医、助産婦、歯科医などを指します) のいずれであるかを示します。
  • Provider_Specialty_Type - 特定の医療提供者の専門分野を示します。
  • Provider_City - 医療提供者が治療を行っている都市の名前です。
  • Provider_County - 医療提供者が治療を行っている郡の名前です。
  • Provider_State - 医療提供者が治療を行っている州の名前です。
  • Provider_Postal_Code - 医療提供者が治療を行っている場所の 5 桁の郵便番号です。

Rejection Reason (拒否理由)

請求を拒否する一般的な理由が含まれています。各レコードには、主要な拒否コードとオプションの二次的な拒否コードおよび説明が含まれています。

  • Rejection_Code - 特定の拒否の説明を示すために使用する固有の番号です。
  • Rejection_Desc - テキストによる拒否の理由の説明です。

Service (サービス)

医療提供者が患者に提供する特定の治療サービスを示します。治療サービスには、具体的な治療や入院が含まれます。

  • Service_Code - 患者に提供される具体的な治療または処置を示すために使用される固有の番号です。
  • Service_Category - 入院または外来の治療のカテゴリーです。
  • Service_Description - テキストによる治療または処置の説明です。

スキーマの設計

医療保険事業での患者の支払請求分析用ソリューション・テンプレートには、支払請求のトランザクション・レベル、および支払請求の要約レベルのデータが含まれています。このスキーマは、実施した治療の頻度と費用、処方薬の使用頻度と費用、および診断の正確性に関する医療提供者の分析をサポートします。また、保険契約者による請求の頻度と金額の追跡にも使用できます。

Claim Item (支払請求項目) ファクト表には、請求に記載された各項目のレコードが含まれています。このファクト表には、Member (保険契約者)、Patient (患者)、Diagnosis (診断)、Service (サービス)、Prescription Drug (処方薬)、Provider (医療提供者)、Rejection Reason (拒否理由)、および Period (期間) の各ディメンションが関連付けられています。さらに、外部表を使用して Member Group (保険契約者グループ) を示します。

Claim Summary (支払請求の要約)
Claim Summary (支払請求の要約) ファクト表には、それぞれの請求ごとに 1 つのレコードが含まれています。各レコードには、支払請求総額、支払請求に基づく支払金額、および支払請求を提出した保険契約者の自己負担額の要約が含まれています。このファクト表には、Member (保険契約者)、Patient (患者)、Provider (医療提供者)、Rejection Reason (拒否理由)、および Period (期間) の各ディメンションが関連付けられています。 さらに、外部表を使用して Member Group (保険契約者グループ) を示します。

ここで示すスキーマの設計は、考えられる数多くの設計の 1 つにすぎません。このような設計への一般的な拡張としては、集約表の使用があります。Member (保険契約者) および/または Patient (患者) のディメンションが非常に大きく、Member (保険契約者) あるいは Patient (患者) の制約条件を使わずに必要なディメンションの分析を行う場合には、集約表(*1)が役立ちます。

(*1)集約表
Red BrickのVista機能を使用し集約表を管理します。この機能はデータ一貫性を保証します。

(*2)ディメンション表について
支払請求の要約スキーマのディメンション表は、支払請求トランザクションスキーマのディメンション表と同一です。

テーブルの作成とスターインデックスの設定

ここでは、テーブル作成用のスクリプトインデックス作成用のスクリプトを一つの例として提供しています。各項目の説明は、「エンティティーと属性の説明」を参照して下さい。

Period(期間)
create table period (
date_key			integer not null, 
date_full			date,
fiscal_year_num		integer,
fiscal_quarter_num		integer,
fiscal_month_num		integer,
fiscal_month_name		char(10),
fiscal_month_abbr		char(3),
fiscal_week_num		integer,
calendar_year_num		integer,
calendar_quarter_num	               integer,
calendar_month_num	               integer,
calendar_month_name	               char(10),
calendar_month_abbr	               char(3),
calendar_day_num		integer,
day_of_week_num		integer,
day_name			char(10),
day_type			char(4),
primary key (date_key))
maxrows per segment 1000 ;
create index period_index1 on period(calendar_year_num);
create index period_index2 on period(calendar_month_num);
create index period_index3 on period(date_full);
Member(保険契約者)
create table member (
member_id		                 integer not null,
group_policy_number	                 int not null,
member_name		                 char(30),
member_occupation	                 char(20),
member_employment_status 	  char(10),
member_marital_status	  char(1),
member_gender		  char(1),
member_city		                char(20),
member_county		 char(20),
member_state		                char(20),
member_postal_code	integer,
primary key (member_id),
foreign key (group_policy_number)
references member_group (group_policy_number))
maxrows per segment 1000 ;
Member Group(保険契約者グループ)
create table member_group (
group_policy_number	int not null,
group_name		char(30),
group_description	char(40),
effective_date		date,
cancel_date		date,
renewal_date		date,
group_city		char(20),
group_state		char(20),
group_postal_code		integer,
group_sic_code		char(4),
insurance_carrier		char(20),
sales_rep_name		char(30),
primary key (group_policy_number))
maxrows per segment 1000 ;
Patient(患者)
create table patient (
patient_id		integer not null,
patient_name		char(30),
patient_mbr_relationship char(10),
patient_occupation	char(20),
patient_marital_status	char(1),
patient_age		integer,
patient_gender		char(1),
patient_city		char(20),
patient_county		char(20),
patient_state		char(20),
patient_postal_code	integer,
primary key (patient_id))
maxrows per segment 1000 ;

create index patient_index on patient (patient_age);
Diagnosis(診断)
create table diagnosis (
diag_code		integer not null,
diag_description	char(40),
diag_source	char(20),
primary key (diag_code))
maxrows per segment 1000 ;
Service(サービス)
create table service (
service_code		integer not null,
service_category		char(10),
service_description		char(40),
primary key (service_code))
maxrows per segment 1000 ;

create index service_index on service (service_description);
Prescription_drug (処方箋)
create table prescription_drug (
drug_code		integer not null,
drug_description		char(40),
drug_generic_indicator	char(1),
primary key (drug_code))
maxrows per segment 1000 ;
Provider(医療提供者)
create table provider (
provider_id		integer not null,
provider_name		char(30),
provider_category		char(10),
provider_type		char(10),
provider_specialty_type	char(10),
provider_city		char(20),
provider_county		char(20),
provider_state		char(20),
provider_postal_code	integer,
primary key (provider_id))
maxrows per segment 1000 ;

create index provider_index on provider (provider_name);
Rejection Reason(拒否理由)
create table rejection_reason (
rejection_code		integer not null,
rejection_description	char(40),
primary key (rejection_code))
maxrows per segment 1000 ;
Claim Item(支払請求項目)
create table claim_item (
claim_id		integer not null,
claim_date_id		integer not null,
line_item_number	integer not null,
service_code		integer not null,
patient_id		integer not null,
member_id		integer	not null,
provider_id		integer not null,
drug_code		integer not null,
rejection_code		integer not null,
primary_diag_code	integer not null,
secondary_diag_code 	integer not null,
service_date_id		integer not null,
claim_item_amount	decimal (7,2),
primary key (claim_id, claim_date_id, line_item_number),
foreign key (claim_date_id) references period (date_key),
foreign key (service_code) references service (service_code),
foreign key (patient_id) references patient (patient_id),
foreign key (member_id) references member (member_id),
foreign key (provider_id) references provider (provider_id),
foreign key (drug_code) references prescription_drug (drug_code),
foreign key (rejection_code) references rejection_reason
(rejection_code),
foreign key (primary_diag_code) references diagnosis (diag_code),
foreign key (secondary_diag_code) references diagnosis (diag_code),
foreign key (service_date_id) references period (date_key)
);

create star index claim_item_index1 on claim_item 
(claim_date_id, provider_id, service_code);
create star index claim_item_index2 on claim_item 
(claim_date_id, service_code);
Claim Summary(支払請求の要約)
create table claim_summary (
claim_id			integer not null,
claim_date_id		integer not null,
rejection_code		integer not null,
patient_id			integer not null,
member_id		integer	not null,
provider_id		integer not null,
disability_start_date_id 	integer not null,
disability_end_date_id	integer not null,
date_deceased_id		integer not null,
claim_amount		decimal (7,2),
payment_amount		decimal (7,2),
co_payment_amount	decimal (7,2),
primary key (claim_id, claim_date_id),
foreign key (claim_date_id) references period (date_key),
foreign key (rejection_code) references rejection_reason
(rejection_code),
foreign key (patient_id) references patient (patient_id),
foreign key (member_id) references member(member_id),
foreign key (provider_id) references provider(provider_id),
foreign key (disability_start_date_id) references period (date_key),
foreign key (disability_end_date_id) references period (date_key), 
foreign key (date_deceased_id) references period (date_key) 
);

create star index claim_sum_index2 on claim_summary 
(claim_date_id, disability_start_date_id);

ビジネス上の質問に答えるための SQL ステートメント

質問 1: 2001年の1月に一般医療提供者が行った上位 10種類の処置は? 件数別および請求総額別にリストしてください。

select rank (count(*)) as service_rank, service.service_code, count(*) as service_count
from provider, service, period, claim_item where provider.provider_id = claim_item.provider_id 
and service.service_code = claim_item.service_code
  and period.date_key = claim_item.claim_date_id and period.calendar_year_num = 2000
  and period.calendar_month_num = 12 and provider.provider_category = 'PPO'
                              group by service.service_code when service_rank < 11
order by service_count desc;

質問 2: 2000 年中に治療「X」を最も多く使用した一般医療提供者は?

select provider_name, count(*) as num_procedures
from provider, service, period, claim_item
where claim_item.provider_id = provider.provider_id and claim_item.service_code 
= service.service_code
  and claim_item.claim_date_id = period.date_key and period.calendar_year_num 
= 2000
  and service.service_code = 1
group by provider_name
order by num_procedures desc;

質問 3: 2000 年中に治療「X」の治療費の請求額が「Y」ドルを超えた医療提供者は?

select provider_name
from provider, service, period, claim_item
where claim_item.provider_id = provider.provider_id and claim_item.service_code 
= service.service_code
  and claim_item.claim_date_id = period.date_key and period.calendar_year_num 
= 2000
                                and service.service_code = 1 and claim_item_amount > 950.00
group by provider_name;

質問 4: 特定の医療提供者において、2000 年中に請求額が高額だった上位 10 種類の治療は?

select rank (count(claim_item_amount)) as freq_rank, count (claim_item_amount) 
as claim_count,
  service.service_code as code, service_category as category, service_description 
as descr
from provider, service, period, claim_item
where claim_item.provider_id = provider.provider_id and claim_item.service_code 
= service.service_code
  and claim_item.claim_date_id = period.date_key and provider_name = 'Bob Wilson'
  and period.calendar_year_num = 2000
                              group by service.service_code, service_category, service_description when
                              freq_rank <= 10
order by claim_count;

質問 5: 2000 年中の治療「X」の平均の請求額は?

select service.service_description, avg(claim_item_amount) as avg_amount
from service, period, claim_item
where claim_item.service_code = service.service_code and claim_item.claim_date_id 
= period.date_key
  and period.calendar_year_num = 2000 and period.calendar_quarter_num = 4  and 
service.service_code = 1
group by service.service_description;

質問 6: 2000 年に治療「X」の請求を行った保険契約者のうち、入院治療を受けた保険契約者と外来治療を受けた保険契約者との比率は?

select tot_claims, inpatient, (inpatient/tot_claims) as percent_in from
(select service_description, count (*) from service, period, claim_item
  where claim_item.service_code = service.service_code
    and claim_item.claim_date_id = period.date_key and period.calendar_year_num 
= 2000
    and period.calendar_quarter_num = 4 and service.service_description = 'hand 
surgery' 
    and service.service_category = 'inpatient'
   group by service_description) as in_patient(service_desc, inpatient)
natural join
  select service_description, count (*) from service, period, claim_item
   where claim_item.service_code = service.service_code and claim_item.claim_date_id 
= period.date_key
    and period.calendar_year_num = 2000 and period.calendar_quarter_num = 4 and
service.service_description = 'hand surgery'
  group by service_description) as patient(service_desc, tot_claims) ;

質問 7: 特定の医療提供者において、2000 年中に治療「X」について提出された請求の件数は?

select service.service_code, count (*) as num_claims
from service, provider, period, claim_item
where claim_item.service_code = service.service_code and claim_item.provider_id 
= provider.provider_id
  and claim_item.claim_date_id = period.date_key and period.calendar_year_num 
= 2000
  and provider.provider_name = 'Bob Wilson'
group by service.service_code
order by num_claims desc;

質問 8: 2000 年中に治療「X」を使用した医療提供者の割合は?

select num_providers, users, ((users / num_providers) * 100) as percent_using from
  (select calendar_year_num, count(distinct(provider_name)) from service, provider, 
period, claim_item
  where claim_item.service_code = service.service_code and claim_item.provider_id 
= provider.provider_id
    and claim_item.claim_date_id = period.date_key and period.calendar_year_num 
= 2000
    and service.service_code = 1
   group by calendar_year_num) as used_it(year_num, users)
natural join
 (select calendar_year_num, count (distinct(provider_name)) from service, provider, 
period, claim_item
  where claim_item.service_code = service.service_code and claim_item.provider_id 
= provider.provider_id
   and claim_item.claim_date_id = period.date_key and period.calendar_year_num 
= 2000
  group by calendar_year_num) as all_providers(year_num, num_providers) ;

質問 9: 2001 年の第 2 四半期に提出された請求金額が多かった上位 5 州は?

select rank (sum(claim_amount)) as prov_rank, provider.provider_state as 
state,
 sum(claim_amount) as total_claim
from provider, period, claim_summary
where claim_summary.provider_id = provider.provider_id and claim_summary.claim_date_id 
= period.date_key
  and period.calendar_year_num = 2001 and period.calendar_quarter_num = 2
                              group by provider.provider_state when prov_rank < 6
order by total_claim desc;

質問 10: 2000 年の第 4 四半期に受領した、50 歳を超える女性患者についての請求の件数は?

select count (*) as num_claims
from patient, period, claim_summary
where claim_summary.patient_id = patient.patient_id and claim_summary.claim_date_id 
= period.date_key
and period.calendar_year_num = 2000 and period.calendar_quarter_num = 4 and patient.patient_gender 
= 'f'
                              and patient.patient_age > 50;

質問 11: 今年の最初の月に、指定ブランドの薬を使用した処方箋を要求した保険契約者の割合は?

select num_patients, num_generic, (num_generic / num_patients) 
as percent_generic from
  (select calendar_year_num, count(*) from patient, prescription_drug, period, 
claim_item
  where claim_item.patient_id = patient.patient_id and claim_item.claim_date_id 
= period.date_key
    and claim_item.drug_code = prescription_drug.drug_code and period.calendar_year_num 
= 2001
    and period.calendar_month_num = 1 and prescription_drug.drug_generic_indicator 
= '1'
  group by calendar_year_num) as generic_user(year_num, num_generic)
natural join
  (select calendar_year_num, count (*) from patient, prescription_drug, period, 
claim_item
  where claim_item.patient_id = patient.patient_id and claim_item.claim_date_id 
= period.date_key
   and claim_item.drug_code = prescription_drug.drug_code and period.calendar_year_num 
= 2001
   and period.calendar_month_num = 1
  group by calendar_year_num) as all_user(year_num, num_patients) ;

質問 12: 2000 年中の、患者の死亡に関係する請求件数は?

select count (*) as num_dead
from period, claim_summary where claim_summary.claim_date_id = period.date_key 
and period.calendar_year_num = 2000
                              and claim_summary.date_deceased_id > 0;

ダウンロード可能なリソース


関連トピック


コメント

コメントを登録するにはサインインあるいは登録してください。

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=Information Management
ArticleID=322028
ArticleTitle=赤レンガ倉庫6丁目2番地: 第4回 システム構築に有効なソリューション・テンプレート(医療保険事業編)
publish-date=09302005