GREATEST oraz LEAST – nowe funkcje TSQL w Azure SQL i Synapse Serverless

Nie tak dawno temu w świecie TSQL pojawiły się dwie nowe funkcje, a mianowicie GREATEST oraz LEAST. Obie z nich są obecnie dostępne w Azure SQL Database, Managed Instance oraz w Synapse Serverless – dodatkowo dostaliśmy informację o tym, że będą one również dostępne w najnowszej wersji SQL Server. Czym te funkcje są i w jakich scenariuszach mogą okazać się pomocne? Zapraszam do lektury!

Obie funkcje są skalarne to oznacza, że zwracają pojedynczą wartość gdzie w przypadku GREATEST będzie to wartość największa spośród podanych parametrów, a przy LEAST odpowiednio wartość najmniejsza. Można więc powiedzieć, że są to odpowiedniki funkcji MIN oraz MAX działające horyzontalnie na pojedynczym wierszu zamiast wertykalnie na zbiorze wierszy.

Sama składnia jest dosyć prosta i wygląda następująco:

GREATEST ( expression1 [ ,...expressionN ] ) LEAST ( expression1 [ ,...expressionN ] )

Zwróćcie proszę uwagę, że parametrów może być wiele i są one wyrażeniami, a nie kolumnami. Co za tym idzie jesteśmy w stanie przekazać do funkcji jakieś działanie napisane w TSQL, które następnie będzie porównywane z pozostałymi elementami przekazanymi do funkcji i spośród nich zwrócona będzie wartość największa/najmniejsza.

Przetestujemy sobie funkcjonalność używając SQL Synapse Serverless jednakże działanie jest dokładnie takie samo również w Azure SQL Db i Managed Instance (zainteresowanych działaniem Synapse Serverless odsyłam do przygotowanych przeze mnie videotutoriali). Nasz zbiór testowy przedstawia się w następujący sposób:

SELECT 
       VendorID
      ,fare_amount
      ,extra
      ,mta_tax
      ,tip_amount
      ,total_amount
FROM
    OPENROWSET(
         BULK 'https://sqlday2021.blob.core.windows.net/sqlday2021adsl/CSV/green_tripdata_2020-01.csv'
        ,FORMAT = 'CSV'
        ,HEADER_ROW=TRUE
        ,PARSER_VERSION='2.0'
    ) AS [r]

GO

Są to dane pochodzące z zestawu danych TLC Trip Record Data, które możecie znaleźć tutaj. Dane zawierają informacje o przejazdach taksówek jednakże w naszym przykładzie nie jest to najważniejsze bo skupimy się na konkretnych funkcjonalnościach. Jak zauważyliście wyodrębniłem w zapytaniu testowym kolumny liczbowe bo to na nich będzie opierała się nasza demonstracja. Powyższe zapytanie zmodyfikuje nieco dodając element losowy:

DECLARE @RandomNumber DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber2 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber3 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber4 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber5 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())

SELECT 
       VendorID
      ,fare_amount * @RandomNumber AS fare_amount
      ,extra * @RandomNumber2 AS extra
      ,mta_tax * @RandomNumber3 AS mta_tax
      ,tip_amount *@RandomNumber4 AS tip_amount
      ,total_amount *@RandomNumber5 AS total_amount
FROM
    OPENROWSET(
         BULK 'https://sqlday2021.blob.core.windows.net/sqlday2021adsl/CSV/green_tripdata_2020-01.csv'
        ,FORMAT = 'CSV'
        ,HEADER_ROW=TRUE
        ,PARSER_VERSION='2.0'
    ) 
WITH(
       VendorID BIGINT
      ,fare_amount DECIMAL(16,2)
      ,extra DECIMAL(16,2)
      ,mta_tax DECIMAL(16,2)
      ,tip_amount DECIMAL(16,2)
      ,total_amount DECIMAL(16,2)
) AS [r]
GO

Jeśli teraz chcielibyśmy zwrócić największą wartość z powyższych kolumn musielbyśmy wykonać porównanie np. z użyciem instrukcji CASE:

DECLARE @RandomNumber DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber2 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber3 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber4 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber5 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())

