SQL月度加入和月度总数百分比

My head is smoking from (stupid) tries of using JOIN, WITH and GROUP BY to come up with a solution for my pretty common scenario - I just can't wrap my head around it. Let me throw the example at you right away:

我的脑子在冒烟，因为(愚蠢的)尝试使用JOIN、WITH和GROUP BY来为我非常常见的场景想出一个解决方案——我就是想不出来。我来举个例子:

I have two tables (ColorCount and Colorname):

我有两个表(ColorCount和Colorname):

ColorCount:
ColorID Count Date
1       42    2010-09-07
1       1     2010-09-08
2       22    2010-09-14
1       20    2010-10-10
3       4     2010-10-14

ColorName:
ColorID  Name
1        Purple
2        Green
3        Yellow
4        Red

Now all I want is to join the ColorName table to the ColorCount table, sum up all the counts of colors per month and calculate the percentage of each count from the the monthly total. Tables are better than words:

现在我要做的就是将ColorName表连接到ColorCount表中，将每个月的颜色计数汇总起来，然后从每月的总数中计算每个计数的百分比。表胜于言:

Output:
Month Color   Count Percentage
09    Purple  43    66%
09    Green   22    33%
09    Yellow  0     0%
09    Red     0     0%
10    Purple  20    83%
10    Green   0     0%
10    Yellow  4     16%
10    Red     0     0%

(Please note the total Count of Month 09 is 65, hence the 66% for Purple and also the 0's for non-existing colors):

(请注意09月的总数是65，因此紫色为66%，其他颜色为0):

I hope somebody dreams in SQL and this is an easy task...

我希望有人梦想用SQL，这是一个简单的任务……

2 个解决方案

#1

This works, with the following caveats:

这是有效的，但有以下几点需要注意:

The datetime values must be date only
datetime值必须为日期
It only lists those months for which there is any data
它只列出了有任何数据的月份。
I list by first day of the month, in case you have data that crosses years (I'm assuming you don't want to aggregate data from Jan 2009 with data from Jan 2010)
我按月的第一天列出这些数据，以防您有跨年的数据(我假设您不希望使用2010年1月的数据汇总2009年1月的数据)
The precise Percentage column formatting details I leave up to you, I gotta get back to work
具体的百分比列格式细节我留给你，我得回去工作了

Code:

代码:

;with cte (ColorId, Mth, TotalCount)
 as (select
        ColorId
       ,dateadd(dd, -datepart(dd, Date) + 1, Date) Mth
       ,sum(Count) TotalCount
      from ColorCount
      group by ColorId, dateadd(dd, -datepart(dd, Date) + 1, Date))
 select
    AllMonths.Mth [Month]
   ,cn.Name
   ,isnull(AggData.TotalCount, 0) [Count]
   ,isnull(100 * AggData.TotalCount / sum(AggData.TotalCount * 1.00) over (partition by AllMonths.Mth), 0) Percentage
  from (select distinct Mth from cte) AllMonths
   cross join ColorName cn
   left outer join cte AggData
    on AggData.ColorId = cn.ColorId
     and AggData.Mth = AllMonths.Mth
  order by AllMonths.Mth, cn.ColorId

#2

SELECT
    [Month],
    [Name],
    [Count],
    CASE WHEN TotalMonth=0 THEN 'INF' ELSE cast(round([Count],0)*100.0/TotalMonth,0) as int) + '%' END as [Percentage]
FROM 
(
SELECT 
    [Months].[Month] as [Month],
    CN.[Name],
    isnull(CC.[Count],0) as [Count],
    (SELECT SUM([Count]) FROM ColorCount WHERE 
            datepart(month,[Date])=datepart(month,CC.[Date])
     ) as [TotalMonth]
FROM (SELECT DISTINCT datepart(month,[Date]) as [Month] FROM ColorCount) [Months]
LEFT JOIN ColorName CN ON [Months].[Month]=datepart(month,CC.[Date])
LEFT JOIN ColorCount CC ON CN.ColorID=CC.ColorID
) AS tbl1
ORDER BY
    [Month] ASC,
    [Name] ASC

Something like that.... It won't display the leading zero of the month, but does it really matter?

这样....它不会显示本月的领先零点，但它真的重要吗?

#1