Ежемесячное соединение SQL и ежемесячный общий процент

Моя голова дымится от (глупых) попыток использовать JOIN, WITH и GROUP BY, чтобы придумать решение для моего довольно распространенного сценария - я просто не могу понять это. Позвольте мне сразу бросить вам пример:

У меня есть две таблицы (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

Теперь все, что я хочу, это соединить таблицу 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%

(Обратите внимание, что общее количество месяцев 09 равно 65, поэтому 66% соответствует Purple, а также 0 – несуществующим цветам):

Я надеюсь, что кто-то мечтает в SQL, и это простая задача...


sql sql-server-2008 subtotal
person Dennis G    schedule 16.11.2010    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Это работает со следующими оговорками:

  • Значения даты и времени должны быть только датой
  • В нем перечислены только те месяцы, за которые есть какие-либо данные
  • Я перечисляю по первому дню месяца, если у вас есть данные за несколько лет (я предполагаю, что вы не хотите объединять данные за январь 2009 г. с данными за январь 2010 г.)
  • Точные детали форматирования столбца в процентах я оставляю на ваше усмотрение, мне пора возвращаться к работе.

Код:

;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
person Philip Kelley    schedule 16.11.2010
comment
Хорошее обсуждение методов работы с окнами на SimpleTalk по адресу simple-talk.com/sql/learn-sql-server/ - person Philip Kelley; 16.11.2010

arrow_upward
2
arrow_downward 

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

Что-то в этом роде.... Он не будет отображать начальный нуль месяца, но действительно ли это имеет значение?

person AlexanderMP    schedule 16.11.2010
comment
Хорошая попытка, но проценты не работают. - person Lieven Keersmaekers; 16.11.2010
comment
лучше, но вы должны преобразовать в число с плавающей запятой, чтобы избежать ошибок округления. - person Lieven Keersmaekers; 16.11.2010
comment
он также может генерировать исключение деления на ноль, но чтобы избежать этого, требуется просто МНОГО ввода. я постараюсь разобраться с этим. А как же проценты? Почему выключен? - person AlexanderMP; 16.11.2010
comment
ведущий 0 не имеет значения, но ваш SQL не совсем дает правильный результат. вывод не содержит пустых цветов, а только общий пустой красный цвет - см. сгенерированный вывод здесь: imgur.com/ tv0fX.jpg - person Dennis G; 16.11.2010
comment
Попробуй это сейчас. Хотя, если это не сработает, мне придется создать таблицы на моем сервере. - person AlexanderMP; 16.11.2010
comment
Извини, Александр The multi-part identifier "CC.Date" could not be bound. - person Dennis G; 16.11.2010