NULL 처리를 활용한 고객 데이터 정제

from google.cloud import bigquery
import pandas as pd
 
# 인증 설정 (서비스 계정 키 사용 시)
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your-key.json"
 
# BigQuery 클라이언트 생성
client = bigquery.Client(project='your-project-id')
 
# 쿼리 실행 함수
def run_query(query):
    return client.query(query).to_dataframe()

WHERE column IS NULL
WHERE column IS NOT NULL

SELECT
    'age' AS column_name,
    COUNT(*) AS total_rows,
    COUNT(age) AS non_null_count,
    COUNT(*) - COUNT(age) AS null_count,
    ROUND((COUNT(*) - COUNT(age)) * 100.0 / COUNT(*), 2) AS null_percentage
FROM `your-project-id.thelook_ecommerce.users`
 
UNION ALL
 
SELECT
    'gender' AS column_name,
    COUNT(*) AS total_rows,
    COUNT(gender) AS non_null_count,
    COUNT(*) - COUNT(gender) AS null_count,
    ROUND((COUNT(*) - COUNT(gender)) * 100.0 / COUNT(*), 2) AS null_percentage
FROM `your-project-id.thelook_ecommerce.users`
 
UNION ALL
 
SELECT
    'state' AS column_name,
    COUNT(*) AS total_rows,
    COUNT(state) AS non_null_count,
    COUNT(*) - COUNT(state) AS null_count,
    ROUND((COUNT(*) - COUNT(state)) * 100.0 / COUNT(*), 2) AS null_percentage
FROM `your-project-id.thelook_ecommerce.users`
 
ORDER BY null_percentage DESC

COALESCE(value1, value2, value3, default_value)

SELECT
    user_id,
    first_name,
    last_name,
    state,
    country,
    COALESCE(state, 'Unknown State') AS state_cleaned,
    COALESCE(country, 'Unknown Country') AS country_cleaned,
    COALESCE(state, country, 'Unknown Location') AS location
FROM `your-project-id.thelook_ecommerce.users`
WHERE state IS NULL OR country IS NULL
LIMIT 20

IFNULL(column, default_value)

SELECT
    user_id,
    first_name,
    last_name,
    gender,
    state,
    IFNULL(gender, 'Unknown') AS gender_cleaned,
    IFNULL(state, 'N/A') AS state_cleaned,
    CONCAT(IFNULL(first_name, ''), ' ', IFNULL(last_name, '')) AS full_name
FROM `your-project-id.thelook_ecommerce.users`
WHERE gender IS NULL OR state IS NULL
LIMIT 30

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

SELECT
    user_id,
    first_name,
    age,
    CASE
        WHEN age IS NULL THEN 'Unknown'
        WHEN age < 20 THEN '10대'
        WHEN age < 30 THEN '20대'
        WHEN age < 40 THEN '30대'
        WHEN age < 50 THEN '40대'
        WHEN age < 60 THEN '50대'
        ELSE '60대+'
    END AS age_group,
    CASE
        WHEN age IS NULL THEN 'N/A'
        WHEN age >= 40 THEN 'VIP'
        ELSE 'Regular'
    END AS customer_tier
FROM `your-project-id.thelook_ecommerce.users`
LIMIT 50

NULLIF(expression1, expression2)
-- expression1 == expression2이면 NULL, 아니면 expression1

SELECT
    p.category,
    COUNT(*) AS total_products,
    SUM(CASE WHEN oi.order_id IS NOT NULL THEN 1 ELSE 0 END) AS sold_products,
    -- NULLIF로 0 나누기 방지
    ROUND(
        SUM(CASE WHEN oi.order_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 /
        NULLIF(COUNT(*), 0),
        2
    ) AS sell_through_rate
FROM `your-project-id.thelook_ecommerce.products` p
LEFT JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY sell_through_rate DESC
LIMIT 15