CREATE TABLE ads_complaint_final_stats (
    -- 时间维度
    time_code STRING COMMENT '时间编码(YYYYQMMDD)',
    year_code STRING COMMENT '年份(YYYY)',
    quarter_code STRING COMMENT '季度编码(含汇总标识)',
    month_code STRING COMMENT '月份编码(含汇总标识)',
    day_code STRING COMMENT '日编码(含汇总标识)',

    -- 维度类型
    dimension_type STRING COMMENT '维度类型(LINE-条线/DEPT-部门)',
    dimension_code STRING COMMENT '维度代码',
    dimension_name STRING COMMENT '维度名称',

    -- 渠道维度
    channel_root STRING COMMENT '渠道根分类(电话/APP/网点等)',
    complaint_source STRING COMMENT '投诉来源(二级分类)',

    -- 状态维度
    is_deleted_flag INT COMMENT '是否删除(0-正常/1-已删除)',
    is_effective_flag INT COMMENT '是否生效(0-无效/1-有效)',

    -- 统计指标
    total_complaint_count BIGINT COMMENT '投诉总量',
    distinct_phone_count BIGINT COMMENT '去重后总量(按电话去重)',

    -- 元数据
    data_granularity STRING COMMENT '数据粒度(Y/Q/M/D)',
    etl_time TIMESTAMP COMMENT 'ETL时间',
    dt STRING COMMENT '分区日期'
)
COMMENT '全渠道投诉最终统计表'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 日粒度：2026年3月11日
INSERT INTO ads_complaint_final_stats
SELECT
    '2026010311' as time_code,
    '2026' as year_code,
    '202601' as quarter_code,  -- 2026年Q1
    '202603' as month_code,     -- 2026年3月
    '11' as day_code,
    dimension_type,
    dimension_code,
    dimension_name,
    channel_root,
    complaint_source,
    is_deleted_flag,
    is_effective_flag,
    COUNT(*) as total_complaint_count,
    COUNT(DISTINCT phone_no) as distinct_phone_count,
    'D' as data_granularity,
    CURRENT_TIMESTAMP as etl_time,
    '20260311' as dt
FROM dwd_complaint_detail
WHERE dt = '20260311'
GROUP BY
    dimension_type, dimension_code, dimension_name,
    channel_root, complaint_source,
    is_deleted_flag, is_effective_flag;

-- 月粒度：2026年3月
INSERT INTO ads_complaint_final_stats
SELECT
    '2026010300' as time_code,  -- 日部分为00
    '2026',
    '202601',
    '202603',
    '00',
    -- 其他维度同上
    'M' as data_granularity,
    CURRENT_TIMESTAMP,
    '20260331'  -- 用月末日期做分区
FROM ...
GROUP BY ...;

-- 季粒度：2026年Q1
INSERT INTO ads_complaint_final_stats
SELECT
    '2026010000' as time_code,  -- 月、日均为00
    '2026',
    '202601',
    '0000',      -- 月为0000表示汇总
    '00',
    'Q' as data_granularity,
    ...
FROM ...
WHERE quarter_code = '202601';

-- 年粒度：2026年
INSERT INTO ads_complaint_final_stats
SELECT
    '2026000000' as time_code,  -- 季、月、日均为0
    '2026',
    '0000',      -- 季为0000表示汇总
    '0000',
    '00',
    'Y' as data_granularity,
    ...
FROM ...
WHERE year_code = '2026';

-- 查询2026年3月11日，按条线统计
SELECT
    dimension_name,
    channel_root,
    SUM(CASE WHEN is_effective_flag=1 THEN distinct_phone_count ELSE 0 END) as effective_count,
    SUM(distinct_phone_count) as total_count
FROM ads_complaint_final_stats
WHERE time_code = '2026010311'  -- 精确日粒度
    AND dimension_type = 'LINE'
GROUP BY dimension_name, channel_root;

-- 查询2026年Q1各月趋势
SELECT
    time_code,
    month_code,
    SUM(distinct_phone_count) as monthly_total
