SELECT
    line_item_usage_start_date, -- 開始日時(UTC)
	line_item_usage_end_date, -- 終了日時(UTC)
	line_item_usage_account_id, -- AWSアカウントID
	line_item_line_item_type, -- 請求の種類
	line_item_product_code, -- 製品のコード
	line_item_resource_id, -- リソースID
	line_item_operation, -- 明細項目の対象となる特定の AWS オペレーション
	line_item_usage_type, -- 明細項目の使用状況の詳細
	line_item_usage_amount, -- 指定した期間に発生した使用量
	line_item_unblended_rate, -- サービスの料金レート
	line_item_unblended_cost, -- 利用料金
	pricing_unit, -- 料金単位
	line_item_line_item_description -- 明細項目タイプの説明
FROM "athenacurcfn_cost_report"."cost_report"
where year = '2025'
	AND month = '2'
	AND line_item_usage_type = 'APN1-Requests-Tier3'
	order by line_item_usage_amount desc
	limit 10

{
    "accountId": "123456789012",
    "anomalyDetailsLink": "https://console.aws.amazon.com/cost-management/home#/anomaly-detection/monitors/abcdef12-1234-4ea0-84cc-918a97d736ef/anomalies/12345678-abcd-ef12-3456-987654321a12",
    "anomalyEndDate": "2021-05-25T00:00:00Z",
    "anomalyId": "12345678-abcd-ef12-3456-987654321a12",
    "anomalyScore": {
        "currentScore": 0.47,
        "maxScore": 0.47
    },
    "anomalyStartDate": "2021-05-25T00:00:00Z",
    "dimensionalValue": "ServiceName",
    "impact": {
        "maxImpact": 151,
        "totalActualSpend": 1301,
        "totalExpectedSpend": 300,
        "totalImpact": 1001,
        "totalImpactPercentage": 333.67
    },
    "monitorArn": "arn:aws:ce::123456789012:anomalymonitor/abcdef12-1234-4ea0-84cc-918a97d736ef",
    "rootCauses": [
        {
            "linkedAccount": "AnomalousLinkedAccount",
            "linkedAccountName": "AnomalousLinkedAccountName",    
            "region": "AnomalousRegionName",
            "service": "AnomalousServiceName",
            "usageType": "AnomalousUsageType",
            "impact": {
                "contribution": 601,
            }
        }                
    ],
    "subscriptionId": "874c100c-59a6-4abb-a10a-4682cc3f2d69",
    "subscriptionName": "alertSubscription"
}

SELECT 
    DATE_TRUNC('month', line_item_usage_start_date) AS month,
    line_item_resource_id AS bucket,
    CASE
        -- Standard Storage
        WHEN line_item_usage_type LIKE '%-TimedStorage-ByteHrs' 
            AND line_item_usage_type NOT LIKE '%-INT-%'
            AND line_item_usage_type NOT LIKE '%-GDA-%'
            AND line_item_usage_type NOT LIKE '%-Glacier%'
            AND line_item_usage_type NOT LIKE '%-SIA-%'
        THEN 'Standard'
        
        -- Intelligent-Tiering
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-FA-%' THEN 'Intelligent-Tiering Frequent Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-IA-%' THEN 'Intelligent-Tiering Infrequent Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-AA-%' THEN 'Intelligent-Tiering Archive Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-DAA-%' THEN 'Intelligent-Tiering Deep Archive Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-AIA-%' THEN 'Intelligent-Tiering Archive Instant Access'
        
        -- Standard-IA
        WHEN line_item_usage_type LIKE '%-TimedStorage-SIA-%' THEN 'Standard-IA'
        
        -- Glacier
        WHEN line_item_usage_type LIKE '%-TimedStorage-GlacierByteHrs' THEN 'Glacier'
        
        -- Glacier Deep Archive
        WHEN line_item_usage_type LIKE '%-TimedStorage-GDA-%' THEN 'Glacier Deep Archive'
        
        -- その他のストレージ関連
        WHEN line_item_usage_type LIKE '%-TagStorage-%' THEN 'Tag Storage'
        WHEN line_item_usage_type LIKE '%-StorageLensFreeTier-%' THEN 'Storage Lens Free Tier'
        
        ELSE 'Other'
    END AS storage_class,
    SUM(line_item_usage_amount) AS storage_size,
    SUM(line_item_unblended_cost) AS cost
FROM "athenacurcfn_cost_report"."cost_report"
WHERE 
    line_item_product_code = 'AmazonS3'
    AND line_item_usage_type LIKE '%Storage%'
    AND (
        (year = '2024' AND month >= '9') OR
        (year = '2025' AND month <= '3')
    )
GROUP BY 
    DATE_TRUNC('month', line_item_usage_start_date),
    line_item_resource_id,
    CASE
        WHEN line_item_usage_type LIKE '%-TimedStorage-ByteHrs' 
            AND line_item_usage_type NOT LIKE '%-INT-%'
            AND line_item_usage_type NOT LIKE '%-GDA-%'
            AND line_item_usage_type NOT LIKE '%-Glacier%'
            AND line_item_usage_type NOT LIKE '%-SIA-%'
        THEN 'Standard'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-FA-%' THEN 'Intelligent-Tiering Frequent Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-IA-%' THEN 'Intelligent-Tiering Infrequent Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-AA-%' THEN 'Intelligent-Tiering Archive Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-DAA-%' THEN 'Intelligent-Tiering Deep Archive Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-INT-AIA-%' THEN 'Intelligent-Tiering Archive Instant Access'
        WHEN line_item_usage_type LIKE '%-TimedStorage-SIA-%' THEN 'Standard-IA'
        WHEN line_item_usage_type LIKE '%-TimedStorage-GlacierByteHrs' THEN 'Glacier'
        WHEN line_item_usage_type LIKE '%-TimedStorage-GDA-%' THEN 'Glacier Deep Archive'
        WHEN line_item_usage_type LIKE '%-TagStorage-%' THEN 'Tag Storage'
        WHEN line_item_usage_type LIKE '%-StorageLensFreeTier-%' THEN 'Storage Lens Free Tier'
        ELSE 'Other'
    END
ORDER BY 
    month ASC,
    bucket ASC;