Google BigQuery で SQL を使用して名前の大文字と小文字を正しくフォーマットする方法

Google BigQuery での名前の標準化の修正: 実践ガイド

名前があちこちに記載されているレポートを受け取ったところを想像してみてください。すべて大文字のもの、大文字が適切なもの、まったく一貫性のないものもあります。それは単なる美的問題ではありません。クリーンなデータ分析には、標準化された形式が不可欠です。 🧐

ディレクターまたは営業担当者の名前を格納するデータセットを扱っている場合、このシナリオはよく知られているように聞こえるかもしれません。 「STEVE MARK」や「ANDRY WIU」などの名前が、「Jonathan Lu」などの適切にフォーマットされたエントリと混在すると、作業が不必要に難しくなります。 🙈

ありがたいことに、Google BigQuery の SQL は、この問題に対処するための強力なツールを提供します。 REGEX のファンではない場合や、REGEX の複雑さについてよくわからない場合でも、これらの名前を適切な大文字 (各単語の最初の文字が大文字) に変換する解決策があります。結果？クリーンでプロフェッショナルなデータセット!

この記事では、シンプルな SQL テクニックを使用して BigQuery でフルネームを効率的に標準化する方法を説明します。最終的には、現在の混沌とし​​た状態ではなく、「Steve Mark」や「Sally Chow」のような標準化されたリストが得られるでしょう。データをクリーンアップしてピカピカにしましょう! ✨

指示	使用例
INITCAP	文字列内の各単語の最初の文字を大文字にし、他のすべての文字を小文字に変換するために使用されます。 例： INITCAP('スティーブ・マーク') 結果として 「スティーブ・マーク」。
LOWER	文字列内のすべての文字を小文字に変換します。 例： 下('アンドリー・ウィウ') 結果として 「アンドリー・ウィウ」。
REGEXP_REPLACE	正規表現パターンに一致する文字列の一部を置換します。 例： REGEXP_REPLACE('ジョナサン・ルー', r'(bw)', UPPER('1')) 各単語の最初の文字を大文字にします。
ARRAY_TO_STRING	指定された区切り文字を使用して、配列の要素を単一の文字列に結合します。 例： ARRAY_TO_STRING(['スティーブ', 'マーク'], ' ') 結果として 「スティーブ・マーク」。
SPLIT	指定された区切り文字に基づいて文字列を部分文字列の配列に分割します。 例： SPLIT('ジョナサン・ルー', ' ') 結果として [「ジョナサン」、「ルー」]。
UNNEST	配列を一連の行に変換し、個々の要素に対する操作を可能にします。 例： UNNEST(SPLIT('ジョナサン・ルー', ' ')) 「Jonathan」と「Lu」に個別の行を生成します。
WITH	複雑なクエリまたは再利用可能なサブクエリを整理するための共通テーブル式 (CTE) を作成します。 例： WITH test_data AS (「ジョナサン・ルー」を選択)。
CREATE TEMP FUNCTION	カスタム操作のクエリ内に一時的なユーザー定義関数 (UDF) を定義します。 例： CREATE TEMP FUNCTION ProperCase(input STRING) AS (...)。
UPPER	文字列内のすべての文字を大文字に変換します。 例： UPPER('スティーブ・マーク') 結果として 「スティーブ・マーク」。

名前を適切な大文字小文字に変換する: 段階的な説明

上記で提供されているスクリプトは、Google BigQuery における標準化されていない名前の形式の問題に対処するように設計されています。最初の方法では、 関数。これは BigQuery の組み込み SQL 機能です。このコマンドは簡単かつ効率的で、あらゆる文字列を適切な大文字と小文字の形式に変換します。たとえば、各単語の最初の文字を大文字にし、残りを小文字に変換することにより、「STEVE MARK」を「Steve Mark」に変換します。これは、不一致によってレポートが中断される可能性がある大規模なデータセットを処理する場合に特に役立ちます。これは、最小限の労力でデータをプレゼンテーションできる状態にするための魔法の杖だと考えてください。 🪄

2 番目の方法では、 、正規表現 (REGEX) を利用したより詳細なアプローチです。この技術により、テキスト変換プロセスを正確に制御できます。最初に使用することで、 REGEXP_REPLACE 関数を使用してすべての文字が小文字であることを確認し、各単語の最初の文字を大文字にするパターンを適用します。この方法は、予期しない大文字パターンや特殊文字を含む入力など、非標準データを扱う場合に最適です。結婚式のために混沌としたゲストリストを整理しようとしているところを想像してください。このアプローチにより、すべての名前が美しく一貫してフォーマットされるようになります。 💍

