Keskustelu: Ohjelmointikysymykset: SQL -suorituskerrat

Hei,

Minulla olisi seuraavanlainen SQL -haku ongelma:

Miten saada seuran kilpailijoiden suorituksia niin, että laskisi niiden
kilpailijoiden määrän, jotka ovat suoriutuneet tehtävästä ensimmäisellä
yrittämällä?

Esim. tällaisesta tietojoukosta:

Seura	KilpNRO	Suorituserä	Hyl=0/Hyv=5
-----------------------------------------------------
Erä	    123	    1		    0
Erä	    123	    2		    5
Sarja	234	    3		    5
Sarja	345	    4		    5
Sarja	456	    5		    0
Juttu	567	    6		    0
Joiku	678	    7		    5
Joiku	678	    8		    5

Tuloste pitäisi mennä näin:

Seura	|Heti suoritetut 	|Suorituskertoja
--------------------------------------------------
Erä	    0		 	         2
Sarja	2		             3
Juttu	0			         1
Joiku	0			         2

Selitys tulosteelle:
Erä: sama kilpailija suorittanut kaksi kertaa = 0 heti suoritettua.
Sarja: 234 ja 345 suorittaneet heti, 456 hylätysti = 2 heti suoritettua.
Juttu: 567 suor hylätysti = 0 heti suoritettua.
Joiku: sama kilpailija suorittanut kaksi kertaa hyväksytysti = 0 heti suoritettua.

'Heti suoritetuksi' lasketaan siis vain sellainen suoritus, jossa tietyllä
kilpailijalla on VAIN YKSI HYVÄKSYTTY suoritus.

Miten päätyä oikeanlaiseen tulosteeseen?

t.jj

Tuo on hieman hämäävää, ettei 678:n suoritusta lasketa "heti suoritetuksi" vaikka ekalla suorituskerralla tulikin 5. Tosin on myös outoa, miksi hän suoritti sen uudelleenkin.

Tein MS SQL Serverillä seuraavalla taulumääritteellä

create table Suoritus (
	Id int identity primary key,
	Seura nvarchar(20) not null,
	KilpNRO int not null,
	Suorituserä int not null,
	Tulos int not null
)

Ja haluttu tulos saatiin seuraavalla kyselyllä:

WITH ES AS (select Seura from Suoritus GROUP BY Seura, KilpNro HAVING COUNT(KilpNro)=1 AND MIN(Tulos)=5)
, ESM AS (SELECT Seura, COUNT(Seura) KPL FROM ES GROUP BY Seura)
, Kok AS (SELECT Seura, COUNT(Seura) KPL FROM Suoritus GROUP BY Seura)
SELECT Kok.Seura, COALESCE(ESM.KPL,0) [Heti suoritetut], Kok.KPL Suorituskertoja FROM Kok LEFT JOIN ESM ON Kok.Seura=ESM.Seura

Voisin vielä hieman selventää noita temporary viewejä joita käytin:
ES eli "ensimmäissuoritukset" listaa niiden kilpailijanumeroiden seurat, joilla on vain 1 suoritus ja jonka tulos on 5
ESM eli "ensimmäisuoritusten määrä" laskee ES-näkymän suoritusten määrän per seura
Kok eli "kokonaissuoritukset" laskee montako suoritusta kullakin seuralla on

Siinä on lausetta kerrakseen :)
Tuollaisenaan ei taipune ASP.NET:n tai PHP:n sisään, vaikka yritin tuosta soveltaa.

678 ovat tapauksia, jotka ovat halunneet suorittaa uudelleen (tai harvemmin muuten jälkikäteen tulos mitätöity). Kantaan sitä ei korjata ainakaan toistaiseksi, vaan se näkyy tuollain tupla (tai enemmänkin) -hyväksytty suorituksina. Tämähän oisi helpompi ratkaista ilman näitä rivejä.

-jj

Vaikka suorittaisi uudestaan, eikö henkilö ole silloin kuitenkin suorittanut hyväksytysti myös ensimmäisellä yrityksellä?

En jaksa kikkailla yhtenäistä kyselyä, mutta ainakin erillisillä kyselyillä haku on hyvin helppo.

Suorituskerrat saa ihan tavallisella ryhmittelyllä.

SELECT Seura, COUNT(*) AS Suorituskertoja
FROM Suoritus
GROUP BY Seura

Ensimmäisellä yrityksellä onnistumiset saa, kun ottaa kyselyyn LEFT JOINilla mukaan saman henkilön aiemmat yritykset ja tarkistaa WHERE-kohdassa, ettei niitä löydy (eli aiempi tulos on NULL).

SELECT Seura, COUNT(*) AS Heti
FROM Suoritus
LEFT JOIN Suoritus AS Aiempi
	ON Aiempi.KilpNRO = Suoritus.KilpNRO
	AND Aiempi.Suorituserä < Suoritus.Suorituserä
WHERE Aiempi.Tulos IS NULL AND Suoritus.Tulos = 'Hyväksytty'
GROUP BY Seura

Jos kuitenkin olet sitä mieltä, että kaksi hyväksyttyä suoritusta ei kelpaa, vaihda <-merkin tilalle epäsuuruus eli <>.