損害率の分析と異常請求の検知

目標

契約と請求データから、商品・四半期別の損害率と合算比率を計算し、契約直後の請求・特定病院への集中・受取口座の共有という3つの異常の兆候を見つけて、調査依頼リストを作ります。

なぜ重要なのか

損害保険の成績表は、2つの数字です。損害率は、受け取った保険料のうち、いくらが保険金として出ていったかで、合算比率は、そこに事業費まで足して、100%を超えるかを見るものです。合算比率が100%を超えれば、保険営業そのものでは損失という意味です。経営陣が毎月見る数字で、料率の引き上げ・引受基準の強化・販売中止が、この数字で決まります。

分母が経過保険料であることが重要です。1年分の保険料を受け取っても、今四半期に稼いだのは、そのうち3か月分だけです。分子は発生損害額で、すでに支払ったものだけでなく、受け付けられたがまだ審査中の案件の見積額も入ります。支払額だけを数えると、損害率が実際よりよく見え、その錯覚が、次の四半期に噴き出します。

不正検知は、損害率管理の別の顔です。加入するとすぐに請求が入る契約、とりわけ1つの病院で大きな金額が繰り返される請求、別々の契約なのに同じ口座にお金が出ていく請求。1つ1つは偶然かもしれませんが、パターンは調査の対象です。ルールは判断ではなく、調査依頼の根拠を作るもので、最終的な判断は、調査組織(SIU)が行います。

このラボのルール

四半期は2026Q1(1–3月)・2026Q2(4–6月)。経過保険料は、四半期の初日に有効な契約(start_dateが四半期の初日以前)ごとにannual_premium ÷ 4の整数の商(ウォン未満切り捨て)を求めて足した値です(ラボの単純化)。合計を出してから4で割ると、1ウォンずれることがあります。
発生損害額 = 事故日がその四半期の請求のうち、DENIEDを除いたもののpaid_amount + reserve_amountの合計。不支払いの案件に見積額が残っていても入れません。
損害率(%) = 発生損害額 ÷ 経過保険料 × 100、事業費率(%) = 事業費 ÷ 経過保険料 × 100で、どちらも小数第1位に四捨五入します。合算比率は、四捨五入した損害率と事業費率を足した値です。
契約直後の請求 = 事故日がstart_dateから90日未満(ちょうど90日は含まない。「3か月」ではなく日数)。請求の状態に関係なく数えます。四半期を分ける日付は、常に事故日です。
病院集中 = その病院のPAIDの請求が4件以上で、かつそのPAIDの請求の平均支払額が、全体のPAIDの請求の平均の2倍超。不支払い・未決の請求は、件数にも平均にも入れず、HOSPITALの理由も、引っかかった病院のPAIDの請求にだけ付けます。
受取口座の共有 = 同じpayee_accountで、異なる契約が3件以上。引っかかった口座の請求は、状態に関係なく、すべてPAYEEの理由を受けます。

ステップ

右の例にあるジェネレーターを/root/ins/make_claims.pyとして保存し、python3 make_claims.pyで実行してください。claims.dbに、契約65件、請求110件、事業費6行が入ります。
商品・四半期別の経過保険料を/root/ins/earned.csvにproduct,quarter,policies,earned_premiumのヘッダーで6行書いてください。
商品・四半期別の損害率を/root/ins/loss_ratio.csvにproduct,quarter,earned,incurred,loss_ratio_pctのヘッダーで書いてください。
合算比率を/root/ins/combined.csvにproduct,quarter,loss_ratio_pct,expense_ratio_pct,combined_pctのヘッダーで書き、/root/ins/combined.txtにworst_product=、worst_quarter=、over_100_count=の3行を書いてください。
契約直後の請求を/root/ins/early_claims.csvにclaim_no,policy_no,days_since_start,paid_amountのヘッダーで書いてください。
病院集中を/root/ins/hospital_flag.csvにhospital_id,claims,avg_amount,ratioのヘッダーで書いてください。claimsはその病院のPAIDの請求数、ratioは全体のPAIDの平均に対する倍数(小数第2位)です。
受取口座の共有を/root/ins/payee_flag.csvにpayee_account,policies,claims,total_paidのヘッダーで書き、3つのルールのうち1つでも引っかかった請求を/root/ins/siu_referral.csvにclaim_no,reasonsのヘッダーで書いてください。reasonsは、EARLY・HOSPITAL・PAYEEをアルファベット順に+でつなぎます。
/root/ins/loss_report.mdに、## Loss Ratio and Combined Ratio(損害率と合算比率)、## Anomalies(異常の兆候)、## SIU Referral(調査依頼)、## Limitations(限界)の4つの節で報告書を書いてください。節の見出しは、英語の見出しでも、同じ意味の韓国語・日本語・中国語の見出しでもよく、両方を括弧で併記してもかまいません。

参考

