账单看不清归属，优化就永远推不动

-- 基于 Cost and Usage Report 的业务线成本归因分析
-- 前置：CUR 已导出到 S3 并通过 Glue Crawler 建表

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', line_item_usage_start_date)          AS month,
        COALESCE(NULLIF(resource_tags_user_cost_center, ''),
                 '__UNTAGGED__')                                 AS cost_center,
        COALESCE(NULLIF(resource_tags_user_environment, ''),
                 '__UNKNOWN__')                                  AS env,
        product_product_name                                     AS service,
        line_item_line_item_type                                 AS item_type,
        SUM(line_item_unblended_cost)                            AS unblended,
        SUM(CASE WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage'
                 THEN savings_plan_savings_plan_effective_cost
                 WHEN line_item_line_item_type = 'DiscountedUsage'
                 THEN reservation_effective_cost
                 ELSE line_item_unblended_cost END)              AS amortized
    FROM cur_database.cur_table
    WHERE line_item_usage_start_date >= DATE_ADD('month', -6, CURRENT_DATE)
      AND line_item_line_item_type NOT IN ('Tax', 'Refund', 'Credit')
    GROUP BY 1, 2, 3, 4, 5
)
SELECT
    month,
    cost_center,
    env,
    ROUND(SUM(amortized), 2)                                     AS amortized_cost,
    ROUND(SUM(unblended), 2)                                     AS unblended_cost,
    -- 环比变化，用于识别异动
    ROUND(SUM(amortized) - LAG(SUM(amortized)) OVER (
        PARTITION BY cost_center, env ORDER BY month
    ), 2)                                                        AS mom_delta,
    ROUND(100.0 * (SUM(amortized) - LAG(SUM(amortized)) OVER (
        PARTITION BY cost_center, env ORDER BY month
    )) / NULLIF(LAG(SUM(amortized)) OVER (
        PARTITION BY cost_center, env ORDER BY month
    ), 0), 1)                                                    AS mom_pct
FROM monthly
GROUP BY month, cost_center, env
ORDER BY month DESC, amortized_cost DESC;

-- 未打标资源专项：这部分必须持续压缩到 5% 以内
SELECT
    line_item_product_code                                       AS service,
    line_item_resource_id                                        AS resource_id,
    ROUND(SUM(line_item_unblended_cost), 2)                      AS cost_30d
FROM cur_database.cur_table
WHERE line_item_usage_start_date >= DATE_ADD('day', -30, CURRENT_DATE)
  AND COALESCE(NULLIF(resource_tags_user_cost_center, ''), '') = ''
  AND line_item_resource_id <> ''
GROUP BY 1, 2
HAVING SUM(line_item_unblended_cost) > 10
ORDER BY cost_30d DESC
LIMIT 100;