3 番目の方法は、 は、独自の書式設定要件を持つ複雑なデータセットに特に適しています。このメソッドは、BigQuery 内で JavaScript を使用してカスタム関数を作成することにより、各名前をより動的に処理します。名前を配列に分割し、各要素に書式設定ロジックを適用して、適切に書式設定された文字列に結合します。このモジュール式アプローチは、進化するデータ構造に適応する再利用可能なソリューションを必要とするチームに最適です。たとえば、企業のデータベースに追加のメタデータまたは混合区切り文字を含む名前が保存されている場合、UDF は柔軟性とカスタマイズ オプションを提供します。

各メソッドは特定のユースケースに対応するように作成されており、シンプルさ、精度、適応性のバランスが取れています。たとえば、 この方法はクリーンなデータセットに対しては迅速かつ効果的ですが、REGEX アプローチは少し乱雑なデータに対して多用途性を提供します。一方、UDF メソッドは、非常に変動性の高い入力を扱う場合に威力を発揮します。これらの手法により、データセットの状態に関係なく、名前を効果的に標準化し、プロフェッショナルで洗練された出力を維持できるようになります。このようなツールを使用すると、データセットのクリーンアップが困難な戦いのように感じられなくなり、むしろ達成可能で満足のいく作業のように感じられます。 🌟

-- Approach 1: Using BigQuery's INITCAP function (Optimized for simplicity)
-- This approach converts names to Proper Case using INITCAP, handling capitalization directly.
SELECT
  INITCAP(Director) AS StandardizedDirector,
  INITCAP(Salesperson) AS StandardizedSalesperson
FROM
  your_dataset.your_table;
-- The INITCAP function automatically handles capitalizing the first letter of each word.
-- Ensure you replace 'your_dataset.your_table' with your actual table reference.

-- Approach 2: Applying REGEX to ensure each word's first letter is capitalized
SELECT
  REGEXP_REPLACE(LOWER(Director), r'(\b\w)', UPPER('\\1')) AS StandardizedDirector,
  REGEXP_REPLACE(LOWER(Salesperson), r'(\b\w)', UPPER('\\1')) AS StandardizedSalesperson
FROM
  your_dataset.your_table;
-- This method first converts all text to lowercase using LOWER,
-- and then capitalizes the first letter of each word using REGEXP_REPLACE.
-- Replace 'your_dataset.your_table' with your actual table name.

-- Approach 3: Defining a UDF for custom name formatting
CREATE TEMP FUNCTION ProperCase(input STRING) AS (
  (ARRAY_TO_STRING(
    ARRAY(SELECT INITCAP(word)
          FROM UNNEST(SPLIT(input, ' ')) AS word), ' '))
);
-- Applying the UDF to standardize columns
SELECT
  ProperCase(Director) AS StandardizedDirector,
  ProperCase(Salesperson) AS StandardizedSalesperson
FROM
  your_dataset.your_table;
-- This UDF splits the text into words, applies INITCAP to each, and then joins them back.

-- Test Script: Validate output consistency
WITH test_data AS (
  SELECT 'JONATHAN LU' AS Director, 'STEVE MARK' AS Salesperson
  UNION ALL
  SELECT 'LIAM LEE', 'WINDY WU'
  UNION ALL
  SELECT 'ANDRY WIU', 'SALLY CHOW'
)
SELECT
  INITCAP(Director) AS TestDirector1,
  REGEXP_REPLACE(LOWER(Salesperson), r'(\b\w)', UPPER('\\1')) AS TestSalesperson2
FROM
  test_data;
-- Replace the test_data CTE with your actual dataset to test in production.

高度な SQL テクニックによるデータ クリーニングの合理化

一貫性のないデータセットを操作する場合 、データの正確性と使いやすさのためには、分野全体での標準化を確保することが不可欠です。名前などのテキストの多い列を管理する場合、大文字と小文字の区別が一致しないと分析が中断される可能性があるため、重要な課題が発生します。 BigQuery は、標準的なケース変換手法に加えて、追加のロジックを使用してデータ クリーニングを強化する機会を提供します。たとえば、特定の単語 (「マクドナルド」や「オコナー」など) に、汎用関数が好む特別な書式設定ルールが必要になるシナリオに遭遇する可能性があります。 効果的に処理できない可能性があります。 SQL を条件付きロジックまたは外部 UDF と組み合わせることで、変換を微調整して、そのような例外をシームレスに処理できます。 🚀