FROM ads_complaint_final_stats
WHERE time_code LIKE '20260103%'  -- 匹配Q1所有日粒度
    AND data_granularity = 'D'
    AND dimension_type = 'DEPT'
    AND dimension_code = 'BJ001'  -- 北京分行
GROUP BY time_code, month_code
ORDER BY time_code;

-- 2026年3月 vs 2025年3月（同比）
WITH current_month AS (
    SELECT SUM(distinct_phone_count) as cnt
    FROM ads_complaint_final_stats
    WHERE time_code = '2026010300'  -- 2026年3月
        AND dimension_type = 'LINE'
        AND dimension_code = 'RETAIL'
),
last_year_month AS (
    SELECT SUM(distinct_phone_count) as cnt
    FROM ads_complaint_final_stats
    WHERE time_code = '2025010300'  -- 2025年3月
        AND dimension_type = 'LINE'
        AND dimension_code = 'RETAIL'
)
SELECT
    current_month.cnt as current_value,
    last_year_month.cnt as last_year_value,
    CASE
        WHEN last_year_month.cnt = 0 OR last_year_month.cnt IS NULL THEN '/'
        ELSE ROUND((current_month.cnt - last_year_month.cnt) * 100.0 / last_year_month.cnt, 2)
    END as yoy_ratio
FROM current_month, last_year_month;

-- 每日跑批：同时写入所有粒度
INSERT OVERWRITE TABLE ads_complaint_final_stats
PARTITION(dt='${bizdate}')
-- 日粒度
SELECT
    CONCAT(year_code, quarter_code, month_code, day_code) as time_code,
    -- ... 其他字段
    'D' as data_granularity
FROM daily_agg

UNION ALL

-- 月粒度（月末执行）
SELECT
    CONCAT(year_code, quarter_code, month_code, '00') as time_code,
    'M' as data_granularity
FROM month_agg
WHERE day_code = 'LAST_DAY'

UNION ALL

-- 季粒度（季末执行）
SELECT
    CONCAT(year_code, quarter_code, '0000') as time_code,
    'Q' as data_granularity
FROM quarter_agg
WHERE month_code = 'LAST_MONTH_OF_QUARTER'

UNION ALL

-- 年粒度（年末执行）
SELECT
    CONCAT(year_code, '000000') as time_code,
    'Y' as data_granularity
FROM year_agg
WHERE quarter_code = 'Q4';

调度任务:
  - 名称: 每日增量统计
    频率: 每天
    执行: 写入当日粒度和汇总月/季/年（若月末/季末/年末）

  - 名称: 月末全量重刷
    频率: 每月1号
    执行: 重刷上月所有日粒度和月粒度

  - 名称: 季末全量重刷
    频率: 每季度初
    执行: 重刷上季度所有粒度

@RestController
@RequestMapping("/api/v1/complaint/stats")
public class ComplaintStatsApi {

    @GetMapping("/query")
    public Result queryStats(
        @RequestParam String startTime,  // 2026010100
        @RequestParam String endTime,    // 2026033100
        @RequestParam String granularity, // D/M/Q/Y
        @RequestParam String dimensionType,
        @RequestParam(required=false) String dimensionCode,
        @RequestParam(required=false) Boolean includeYoY,
        @RequestParam(required=false) Boolean includeMoM
    ) {
        // 1. 解析时间范围
        String timePattern = buildTimePattern(startTime, endTime, granularity);

        // 2. 查询数据
        List<StatsVO> data = sqlSession.selectList(
            "queryTimeSeries",
            Map.of(
                "timePattern", timePattern,
                "dimensionType", dimensionType,
                "dimensionCode", dimensionCode,
                "dataGranularity", granularity
            )
        );

        // 3. 计算同比环比（如需）
        if (includeYoY || includeMoM) {
            enrichWithYoYMoM(data, granularity);
        }

        return Result.success(data);
    }
}