Permalänk
Lego Master

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.

Visa signatur

* Vänsterhänt högerskytt med tummen mitt i handen.
* A franchises worst enemies are its biggest fans.
* 🖥️ i5 12600K | Z690 | 32GB | RTX 3070 | Define R6 | 48" 4K OLED | Win11 | 💻 Surface Go 3
* ⌨️ G915 Tactile | ⌨️ G13 | 🖱️ G502 X | 🎧 Pro X | 🎙️ QuadCast | 📹 EOS 550D | 🕹️ X52 Pro | 🎮 Xbox Elite 2
* 📱 Galaxy Fold4 | 🎧 Galaxy Buds Pro | ⌚ Galaxy Watch5 Pro | 📺 65" LG OLED | 🎞️ Nvidia Shield

Permalänk
Medlem

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)

Permalänk
Lego Master

@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'.

Visa signatur

* Vänsterhänt högerskytt med tummen mitt i handen.
* A franchises worst enemies are its biggest fans.
* 🖥️ i5 12600K | Z690 | 32GB | RTX 3070 | Define R6 | 48" 4K OLED | Win11 | 💻 Surface Go 3
* ⌨️ G915 Tactile | ⌨️ G13 | 🖱️ G502 X | 🎧 Pro X | 🎙️ QuadCast | 📹 EOS 550D | 🕹️ X52 Pro | 🎮 Xbox Elite 2
* 📱 Galaxy Fold4 | 🎧 Galaxy Buds Pro | ⌚ Galaxy Watch5 Pro | 📺 65" LG OLED | 🎞️ Nvidia Shield

Permalänk

@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

Permalänk
Lego Master

@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.

Visa signatur

* Vänsterhänt högerskytt med tummen mitt i handen.
* A franchises worst enemies are its biggest fans.
* 🖥️ i5 12600K | Z690 | 32GB | RTX 3070 | Define R6 | 48" 4K OLED | Win11 | 💻 Surface Go 3
* ⌨️ G915 Tactile | ⌨️ G13 | 🖱️ G502 X | 🎧 Pro X | 🎙️ QuadCast | 📹 EOS 550D | 🕹️ X52 Pro | 🎮 Xbox Elite 2
* 📱 Galaxy Fold4 | 🎧 Galaxy Buds Pro | ⌚ Galaxy Watch5 Pro | 📺 65" LG OLED | 🎞️ Nvidia Shield