날짜/시간 함수를 활용한 매출 분석

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

-- 현재 날짜/시간 조회
CURRENT_DATE()        -- 현재 날짜
CURRENT_DATETIME()    -- 현재 날짜시간
CURRENT_TIMESTAMP()   -- 현재 타임스탬프
 
-- 타입 변환
CAST(timestamp_col AS DATE)      -- TIMESTAMP → DATE
CAST(timestamp_col AS DATETIME)  -- TIMESTAMP → DATETIME

SELECT
    CURRENT_DATE() AS current_date,
    CURRENT_DATETIME() AS current_datetime,
    CURRENT_TIMESTAMP() AS current_timestamp,
 
    -- created_at을 각 타입으로 변환
    CAST(created_at AS DATE) AS order_date,
    CAST(created_at AS DATETIME) AS order_datetime,
    created_at AS order_timestamp
FROM `your-project-id.thelook_ecommerce.orders`
LIMIT 5

EXTRACT(part FROM date_expression)

SELECT
    EXTRACT(DAYOFWEEK FROM created_at) AS day_of_week,
    CASE EXTRACT(DAYOFWEEK FROM created_at)
        WHEN 1 THEN '일요일'
        WHEN 2 THEN '월요일'
        WHEN 3 THEN '화요일'
        WHEN 4 THEN '수요일'
        WHEN 5 THEN '목요일'
        WHEN 6 THEN '금요일'
        WHEN 7 THEN '토요일'
    END AS day_name,
    COUNT(*) AS order_count
FROM `your-project-id.thelook_ecommerce.orders`
GROUP BY day_of_week, day_name
ORDER BY day_of_week

DATE_TRUNC(date_expression, date_part)

SELECT
    DATE_TRUNC(CAST(o.created_at AS DATE), MONTH) AS month,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.user_id) AS customer_count,
    SUM(oi.sale_price) AS total_revenue,
    ROUND(SUM(oi.sale_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
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.created_at >= '2024-01-01'
GROUP BY month
ORDER BY month

DATE_DIFF(end_date, start_date, date_part)

SELECT
    user_id,
    MIN(CAST(created_at AS DATE)) AS first_order_date,
    MAX(CAST(created_at AS DATE)) AS last_order_date,
    DATE_DIFF(MAX(CAST(created_at AS DATE)), MIN(CAST(created_at AS DATE)), DAY) AS days_active,
    COUNT(DISTINCT order_id) AS total_orders,
    ROUND(
        COUNT(DISTINCT order_id) * 1.0 /
        NULLIF(DATE_DIFF(MAX(CAST(created_at AS DATE)), MIN(CAST(created_at AS DATE)), MONTH), 0),
        2
    ) AS orders_per_month
FROM `your-project-id.thelook_ecommerce.orders`
GROUP BY user_id
HAVING COUNT(DISTINCT order_id) > 1  -- 2회 이상 주문 고객만
ORDER BY days_active DESC
LIMIT 10

-- 전월 데이터 조인
DATE_SUB(current_month, INTERVAL 1 MONTH)
 
-- 전년 동월 데이터 조인
DATE_SUB(current_month, INTERVAL 1 YEAR)

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
    curr.month,
    curr.revenue AS current_revenue,
    prev.revenue AS previous_revenue,
    ROUND((curr.revenue - prev.revenue) / prev.revenue * 100, 2) AS mom_growth_pct
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
    ON curr.month = DATE_ADD(prev.month, INTERVAL 1 MONTH)
WHERE curr.month >= '2024-01-01'
ORDER BY curr.month