AWS CUR 学习文档

SELECT DATE(line_item_usage_start_date) AS usage_day,
       ROUND(SUM(line_item_unblended_cost),2) AS daily_cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1
ORDER BY usage_day;

SELECT date_trunc('hour', line_item_usage_start_date) AS usage_hour,
       ROUND(SUM(line_item_unblended_cost),4) AS hourly_cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_product_code = 'AmazonEC2'
  AND line_item_usage_start_date >= DATE '2026-07-20'
  AND line_item_usage_start_date <  DATE '2026-07-27'
GROUP BY 1
ORDER BY usage_hour;

WITH daily AS (
  SELECT DATE(line_item_usage_start_date) AS usage_day,
         service,
         SUM(line_item_unblended_cost) AS cost
  FROM cur
  WHERE line_item_line_item_type = 'Usage'
    AND line_item_usage_start_date >= DATE '2026-07-01'
  GROUP BY 1,2
)
SELECT usage_day, service,
       ROUND(cost,2)                                    AS cost,
       ROUND(LAG(cost) OVER (PARTITION BY service ORDER BY usage_day),2) AS prev_day,
       ROUND(cost - LAG(cost) OVER (PARTITION BY service ORDER BY usage_day),2) AS delta
FROM daily
ORDER BY delta DESC
LIMIT 20;

SELECT service,
       ROUND(SUM(CASE WHEN line_item_usage_start_date >= DATE '2026-07-01'
                      THEN line_item_unblended_cost ELSE 0 END),2) AS this_month,
       ROUND(SUM(CASE WHEN line_item_usage_start_date <  DATE '2026-07-01'
                      THEN line_item_unblended_cost ELSE 0 END),2) AS last_month,
       ROUND(SUM(CASE WHEN line_item_usage_start_date >= DATE '2026-07-01'
                      THEN line_item_unblended_cost ELSE -line_item_unblended_cost END),2) AS diff
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-06-01'
  AND line_item_usage_start_date <  DATE '2026-08-01'
GROUP BY 1
ORDER BY diff DESC;

-- 从用例 8 找到「7 月 23 日某服务暴涨」，用这条查出具体是什么在涨
SELECT account_name, service,
       line_item_usage_type, line_item_operation,
       ROUND(SUM(line_item_usage_amount),2)   AS qty,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-23'
  AND line_item_usage_start_date <  DATE '2026-07-24'
GROUP BY 1,2,3,4
ORDER BY cost DESC
LIMIT 30;

SELECT account_id, account_name,
       ROUND(SUM(line_item_unblended_cost),2) AS cost,
       ROUND(100.0 * SUM(line_item_unblended_cost)
             / SUM(SUM(line_item_unblended_cost)) OVER (),1) AS pct
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2
ORDER BY cost DESC;

WITH t AS (
  SELECT account_name, service,
         SUM(line_item_unblended_cost) AS cost,
         ROW_NUMBER() OVER (PARTITION BY account_name
                            ORDER BY SUM(line_item_unblended_cost) DESC) AS rn
  FROM cur
  WHERE line_item_line_item_type = 'Usage'
    AND line_item_usage_start_date >= DATE '2026-07-01'
  GROUP BY 1,2
)
SELECT account_name, service, ROUND(cost,2) AS cost
FROM t
WHERE rn <= 3
ORDER BY account_name, cost DESC;

WITH this_m AS (
  SELECT account_name, service, SUM(line_item_unblended_cost) AS cost
  FROM cur
  WHERE line_item_line_item_type = 'Usage'
    AND line_item_usage_start_date >= DATE '2026-07-01'
  GROUP BY 1,2
), last_m AS (
  SELECT DISTINCT account_name, service
  FROM cur
  WHERE line_item_line_item_type = 'Usage'
    AND line_item_usage_start_date >= DATE '2026-06-01'
    AND line_item_usage_start_date <  DATE '2026-07-01'
)
SELECT t.account_name, t.service, ROUND(t.cost,2) AS new_cost
FROM this_m t
LEFT JOIN last_m l
  ON t.account_name = l.account_name AND t.service = l.service
