顧客・注文・アクティブ - 三つのシステムは別のものを数えている

目標

顧客が「顧客」「アクティブ」「注文」と呼ぶものが3つのシステムでそれぞれ何なのかを対照表に固め、同じ名前が指す集合の大きさと重なりをデータで測ります。識別子の正規化を変えると答えがまるごと変わることを自分で確かめ、名前が同じで集合が違うものと、名前が違って集合が同じものを分けて、レポートにして出します。

なぜ重要なのか

会議で「アクティブ顧客は何人ですか」と尋ねると、3人が3つの数字を答え、3つとも正しいことがあります。営業は状態文字がAの行を、請求は請求可能なアカウントを、運用は最近ログインした人を数えたものです。3つの定義はそれぞれの業務で正しく、間違っているのは、1つの言葉が3つの定義をすべて含んでいると信じた側です。 この食い違いはエラーとして表れません。クエリは成功し、レポートは印刷されます。表れるときには、すでにその数字で決定が下されたあとです。そこで、最初の週に用語と識別子の対照表をファイルとして作り、集計ツールがそのファイルを読むようにします。 集合を比べるには、「同じもの」とは何かを先に決めなければなりません。RFC 5321の2.4節は、メールアドレスのローカルパートを大文字小文字を区別して扱うと定め、ドメインは区別しないと書いています。規格どおりに見れば重なる人は1人もおらず、すべて小文字にそろえればほとんど全部が重なります。同じデータから2つの答えが出ます。 採点ツールは、提出された文言を信じません。毎回違うテーブル名と値で作ったデータベースで、作成した集計ツールを実際に動かし、集合の大きさと重なりを自分で計算した値と照合します。

ステップ

1. /root/glossary/gen_systems.pyを作成して実行し、/root/glossary/systems.dbを作成してください。営業(crm)・請求(bill)・運用(ops)の3つのシステムのテーブル6つが入ります。
2. /root/glossary/terms.jsonに、用語と識別子の対照表を書いてください。顧客・アクティブ・注文の3つの用語×3つのシステム=9行で、行ごとにsystem・table・id_column・predicateを書きます。
3. /root/glossary/census.pyを作成し、対照表の行ごとに集合の大きさを測って、/root/glossary/census.jsonを書かせてください。
4. --overlap <경로>を追加し(プレースホルダーは出力先のパスです)、1つの用語の中でシステムを2つずつ比べた結果を書かせてください。重なった数と、片方にしかない数を出します。
5. --normalize none|strict|looseを追加してください。strictは空白を取り除いてドメインだけを小文字に、looseは空白を取り除いてすべて小文字にそろえます。
6. looseで測った値から、/root/glossary/conflicts.jsonを作成し、用語ごとに最も食い違うシステムの組とジャッカード係数を書いてください。
7. /root/glossary/naming.jsonに、名前が同じで集合が違うもの(ジャッカード0.1以下)と、名前が違って集合が同じもの(顧客用語のカラムの組のうちジャッカード0.5以上)を分けて書いてください。
8. /root/glossary/glossary_report.mdに4つの節で報告してください。

参考

• 実行契約: python3 /root/glossary/census.py --db <DB> --terms <대조표> --out <집계 JSON> [--overlap <겹침 JSON>] [--normalize none|strict|loose](プレースホルダーは入力と出力のパスで、順にDB、対照表、集計JSON、重なりJSONです)は、1行の要約を標準出力に出して、終了コード0で終わります。入力を読み込めない場合は3です。
• 対照表: {"entries": [{"term": …, "system": …, "table": …, "id_column": …, "predicate": "SQL 조건", "note": …}]}(韓国語の部分は「SQL条件」という意味です)。predicateはWHEREのあとにそのまま付く条件文字列で、条件がなければ1=1です。
• 集計JSON: {"normalize": …, "counts": [{"term", "system", "table", "rows", "distinct_ids"}]}。countsはterm、systemの昇順です。rowsは条件に合う行数、distinct_idsは正規化した識別子の種類数です。
• 重なりJSON: {"normalize": …, "pairs": [{"term", "left", "right", "both", "left_only", "right_only"}]}。1つの用語の中でシステム名の昇順に2つずつ組み、leftがrightより前です。
• 正規化: noneは値そのまま、strictは공백 제거 + 마지막 @ 뒤만 소문자、looseは공백 제거 + 전부 소문자です(韓国語の式は、strictが「空白を除去し、最後の@の後ろだけを小文字にする」、looseが「空白を除去し、すべて小文字にする」という意味です)。
• conflicts.json: {"normalize": "loose", "terms": [{"term", "sizes": {시스템: 크기}, "min_pair": [시스템, 시스템], "min_jaccard": 소수점 셋째 자리, "conflict": true|false}]}(プレースホルダーはシステム名、大きさ、小数点以下第3位です)。ジャッカードは積集合の大きさを和集合の大きさで割った値で、和集合が空なら1.0とみなします。conflictはmin_jaccardが1.0未満のとき真です。
• naming.json: {"same_name_different_thing": [{"term", "min_pair", "jaccard"}], "different_name_same_thing": [{"a", "b", "jaccard"}]}。aとbは시스템.표.칼럼の形で(プレースホルダーはシステム名とテーブル名とカラム名です)、aがbより前です。
• よくあるミス: 対照表に条件を書き忘れること(アクティブの違いはテーブルではなく条件にあります)、正規化ルールを集計ごとに違えて使うこと、重なりを数える前に重複を取り除かないこと。
• ジャッカードのしきい値0.1と0.5、そして小数点以下第3位の四捨五入は、このラボの前提です。標準が決めた値ではなく、レポートに書いておいて使う値です。
• 参考ドキュメント: RFC 5321 2.4節はメールアドレスの大文字小文字のルールを、RFC 4949は同じ言葉を複数の意味で使う問題を扱う用語集の手本を、SQLite SELECTドキュメントはINTERSECTとEXCEPTを説明しています。

