Группировать по номеру недели, включая пустые недели и суммировать другой столбец

Я пытаюсь выбрать из таблицы в SQL Server, чтобы он группировал мой столбец FinalDate по номерам недель и суммировал столбец ThisApp для этой недели в следующей ячейке. Я посмотрел в Интернете, и я не могу найти то, что мне нужно.

Мне было интересно, могу ли я это сделать в T-SQL?

В настоящее время это строки в моей таблице:

    FinalDate   ThisApp
    ------------------------
    15/04/2016  20459.92
    29/05/2016  7521.89
    30/05/2016  5963.61
    31/05/2016  3293.72
    03/06/2016  27413.20
    04/06/2016  8392.16
    05/06/2016  7789.46
    05/06/2016  11414.73
    10/06/2016  48893.46
    11/06/2016  14685.47
    11/06/2016  7030.03

Я хотел бы заменить столбец FinalDate номерами недель и суммировать This App для каждого номера недели.

Также::

Мне нужно, чтобы он отображал непрерывные номера недель, поэтому я бы не хотел, чтобы он пропускал какие-либо недели, например:

    FinalDate   ThisApp
    ------------------------
    01/01/2016  10.00 -- (Would be week 1)
    02/01/2016  10.00 -- (Would be week 1)
    15/01/2016  10.00 -- (Would be week 3)

Будет отображаться как:

    FinalDate   ThisApp
    ------------------------
     1          20.00
     2          0.00 --This would show as 0.00 because there was no week 2.
     3          10.00

Я понимаю, что это очень специфический запрос, поэтому мне было интересно, могу ли я сделать это в SQL.


sql sql-server group-by week-number
person djmcr    schedule 06.05.2016    source источник
comment
Хорошим советом было бы использовать MM/DD/YYYY при размещении дат здесь, чтобы избежать путаницы. Большинство людей примут этот формат, и когда вы используете ДД/ММ/ГГГГ, это может сбить с толку, если вы не включите то, что в противном случае было бы невозможно (например, «15/01/2016»).   -  person Stan Shaw    schedule 06.05.2016

Ответы (3)


arrow_upward
1
arrow_downward 

 DECLARE @tmp table(dat datetime,
                    val float)

 insert into @tmp 
 values  ('15/04/2016' , 20459.92),
         ('29/05/2016',  7521.89),
         ('30/05/2016',  5963.61),
         ('31/05/2016',  3293.72),
         ('03/06/2016',  27413.20),
         ('04/06/2016',  8392.16),
         ('05/06/2016',  7789.46),
         ('05/06/2016',  11414.73),
         ('10/06/2016',  48893.46),
         ('11/06/2016',  14685.47),
         ('11/06/2016',  7030.03)

 SELECT  Weeknumb,
         ISNULL(sumvals,0) as weekval
 FROM 
  (SELECT DATEPART(ISOWK,DATEADD(wk,t2.number,'2016')) as Weeknumb
   FROM master..spt_values t2
   WHERE t2.type = 'P'
   AND t2.number <= 255
   AND YEAR(DATEADD(wk,t2.number,'2016'))=2016)allWeeks
   LEFT JOIN
  (SELECT  sum(val) as  sumvals,
           datepart(ISOWK,dat) as  weeks
   FROM @tmp
   GROUP BY datepart(ISOWK,dat) ) actualData
   ON weeks = Weeknumb
  ORDER BY Weeknumb asc

ну вот. Все недели 2016 года с суммированием ваших значений

person sapi    schedule 06.05.2016
comment
Этот запрос будет работать только для определенного года (в данном случае 2016). - person Stan Shaw; 06.05.2016

arrow_upward
1
arrow_downward 

SELECT   DATEPART(WEEK, FinalDate) FinalDate
,        SUM(ThisApp) ThisApp
FROM     Your_Table
GROUP BY DATEPART(WEEK, FinalDate)

Чтобы получить 0 для недель, не существующих в наборе данных, вы должны создать таблицу с номерами недель (1-52) и right join к ней. В этом случае вы получите что-то вроде:

SELECT        wk.Number
,             ISNULL(SUM(ThisApp), 0)
FROM          Your_Table T
RIGHT JOIN    WeekNumbers wk
           ON wk.Number = DATEPART(WEEK, T.FinalDate)
GROUP BY      wk.Number
person HoneyBadger    schedule 06.05.2016
comment
интересно, попробую, оценил. - person djmcr; 06.05.2016
comment
Возможно, мне придется попробовать это в понедельник, так как через пару минут мне нужно уехать на выходные. Если это сработает для меня, я приму ваш ответ в понедельник. - person djmcr; 06.05.2016
comment
Удачи и хороших выходных - person HoneyBadger; 06.05.2016
comment
@HoneyBadger Это хороший ответ, но я думаю, что с ним есть несколько проблем. Пожалуйста, ознакомьтесь с моим ответом и ответьте своими мыслями. - person Stan Shaw; 06.05.2016

arrow_upward
0
arrow_downward

Ответ HoneyBadger хорош, но он будет группировать значения недели 1 каждого года вместе (поэтому 1 января 1975 года будет сгруппировано с 1 января 2016 года). Чтобы избежать этого, вам нужно будет добавить год к группировке.

Кроме того, как он указал, вам понадобится отдельная таблица, если вы хотите, чтобы недели NULL также возвращались. Тем не менее, вы должны сделать календарную таблицу (погуглите это - они просты и широко используются), и вы НЕ должны просто создавать таблицу с неделями 1-52 в ней.

Наконец, чтобы гарантировать, что вы не получите записи с 0, возвращаемыми для всех годов/недель, которые предшествуют вашим данным или следуют за ними, вам нужно добавить параметры даты начала и окончания. Вы можете либо принять их, либо установить для них первую и последнюю FinalDate в Your_Table.

Вот что вы должны использовать:

DECLARE @StartDate date = (SELECT MIN(FinalDate) FROM Your_Table),
        @EndDate date = (SELECT MAX(FinalDate) FROM Your_Table)

SELECT   YEAR(C.BaseDate) AS [Year], 
         DATEPART(WEEK, C.BaseDate) AS [Week],
         ISNULL(SUM(YT.ThisApp), 0) AS [This App Total]
FROM     Calendar C
LEFT OUTER JOIN Your_Table YT
    ON C.BaseDate = CAST(YT.FinalDate AS DATE)
WHERE C.BaseDate BETWEEN @StartDate AND @EndDate
GROUP BY YEAR(C.BaseDate), 
         DATEPART(WEEK, C.BaseDate)
person Stan Shaw    schedule 06.05.2016