Pchełki SQL: Pchełkozagadka maksywielokolumnowa

xpil - 2016/01/08 - Pchełki /Pchełki SQL /

Rozwiążemy sobie dziś problem, który w Excelu rozwiązuje się dość prosto, natomiast w SQL-u odrobinę trudniej (ale bez przesady). Spróbujemy też odpowiedzieć sobie na pytanie, która z pokazanych metod jest najbardziej wydajna.

Problem: mając w tabeli kilka kolumn liczbowych tego samego typu, wyszukać maksymalną wartość w tych kolumnach.

Czyli po naszemu, zrobić SELECT id, MAX({kilka kolumn}) FROM jakas_tabelka – na wyjściu oczekujemy identyfikatora rekordy z największą wartością, oraz tej wartości.

Zaczniemy od wygenerowania losowych danych spełniających warunki zadania.

Najpierw tabela:

CREATE TABLE #t1 (
 id INT PRIMARY KEY CLUSTERED
 IDENTITY(1, 1)
 , d1 DATE NULL
 , s1 VARCHAR(100)
 , d2 DATE NULL
 , s2 VARCHAR(100)
 , d3 DATE NULL
 , s3 VARCHAR(100)
 , d4 DATE NULL
 , s4 VARCHAR(100)
 );

Teraz dane:

DECLARE @i INT = 0
WHILE @i < 10000 BEGIN
	INSERT INTO #t1
	 ( d1, s1, d2, s2, d3, s3, d4, s4)
	SELECT
		  DATEADD(d, 5000 - FLOOR(RAND() * ( 10000 )), GETDATE())
		  , CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65)
		  , DATEADD(d, 5000 - FLOOR(RAND() * ( 10000 )), GETDATE())
		  , CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65)
		  , DATEADD(d, 5000 - FLOOR(RAND() * ( 10000 )), GETDATE())
		  , CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65)
		  , DATEADD(d, 5000 - FLOOR(RAND() * ( 10000 )), GETDATE())
		  , CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65) + CHAR(FLOOR(RAND() * 26) + 65)
	SET @i += 1
END

Dane wyglądają tak:

id d1 s1 d2 s2 d3 s3 d4 s4
1 2006-03-17 EWCXNO 2026-10-27 UIOIJR 2014-04-02 AFDXUI 2009-03-22 KOVEZI
2 2010-08-06 XNOECM 2030-01-23 TMELKP 2029-12-14 UJBZDH 2008-07-26 BXITKK
3 2028-05-05 EWHYOM 2030-09-26 PIEZPS 2015-12-27 LLXUDZ 2009-06-22 MTKPNK
4 2029-05-28 PAUYGO 2022-09-03 BQTQIC 2030-11-23 PELHEA 2030-11-08 WPFOJX
5 2011-03-30 OSLYGG 2013-10-26 MIXBOL 2030-01-21 LJBQAE 2006-11-12 CZADIN
6 2029-05-26 EAUNBE 2009-08-25 SFSZYT 2007-09-09 PURTXI 2005-03-20 KZLLLH
7 2004-06-09 GOYNPB 2005-05-29 QKBMUM 2015-06-01 XRPVJU 2027-12-21 IFWUQV
8 2012-01-27 VYJFTM 2020-12-17 XMLKEP 2030-03-31 CJXRXM 2029-01-19 VDGPBE
9 2019-06-04 ZWBBOP 2010-12-14 VSEUBF 2012-09-17 KCHWME 2014-02-11 CYZCBP
10 2015-07-18 PNQBBY 2024-02-20 GPQYNN 2025-04-22 YZVPEE 2027-08-26 VGXYDN
11 2021-07-27 BAUONX 2007-11-19 WVLOWD 2031-01-12 VTNWUX 2007-03-02 RXYSRC
12 2030-11-26 EXCMGO 2027-12-17 REXXJB 2014-05-03 MBXAUR 2027-04-01 VSIIDS
13 2025-12-19 ROMHDL 2013-04-12 KEGJQT 2005-12-09 MTAGLS 2029-04-28 FFTPOF
14 2009-09-03 HQOOJB 2012-11-09 UHMPBW 2027-09-06 OJXPIS 2028-04-15 TAGCNS
15 2014-10-10 QIPDRA 2008-10-17 THMHYX 2010-07-26 EXPATK 2005-04-07 MAWUTS
16 2027-07-18 PCXEFN 2026-05-24 XNURZP 2007-07-03 VYBCIY 2025-09-04 VSKFBP
17 2020-08-14 VFPEPZ 2008-06-01 BGZGZU 2010-03-26 BTSJGH 2017-08-30 XVTINW
18 2004-03-18 SPKZVL 2020-10-08 ZCIIBH 2018-12-10 XDUPGZ 2020-09-02 NREFLT

Pokażę teraz trzy przykładowe metody na znalezienie największej wartości spośród kolumn d1, d2, d3 oraz d4:

Metoda 1

SELECT TOP 1
        id
      , ( SELECT    MAX(daty.data)
          FROM      ( VALUES ( d1), ( d2), ( d3), ( d4) ) AS daty ( DATA ) ) AS daty
FROM    #t1
ORDER BY 2 DESC;

Metoda 2

SELECT TOP 1
        id
      , MAX(daty) AS LastUpdateDate
FROM    #t1 UNPIVOT ( daty FOR DATA IN ( d1, d2, d3, d4 ) ) AS u
GROUP BY id
ORDER BY 2 DESC;

Metoda 3

SELECT TOP 1 
	id
	, DATA 
FROM 
	(			SELECT id, d1 DATA FROM #t1
	UNION ALL	SELECT id, d2 FROM #t1
	UNION ALL	SELECT id, d3 FROM #t1
	UNION ALL	SELECT id, d4 FROM #t1) q
ORDER BY 2 DESC;

Na zakończenie, żeby nie było nudno, zagadka dla Czytelnika: która z powyższych metod jest najbardziej wydajna? Innymi słowy, która wykona się w najkrótszym czasie?

 

Dodaj komentarz

avatar
  Subscribe  
Powiadom o
%d bloggers like this: