윈도우 함수를 활용한 순위 및 이동 분석

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

함수명() OVER (
    [PARTITION BY 그룹_컬럼]     -- 그룹 단위 지정 (선택)
    [ORDER BY 정렬_컬럼]          -- 정렬 기준 (함수에 따라 필수)
    [ROWS/RANGE 프레임_지정]      -- 윈도우 범위 (선택)
)

WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        p.brand,
        SUM(oi.sale_price) AS total_revenue
    FROM `your-project-id.thelook_ecommerce.order_items` oi
    JOIN `your-project-id.thelook_ecommerce.products` p ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category, p.brand
)
SELECT
    category,
    name,
    brand,
    total_revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) AS rank_in_category
FROM product_revenue
QUALIFY rank_in_category <= 3
ORDER BY category, rank_in_category

LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS month,
        SUM(oi.sale_price) AS revenue
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY month
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS revenue_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month)) /
        LAG(revenue, 1) OVER (ORDER BY month) * 100,
        2
    ) AS growth_rate_pct
FROM monthly_revenue
WHERE month >= '2024-01-01'
ORDER BY month

SUM(col) OVER (
    ORDER BY date
    ROWS BETWEEN start AND end
)

WITH daily_revenue AS (
    SELECT
        CAST(o.created_at AS DATE) AS date,
        SUM(oi.sale_price) AS revenue
    FROM `your-project-id.thelook_ecommerce.orders` o
    JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
    GROUP BY date
)
SELECT
    date,
    revenue,
    -- 누적 합계
    SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS cumulative_revenue,
    -- 7일 이동평균
    ROUND(
        AVG(revenue) OVER (
            ORDER BY date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_revenue
WHERE date >= '2024-10-01'
ORDER BY date

SELECT
    o.user_id,
    o.order_id,
    o.created_at,
    oi.sale_price,
    -- 고객별 구매 순서
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS purchase_order,
    -- 고객별 누적 구매액
    SUM(oi.sale_price) OVER (
        PARTITION BY o.user_id
        ORDER BY o.created_at
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative_purchase,
    -- 이전 구매와의 간격 (일)
    DATE_DIFF(
        CAST(o.created_at AS DATE),
        CAST(LAG(o.created_at) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS DATE),
        DAY
    ) AS days_since_last_purchase
FROM `your-project-id.thelook_ecommerce.orders` o
JOIN `your-project-id.thelook_ecommerce.order_items` oi ON o.order_id = oi.order_id
WHERE o.user_id IN (1, 2, 3)  -- 샘플 고객
ORDER BY o.user_id, o.created_at