システム3台を手に入れる

/root/glossary/gen_systems.pyを作成して実行し、/root/glossary/systems.dbを作成してください。crm_customer・crm_order・bill_account・bill_invoice・ops_user・ops_workorderの6つのテーブルが入ります。

現場では、3つのシステムからそれぞれ抽出データを受け取ります。ここではその3つを1つのファイルにまとめます。テーブル名の先頭のcrm・bill・opsが、どのシステムから来たものかを示します。作成したら、sqlite3でテーブルの一覧と数行ずつだけ先に見てください。

用語と識別子の対照表を作る

/root/glossary/terms.jsonに9行を書いてください。用語は顧客・アクティブ・注文の3つ、システムはcrm・bill・opsの3つです。行ごとにterm・system・table・id_column・predicateを書き、アクティブは同じシステムの顧客と同じテーブルを使い、条件で絞り込みます。注文は顧客と別のテーブルです。

条件が抜けると、対照表は半分しか役に立ちません。アクティブの違いはテーブルではなく条件にあるからです。各システムがアクティブを何で分けているかは、テーブルを開けば見えます。状態文字、請求可能かどうか、最後のログイン日です。条件がない行は、predicateを1=1にします。

同じ名前の集合の大きさを測る

/root/glossary/census.pyを作成し、対照表の行ごとに、条件に合う行数(rows)と識別子の種類数(distinct_ids)を測って、/root/glossary/census.jsonに書かせてください。countsはterm、systemの昇順です。

predicateはWHEREのあとにそのまま付く条件文字列です。行数と識別子の種類数を別々に数えるのは、1人が複数の行を持つことがあるからです。この段階では値に手を加えずそのまま比較します。正規化はステップ5で加えます。

2つのシステムずつ比べる

--overlap <경로>を追加し(プレースホルダーは出力先のパスです)、1つの用語の中でシステムを2つずつ比べた結果を書かせてください。bothは両方にある識別子の数、left_onlyとright_onlyは片方にしかない数です。この段階でも値はそのまま比較します。

用語ごとにシステム名を昇順に並べ、2つずつ組みます。3つのシステムなら3組です。値をそのまま比べると重なりがほぼ0になるはずですが、その結果自体が次の段階の出発点です。なぜ0なのかを、目で確かめてください。

何を同じとみなすか

--normalize none|strict|looseを追加してください。strictは空白を取り除いて最後の@の後ろだけを小文字にし、looseは空白を取り除いてすべて小文字にそろえます。集計JSONと重なりJSONのnormalizeの項目に、使った値を書きます。

RFC 5321の2.4節は、メールアドレスのローカルパートを大文字小文字を区別して扱うと定め、ドメインは区別しないと書いています。strictはその規格に従う側で、looseはこの顧客企業のシステムが実際に行っていることです。同じデータから2つの答えが出ることを、自分で確かめてください。

同じ名前が違う集合を指している

looseで測った値を使って、/root/glossary/conflicts.jsonを作成してください。用語ごとに、システム別の集合の大きさ(sizes)、ジャッカードが最も低いシステムの組(min_pair)、その値(min_jaccard、小数点以下第3位)、conflictかどうかを書きます。

ジャッカードは積集合の大きさを和集合の大きさで割った値です。和集合が空なら1.0とみなします。最も低い組が複数あるときは、システム名の昇順で前にある組を選びます。conflictはmin_jaccardが1.0未満のとき真です。少しでも違えば、同じ名前を使うのは危険だという意味です。

違う名前が同じものを指している

/root/glossary/naming.jsonに2つの一覧を書いてください。same_name_different_thingはmin_jaccardが0.1以下の用語で、different_name_same_thingは顧客用語の3つのカラムの組のうちジャッカードが0.5以上の組です。カラムは시스템.표.칼럼の形で書き(プレースホルダーはシステム名とテーブル名とカラム名です)、aがbより前です。

名前が同じで中身が違うものより、名前が違って中身が同じもののほうが、多く見られ、より危険です。名前が違うので誰も照合せず、同じ人が3つのシステムに3回入っていることを誰も知りません。ジャッカードはステップ6で使ったものと同じ定義で、正規化もlooseで同じです。

何を合意するかを書く

/root/glossary/glossary_report.mdに、## 세 시스템이 같은 말을 쓴다、## 용어-식별자 대조표、## 같은 이름 다른 집합、## 합의할 것の4つの節で書いてください(韓国語の見出しは順に「3つのシステムが同じ言葉を使う」「用語と識別子の対照表」「同じ名前で違う集合」「合意すること」という意味です)。3つの用語の名前とシステム別の集合の大きさがすべて出ている必要があり、使った正規化ルールも書きます。

レポートは手で書かず、census・conflicts・namingの3つのファイルから生成してください。そうすれば対照表が変わるときにレポートも一緒に変わります。最後の節には、統一しようという提案ではなく、「レポートごとにどの定義を使ったかを書こう」という合意を書いてください。