数据分析师用多维度分析框架定位GMV增长来源

销售额(指标) × 时间维度 × 地区维度 × 产品维度 = 多维分析立方体

例如："2024年7月北京地区手机类产品的销售额"

固定时间="2024年7月" → 查看各地区×产品类别的销售情况

时间="2024年7月" AND 地区="北京" → 查看北京7月各产品销售情况

钻取：年 → 季度 → 月 → 周 → 日
上钻：产品型号 → 产品系列 → 产品类别

行：地区，列：时间 → 行：时间，列：地区

1. 识别业务流程
   - 确定要分析的核心业务事件
   - 明确分析的业务范围和边界

2. 确定粒度层级
   - 定义事实表的最细粒度
   - 考虑未来扩展和聚合需求

3. 识别维度
   - 列出所有分析角度
   - 设计维度表结构和层次

4. 确定事实
   - 选择可加性数值度量
   - 设计非加性度量的处理方式

5. 构建物理模型
   - 创建表结构和索引
   - 设计ETL加载流程

-- 销售事实表
CREATE TABLE fact_sales (
    sale_id BIGINT,
    date_id INT,
    product_id INT,
    customer_id INT,
    store_id INT,
    channel_id INT,

    -- 可加性指标
    sales_amount DECIMAL(15,2),
    quantity INT,
    cost_amount DECIMAL(15,2),
    profit_amount DECIMAL(15,2),

    -- 半加性指标
    discount_rate DECIMAL(5,4),

    -- 非加性指标(需要特殊处理)
    unit_price DECIMAL(10,2),

    created_time TIMESTAMP,
    updated_time TIMESTAMP
);

-- 时间维度表
CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    full_date DATE,
    year_num INT,
    quarter_num INT,
    month_num INT,
    week_num INT,
    day_of_week INT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN,
    quarter_name VARCHAR(10),
    month_name VARCHAR(20),
    season VARCHAR(10)
);

-- 产品维度表(层次结构)
CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(50),
    product_name VARCHAR(200),
    brand_id INT,
    brand_name VARCHAR(100),
    category_l1_id INT,
    category_l1_name VARCHAR(100),
    category_l2_id INT,
    category_l2_name VARCHAR(100),
    category_l3_id INT,
    category_l3_name VARCHAR(100),
    price_range VARCHAR(50),
    is_active BOOLEAN
);

-- 按时间、地区、产品类别的销售分析
SELECT
    d.year_num,
    d.month_num,
    c.region_name,
    p.category_l1_name,
    SUM(f.sales_amount) as total_sales,
    SUM(f.quantity) as total_quantity,
    COUNT(DISTINCT f.customer_id) as unique_customers
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year_num = 2024
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;

-- 月度销售同比环比分析
WITH monthly_sales AS (
    SELECT
        d.year_num,
        d.month_num,
        SUM(f.sales_amount) as sales_amount
    FROM fact_sales f
    JOIN dim_date d ON f.date_id = d.date_id
    GROUP BY 1,2
)
SELECT
    year_num,
    month_num,
    sales_amount,
    LAG(sales_amount, 1) OVER (
        PARTITION BY month_num
        ORDER BY year_num
    ) as prev_year_sales,
    LAG(sales_amount, 1) OVER (
        ORDER BY year_num, month_num
    ) as prev_month_sales,

    -- 同比增长率
    (sales_amount - LAG(sales_amount, 1) OVER (
        PARTITION BY month_num ORDER BY year_num
    )) / LAG(sales_amount, 1) OVER (
        PARTITION BY month_num ORDER BY year_num
    ) * 100 as yoy_growth_rate,

    -- 环比增长率
    (sales_amount - LAG(sales_amount, 1) OVER (
        ORDER BY year_num, month_num
    )) / LAG(sales_amount, 1) OVER (
        ORDER BY year_num, month_num
    ) * 100 as mom_growth_rate

FROM monthly_sales
ORDER BY year_num, month_num;

