Hjelp til SELECT setning.

[siDDis]

Eg har ein tabell med kolonner: id, tempratur, dag og tidspunkt

 

Eg skal kjøre ein SELECT setning som henter MAX tempratur og tidspunktet den finner MAX tempraturen på. Men eg sitter fast på korleis eg skal få henta tidspunktet.

 

Det blir noko sånt:

 

SELECT MAX(temprature) AS maxtemp, time_moment AS time
FROM wdata WHERE date = CURRENT_DATE

 

Korleis får eg gjort dette?

[roac]

Det enkleste er vel en subquery?

 

SELECT temprature AS , time_moment AS time
FROM wdata WHERE (date = CURRENT_DATE) and (temprature = (
 SELECT MAX(temprature) 
 FROM wdata WHERE data = CURRENT_DATE
))

 

Det kan også løses med en inner join.

[siDDis]

Takk, men her gjør du jo ein INNER JOIN når du seier at temprature = (MAX temprature) ikkje sant?

 

Ein INNER JOIN i bare ein tabell er bare =

 

Har du to tabeller så bruker du feks temptable.temptrature = temptable.maxtemprature. Det er ein måte å bruke INNER JOIN på, ikkje sant?

[Ueland]

Hvorfor gjøre det så avansert?

 

SELECT temprature AS maxtemp, time_moment AS time WHERE date = CURRENT_DATE ORDER BY temprature DESC LIMIT 1

 

(Ble usikker på om det er DESC eller ASC her i sommervarmen)

 

 

Grovt sagt, hent ut den første raden når resultatsettet er sortert på tempratur.

[roac]

Ein INNER JOIN i bare ein tabell er bare =

6294653[/snapback]

Unnskyld uttrykket, men dette er rent sludder.

Men en vanlig misoppfatning

 

Gitt følgende scenario:

Tabell A:

ID
----
1
2


Tabell B:

ID
----
1
2

 

Vi kan da tenke oss følgende:

select A.ID, B.ID from A inner join B on (A.ID = B.ID)

 

Denne spørringen vil returnere et resultatsett med en rad, der begge kolonnene har verdien 1.

 

Men, det er ingen ting som binder oss til å bruke = som sammenligningsoperator, vi kan f eks bruke != isteden:

 

select A.ID, B.ID from A inner join B on (A.ID != B.ID)

 

Denne spørringen vi returnere to rader, en med verdiene (1,3) og en med verdiene (2,1). Dette er også en inner join. Det som kjennetegner et inner join er at det eksisterer data som tilfredsstiller sammenligningen i begge tabeller, mao du vil ALDRI kunne få ut data hvor A.ID er NULL eller B.ID er NULL. Et join som tillater slikt er et outer join. Disse har vi tre av, left outer, right outer og full outer join. Der left og right sier hvilken side av "outer join" som kan være null. Ved full kan null være på begge sider.

 

Jeg håper dette var oppklarende.

 

Og til slutt: Ja, gamlemåten å skrive inner joins på er som du beskrev å bruke = eller en annen operator.

Endret 12. juni 2006 av roac

[roac]

Hvorfor gjøre det så avansert?

 

SELECT temprature AS maxtemp, time_moment AS time WHERE date = CURRENT_DATE ORDER BY temprature DESC LIMIT 1

 

(Ble usikker på om det er DESC eller ASC her i sommervarmen)

 

Grovt sagt, hent ut den første raden når resultatsettet er sortert på tempratur.

6294708[/snapback]

Med fare for at ting er annerledes i MySQL:

 

Løsningen din baserer seg på å opprette et resultatsett som så i sin tur sorteres, for så å returnere den første raden. En test i en annen databasemotor viser at sorteringen selv med små mengder data kan ta tre ganger så mye ressurser som selve uthentingen av dataene. En aggregeringsfunksjon som max trenger ikke å lage et sortert resultatsett, bare vite den høyeste verdien, og er derfor vesentlig mer effektiv enn den løsningen du skisserer. Den absolutt eneste fordelen å bruke DESC LIMIT 1 her er at koden er enklere å skrive.

[siDDis]

Nå bruker eg PostgreSQL og får 15ms på begge 2 forslaga i ein tabell med 100 000 rader. I ein mindre effektiv database så spele vell kanskje dette inn.

 

Nå ser eg heller ikkje heilt korleis eg kan skrive INNER JOIN med bare ein tabell.

Korleis ville ein lausning sett ut med det?

[Ueland]

Så lang tid(!), det virker for meg som at du har noen dårlige indekser der, tror da ikke at PostgreSQL bruker så lang tid? Ville da tro at 1,5 ms hadde hvertfall vært mer realistisk.

[roac]

Nå bruker eg PostgreSQL og får 15ms på begge 2 forslaga i ein tabell med 100 000 rader. I ein mindre effektiv database så spele vell kanskje dette inn.

 

Nå ser eg heller ikkje heilt korleis eg kan skrive INNER JOIN med bare ein tabell.

Korleis ville ein lausning sett ut med det?

6295964[/snapback]

select a.id, b.id from mintabell a inner join mintabell b on (b.id = a.parentid)

 

Dette er vanlig blant annet der trestrukturer er lagret i en og samme tabell, f eks en ansattabell der det er en fremmednøkkel til dennes sjef (som også er en ansatt).

 

Ang det du sier om samme tid på spørringene, så er det flere faktorer som spiller inn, deriblant hvor mye av dataene som allerede er i minnet, i hvilken grad dataene allerede er sortert, og ikke minst hvor effektivt where-biten snevrer inn utvalget. Du bør merke forskjell på målinger hvert sekund i forhold til hver time. Med mange verdier her vil sorteringen bli relativt tung.

 

Men som sagt, med forbehold om at forskjellige databasemotorer oppfører seg forskjellig.

 

Og til Ueland: Denne type tabeller (brukes til logging) er et klassisk eksempel på ting som man forsøker å begrense indekseringen på, all den tid det er mye innsettinger og sannsynligvis relativt lite uthenting av data. Overhead i forbindelse med innsetting blir sannsynligvis vesentlig større enn fortjenesten ved uthenting av data (Dette bør selvfølgelig måles). Clustered index på tidspunkt vil dog kunne være hensiktsmessig, siden denne verdien er strengt stigende, og dette vil (etter hvert) kunne gjøre utvalget mer effektivt.

[siDDis]

Nå gjor eg EXPLAIN SELECT i PSQL som gjekk djupare innpå måling av tid.

roac sitt forslag blir gjort på 6 ms i snitt. Ueland sitt brukte 6.9 ms i snitt.

Men etterkvart så enda spørringane på 0.15 ms, då blei dei troleg lagra i minnet

 

Nå skal eg få til min spørring med INNER JOIN for å sjå om den er noko meir effektiv.