To długa odpowiedź, więc postanowiłem dodać tutaj streszczenie.
- Na początku przedstawiam rozwiązanie, które daje dokładnie taki sam wynik w tej samej kolejności, co w pytaniu. Skanuje główną tabelę 3 razy: aby uzyskać listę
ProductIDsz zakresem dat dla każdego produktu, podsumować koszty dla każdego dnia (ponieważ istnieje kilka transakcji z tymi samymi datami), aby połączyć wynik z oryginalnymi wierszami.
- Następnie porównuję dwa podejścia, które upraszczają zadanie i unikają ostatniego skanowania głównego stołu. Ich wynikiem jest codzienne podsumowanie, tzn. Jeśli kilka transakcji na produkcie ma tę samą datę, są one łączone w jeden wiersz. Moje podejście z poprzedniego kroku dwukrotnie skanuje stół. Podejście Geoffa Pattersona skanuje tabelę raz, ponieważ wykorzystuje zewnętrzną wiedzę na temat zakresu dat i listy Produktów.
- W końcu przedstawiam rozwiązanie jednoprzebiegowe, które ponownie zwraca codzienne podsumowanie, ale nie wymaga zewnętrznej wiedzy na temat zakresu dat lub listy
ProductIDs.
Będę korzystać z bazy danych AdventureWorks2014 i SQL Server Express 2014.
Zmiany w oryginalnej bazie danych:
- Zmieniono typ
[Production].[TransactionHistory].[TransactionDate]z datetimena date. Komponent czasu i tak był zerowy.
- Dodano tabelę kalendarza
[dbo].[Calendar]
- Dodano indeks do
[Production].[TransactionHistory]
.
CREATE TABLE [dbo].[Calendar]
(
[dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[dt] ASC
))
CREATE UNIQUE NONCLUSTERED INDEX [i] ON [Production].[TransactionHistory]
(
[ProductID] ASC,
[TransactionDate] ASC,
[ReferenceOrderID] ASC
)
INCLUDE ([ActualCost])
-- Init calendar table
INSERT INTO dbo.Calendar (dt)
SELECT TOP (50000)
DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '2000-01-01') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);
Artykuł MSDN o OVERklauzuli zawiera link do doskonałego posta na blogu o funkcjach okien autorstwa Itzika Ben-Gana. W tym poście wyjaśnia, jak OVERdziała, różnica między ROWSi RANGEopcji i wspomina ten właśnie problem obliczania toczenia sumę ponad zakresu dat. Wspomina, że bieżąca wersja SQL Server nie implementuje RANGEw pełni i nie implementuje typów danych przedziałów czasowych. Jego wyjaśnienie różnicy między ROWSi RANGEdało mi pomysł.
Daty bez przerw i duplikatów
Jeśli TransactionHistorytabela zawiera daty bez przerw i duplikatów, to następujące zapytanie przyniosłoby prawidłowe wyniki:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 = SUM(TH.ActualCost) OVER (
PARTITION BY TH.ProductID
ORDER BY TH.TransactionDate
ROWS BETWEEN
45 PRECEDING
AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
Rzeczywiście, okno 45 rzędów zajmowałoby dokładnie 45 dni.
Daty z przerwami bez duplikatów
Niestety nasze dane zawierają luki w terminach. Aby rozwiązać ten problem, możemy użyć Calendartabeli do wygenerowania zestawu dat bez przerw, a następnie LEFT JOINoryginalnych danych do tego zestawu i użyć tego samego zapytania ROWS BETWEEN 45 PRECEDING AND CURRENT ROW. Dałoby to prawidłowe wyniki tylko wtedy, gdy daty się nie powtarzają (w tym samym czasie ProductID).
Daty z przerwami z duplikatami
Niestety w naszych danych występują zarówno luki w datach, jak i daty mogą się powtarzać w tym samym czasie ProductID. Aby rozwiązać ten problem, możemy GROUPoryginalne dane, ProductID, TransactionDategenerując zestaw dat bez duplikatów. Następnie użyj Calendartabeli, aby wygenerować zestaw dat bez przerw. Następnie możemy użyć zapytania ROWS BETWEEN 45 PRECEDING AND CURRENT ROWdo obliczenia kroczącego SUM. To dałoby prawidłowe wyniki. Zobacz komentarze w zapytaniu poniżej.
WITH
-- calculate Start/End dates for each product
CTE_Products
AS
(
SELECT TH.ProductID
,MIN(TH.TransactionDate) AS MinDate
,MAX(TH.TransactionDate) AS MaxDate
FROM [Production].[TransactionHistory] AS TH
GROUP BY TH.ProductID
)
-- generate set of dates without gaps for each product
,CTE_ProductsWithDates
AS
(
SELECT CTE_Products.ProductID, C.dt
FROM
CTE_Products
INNER JOIN dbo.Calendar AS C ON
C.dt >= CTE_Products.MinDate AND
C.dt <= CTE_Products.MaxDate
)
-- generate set of dates without duplicates for each product
-- calculate daily cost as well
,CTE_DailyCosts
AS
(
SELECT TH.ProductID, TH.TransactionDate, SUM(ActualCost) AS DailyActualCost
FROM [Production].[TransactionHistory] AS TH
GROUP BY TH.ProductID, TH.TransactionDate
)
-- calculate rolling sum over 45 days
,CTE_Sum
AS
(
SELECT
CTE_ProductsWithDates.ProductID
,CTE_ProductsWithDates.dt
,CTE_DailyCosts.DailyActualCost
,SUM(CTE_DailyCosts.DailyActualCost) OVER (
PARTITION BY CTE_ProductsWithDates.ProductID
ORDER BY CTE_ProductsWithDates.dt
ROWS BETWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
FROM
CTE_ProductsWithDates
LEFT JOIN CTE_DailyCosts ON
CTE_DailyCosts.ProductID = CTE_ProductsWithDates.ProductID AND
CTE_DailyCosts.TransactionDate = CTE_ProductsWithDates.dt
)
-- remove rows that were added by Calendar, which fill the gaps in dates
-- add back duplicate dates that were removed by GROUP BY
SELECT
TH.ProductID
,TH.TransactionDate
,TH.ActualCost
,CTE_Sum.RollingSum45
FROM
[Production].[TransactionHistory] AS TH
INNER JOIN CTE_Sum ON
CTE_Sum.ProductID = TH.ProductID AND
CTE_Sum.dt = TH.TransactionDate
ORDER BY
TH.ProductID
,TH.TransactionDate
,TH.ReferenceOrderID
;
Potwierdziłem, że to zapytanie daje takie same wyniki, jak podejście z pytania wykorzystującego podkwerendę.
Plany wykonania
Pierwsze zapytanie wykorzystuje podkwerendę, drugie - takie podejście. Widać, że w tym podejściu czas trwania i liczba odczytów jest znacznie mniejsza. Większość szacunkowych kosztów w tym podejściu jest ostateczna ORDER BY, patrz poniżej.
Podejście kwerendy ma prosty plan z zagnieżdżonymi pętlami i O(n*n)złożonością.
Zaplanuj skanowanie tego podejścia TransactionHistorykilka razy, ale nie ma żadnych pętli. Jak widać, ponad 70% szacowanego kosztu to Sortfinał ORDER BY.
Najlepszy wynik - na subquerydole - OVER.
Unikanie dodatkowych skanów
Ostatnie skanowanie indeksu, łączenie i sortowanie w powyższym planie jest spowodowane przez finał INNER JOINz oryginalną tabelą, aby końcowy wynik był dokładnie taki sam jak powolne podejście z podzapytaniem. Liczba zwróconych wierszy jest taka sama jak w TransactionHistorytabeli. Istnieją wiersze, w TransactionHistoryktórych kilka transakcji miało miejsce tego samego dnia dla tego samego produktu. Jeśli w wyniku można wyświetlić tylko podsumowanie dzienne, wynik końcowy JOINmożna usunąć, a zapytanie staje się nieco prostsze i nieco szybsze. Ostatnie skanowanie indeksu, łączenie i sortowanie z poprzedniego planu są zastępowane przez Filtr, który usuwa wiersze dodane przez Calendar.
WITH
-- two scans
-- calculate Start/End dates for each product
CTE_Products
AS
(
SELECT TH.ProductID
,MIN(TH.TransactionDate) AS MinDate
,MAX(TH.TransactionDate) AS MaxDate
FROM [Production].[TransactionHistory] AS TH
GROUP BY TH.ProductID
)
-- generate set of dates without gaps for each product
,CTE_ProductsWithDates
AS
(
SELECT CTE_Products.ProductID, C.dt
FROM
CTE_Products
INNER JOIN dbo.Calendar AS C ON
C.dt >= CTE_Products.MinDate AND
C.dt <= CTE_Products.MaxDate
)
-- generate set of dates without duplicates for each product
-- calculate daily cost as well
,CTE_DailyCosts
AS
(
SELECT TH.ProductID, TH.TransactionDate, SUM(ActualCost) AS DailyActualCost
FROM [Production].[TransactionHistory] AS TH
GROUP BY TH.ProductID, TH.TransactionDate
)
-- calculate rolling sum over 45 days
,CTE_Sum
AS
(
SELECT
CTE_ProductsWithDates.ProductID
,CTE_ProductsWithDates.dt
,CTE_DailyCosts.DailyActualCost
,SUM(CTE_DailyCosts.DailyActualCost) OVER (
PARTITION BY CTE_ProductsWithDates.ProductID
ORDER BY CTE_ProductsWithDates.dt
ROWS BETWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
FROM
CTE_ProductsWithDates
LEFT JOIN CTE_DailyCosts ON
CTE_DailyCosts.ProductID = CTE_ProductsWithDates.ProductID AND
CTE_DailyCosts.TransactionDate = CTE_ProductsWithDates.dt
)
-- remove rows that were added by Calendar, which fill the gaps in dates
SELECT
CTE_Sum.ProductID
,CTE_Sum.dt AS TransactionDate
,CTE_Sum.DailyActualCost
,CTE_Sum.RollingSum45
FROM CTE_Sum
WHERE CTE_Sum.DailyActualCost IS NOT NULL
ORDER BY
CTE_Sum.ProductID
,CTE_Sum.dt
;
Mimo TransactionHistoryto jest skanowany dwukrotnie. Potrzebny jest jeden dodatkowy skan, aby uzyskać zakres dat dla każdego produktu. Byłem zainteresowany, aby zobaczyć, jak to wygląda w porównaniu z innym podejściem, w którym wykorzystujemy zewnętrzną wiedzę na temat globalnego zakresu dat TransactionHistory, a także dodatkową tabelę, Productktóra ma wszystko, ProductIDsaby uniknąć tego dodatkowego skanowania. Z tego zapytania usunąłem obliczanie liczby transakcji dziennie, aby porównanie było prawidłowe. Można go dodać w obu zapytaniach, ale chciałbym, aby było to łatwe do porównania. Musiałem także użyć innych dat, ponieważ korzystam z wersji 2014 bazy danych.
DECLARE @minAnalysisDate DATE = '2013-07-31',
-- Customizable start date depending on business needs
@maxAnalysisDate DATE = '2014-08-03'
-- Customizable end date depending on business needs
SELECT
-- one scan
ProductID, TransactionDate, ActualCost, RollingSum45
--, NumOrders
FROM (
SELECT ProductID, TransactionDate,
--NumOrders,
ActualCost,
SUM(ActualCost) OVER (
PARTITION BY ProductId ORDER BY TransactionDate
ROWS BETWEEN 45 PRECEDING AND CURRENT ROW
) AS RollingSum45
FROM (
-- The full cross-product of products and dates,
-- combined with actual cost information for that product/date
SELECT p.ProductID, c.dt AS TransactionDate,
--COUNT(TH.ProductId) AS NumOrders,
SUM(TH.ActualCost) AS ActualCost
FROM Production.Product p
JOIN dbo.calendar c
ON c.dt BETWEEN @minAnalysisDate AND @maxAnalysisDate
LEFT OUTER JOIN Production.TransactionHistory TH
ON TH.ProductId = p.productId
AND TH.TransactionDate = c.dt
GROUP BY P.ProductID, c.dt
) aggsByDay
) rollingSums
--WHERE NumOrders > 0
WHERE ActualCost IS NOT NULL
ORDER BY ProductID, TransactionDate
-- MAXDOP 1 to avoid parallel scan inflating the scan count
OPTION (MAXDOP 1);
Oba zapytania zwracają ten sam wynik w tej samej kolejności.
Porównanie
Oto statystyki czasu i IO.
Wariant z dwoma skanami jest nieco szybszy i ma mniej odczytów, ponieważ wariant z jednym skanem musi często używać tabeli roboczej. Poza tym wariant jednego skanu generuje więcej wierszy, niż jest to potrzebne, jak widać w planach. Generuje daty dla każdego, ProductIDktóry jest w Producttabeli, nawet jeśli ProductIDnie ma żadnych transakcji. ProductTabela zawiera 504 wiersze , ale tylko 441 produktów zawiera transakcje TransactionHistory. Ponadto generuje ten sam zakres dat dla każdego produktu, co jest więcej niż potrzebne. Gdyby TransactionHistorymiał dłuższą historię ogólną, a każdy produkt miałby stosunkowo krótką historię, liczba dodatkowych niepotrzebnych rzędów byłaby jeszcze wyższa.
Z drugiej strony można zoptymalizować wariant dwóch skanów, tworząc inny, bardziej wąski indeks na just (ProductID, TransactionDate). Indeks ten służyłby do obliczania dat rozpoczęcia / zakończenia dla każdego produktu ( CTE_Products) i miałby mniej stron niż indeks i dlatego powodowałby mniej odczytów.
Możemy więc wybrać albo przeprowadzić wyraźny prosty skan, albo mieć domyślny stół roboczy.
Przy okazji, jeśli wynik jest możliwy tylko z codziennymi podsumowaniami, lepiej jest utworzyć indeks, który nie będzie zawierał ReferenceOrderID. Zużyłoby mniej stron => mniej IO.
CREATE NONCLUSTERED INDEX [i2] ON [Production].[TransactionHistory]
(
[ProductID] ASC,
[TransactionDate] ASC
)
INCLUDE ([ActualCost])
Rozwiązanie jednoprzebiegowe z zastosowaniem APLIKACJI KRZYŻOWEJ
Staje się naprawdę długą odpowiedzią, ale tutaj jest jeszcze jeden wariant, który zwraca tylko codzienne podsumowanie, ale wykonuje tylko jeden skan danych i nie wymaga zewnętrznej wiedzy na temat zakresu dat lub listy ProductID. Nie wykonuje również sortowania pośredniego. Ogólna wydajność jest podobna do poprzednich wariantów, choć wydaje się nieco gorsza.
Główną ideą jest użycie tabeli liczb do wygenerowania wierszy, które wypełnią luki w datach. Dla każdej istniejącej daty użyj, LEADaby obliczyć rozmiar luki w dniach, a następnie użyj, CROSS APPLYaby dodać wymaganą liczbę wierszy do zestawu wyników. Najpierw próbowałem z użyciem stałej tabeli liczb. Plan pokazał dużą liczbę odczytów w tej tabeli, chociaż faktyczny czas trwania był prawie taki sam, jak w przypadku generowania liczb w locie za pomocą CTE.
WITH
e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 10
,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) -- 10*10
,e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
,CTE_Numbers
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
FROM e3
)
,CTE_DailyCosts
AS
(
SELECT
TH.ProductID
,TH.TransactionDate
,SUM(ActualCost) AS DailyActualCost
,ISNULL(DATEDIFF(day,
TH.TransactionDate,
LEAD(TH.TransactionDate)
OVER(PARTITION BY TH.ProductID ORDER BY TH.TransactionDate)), 1) AS DiffDays
FROM [Production].[TransactionHistory] AS TH
GROUP BY TH.ProductID, TH.TransactionDate
)
,CTE_NoGaps
AS
(
SELECT
CTE_DailyCosts.ProductID
,CTE_DailyCosts.TransactionDate
,CASE WHEN CA.Number = 1
THEN CTE_DailyCosts.DailyActualCost
ELSE NULL END AS DailyCost
FROM
CTE_DailyCosts
CROSS APPLY
(
SELECT TOP(CTE_DailyCosts.DiffDays) CTE_Numbers.Number
FROM CTE_Numbers
ORDER BY CTE_Numbers.Number
) AS CA
)
,CTE_Sum
AS
(
SELECT
ProductID
,TransactionDate
,DailyCost
,SUM(DailyCost) OVER (
PARTITION BY ProductID
ORDER BY TransactionDate
ROWS BETWEEN 45 PRECEDING AND CURRENT ROW) AS RollingSum45
FROM CTE_NoGaps
)
SELECT
ProductID
,TransactionDate
,DailyCost
,RollingSum45
FROM CTE_Sum
WHERE DailyCost IS NOT NULL
ORDER BY
ProductID
,TransactionDate
;
Ten plan jest „dłuższy”, ponieważ zapytanie używa dwóch funkcji okna ( LEADi SUM).
RunningTotal.TBE IS NOT NULLWarunek (i w konsekwencjiTBEkolumna) nie jest konieczne. Nie dostaniesz zbędnych wierszy, jeśli go upuścisz, ponieważ twój wewnętrzny warunek łączenia zawiera kolumnę daty - dlatego zestaw wyników nie może mieć dat, które nie były pierwotnie w źródle.