Kathi Kellenbergerová
Ve svém kódu v jazyce T-SQL jsem léta používala funkci ROW_NUMBER, aniž bych si uvědomovala, že patří do skupiny neuvěřitelně užitečných funkcí zvaných okenní funkce. Teprve při sestavování prezentace o nových funkcích jazyka T-SQL ve verzi SQL Server 2012 jsem si uvědomila, že je toho tolik, že jsem vytvořila samostatnou prezentaci jen o nových okenních funkcích. Nakonec se z této jedné prezentace staly dvě prezentace, moje kniha vydaná nakladatelstvím Apress a kurz Pluralsight. O těchto skvělých funkcích jsem vyprávěl každému, kdo mě poslouchal, a zjistil jsem, že je denně používám v práci.
Funkce okna umožňují provádět výpočty nad sadou řádků. Pracují podobně jako seskupování pomocí agregačních funkcí, ale umožňují důležitý rozdíl: Agregační dotazy vracejí jeden řádek na skupinu a podrobnosti jsou ve výsledcích eliminovány. Dotazy zahrnující okénkové funkce vracejí všechny řádky s detaily spolu s výsledkem výrazu okénkové funkce. Máte detail a získáte i agregát.
Když mluvím na akcích SQL Serveru a pro vývojáře o okenních funkcích, obvykle zhruba polovina místnosti zná ROW_NUMBER. Hrstka jich bude znát víc. To je zklamání, protože tyto užitečné funkce jsou k dispozici už léta.
Moje oblíbená okenní funkce se jmenuje LAG. LAG umožňuje uchopit libovolný sloupec z jiného řádku výsledkové sady. Stejné věci můžete dosáhnout i jinými metodami, ale výkon je mnohem horší. Následují dva dotazy proti databázi AdventureWorks. Oba dotazy dávají stejný výsledek. První tak činí prostřednictvím vnějšího spojení, zatímco druhý využívá výhod LAG.
–Seznam zákazníků s daty objednávek a dny od předchozí objednávky
SELECT CustomerID, OrderDate,
DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder
FROM Sales.SalesOrderHeader AS SOH
OUTER APPLY (
SELECT TOP(1) OrderDate AS PrevOrder
FROM Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
AND OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) AS OAPPLY
ORDER BY CustomerID, OrderDate;
–Stejné výsledky získáte pomocí LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Druhý dotaz pomocí funkce LAG je jednodušší na zápis a funguje lépe. Skutečný plán provádění ukazuje, že první dotaz zabere 99 % prostředků v dávce. Druhý dotaz zabere pouze 1 % prostředků. Zde jsou oba plány provedení a můžete se o tom přesvědčit sami:
Pokud neznáte syntaxi funkce okna, může být dotaz LAG trochu matoucí. Všechny okenní funkce budou mít klauzuli OVER, která definuje okno nebo množinu řádků pro výpočet. Nechci porovnávat data objednávek napříč zákazníky, proto jsem použil možnost PARTITION BY k rozdělení řádků do skupin podle CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
Klauzule OVER má také možnost ORDER BY. Ta je v případě LAG nutná, aby byly řádky seřazeny ve správném pořadí, aby engine mohl uchopit příslušné hodnoty z jejich správných řádků. Moje konečné volání LAG vypadá takto:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG je jen jednou z mnoha okenních funkcí. Tyto funkce ve skutečnosti neumožňují dělat nic, co by bez nich nebylo možné, ale rozhodně usnadňují kódování. Zjistíte také, že dotazy formulované pomocí těchto nových funkcí mnohdy běží rychleji.
Okenní funkce nakonec používám denně. Usnadňují mi život a zlepšují výkon mých dotazů. Rád je používám a vy byste je měli používat také.
O autorovi
Kathi Kellenberger je konzultantem společnosti Linchpin People. Ráda píše a přednáší o tématech týkajících se SQL Serveru, napsala více než dvě desítky článků, přispěla do čtyř knih a přednášela na mnoha akcích věnovaných SQL Serveru. Ve volném čase Kathi ráda tráví čas s rodinou a přáteli, zpívá a jezdí na kole.
Ještě více informací o okenních funkcích a jejich síle při řešení náročných problémů s dotazy se dozvíte z knihy Kathi Kellenberger Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Mezi další knihy Kathi patří Beginning SQL Server Reporting Services (2016) a Beginning T-SQL (2014).
.
Napsat komentář