czwartek, 2 czerwca 2011

Wartość minimalna nie mniejsza niż...

Czy zastanawialiście sie kiedys jak znaleść najmniejszą wartość w zbiorze, jednakże tak była ona większa od pewnej wartości a w dodatku by rozwiązanie to było dobrze zoptymalizowane?

Problem można rozwiązać na kilka sposobów.

Pierwsza rzecz jaka kazdemu przychodzi zapewne do głowy to:

SELECT ID, MIN(COL) FROM MyTABLE WHERE COL > x GROUP BY ID

Cel osiągniety bez większego nakładu sił. Co jednak w przypadku kiedy potrzebne są wartości minimalne dla kilku kolumn?

Można to zrobić na kilka sposobów pewnie. Żeby nie teoretyzować za dużo poniższe rozwiązanie wykorzystuje fragment notowań dwóch spółek z GPW.

Przykładowa dane zapiszemy w tabeli tymczasowej

SET NOCOUNT ON

IF OBJECT_ID('TEMPDB..#QUOTES') IS NOT NULL
DROP TABLE #QUOTES

;WITH CTE AS (
SELECT 'URLOPYPL' AS NAZWA,'04 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','05 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','06 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','09 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','10 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','11 May 201' AS DATE,187000 AS VOLUME, 41140.00 AS VALUE, 24 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','12 May 201' AS DATE,345662 AS VOLUME, 90760.00 AS VALUE, 36 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','13 May 201' AS DATE,268563 AS VOLUME, 99930.00 AS VALUE, 51 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','16 May 201' AS DATE,765218 AS VOLUME, 425420.00 AS VALUE, 263 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','17 May 201' AS DATE,378111 AS VOLUME, 307780.00 AS VALUE, 170 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','18 May 201' AS DATE,1502701 AS VOLUME, 1485030.00 AS VALUE, 833 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','19 May 201' AS DATE,1397675 AS VOLUME, 1441540.00 AS VALUE, 784 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','20 May 201' AS DATE,191155 AS VOLUME, 203610.00 AS VALUE, 132 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','23 May 201' AS DATE,159533 AS VOLUME, 152170.00 AS VALUE, 132 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','24 May 201' AS DATE,204692 AS VOLUME, 173250.00 AS VALUE, 139 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','25 May 201' AS DATE,246006 AS VOLUME, 187250.00 AS VALUE, 152 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','26 May 201' AS DATE,175840 AS VOLUME, 151150.00 AS VALUE, 113 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','27 May 201' AS DATE,113403 AS VOLUME, 92440.00 AS VALUE, 87 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','30 May 201' AS DATE,221718 AS VOLUME, 176730.00 AS VALUE, 149 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','31 May 201' AS DATE,669521 AS VOLUME, 679490.00 AS VALUE, 383 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','04 May 201' AS DATE,991 AS VOLUME, 496280.00 AS VALUE, 23 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','05 May 201' AS DATE,537 AS VOLUME, 267640.00 AS VALUE, 13 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','06 May 201' AS DATE,966 AS VOLUME, 474170.00 AS VALUE, 29 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','09 May 201' AS DATE,1390 AS VOLUME, 682390.00 AS VALUE, 22 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','10 May 201' AS DATE,89 AS VOLUME, 44370.00 AS VALUE, 8 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','11 May 201' AS DATE,57 AS VOLUME, 28430.00 AS VALUE, 15 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','12 May 201' AS DATE,286 AS VOLUME, 140850.00 AS VALUE, 23 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','13 May 201' AS DATE,367 AS VOLUME, 183830.00 AS VALUE, 24 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','16 May 201' AS DATE,79 AS VOLUME, 39590.00 AS VALUE, 11 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','17 May 201' AS DATE,946 AS VOLUME, 466280.00 AS VALUE, 18 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','18 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','19 May 201' AS DATE,144 AS VOLUME, 72630.00 AS VALUE, 9 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','20 May 201' AS DATE,1412 AS VOLUME, 713060.00 AS VALUE, 10 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','23 May 201' AS DATE,1727 AS VOLUME, 856060.00 AS VALUE, 26 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','24 May 201' AS DATE,70 AS VOLUME, 34580.00 AS VALUE, 9 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','25 May 201' AS DATE,20 AS VOLUME, 9860.00 AS VALUE, 1 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','26 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','27 May 201' AS DATE,38 AS VOLUME, 18740.00 AS VALUE, 5 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','30 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','31 May 201' AS DATE,526 AS VOLUME, 262780.00 AS VALUE, 16 AS TRANSACTIONS
)