WHERE l.service IS NULL
ORDER BY new_cost DESC;

SELECT service, line_item_usage_type,
       ROUND(SUM(line_item_usage_amount),2)   AS qty,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_account_id = '222222222222'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2
ORDER BY cost DESC
LIMIT 30;

SELECT line_item_product_code AS product_code,
       line_item_usage_type   AS usage_type,
       line_item_operation    AS operation,
       MAX(line_item_line_item_description)  AS price_desc,
       ROUND(SUM(line_item_usage_amount),2)   AS qty,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_product_code = 'AmazonEC2'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2,3
ORDER BY cost DESC
LIMIT 30;

SELECT CASE
         WHEN line_item_usage_type LIKE '%BoxUsage%'        THEN '1-按需实例'
         WHEN line_item_usage_type LIKE '%SpotUsage%'       THEN '2-Spot 实例'
         WHEN line_item_usage_type LIKE '%EBS:VolumeUsage%' THEN '3-EBS 卷'
         WHEN line_item_usage_type LIKE '%EBS:Snapshot%'    THEN '4-EBS 快照'
         WHEN line_item_usage_type LIKE '%NatGateway%'      THEN '5-NAT Gateway'
         WHEN line_item_usage_type LIKE '%DataTransfer%'    THEN '6-流量'
         WHEN line_item_usage_type LIKE '%ElasticIP%'       THEN '7-弹性 IP'
         ELSE '9-其他'
       END AS bucket,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_product_code = 'AmazonEC2'
GROUP BY 1
ORDER BY bucket;

SELECT CASE
         WHEN line_item_usage_type LIKE '%AZ-DataTransfer%' THEN '跨 AZ（同区域）'
         WHEN line_item_usage_type LIKE '%DataTransfer-Out%' THEN '出到互联网'
         WHEN line_item_usage_type LIKE '%DataTransfer-In%'  THEN '入向（多为免费）'
         ELSE '跨区域 / 其他'
       END AS transfer_kind,
       line_item_usage_type,
       ROUND(SUM(line_item_usage_amount),2)   AS gb,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_type LIKE '%DataTransfer%'
GROUP BY 1,2
ORDER BY cost DESC;

-- 金额 ÷ 用量 = 实际有效单价，和 description 里的官网价对比
SELECT line_item_usage_type AS usage_type,
       MAX(line_item_line_item_description) AS list_price_desc,
       ROUND(SUM(line_item_usage_amount),2) AS qty,
       ROUND(SUM(pricing_public_on_demand_cost)
             / NULLIF(SUM(line_item_usage_amount),0),6) AS list_rate,
       ROUND(SUM(line_item_net_unblended_cost)
             / NULLIF(SUM(line_item_usage_amount),0),6) AS effective_rate
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_product_code = 'AmazonEC2'
GROUP BY 1
HAVING SUM(line_item_usage_amount) > 0
ORDER BY qty DESC
LIMIT 25;

-- description 是自由文本，适合做关键词排查，不适合做稳定分组键
SELECT service,
       line_item_line_item_description AS description,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND (line_item_line_item_description LIKE '%Windows%'
    OR line_item_line_item_description LIKE '%SQL Server%')
GROUP BY 1,2
ORDER BY cost DESC;

SELECT COALESCE(NULLIF(resource_tags_user_project,''),'(未打 Project)') AS project,
       ROUND(SUM(line_item_unblended_cost),2)     AS unblended,
       ROUND(SUM(line_item_net_unblended_cost),2) AS net
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1
ORDER BY net DESC;

SELECT COALESCE(NULLIF(resource_tags_user_service_name,''),'(未打)') AS biz_service,
       service AS aws_service,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2
ORDER BY biz_service, cost DESC;