-- 支持钻取的层次化查询
SELECT
    -- 时间钻取：年->季度->月
    CASE
        WHEN :drill_level = 'year' THEN CAST(d.year_num AS VARCHAR)
        WHEN :drill_level = 'quarter' THEN d.year_num || '-Q' || d.quarter_num
        WHEN :drill_level = 'month' THEN d.year_num || '-' || LPAD(d.month_num, 2, '0')
    END as time_period,

    -- 产品钻取：类别->子类别->产品
    CASE
        WHEN :product_level = 'l1' THEN p.category_l1_name
        WHEN :product_level = 'l2' THEN p.category_l2_name
        WHEN :product_level = 'product' THEN p.product_name
    END as product_level,

    SUM(f.sales_amount) as sales_amount,
    COUNT(*) as order_count

FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year_num >= 2023
GROUP BY 1, 2
ORDER BY 1, 2;

-- 整体趋势分析
SELECT
    d.year_num || '-' || LPAD(d.month_num, 2, '0') as month,
    SUM(f.sales_amount) as gmv,
    COUNT(DISTINCT f.customer_id) as active_customers,
    COUNT(*) as order_count,
    SUM(f.sales_amount) / COUNT(*) as avg_order_value
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year_num = 2024 AND d.month_num BETWEEN 6 AND 8
GROUP BY 1
ORDER BY 1;

-- 地区维度分析
SELECT
    c.region_name,
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) as july_gmv,
    SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) as august_gmv,
    (SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) -
     SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END)) /
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) * 100 as growth_rate
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
WHERE d.year_num = 2024 AND d.month_num IN (7,8)
GROUP BY 1
ORDER BY 4;

-- 产品类别分析
SELECT
    p.category_l1_name,
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) as july_gmv,
    SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) as august_gmv,
    (SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) -
     SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END)) /
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) * 100 as growth_rate
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year_num = 2024 AND d.month_num IN (7,8)
GROUP BY 1
ORDER BY 4;

-- 地区×产品类别交叉分析
SELECT
    c.region_name,
    p.category_l1_name,
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) as july_gmv,
    SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) as august_gmv,
    (SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) -
     SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END)) /
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) * 100 as growth_rate
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year_num = 2024 AND d.month_num IN (7,8)
GROUP BY 1,2
ORDER BY 5;

-- 华南3C产品的细分品牌分析
SELECT
    p.brand_name,
    p.category_l2_name,
    SUM(CASE WHEN d.month_num = 7 THEN f.sales_amount END) as july_gmv,
    SUM(CASE WHEN d.month_num = 8 THEN f.sales_amount END) as august_gmv,
    SUM(CASE WHEN d.month_num = 7 THEN f.quantity END) as july_qty,
    SUM(CASE WHEN d.month_num = 8 THEN f.quantity END) as august_qty
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year_num = 2024 AND d.month_num IN (7,8)
    AND c.region_name = '华南'
    AND p.category_l1_name = '3C数码'
GROUP BY 1,2
ORDER BY 4-3 DESC;

# 异常检测示例
def detect_anomalies(data, dimensions):
    """多维数据异常检测"""
    anomalies = []

    for dim_combination in itertools.combinations(dimensions, 2):
        # 按维度组合分组
        grouped = data.groupby(list(dim_combination))

        for group_key, group_data in grouped:
            # 使用IQR方法检测异常值
            Q1 = group_data['metric'].quantile(0.25)
            Q3 = group_data['metric'].quantile(0.75)
            IQR = Q3 - Q1

            # 定义异常值边界
            lower_bound = Q1 - 1.5 * IQR
            upper_bound = Q3 + 1.5 * IQR

            # 识别异常值
            anomaly_mask = (group_data['metric'] < lower_bound) | \
                          (group_data['metric'] > upper_bound)

            if anomaly_mask.any():
                anomalies.extend(group_data[anomaly_mask].to_dict('records'))

    return anomalies