;with cte
AS
(
SELECT 
VendorID
,fare_amount * @RandomNumber AS fare_amount
,extra * @RandomNumber2 AS extra
,mta_tax * @RandomNumber3 AS mta_tax
,tip_amount *@RandomNumber4 AS tip_amount
,total_amount *@RandomNumber5 AS total_amount
FROM
OPENROWSET(
BULK 'https://sqlday2021.blob.core.windows.net/sqlday2021adsl/CSV/green_tripdata_2020-01.csv'
,FORMAT = 'CSV'
,HEADER_ROW=TRUE
,PARSER_VERSION='2.0'
) 
WITH(
VendorID BIGINT
,fare_amount DECIMAL(16,2)
,extra DECIMAL(16,2)
,mta_tax DECIMAL(16,2)
,tip_amount DECIMAL(16,2)
,total_amount DECIMAL(16,2)

) AS [r]
)
SELECT *
,CASE
WHEN fare_amount > extra AND fare_amount>mta_tax THEN fare_amount 
WHEN fare_amount < extra AND extra>mta_tax THEN extra
WHEN mta_tax > extra AND extra <mta_tax THEN mta_tax
END AS result 
FROM cte

W powyższym przykładzie porównaliśmy jedynie wartości trzech kolumn, a co jeżeli mielibyśmy porównać ich zdecydowanie więcej? Ilość kombinacji, a co za tym idzie poziom skomplikowania kodu wzrósłby wykładniczo. I tutaj z pomocą przychodzi nam właśnie GREATEST – użycie tej funkcji wygląda następująco:

DECLARE @RandomNumber DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber2 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber3 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber4 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())
DECLARE @RandomNumber5 DECIMAL(16,2) =(SELECT TOP 1 number from master..spt_values ORDER BY newId())

SELECT 
VendorID
,fare_amount * @RandomNumber AS fare_amount
,extra * @RandomNumber2 AS extra
,mta_tax * @RandomNumber3 AS mta_tax
,tip_amount *@RandomNumber4 AS tip_amount
,total_amount *@RandomNumber5 AS total_amount
,GREATEST(fare_amount * @RandomNumber,extra * @RandomNumber2,mta_tax * @RandomNumber3,tip_amount *@RandomNumber4,total_amount *@RandomNumber5) as result
FROM
OPENROWSET(
BULK 'https://sqlday2021.blob.core.windows.net/sqlday2021adsl/CSV/green_tripdata_2020-01.csv'
,FORMAT = 'CSV'
,HEADER_ROW=TRUE
,PARSER_VERSION='2.0'
) 
WITH(
VendorID BIGINT
,fare_amount DECIMAL(16,2)
,extra DECIMAL(16,2)
,mta_tax DECIMAL(16,2)
,tip_amount DECIMAL(16,2)
,total_amount DECIMAL(16,2)

) AS [r]
GO

Proste prawda? Do tego możemy podać naprawdę dużo parametrów bo zgodnie z dokumentacją aż do 254 parametrów. Bardzo podobna w użyciu jest funkcja LEAST tylko oczywiście zwraca najmniejszą wartość z podanych:

Wartym wspomnienia jest również fakt, iż parametry podane do opisywanych funkcji podlegają niejawnej konwersji np. poniższe zapytanie:

SELECT
         GREATEST(1.1,'1',23,-9,19-1-1) AS GreatestResult
        ,LEAST(1.1,'1',23,-9,19-1-1) AS LeastResult

Skutkuje tym, że wszystkie podane parametry zostały przekonwertowane na decimal i taki też został zwrócony rezultat:

Co do kolejności tego jak SQL operuje typami danych odsyłam do dokumentacji gdzie opisany jest mało znany mechanizm o nazwie Data Type Precedence. w skrócie polega to na tym, że w przypadku porównywania określonym operatorem np. wartości tekstowej i wartości całkowitoliczbowej typ z niższym poziomem pierszeństwa będzie konwertowany na ten z wyższym ( o ile to w ogóle możliwe). Tak też powyżej mamy typ INT, DECIMAL oraz VARCHAR patrząc na powyższy wynik oraz na podlinkowaną przeze mnie dokumentację DECIMAL ma pierszeństwo dlatego też rezultat jest taki, a nie inny.

Zwróćcie proszę jeszcze uwagę na to co się stanie gdy jako jeden z argumentów podamy wartość NULL:

SELECT
         GREATEST(1.1,'1',23,-9,19-1-1,NULL) AS GreatestResult
        ,LEAST(1.1,'1',23,-9,19-1-1,NULL) AS LeastResult

W gruncie rzeczy wartość taka zostanie zignorowana dzięki czemu nie musimy dodatkowo obrabiać elementów przy pomocy ISNULL lub COALESCE:

Jak widzicie funkcje są bardzo proste w użyciu jednakże niezwykle przydatne! Mam nadzieje, że użycie CASE lub zagnieżdżonych IF tylko po to żeby zwrócić wartość minimalną maksymalną odejdzie w zapomnienie i w jak największej ilości scenariuszy będziemy mogli uprościć nasz kod poprzez użycie GREATEST oraz LEAST. Pozdrawiam!