英文:
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 Dec
from schema.app_table
--where year(recvd_date) = 2023
group by year(recvd_date)
它会给出以下期望的格式：
year(recvd_date) Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2023 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 Dec
from schema.app_table
--where year(recvd_date) = 2023
group 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 Jul
2023 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) = 2023
group 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) = 2023
group by year(recvd_date)
Tested on mysql here : https://dbfiddle.uk/WbY_jC_W
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论