# 查询 3567 账户下的数据
```sql
select round(sum(abd_3567.line_item_unblended_cost),0) as amount
from
        `gcp-digital-bi`.aws_prod_billing.`aws_prod_billing_data` abd_3567
where
        abd_3567.`year` = 2025
        and abd_3567.`month` = 7
        and
        (abd_3567.line_item_usage_type ="APN1-NatGateway-Bytes"        or 
        abd_3567.line_item_usage_type ="APN1-NatGateway-Hours")
        and abd_3567.resource_tags_user_usedby ="dbaas-control-plane-seed"
limit 100;
```

# 按天查询各服务类型费用，7月
```sql
SELECT
  DATE(TIMESTAMP(abd.line_item_usage_start_date)) AS usage_date,
  COALESCE(abd.product_product_name, abd.line_item_product_code) AS service_name,
  ROUND(SUM(abd.line_item_unblended_cost), 2) AS amount_usd
FROM `gcp-digital-bi`.aws_prod_billing.`aws_prod_billing_data` AS abd
WHERE
  abd.`year` = 2025
  AND abd.`month` = 7
  AND abd.line_item_usage_account_id = '380838443567'  -- 换成你的账户ID；或用 bill_payer_account_id
  -- AND abd.resource_tags_user_usedby = 'dbaas-control-plane-seed'  -- 若需按标签收敛
GROUP BY usage_date, service_name
ORDER BY usage_date, amount_usd DESC;
```

# 按天查询各服务类型费用，最近七天
```sql
SELECT
  DATE(TIMESTAMP(line_item_usage_start_date)) AS usage_date,
  COALESCE(product_product_name, line_item_product_code) AS service_name,
  ROUND(SUM(line_item_unblended_cost), 2) AS amount_usd
FROM `gcp-digital-bi`.aws_prod_billing.`aws_prod_billing_data`
WHERE
  DATE(TIMESTAMP(line_item_usage_start_date)) BETWEEN
  DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY) AND CURRENT_DATE()
  AND line_item_usage_account_id = '380838443567'  -- 如需限定账户，解除注释并替换
  AND COALESCE(line_item_product_code, '') != 'ComputeSavingsPlans'
  AND COALESCE(line_item_line_item_type, '') NOT IN (
    'SavingsPlanCoveredUsage','SavingsPlanRecurringFee','SavingsPlanUpfrontFee','SavingsPlanNegation'
  )
  -- AND resource_tags_user_usedby = 'dbaas-control-plane-seed'  -- 如需限定标签
GROUP BY usage_date, service_name
ORDER BY usage_date DESC, amount_usd DESC;
```

# d

# 按天查询最近一周，EC2 在 SP 覆盖和非覆盖下的真实花费
```sql
SELECT
  DATE(TIMESTAMP(line_item_usage_start_date)) AS usage_date,
  ROUND(SUM(CASE
    WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN COALESCE(savings_plan_savings_plan_effective_cost, line_item_unblended_cost)
    ELSE 0
  END), 2) AS ec2_sp_covered_cost_usd,
  ROUND(SUM(CASE
    WHEN line_item_line_item_type = 'Usage' THEN line_item_unblended_cost
    ELSE 0
  END), 2) AS ec2_on_demand_cost_usd,
  ROUND(SUM(CASE
    WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN COALESCE(savings_plan_savings_plan_effective_cost, line_item_unblended_cost)
    WHEN line_item_line_item_type = 'Usage' THEN line_item_unblended_cost
    ELSE 0
  END), 2) AS ec2_total_effective_cost_usd
FROM `gcp-digital-bi`.aws_prod_billing.`aws_prod_billing_data`
WHERE
  DATE(TIMESTAMP(line_item_usage_start_date)) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY) AND CURRENT_DATE()
  AND line_item_product_code = 'AmazonEC2'
  -- 如需限定账户，解除注释
  -- AND line_item_usage_account_id = '380838443567'
  AND COALESCE(line_item_line_item_type, '') NOT IN (
    'SavingsPlanRecurringFee','SavingsPlanUpfrontFee','SavingsPlanNegation','Credit','Refund','Tax','RIFee'
  )
GROUP BY usage_date
ORDER BY usage_date DESC;
```

-- 说明：
-- 1) SP 覆盖行使用 savings_plan_effective_cost 作为“真实成本”；若字段缺失则回退到 unblended。
-- 2) 非覆盖（按需）行使用 line_item_unblended_cost。
-- 3) 以上仅统计 EC2（AmazonEC2）用量行，并排除了 SP 月费/预付费等非用量行，避免重复计费。