Próbuję uzyskać pełny zestaw zasobników dla danego zbioru danych, nawet jeśli nie istnieją żadne rekordy dla niektórych zasobników .

Na przykład chcę wyświetlić sumy według dnia tygodnia, z zerową sumą dla dni bez rekordów.

SELECT
WEEKDAY(transaction_date) AS day_of_week,
SUM(sales) AS total_sales
FROM table1
GROUP BY day_of_week

Jeśli codziennie prowadzę sprzedaż, w wyniku otrzymam 7 wierszy, które reprezentują całkowitą sprzedaż w dniach 0–6.

Jeśli nie mam sprzedaży w dniu 2, nie otrzymuję wyniku w dniu 2.

Jaki jest najskuteczniejszy sposób na wymuszenie wartości zerowej w drugim dniu?

Czy powinienem dołączyć do tymczasowej tabeli lub tablicy zdefiniowanych segmentów? [„0”, „1”, „2”, „3”, „4”, „5”, „6”]

A może lepiej jest wstawiać zera poza MySQL po wykonaniu zapytania?

Używam MySQL, ale to jest ogólne pytanie dotyczące SQL.

1
Womprat 31 marzec 2020, 19:38

3 odpowiedzi

Najlepsza odpowiedź

W MySQL możesz po prostu użyć pochodnej tabeli liczb od 1 do 7, left join ją z tabelą, a następnie zagregować:

select d.day_of_week, sum(sales) AS total_sales
from (
    select 1 day_of_week union all select 2 union all select 3 union all select 4
    union all select 5 union all select 6 union all select 7
) d
left join table1 t1 on weekday(t1.transaction_date) = d.day_of_week
group by day_of_week

Najnowsze wersje mają składnię values(row...), która skraca zapytanie:

select d.day_of_week, sum(sales) AS total_sales
from (values row(1), row(2), row(3), row(4), row(5), row(6), row(7)) d(day_of_week)
left join table1 t1 on weekday(t1.transaction_date) = d.day_of_week
group by day_of_week
1
GMB 31 marzec 2020, 17:09

Po pierwsze potrzebujesz tabeli kalendarza; coś takiego jak to lub to. Lub utwórz podzestaw kalendarza w locie. Nie jestem pewien składni MySQL, ale oto jak by to wyglądało w SQL Server.

DECLARE
    @FromDate DATE
  , @ToDate   DATE

-- set these variables to appropriate values
SET @FromDate = '2020-03-01';
SET @ToDate = '2020-03-31';

;WITH cteCalendar (MyDate) AS
(
    SELECT CONVERT(DATE, @FromDate) AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate)
    FROM   cteCalendar
    WHERE  DATEADD(DAY, 1, MyDate) <= @ToDate
)
SELECT WEEKDAY(cte.MyDate) AS day_of_week,
SUM(sales) AS total_sales
FROM cteCalendar cte
LEFT JOIN table1 t1 ON cte.MyDate = t1.transaction_date
GROUP BY day_of_week
1
Isaac 31 marzec 2020, 16:54

Zasadniczo chcesz, aby odpowiedź wynosiła 0, gdy dane dla tego segmentu są w rzeczywistości zerowe, dlatego chcesz, aby wartość maksymalna (null, 0). Funkcja max nie działałaby natywnie z NULL w ten sposób, jednak możesz użyć COALESCE, aby ją wymusić:

COALESCE(MAX(SUM(sales)),0)

Zgodnie z sugestią tej odpowiedzi

1
mdmjsh 31 marzec 2020, 16:47