Pytanie Grupuj według tygodni, jak uzyskać puste tygodnie?


Mam następujący kod, który grupuje niektóre zdarzenia (RRRR-MM-DD HH: MM: SS) w tygodniach, które są wyświetlane jak na przykład "Y: 2010 - Tydzień: 50".

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(*) as number 

działa dobrze, ale chciałbym zwrócić wszystkie tygodnie, nawet te, w których nie ma miejsca na żadne wydarzenie.

Jeśli żadne wydarzenie nie zostanie zarejestrowane w trzecim tygodniu, otrzymuję:

week 1: 10
week 2: 1
week 4: 2

Chciałbym dostać:

week 1: 10
week 2: 1
week 3: 0
week 4: 2

11
2018-01-26 14:21


pochodzenie


Chcesz wszystko tygodnie od kiedy? Początek czasu? - thkala
Wszystkie tygodnie od pierwszego wydarzenia lub ostatnich N miesięcy będą również w porządku. Powinien to być "ciągły" okres czasu - Danilo


Odpowiedzi:


SQL nie może zwracać wierszy, które nie istnieją w niektórych tabelach. Aby uzyskać pożądany efekt, będziesz potrzebował tabeli "Tygodnie" ("WeekNo INT") z jednym rzędem na każdy możliwy tydzień w roku (który, IIRC, to 53 lub 54 możliwe tygodnie, w zależności od tego, jak się liczysz).

Następnie DOŁĄCZ do tej tabeli, aby uzyskać regularne wyniki w połączeniu OUTER JOIN, aby dodać dodatkowe tygodnie.

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(date) as number 
    FROM YourTable RIGHT OUTER JOIN Weeks ON WEEK(YourTable.date) = Weeks.WeekNo

[Aktualizuj]: zwróć uwagę użytkownikowi na COUNT (datę) zamiast COUNT (*). SQL nie będzie zawierał wartości NULL w kolumnie daty podczas dodawania COUNT. Ponieważ brakujące tygodnie nie będą zawierały żadnych dat, spowoduje to 0 wydarzeń w tych tygodniach.


8
2018-01-26 14:30



Dzięki za wskazówkę. Próbuję podążać tą drogą, ale prawe łączyć zwróci liczbę = 1 na określony tydzień, nawet jeśli nie było żadnego wydarzenia w tym tygodniu, prawda? - Danilo
Musisz zmienić COUNT (*) na COUNT (data). Ponieważ data "brakujące" będzie równa NULL, nie zostanie uwzględniona w liczeniu. Zaktualizuję moją odpowiedź. - Larry Lustig
wspaniale! Jeśli się nie mylę, twoja odpowiedź również wymaga "grupy od Weeks.WeekNo", aby działała poprawnie - Danilo
Tak, to jest poprawne. Po prostu wziąłem twój fragment SQL z pierwotnego pytania i dodałem JOIN, nadal będziesz musiał zastosować filtrowanie, grupowanie i porządek, którego używałeś wcześniej (ale oczywiście używając WeekNo zamiast regeek, oczywiście). - Larry Lustig


Na koniec postanowiłem rozwiązać problem w następujący sposób, tworząc tymczasowy stół na tygodnie i używając kodu, który został znaleziony w odpowiedzi tutaj powyżej.

CREATE TEMPORARY TABLE weeks (
         id INT
       );
INSERT INTO weeks (id) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54);
SELECT 
w.id, COUNT(c.issuedate) as numberPerWeek
FROM tableName c RIGHT OUTER JOIN weeks w ON WEEK(c.issuedate) = w.id group by w.id;

1
2018-01-26 16:10



Tworzysz w ten sposób tabelę numerów tymczasowych, aby uzyskać wynik :-) To dobra ogólna technika, warta przeczytania. - eftpotrm


Jest to rodzaj problemu, dla którego tworzone są tabele liczb / tabel - tylko prosta tabela, która liczy od 0 do dowolnego. Są ogólnie przydatne, zawsze zalecałbym dodawanie ich do bazy danych.

Dzięki temu możesz stosunkowo łatwo wygenerować listę tygodni w locie, bez potrzeby korzystania z określonej wstępnie obliczonej tabeli tygodni (nie musisz więc ani wielkiego stołu, ani martwić się o to, że skończy się tygodniami), a potem dołóż do listy na liście spotkań, aby pokazać wszystkie tygodnie z umówionym spotkaniem.

Szybki start:

declare @min    datetime
set     @min    ='2010-01-01'

select
    dateadd(wk, number, @min) as weekDate
from
    numbers
where
    number between 0 and datediff(wk,@min,getdate())

z


0
2018-01-26 14:51