如何在 Athena 数据库中执行 SQL 查询，该数据库返回按月分组的数据计数?

Question

我正在努力使用此查询来接收按月而不是每天分组的数据。现在，它每天为每个产品代码提供结果，这使我的Excel文件太大而无法通过电子邮件发送。

SELECT line_item_usage_account_id,  
line_item_product_code, 
SUM(line_item_unblended_cost) AS cost, 
SUM(CASE   
WHEN line_item_product_code = 'AmazonEC2' THEN  
line_item_unblended_cost * 0.89 
ELSE line_item_unblended_cost * 0.85 END) AS discounted_cost,   
CAST(line_item_usage_start_date AS DATE) AS start_day, CAST(line_item_usage_end_date AS DATE) AS end_day   
FROM cost_management_2ef5a060_prod  
WHERE line_item_usage_account_id IN ('123456789122')    
AND line_item_usage_start_date  
BETWEEN DATE '2019-06-01'   
AND DATE '2020-01-01'   
GROUP BY  line_item_usage_account_id, CAST(line_item_usage_start_date AS DATE), CAST(line_item_usage_end_date AS DATE),line_item_product_code   
HAVING sum(line_item_blended_cost) > 0  
ORDER BY  line_item_usage_account_id    

数据结果：

按天划分的当前数据结果

按月划分的数据结果

我的数据可以看起来像这样，还是没有那么多行？

谢谢！

Answer 1

下面的查询应该对您有所帮助，在您继续之前，查询将写入当前查询输出，并且start_day和end_day是存在记录的实际日期。还考虑月份start_day，如果该记录的end_day在下个月，那么我们根本无法计算。

select line_item_usage_account_id, date_format(date_parse(start_day ,'%c/%e/%Y' ), '%Y/%c') as Mo_Year,
sum(discounted_cost) as discounted_cost 
,min(date_parse(start_day ,'%c/%e/%Y' )) as start_day,
Max(date_parse(end_day ,'%c/%e/%Y' )) as end_day
from <table_name>  
group by date_format(date_parse(start_day ,'%c/%e/%Y' ), '%Y/%c'), line_item_usage_account_id