Pchełki SQL: Pchełkozagadka maksywielokolumnowa

https://xpil.eu/d5szg 📋

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

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?

https://xpil.eu/d5szg 📋