英文:
SQL Pivot - Months as Column headers
问题
我有一个SQL查询，如下所示：
select year(recvd_date),sum(case when month(recvd_date)=1 then count(distinct(app_id)) else 0 end) as Jan,sum(case when month(recvd_date)=2 then count(distinct(app_id)) else 0 end) as Feb,sum(case when month(recvd_date)=3 then count(distinct(app_id)) else 0 end) as Mar,sum(case when month(recvd_date)=4 then count(distinct(app_id)) else 0 end) as Apr,sum(case when month(recvd_date)=5 then count(distinct(app_id)) else 0 end) as May,sum(case when month(recvd_date)=6 then count(distinct(app_id)) else 0 end) as Jun,sum(case when month(recvd_date)=7 then count(distinct(app_id)) else 0 end) as Jul,sum(case when month(recvd_date)=8 then count(distinct(app_id)) else 0 end) as Aug,sum(case when month(recvd_date)=9 then count(distinct(app_id)) else 0 end) as Sep,sum(case when month(recvd_date)=10 then count(distinct(app_id)) else 0 end) as Oct,sum(case when month(recvd_date)=11 then count(distinct(app_id)) else 0 end) as Nov,sum(case when month(recvd_date)=12 then count(distinct(app_id)) else 0 end) as Decfrom schema.app_table--where year(recvd_date) = 2023group by year(recvd_date)
它会给出以下期望的格式：
year(recvd_date) Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec2023 66 45 21 22 10 9 8 0 0 0 0 0
请注意，我已将每个月份的计数相加，以便它们出现在同一行，并且对于其他月份，我将它们填充为零。
英文:
I have a SQL written as below:
select year(recvd_date),(case when month(recvd_date)=1 then count(distinct(app_id)) else 0 end) as Jan,(case when month(recvd_date)=2 then count(distinct(app_id)) else 0 end) as Feb,(case when month(recvd_date)=3 then count(distinct(app_id)) else 0 end) as Mar,(case when month(recvd_date)=4 then count(distinct(app_id)) else 0 end) as Apr,(case when month(recvd_date)=5 then count(distinct(app_id)) else 0 end) as May,(case when month(recvd_date)=6 then count(distinct(app_id)) else 0 end) as Jun,(case when month(recvd_date)=7 then count(distinct(app_id)) else 0 end) as Jul,(case when month(recvd_date)=8 then count(distinct(app_id)) else 0 end) as Aug,(case when month(recvd_date)=9 then count(distinct(app_id)) else 0 end) as Sep,(case when month(recvd_date)=10 then count(distinct(app_id)) else 0 end) as Oct,(case when month(recvd_date)=11 then count(distinct(app_id)) else 0 end) as Nov,(case when month(recvd_date)=12 then count(distinct(app_id)) else 0 end) as Decfrom schema.app_table--where year(recvd_date) = 2023group by year(recvd_date), month(recvd_date)
It is giving me the right numbers but the format is not exactly what I expect. The numbers appear on different rows and zero filled for other months. How can I achieve the below format.
Expected Format:
year(recvd_date) Jan Feb Mar Apr May Jun Jul2023 66 45 21 22 10 9 8
答案1
得分: 1
这可以通过使用条件聚合来完成：
select year(recvd_date),count(distinct case when month(recvd_date) = 1 then app_id end) as 一月,count(distinct case when month(recvd_date) = 2 then app_id end) as 二月,....from schema.app_table-- where year(recvd_date) = 2023group by year(recvd_date)
在此处测试过MySQL：https://dbfiddle.uk/WbY_jC_W
英文:
This can be done using the conditional aggregation :
select year(recvd_date),count(distinct case when month(recvd_date) = 1 then app_id end) as Jan,count(distinct case when month(recvd_date) = 2 then app_id end) as Feb,....from schema.app_table-- where year(recvd_date) = 2023group by year(recvd_date)
Tested on mysql here : https://dbfiddle.uk/WbY_jC_W
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论