SELECT * INTO #QUOTES FROM CTE

CREATE NONCLUSTERED INDEX IDX_QUOTES ON #QUOTES(NAZWA,[DATE]) INCLUDE(VOLUME, VALUE, TRANSACTIONS)

Powiedzmy, że potrzebujemy wartości minimalne dla ilości transakcji, wolumenu oraz wartości obrotu, jednakże tylko z sesji gdzie wystąpiły transakcje.

SELECT
NAZWA,
MIN(TRANSACTIONS) MIN_TRANSACTIONS,
MIN(VOLUME) MIN_VOLUME,
MIN(VALUE) MIN_VALUE,
'Excluded sessions without transactions' NOTES
FROM #QUOTES
WHERE TRANSACTIONS > 0

Powyższe zapytanie zwróci oczekiwane wyniki. Przykład jest dość trywialny, gdyż wartości w kolumnach, z który pobierane są wartości minimalne są skorelowane z ilością transakcji. Jeżeli nie bylo transakcji to zarówno wolumen jak i wartość obrotu wynoszą 0.

Powyższe zapytanie zwróci następujące wyniki:
NAZWA MIN_TRANSACTIONS MIN_VOLUME MIN_VALUE NOTES
-------- ---------------- ----------- --------------------------------------- --------------------------------------
URLOPYPL 24 113403 41140.00 Excluded sessions without transactions
WAWEL 1 20 9860.00 Excluded sessions without transactions

Przy okazji cel został osiągnięty przy jednym skanie całej tabeli.

Co jednak w przypadku gdybyśmy chcieli obliczyć dodatkowo średnią ilość transakcji, ale biorąc pod uwagę także sesje bez transakcji?

Poniższe zapytanie, z warunkiem na ilość transakcji niestety, ale zwróci niepoprawne wyniki
SELECT
NAZWA,
MIN(TRANSACTIONS) MIN_TRANSACTIONS,
MIN(VOLUME) MIN_VOLUME,
MIN(VALUE) MIN_VALUE,
AVG(TRANSACTIONS) AVG_TRANSACTIONS,
'Excluded sessions without transactions' NOTES
FROM #QUOTES
WHERE TRANSACTIONS > 0
GROUP BY NAZWA

Problem można by rozwiązać za pomocą skorelowanego zapytania lub CROSS APPLY, jednakże będzie się to wiązać z dodatkowym skanem tabeli.

Czy komuś przychodzi do głowy inne rozwiązanie, takie które by zwrócilo pożądane wyniki z wykorzystaniem tylko jednego skanu tabeli?

Otóż z pomocą przychodzi tutaj CASE WHEN ... END.

SELECT
NAZWA,
MIN(CASE WHEN TRANSACTIONS = 0 THEN NULL ELSE TRANSACTIONS END) MIN_TRANSACTIONS,
MIN(CASE WHEN VOLUME = 0 THEN NULL ELSE VOLUME END) MIN_VOLUME,
MIN(CASE WHEN VALUE = 0 THEN NULL ELSE VALUE END) MIN_VALUE,
AVG(TRANSACTIONS) AVG_TRANSACTIONS,
'Included sessions without transactions' NOTES
FROM #QUOTES
GROUP BY NAZWA

W przypadku gdy nie wystąpiły żadne transakcje w ciągu danej sesji, funkcja agregująca przyjmuje NULL w pozostałym przypadku wartość dla danej sesji. Rozwiązanie jest to o tyle uniwersalne, że możemy pobrac wartość minimalną nie mniejszą niż, i może być to zarazem różna wartość dla każdej z kolumn.

Prześlij komentarz