もう 1 つの有用な角度は、これらの変換をより大きなワークフローに統合することです。たとえば、名前をクリーニングするときに、事前定義されたリストに対して名前を検証したり、異常を特定するためにフィルターを適用したりする必要がある場合があります。大文字と小文字の変換ロジックを Common Table Expressions (CTE) に埋め込むことで、デバッグを簡素化し、再利用性を向上させるモジュール式クエリを作成できます。この階層化されたアプローチにより、単一のパイプライン内で書式設定と検証を処理できるため、時間と労力を節約できます。このようなプロセスは、顧客プロファイリングやマーケティング分析などの大規模な業務において特に価値があります。

最後に、BigQuery のスケーラビリティを活用することは、大規模なデータセットを処理する際の大きな変革となります。何百万行もの顧客名であっても、地域のデータ レコードであっても、UDF や REGEX などの最適化された SQL 技術により、パフォーマンスの効率性が維持されます。これらのツールをインデックス作成やパーティション化などのベスト プラクティスと組み合わせることで、最も複雑なクエリであっても確実に迅速に実行できます。包括的なアプローチを採用することで、差し迫ったフォーマットの問題を解決するだけでなく、クリーンで保守可能なデータベースの基盤を確立することもできます。 🌟

1. は何ですか 機能はありますか？
2. の この関数は、文字列内の各単語の最初の文字を大文字にし、残りを小文字に変換します。
3. できる 特殊文字を含む名前などの特殊なケースを処理しますか?
4. はい、カスタム パターンを作成できます。 「O'Connor」など、ハイフンまたはアポストロフィを含む名前を考慮します。
5. を使用するメリットは何ですか このタスクのために BigQuery を使用しますか?
6. と を使用すると、再利用可能でカスタマイズ可能なロジックを作成して、独自の書式設定の課題に対処できるため、大規模または複雑なデータセットに最適です。
7. 変換を検証するにはどうすればよいですか?
8. 変換を CTE と組み合わせて、出力を参照テーブルまたはパターンと照合して精度を高めます。
9. BigQuery はこれらの関数を使用して大規模なデータセットを効率的に処理できますか?
10. はい、BigQuery は大規模なデータセットを処理し、次のような最適化されたクエリを使用するように設計されています。 そして 迅速な実行を保証します。
11. このプロセスを自動化する方法はありますか?
12. BigQuery で SQL スクリプトをスケジュールしたり、Dataflow や Cloud Composer などのツールを介して SQL スクリプトをワークフローに統合したりできます。
13. このプロセスは多言語データを処理できますか?
14. はい、ただしパターンを調整する必要がある場合があります または、UDF で言語固有のロジックを使用します。
15. これらのスクリプトをテストする最良の方法は何でしょうか?
16. テスト データセットを作成し、サブセットに対してクエリを実行して、出力が完全なデータセットに適用する前に期待を満たしていることを確認します。
17. 完全に大文字の名前を処理するにはどうすればよいですか?
18. まず、使用します 小文字に変換してから適用します またはREGEXベースの変換。
19. これらのメソッドは複数の言語で保存された名前を処理できますか?
20. はい、BigQuery は次のように機能します。 UDF は適応可能ですが、非ラテン文字の場合は追加のロジックが必要になる場合があります。
21. 私の名前が複数のフィールドに分かれている場合はどうなりますか?
22. 使用 変換を適用する前にフィールドを 1 つの列に結合します。

BigQuery での名前の標準化は、必ずしも頭の痛い問題ではありません。 INITCAP などの組み込み関数を使用したり、カスタム REGEX パターンを活用したりすることで、最も乱雑なデータセットでもクリーンで一貫性のある形式に変換できます。適切なアプローチは、データセットの複雑さとサイズによって異なります。 😊

顧客リスト、従業員記録、マーケティング データのいずれを処理する場合でも、適切な書式設定により、明確さと専門性が保証されます。これらの手法は、現在のデータセットをクリーンアップするだけでなく、将来のニーズに備えたスケーラブルなワークフローの構築にも役立ちます。適切に構造化されたデータベースには、常に努力する価値があります。 🌟