未验证,
```
WITH cost_data AS (
SELECT
line_item_usage_account_id AS account_id,
product_product_name AS service_name,
product_region AS region,
line_item_resource_id AS resource_id,
-- 处理不同类型的成本
CASE
WHEN line_item_line_item_type IN ('SavingsPlanCoveredUsage', 'SavingsPlanNegation') THEN
savings_plan_effective_cost
WHEN line_item_line_item_type IN ('DiscountedUsage') THEN
reservation_effective_cost
WHEN line_item_line_item_type IN ('Usage') THEN
line_item_unblended_cost
WHEN line_item_line_item_type IN ('RIFee') THEN
reservation_amortized_upfront_fee_for_billing_period + reservation_recurring_fee_for_usage
WHEN line_item_line_item_type IN ('SavingsPlanRecurringFee') THEN
savings_plan_recurring_commitment_for_billing_period
WHEN line_item_line_item_type IN ('Credit', 'Refund') THEN
line_item_unblended_cost -- Credits通常为负数
ELSE
0
END AS effective_cost
FROM
your_cur2_table_name
WHERE
line_item_usage_start_date BETWEEN TIMESTAMP '2025-04-01' AND TIMESTAMP '2025-04-30'
)
SELECT
account_id,
service_name,
region,
resource_id,
SUM(effective_cost) AS total_effective_cost
FROM
cost_data
GROUP BY
account_id, service_name, region, resource_id
ORDER BY
total_effective_cost DESC;
```