四半期は、CASE WHEN substr(accident_date, 6, 2) <= '03' THEN '2026Q1' ELSE '2026Q2' ENDで分けられます。
比率は、ROUND(x * 100.0 / y, 1)のように、実数の割り算で行ってください。整数同士で割ると0になります。
よくある間違い1: 損害率の分子から、OPENの案件の見積額を除いてしまうことです。まだ出ていないお金も、発生損害です。
よくある間違い2: 2026-02-15に加入した契約を、第1四半期の経過保険料に入れてしまうことです。四半期の初日にはなかった契約です。
よくある間違い3: 病院の平均を、PAIDではなく全請求で出してしまうことです。不支払い・未決の案件は、支払額が0なので、平均を引き下げます。

契約・請求データを作る

右の例にあるジェネレーターを/root/ins/make_claims.pyとして保存し、python3 make_claims.pyで実行してください。claims.dbに、契約65件、請求110件、事業費6行が入ります。

ラボの案内にあるジェネレーターを/root/ins/make_claims.pyとして保存し、python3で実行してください。手で直すと、後のステップの数字がすべてずれます。

経過保険料を計算する

商品・四半期別の経過保険料を/root/ins/earned.csvにproduct,quarter,policies,earned_premiumのヘッダーで6行書いてください。

四半期ごとにstart_date <= 四半期の初日の契約を商品別に数え、annual_premium / 4(整数除算)を足してください。2つのクエリをUNION ALLで合わせれば6行です。四半期の初日に加入した契約はその四半期に入り、2026-02-15に加入した契約は第2四半期にだけ入ります。商は契約ごとに出してから足します。

発生損害額と損害率を計算する

商品・四半期別の損害率を/root/ins/loss_ratio.csvにproduct,quarter,earned,incurred,loss_ratio_pctのヘッダーで書いてください。

claimをpolicyと結合して商品を得て、事故日の月で四半期を分けて、paid_amount + reserve_amountを足してください(DENIED除外)。ステップ2の結果を.importで上げてLEFT JOINすれば、分母が付きます。ROUND(x * 100.0 / y, 1)。

事業費を足して合算比率を見る

合算比率を/root/ins/combined.csvにproduct,quarter,loss_ratio_pct,expense_ratio_pct,combined_pctのヘッダーで書き、/root/ins/combined.txtにworst_product=、worst_quarter=、over_100_count=の3行を書いてください。

expense表を商品・四半期で結合して、事業費 ÷ 経過保険料 × 100を小数第1位に四捨五入し、四捨五入した損害率に足してください(両方を四捨五入してから足す)。worstはcombined_pctが最も大きい行、over_100_countは100を超える行の数です。

加入直後の請求を探す

契約直後の請求を/root/ins/early_claims.csvにclaim_no,policy_no,days_since_start,paid_amountのヘッダーで書いてください。

claimとpolicyを結合して、julianday(accident_date) − julianday(start_date) < 90の案件を抜き出してください。ちょうど90日の請求は入りません。

特定の病院に集中した高額請求を探す

病院集中を/root/ins/hospital_flag.csvにhospital_id,claims,avg_amount,ratioのヘッダーで書いてください。claimsはその病院のPAIDの請求数、ratioは全体のPAIDの平均に対する倍数(小数第2位)です。

status='PAID'の請求だけで全体の平均を先に求め(CTE)、病院別にCOUNT(*) >= 4で、かつAVG(paid_amount) > 2 × 全体平均の病院だけを残してください。avg_amountは整数、ratioはROUND(…, 2)。

受取口座の共有と調査依頼リストを作る

受取口座の共有を/root/ins/payee_flag.csvにpayee_account,policies,claims,total_paidのヘッダーで書き、3つのルールのうち1つでも引っかかった請求を/root/ins/siu_referral.csvにclaim_no,reasonsのヘッダーで書いてください。reasonsは、EARLY・HOSPITAL・PAYEEをアルファベット順に+でつなぎます。

payee_flagはpayee_accountでGROUP BYして、COUNT(DISTINCT policy_no) >= 3。siu_referralは、ステップ5の請求 + ステップ6の病院のPAIDの請求 + ステップ7の口座の請求を集めて、請求ごとの理由をアルファベット順に+でつなぎ、claim_no順にソートします。

損害率・異常請求の報告書を書く

/root/ins/loss_report.mdに、## Loss Ratio and Combined Ratio(損害率と合算比率)、## Anomalies(異常の兆候)、## SIU Referral(調査依頼)、## Limitations(限界)の4つの節で報告書を書いてください。節の見出しは、英語の見出しでも、同じ意味の韓国語・日本語・中国語の見出しでもよく、両方を括弧で併記してもかまいません。

節の見出し4つは、英語の見出しか、同じ意味の韓国語・日本語・中国語の見出しのとおりに。損害率(loss ratio)の節には最も悪い商品とその合算比率(combined ratio)を1行に、異常の兆候(Anomalies)の節にはステップ6の病院番号とステップ7の共有口座(payee account)をすべて、調査依頼(SIU Referral)の節にはsiu_referral.csvの件数を、数字で書いてください。