はじめに
小売業者はファイルをテーブルに対応付けられるようになりましたが、読める表が、そのままレポートに使えるとは限りません。状態の値に余分な空白があるもの、顧客 ID が欠損したもの、空文字列のもの、価格が不明なもの、数量が0のものがあります。これらの違いを無視したクエリは、行の選択を誤ったり、紛らわしい値を表示したりします。
今回は、小さな確認用一覧を作ります。数量が正で価格が既知の支払い済み行を選び、状態の文字列を整え、顧客 ID が欠損していれば分かりやすい既定値を表示します。欠損した価格は0に置き換えず、対象から除外します。不明な顧客のラベルを表示しても、実際の顧客レコードが作られたり、元データが変わったりするわけではありません。
この実験では、以前の実験の正常な売上データとは別の、データ品質を学ぶ7行のサンプルを使います。条件の組み合わせから始め、欠損値と単純な関数を調べ、CASE でラベルを付け、条件を1つ変えて理解を確認します。SELECT、WHERE、ORDER BY、SQL ファイルは既習です。サービスとテーブルのデータは独立して準備済みで、ターミナルを使います。
行の条件を意図どおりに組み合わせる
このステップでは、数量の判定と複数の状態の選択肢を組み合わせ、括弧が重要な理由を理解します。
プロジェクトから始めます。
cd /home/labex/project
クエリの前に、準備済みの CSV を読みます。
cat /home/labex/project/data/quality-orders.csv
ヘッダーの後に業務上の7行があります。r1 の paid の前後の空白、r2 と r3 の \N、r6 の空の顧客フィールド、r7 の数量0に注目してください。準備済みのテーブルでは \N を SQL の NULL として解釈し、空のテキストフィールドは空文字列のままにします。この違いは間もなく調べます。
quantity > 0 などの SQL の比較は、行ごとに判定します。AND は両条件が真であることを求め、OR はいずれかを満たせば受け入れます。括弧で状態の選択肢をまとめ、数量0の pending 行が数量の判定をすり抜けないようにします。
練習済みの SQL ファイルの方法で、最初のフィルターを保存します。
cat > /home/labex/project/queries/conditions.sql <<'SQL'
USE retail_filters;
SELECT order_id, quantity, order_status
FROM quality_orders
WHERE quantity > 0
AND (order_status = 'paid' OR order_status = 'pending')
ORDER BY order_id;
SQL
既知の Beeline 接続とタブ区切り出力オプションで実行します。
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/conditions.sql > /home/labex/project/output/conditions.txt
結果を読みます。
cat /home/labex/project/output/conditions.txt
注文の行は次のようになるはずです。
r2 1 paid
r3 1 paid
r4 1 pending
r6 1 paid
r7 は pending でも数量によって除外されます。r1 は保存されたテキストに空白があり、'paid' と完全には一致しないので除外されます。r3 の価格の欠損は、このフィルターではまだ扱っていません。各条件は特定の問題を解決します。次のステップで不足する部分を加えます。
欠損値と空のテキストを区別する
このステップでは、NULL を正しく判定し、保存された行を変えずに表示値を整えます。
NULL は、欠損または不明の値を表します。文字列 'NULL' でも空文字列でもありません。判定には = NULL ではなく、IS NULL または IS NOT NULL を使います。
欠損した顧客 ID と空の顧客 ID を、別々に問い合わせます。最初の文は r2、2つ目は r6 を選びます。
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e "SELECT order_id FROM retail_filters.quality_orders WHERE customer_id IS NULL; SELECT order_id FROM retail_filters.quality_orders WHERE customer_id = '';"
2つの小さな関数で、記録を分かりやすく表示できます。TRIM(text) は文字列の両端の空白を除きます。COALESCE(a,b) は、最初の NULL でない値を返すので、ID が NULL なら 'unknown' と表示できます。空文字列は置き換えず、元のテーブルも変更しません。
7行すべてにこれらの関数を適用する確認用クエリを保存します。
cat > /home/labex/project/queries/normalized-values.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
unit_price
FROM quality_orders
ORDER BY order_id;
SQL
実行します。
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/normalized-values.sql > /home/labex/project/output/normalized-values.txt
記録を読みます。
cat /home/labex/project/output/normalized-values.txt
3点を確認してください。r1 の paid の余白がなくなり、r2 の顧客ラベルが unknown になり、r6 の顧客フィールドは空のままです。r3 の価格は NULL です。この確認用一覧では欠損した顧客ラベルに表示上の既定値を使うのは適切ですが、価格0を作り出すと注文を誤って表現してしまいます。最後のフィルターでは、代わりに不明な価格を除外します。
ラベルを付けて確認用一覧を選ぶ
このステップでは、分かりやすい支払い状態のラベルを加え、行の品質チェックを1つのクエリにまとめます。
SQL の CASE 式は、条件に応じて値を選びます。WHEN の分岐を順に評価し、一致する THEN の値を使い、どれにも一致しなければ ELSE を使います。支払い済みには Payment received、支払い待ちには Awaiting payment、その他の状態には Other status のラベルを付けます。
確認用一覧のフィルターを加える前に、全行のラベルを見ます。
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e "SELECT order_id, CASE WHEN TRIM(order_status) = 'paid' THEN 'Payment received' WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment' ELSE 'Other status' END AS payment_label FROM retail_filters.quality_orders ORDER BY order_id;"
返金済みのため ELSE 分岐になる r5 を探してください。r4 と r7 は支払い待ちのラベルになります。状態ラベルは状態を説明するもので、それだけで数量や価格が正しいと判断できるわけではありません。
次に、一覧の完全なルールを作ります。数量が正、価格が既知、整えた状態が支払い済み、という条件です。元の顧客 ID は変更しません。クエリ結果では COALESCE で顧客ラベルを表示します。表示結果には元の ID の別のコピーは含めません。クエリを保存します。
cat > /home/labex/project/queries/filtered-orders.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
CASE
WHEN TRIM(order_status) = 'paid' THEN 'Payment received'
WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment'
ELSE 'Other status'
END AS payment_label
FROM quality_orders
WHERE quantity > 0
AND unit_price IS NOT NULL
AND TRIM(order_status) = 'paid'
ORDER BY order_id;
SQL
独立して実行します。
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/filtered-orders.sql > /home/labex/project/output/filtered-orders.txt
結果を読みます。
cat /home/labex/project/output/filtered-orders.txt
期待される注文の行は次のとおりです。
r1 c1 paid Payment received
r2 unknown paid Payment received
r6 paid Payment received
r6 の顧客表示フィールドが空なのは意図した結果です。NULL ではなく空文字列だからです。r1 は状態の空白を除いたことで含まれます。r3 は価格不明のため除外され、支払い待ち、返金済み、数量0の行もこの一覧の対象外です。不明な価格を0と見なさず、対象の行を定義できました。
条件を1つ変えて結果を予想する
このステップでは、同じ表示式と品質ルールを、支払い待ち注文の確認用一覧に再利用します。
実行する前に、最後の状態条件だけを pending に変えた結果を予想してください。r4 は正の数量と既知の価格を持ち、r7 は数量0です。r4 だけが残り、CASE ラベルは Awaiting payment になるはずです。
最初のクエリを残し、2つ目のファイルを保存します。選択する式と品質チェックは変えず、最後の WHERE の状態値だけを変えます。
cat > /home/labex/project/queries/pending-review.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
CASE
WHEN TRIM(order_status) = 'paid' THEN 'Payment received'
WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment'
ELSE 'Other status'
END AS payment_label
FROM quality_orders
WHERE quantity > 0
AND unit_price IS NOT NULL
AND TRIM(order_status) = 'pending'
ORDER BY order_id;
SQL
実行します。
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/pending-review.sql > /home/labex/project/output/pending-review.txt
出力を読みます。
cat /home/labex/project/output/pending-review.txt
期待される注文行は次のとおりです。
r4 c1 pending Awaiting payment
表自体は変わっていません。クエリが別の行の集合を選び、既存の CASE 式が対応するラベルを選びました。次の実験では、データ品質のルールと初めての集計を一度に学ぶのではなく、正しく選択できた行から合計を作ることに進みます。
まとめ
AND、OR、括弧で条件を組み合わせ、NULL と空文字列を区別し、TRIM と COALESCE で表示方法を明確にしました。CASE ラベルを加え、金額を作り出したり元データを変更したりせずに、確認用一覧を定義しました。
条件を1つ変えると、品質チェックを保ったまま、予想した支払い待ちの一覧になりました。次は、正常で意図的に選択した注文行を、売上指標に集計します。
