Keskustelu: Nettisivujen teko: MySQL/PHP - Arvojen muutos

Minulla on SQL taulu jossa on päivittäin muuttuvia datasarakkeita parikymmentä kappaletta. Nyt pitäisi jotenkin fiksusti saada tehtyä kysely, josta saisi helposti muutokseen edelliseen päivään (tai määriteltyyn päivään) nähden (arvo/%).

MySQL:

date        data_1  data_2
2007-12-01      11      12
2007-12-02       9      13
2007-12-03      10       9

Haettu tulostus:
2007-12-03 +1 -4
2007-12-02 -2 +1
2007-12-01

En itse onnistu kehittämään muuta ratkaisua, kuin julmettu määrä erillisiä kyselyitä, jossa arvoja verrataan, joten parempaa ratkaisua kaipaisin.

Jos vaan laskisit muutoksen (ts. erotus, ns. miinuslasku) ihan php:ssä taulukon edellisestä arvosta.

Simppeli for-lause, jossa käyt taulukon solut läpi ja vähennät edellisen arvon solujen arvot samalla.

Leben idea on huomattavasti parempi, mutta oli pakko kokeilla miten tuon saisi kyselyllä tehtyä :)

SELECT
	pvm,
	data1 - (SELECT data1 FROM test AS r2 WHERE r1.pvm > r2.pvm ORDER BY r2.pvm DESC LIMIT 0, 1) AS Diff1,
	data2 - (SELECT data2 FROM test AS r2 WHERE r1.pvm > r2.pvm ORDER BY r2.pvm DESC LIMIT 0, 1) AS Diff2
FROM test AS r1

Jos taulussa on id-kenttä (ja se on vielä indeksoitu), niin tuosta voi saada jopa tehokkaan, ku ei tartte alikyselyissä hakea kaikkia rivejä ja sortata, vaan voi viitata suoraan yhteen riviin: WHERE r2.id = r1.id - 1

Tulos tuolla testikyselyllä:

pvm 	Diff1 	Diff2
2007-12-01 	NULL 	NULL
2007-12-02 	-2 	1
2007-12-03 	1 	-4

Edit:

Niin ja sitten kun tuon koko roskan piilottaa näkymän taakse näin:

CREATE VIEW Datadiff AS
SELECT
	pvm,
	data1 - (SELECT data1 FROM test AS r2 WHERE r1.pvm > r2.pvm ORDER BY r2.pvm DESC LIMIT 0, 1) AS Diff1,
	data2 - (SELECT data2 FROM test AS r2 WHERE r1.pvm > r2.pvm ORDER BY r2.pvm DESC LIMIT 0, 1) AS Diff2
FROM test AS r1

Niin tuloksen saa helpon näköisesti clienttipuolella näin:

SELECT pvm, Diff1, Diff2 FROM Datadiff

En ota kantaa kuinka järkevää tämä kaikki on... :)

Edit2: Testasin indeksoitu Id versus date, dataa 2000 riviä, SQL: SELECT pvm, Diff1, Diff2 FROM Datadiff LIMIT 0, 30, tulos:
date 5.4823 s
Id: 0.0393 s

ajv:lle suuri kiitos.

Tuo kun on päivittäinen statistiikkataulu, ei sinne tule kuin yksi rivi päivässä lisää; id primääri ja date uniikki, joten melko vauhdilla tuo puskee dataa pihalle (nopeusero on huomattava verrattuna tuohon PHP-viritykseeni).

Hieman meinasi vain tuottaa harmaita hiuksia, kun kaikki arvot tuolla statistiikka taulussa oli UNSIGNED, ja tämän takia tuolla VIEW:ssä arvot oli ihan mitä sattuu, ei meinannut ongelma aueta mitenkään. Nyt kuitenkin toimii. Kiitoksia tästä.

Eipä kestä :) Päivitithän nuo alikyselyt käyttämään sitä id-kenttää:

SELECT data1 FROM test AS r2 WHERE WHERE r2.id = r1.id - 1

Jostain syystä tuo on nopeampi tuolla date::lla kuin id:llä, ero tyyliin 0.004 sekuntia jokaisella haulla daten hyväksi, mutta eipä taulussa ole vasta kuin kolme testiriviä.

Toisaalta tuo date on myös uniikki, joten onhan sekin? indeksoitu.

Vaihdoinpa nyt kuitenkin alikyselyt käyttämään tuota id-kenttää... ;)

Juu, ei sitä nopeuseroa 3 rivillä vielä huomaakkaan, mutta rivien lisääntyessä tuo id-kyselyn aika pysyy aina samana, mutta date-kyselyn aika kasvaa suoraan suhteessa siihen kuinka paljon kannassa on rivejä ja kuinka paljon näkymä-taulussa on noita alikyselyllä tehtyjä kenttiä. date-kentän indeksillä ei tässä tapauksessa ole merkitystä, koska:

SELECT data1 FROM test AS r2 WHERE r1.pvm > r2.pvm ORDER BY r2.pvm DESC LIMIT 0, 1

hakee taulusta kaikki rivit, järjestää ne päivämäärän mukaan ja antaa ulos ensimmäisen tuloksen tästä tulosjoukosta.

SELECT data1 FROM test AS r2 WHERE WHERE r2.id = r1.id - 1

hakee vain ja ainoastaan yhden rivin ja senkin aika sutjakasti, koska hakuehdossa on käytetty primary key -kenttää.

P.S. Huomaa, et oon lomalla ku jaksaa notkuu netissä auttelemas immeisiä viel tähän aikaan näinkin perusteellisilla selityksillä :)