문자열 함수를 활용한 제품 분석

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()

CONCAT(string1, string2, string3, ...)

SELECT
    user_id,
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    state,
    country,
    CONCAT(IFNULL(state, 'Unknown'), ', ', IFNULL(country, 'Unknown')) AS location,
    email,
    CONCAT('Customer ID: ', CAST(user_id AS STRING), ' - ', first_name, ' ', last_name) AS customer_label
FROM `your-project-id.thelook_ecommerce.users`
WHERE first_name IS NOT NULL AND last_name IS NOT NULL
LIMIT 20

WHERE CONTAINS(column, 'keyword')

WITH keyword_products AS (
    SELECT
        p.id AS product_id,
        p.name,
        p.category,
        p.brand,
        p.retail_price,
        CASE
            WHEN LOWER(p.name) LIKE '%shirt%' THEN 'Shirt'
            WHEN LOWER(p.name) LIKE '%pant%' THEN 'Pants'
            WHEN LOWER(p.name) LIKE '%jean%' THEN 'Jeans'
            WHEN LOWER(p.name) LIKE '%dress%' THEN 'Dress'
            WHEN LOWER(p.name) LIKE '%jacket%' THEN 'Jacket'
            ELSE 'Other'
        END AS product_type
    FROM `your-project-id.thelook_ecommerce.products` p
)
SELECT
    product_type,
    COUNT(*) AS product_count,
    AVG(retail_price) AS avg_price
FROM keyword_products
GROUP BY product_type
ORDER BY product_count DESC

SUBSTR('Hello World', 1, 5)  → 'Hello'
LEFT('Hello World', 5)       → 'Hello'
RIGHT('Hello World', 5)      → 'World'

SELECT
    id AS product_id,
    name,
    sku,
    LEFT(sku, 3) AS sku_prefix,
    SUBSTR(sku, 4, 3) AS sku_middle,
    RIGHT(sku, 3) AS sku_suffix,
    LENGTH(sku) AS sku_length
FROM `your-project-id.thelook_ecommerce.products`
WHERE sku IS NOT NULL
LIMIT 30

WITH normalized_brands AS (
    SELECT
        brand AS original_brand,
        UPPER(TRIM(brand)) AS normalized_brand,
        COUNT(*) AS product_count
    FROM `your-project-id.thelook_ecommerce.products`
    WHERE brand IS NOT NULL
    GROUP BY brand
)
SELECT
    normalized_brand,
    SUM(product_count) AS total_products,
    COUNT(DISTINCT original_brand) AS brand_variations
FROM normalized_brands
GROUP BY normalized_brand
HAVING COUNT(DISTINCT original_brand) > 1 OR SUM(product_count) >= 10
ORDER BY brand_variations DESC, total_products DESC
LIMIT 20

SPLIT(string, delimiter)
-- 결과: ARRAY

SPLIT('a,b,c', ',')[OFFSET(0)]  → 'a'
SPLIT('a,b,c', ',')[OFFSET(1)]  → 'b'
SPLIT('a,b,c', ',')[SAFE_OFFSET(10)]  → NULL (안전)

WITH email_parts AS (
    SELECT
        id AS user_id,
        email,
        SPLIT(email, '@')[SAFE_OFFSET(0)] AS username,
        SPLIT(email, '@')[SAFE_OFFSET(1)] AS domain,
        ARRAY_LENGTH(SPLIT(email, '@')) AS parts_count
    FROM `your-project-id.thelook_ecommerce.users`
    WHERE email IS NOT NULL
        AND email LIKE '%@%'
)
SELECT
    user_id,
    email,
    username,
    domain,
    LENGTH(username) AS username_length,
    CASE
        WHEN domain LIKE '%.com' THEN 'Commercial'
        WHEN domain LIKE '%.org' THEN 'Organization'
        WHEN domain LIKE '%.edu' THEN 'Education'
        WHEN domain LIKE '%.gov' THEN 'Government'
        ELSE 'Other'
    END AS domain_type
FROM email_parts
WHERE parts_count = 2
LIMIT 30

-- 1. 정규화
UPPER(TRIM(column))
 
-- 2. 이메일 도메인 추출
SPLIT(LOWER(TRIM(email)), '@')[SAFE_OFFSET(1)]
 
-- 3. 코드 파싱
LEFT(sku, 3) AS category_code
 
-- 4. 키워드 검색
WHERE LOWER(name) LIKE '%keyword%'
 
-- 5. 포맷팅
CONCAT('[', brand, '] ', name)