Trädvy Permalänk
Medlem
Plats
NV Skåne
Registrerad
Jun 2007

SQL - slumpnummer och case

Hej!

I testsyfte håller jag på att fylla en webapp med slumpade värden, som ska generera vissa utfall.
Jag kör följande kod;

Citat:

cast((case (abs(checksum(newid())) % 3) when 0 then 'up' when 1 then 'down' when 2 then 'straight' else 'annat' end) as varchar(20)) as direction

Enligt min förståelse ska abs(checksum(newid())) % 3 generera ett tal mellan 0-2, dvs täckas in av mina case 0, 1 och 2. Men trots det får jag titt som tätt 'annat' som resultat.

Vad skulle abs(checksum(newid())) % 3 kunna ha för värde som inte täcks av 0, 1 och 2? Jag har gjort en test-kolumn enligt:

Citat:

abs(checksum(newid())) % 3 as test

men den ger bara 0, 1 och 2 enligt mina förutsägelser. Trots det får jag alltså case ELSE ovan. WHAT?!

Klart att jag kan sätta ELSE som sista 'straight', men just nu vill jag bara förstå varför det blir ELSE.

i5 8600k@4.68 GHz || ASUS ROG STRIX GTX 1080 || 16GB RAM 3000MHz || ASUS Z370-H Gaming || Be Quiet! 580W Modular || Noctua NH-D15 || Fractal Design Define R6 || 2x 250GB SSD, 5TB HDD || ASUS VG278HE & 2x BenQ RL2450HT || Logitech G710 & G700 & G13 & G533
* De gav mig en present. Jag gav dem en present. Vet du inte skillnaden, använd "dom".
* Fraktfritt på Inet för verifierade SweC-medlemmar

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Jul 2004

Svaret hittar du i exekveringsplanen och förklaras av hur SQL Server hanterar denna typ av CASE expressions.

När du använder denna typ av CASE, dvs

CASE <expression> WHEN 1 then

så är det egentligen bara en shorthand för

CASE WHEN <expression> = 1 then ...

SQL Server kommer att strukturera om din shorthand-version till long hand, på så vis:

CASE WHEN <expression> = 0 then 'up' ELSE CASE WHEN <expression> = 1 then 'down' ELSE CASE WHEN <expression> = 2 then 'straight' ELSE 'annat' END END END

Detta innebär att <expression> kommer att evauleras 3 gånger och för varje gång det evalueras så kommer ett nytt slumptal tas fram. Den kör alltså NEWID() för varje gång den testar ett WHEN.

Om du tittar i exekveringsplanen så ser du hur SQL Server har förvandlat ditt enkla CASE till detta åbäke

SELECT CONVERT(varchar(20), CASE WHEN abs(checksum(newid()))%(3)=(0) THEN 'up' ELSE CASE WHEN abs(checksum(newid()))%(3)=(1) THEN 'down' ELSE CASE WHEN abs(checksum(newid()))%(3)=(2) THEN 'straight' ELSE 'annat' END END END,0)

Sannolikheten för varje <expression> = ? är bara 1/3 vilket gör att man ser en överväldigande majoritet av 'annat'.

En enkel lösningen är att du endast beräknar newid() en gång per rad, tex via en cross apply. Då kommer varje WHEN att jobba med samma slumptal och inga 'annat' kan dyka upp.

SELECT cast((case (abs(checksum(id)) % 3) when 0 then 'up' when 1 then 'down' when 2 then 'straight' else 'annat' end) as varchar(20)) as direction from spt_values CROSS APPLY (VALUES (newid())) nid(id)

Trädvy Permalänk
Medlem
Plats
NV Skåne
Registrerad
Jun 2007

@MrMygel:
Tack för förklaringen!
Sicket jäkla mög, rent ut sagt. Lösningen fick bli att sätta case för 0 och 1 och sen else för 'straight' istället. Då får jag åtminstone inga NULL eller 'annat'.

i5 8600k@4.68 GHz || ASUS ROG STRIX GTX 1080 || 16GB RAM 3000MHz || ASUS Z370-H Gaming || Be Quiet! 580W Modular || Noctua NH-D15 || Fractal Design Define R6 || 2x 250GB SSD, 5TB HDD || ASUS VG278HE & 2x BenQ RL2450HT || Logitech G710 & G700 & G13 & G533
* De gav mig en present. Jag gav dem en present. Vet du inte skillnaden, använd "dom".
* Fraktfritt på Inet för verifierade SweC-medlemmar

Trädvy Permalänk
Medlem
Registrerad
Nov 2013

@Shudnawz:
Annars kan du spara guiden i en variabel först så är det lugnt. Ex:

declare @ap uniqueidentifier select @ap = newid() select case (abs(checksum(@ap)) % 3) when 0 then 'up' when 1 then 'down' when 2 then 'straight' else 'annat' end as direction

Trädvy Permalänk
Medlem
Plats
NV Skåne
Registrerad
Jun 2007

@miffloman:
Också en lösning. Men eftersom det bara är slumptal för test, så är fördelningen inte så viktig. Det primära var nya värden (oftast) när jag anropar den, plus förutsägbart output. Jag har lyckats med båda.

Jag har en likadan funktion som ger färg (blue, green, yellow) och dessa kombineras på HTML-sidan av JS till en class som sätts på ett objekt, vilket i sin tur ger en viss visuell effekt; pilar som pekar i nån riktning med en viss färg.

i5 8600k@4.68 GHz || ASUS ROG STRIX GTX 1080 || 16GB RAM 3000MHz || ASUS Z370-H Gaming || Be Quiet! 580W Modular || Noctua NH-D15 || Fractal Design Define R6 || 2x 250GB SSD, 5TB HDD || ASUS VG278HE & 2x BenQ RL2450HT || Logitech G710 & G700 & G13 & G533
* De gav mig en present. Jag gav dem en present. Vet du inte skillnaden, använd "dom".
* Fraktfritt på Inet för verifierade SweC-medlemmar