SELECT ROUND(SUM(line_item_unblended_cost),2) AS total_cost,
       ROUND(100.0 * SUM(CASE WHEN resource_tags_user_project <> ''
                              THEN line_item_unblended_cost ELSE 0 END)
             / NULLIF(SUM(line_item_unblended_cost),0),1) AS project_pct,
       ROUND(100.0 * SUM(CASE WHEN resource_tags_user_service_name <> ''
                              THEN line_item_unblended_cost ELSE 0 END)
             / NULLIF(SUM(line_item_unblended_cost),0),1) AS service_name_pct,
       ROUND(100.0 * SUM(CASE WHEN resource_tags_user_name <> ''
                              THEN line_item_unblended_cost ELSE 0 END)
             / NULLIF(SUM(line_item_unblended_cost),0),1) AS name_pct
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_resource_id <> ''   -- 只统计能打标签的行
  AND line_item_usage_start_date >= DATE '2026-07-01';

SELECT account_name, service,
       line_item_resource_id AS resource_id,
       COALESCE(NULLIF(resource_tags_user_name,''),'(连 Name 都没有)') AS name_tag,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_resource_id <> ''
  AND COALESCE(resource_tags_user_project,'') = ''
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2,3,4
ORDER BY cost DESC
LIMIT 30;

SELECT DATE(line_item_usage_start_date) AS usage_day,
       COALESCE(NULLIF(resource_tags_user_service_name,''),'(未打)') AS biz_service,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND resource_tags_user_project = 'trading-core'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2
ORDER BY usage_day, cost DESC;

SELECT line_item_resource_id AS resource_id,
       account_name, service,
       COALESCE(NULLIF(resource_tags_user_name,''),'-')         AS name_tag,
       COALESCE(NULLIF(resource_tags_user_project,''),'-')      AS project,
       COALESCE(NULLIF(resource_tags_user_service_name,''),'-') AS biz_service,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_resource_id <> ''
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2,3,4,5,6
ORDER BY cost DESC
LIMIT 30;

-- ① 这个资源的钱花在哪些计量项上
SELECT line_item_usage_type, line_item_operation,
       MAX(line_item_line_item_description)  AS description,
       ROUND(SUM(line_item_usage_amount),2)   AS qty,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_resource_id = 'i-0a1b2c3d4e5f67890'
  AND line_item_line_item_type = 'Usage'
GROUP BY 1,2
ORDER BY cost DESC;

-- ② 它是从哪天开始涨的
SELECT DATE(line_item_usage_start_date) AS usage_day,
       ROUND(SUM(line_item_usage_amount),2)   AS qty,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_resource_id = 'i-0a1b2c3d4e5f67890'
GROUP BY 1
ORDER BY usage_day;

SELECT CASE WHEN COALESCE(line_item_resource_id,'') = ''
            THEN '无资源 ID' ELSE '有资源 ID' END AS has_resource,
       line_item_line_item_type AS line_type,
       ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM cur
WHERE line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2
ORDER BY has_resource, cost DESC;

SELECT service,
       ROUND(SUM(pricing_public_on_demand_cost),2) AS list_price,
       ROUND(SUM(line_item_unblended_cost),2)      AS paid,
       ROUND(SUM(line_item_net_unblended_cost),2)  AS net_paid,
       ROUND(100 * (1 - SUM(line_item_net_unblended_cost)
                        / NULLIF(SUM(pricing_public_on_demand_cost),0)),1) AS discount_pct
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1
HAVING SUM(pricing_public_on_demand_cost) > 0
ORDER BY list_price DESC;

-- unblended 减 net_unblended，就是协议类折扣的金额
SELECT service, line_item_usage_type,
       ROUND(SUM(line_item_unblended_cost),2)     AS unblended,
       ROUND(SUM(line_item_net_unblended_cost),2) AS net,
       ROUND(SUM(line_item_unblended_cost)
             - SUM(line_item_net_unblended_cost),2) AS discount_amount
FROM cur
WHERE line_item_line_item_type = 'Usage'
  AND line_item_usage_start_date >= DATE '2026-07-01'
GROUP BY 1,2
ORDER BY discount_amount DESC
LIMIT 25;