Data Warehouse

-- Star Schema 설계 예제: 판매 분석 데이터 웨어하우스

-- 1. 차원 테이블: 날짜 차원
CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,
    full_date DATE NOT NULL,
    year INT,
    quarter INT,
    month INT,
    month_name VARCHAR(20),
    week INT,
    day_of_week INT,
    day_name VARCHAR(20),
    is_weekend BOOLEAN,
    is_holiday BOOLEAN
);

-- 2. 차원 테이블: 제품 차원
CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,
    product_id VARCHAR(50),
    product_name VARCHAR(200),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    unit_price DECIMAL(10, 2)
);

-- 3. 차원 테이블: 고객 차원
CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY,
    customer_id VARCHAR(50),
    customer_name VARCHAR(200),
    segment VARCHAR(50),    -- Enterprise, SMB, Consumer
    region VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100)
);

-- 4. 사실 테이블: 판매 사실 (중앙)
CREATE TABLE fact_sales (
    sale_key BIGINT PRIMARY KEY,
    date_key INT REFERENCES dim_date(date_key),
    product_key INT REFERENCES dim_product(product_key),
    customer_key INT REFERENCES dim_customer(customer_key),
    -- 측정값 (Measures)
    quantity INT,
    unit_price DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    total_amount DECIMAL(12, 2),
    profit DECIMAL(10, 2)
);

-- 분석 쿼리 예제: 월별 카테고리별 매출
SELECT
    d.year,
    d.month_name,
    p.category,
    SUM(f.total_amount) AS total_sales,
    SUM(f.profit) AS total_profit,
    COUNT(DISTINCT f.customer_key) AS unique_customers
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year = 2024
GROUP BY d.year, d.month, d.month_name, p.category
ORDER BY d.month, total_sales DESC;