Mysql Query, data från olika dagar från samma kolumn.

[PopUnoNkoK]

Hej

Jag vänder mig till er då jag vet att det finns många php/mysql experter här.

Jag har en tabell som ser ut så här.

Kod: Markera allt

id	datum			sensor	temp

7	2013-11-24 10:17:00	ute	-3.1875
8	2013-11-24 10:18:00	ute	-4.25
9	2013-11-24 10:19:00	ute	-4.375
10	2013-11-24 10:20:00	ute	-4.5

Jag skulle vilja hämta datan från olika dagar, jag ska försöka förklara hur jag menar.

Jag vill inte Summera eller ta medelvärde på tempdatan från varje dag, utan jag vill kunna sen få det till en array med varje teemperaturvärde från varje dag. Så som jag förstått det funkar det inte med Group by.

Tanken är att kunna (med hjälp av Jpgraph) visa en linjegraf som visar hur tempen sett ut de senaste 24 timmarna. Men OCKSÅ visa hur grafen såg ut föregående 24 timmar med ett svagt streck över samma graf.

Här är en bild hur det ser ut i programmet Logtemp http://www.mrsoft.fi/images/day0.png De streckade linjerna till höger i grafen är alltså värdet föregående dygn, trots att det i grafen ligger i "framtiden". Jag är dock ute efter att kunna lägga hela gårdagens graf som en svag linje över hela 24 timmarna.

Jag är ute efter något liknande denna PSEUDOKOD:

Kod: Markera allt

SELECT 
DATE_ADD(CURDATE(), INTERVAL -1 DAY) AS idag,
DATE_ADD(CURDATE()-1, INTERVAL -1 DAY) AS igår,
DATE_ADD(CURDATE()-2, INTERVAL -1 DAY) AS förrgår
;

Jag har googlat runt men känner att jag inte hittar några bra söktermer för det jag är ute efter.

Tacksam för hjälp.

MVH Peter

[X-IL]

GROUP BY var ju det naturliga svaret, men du säger att det inte fungerar för ditt behov? Kan du inte köra tre queries? Eller slå samman tre queries mot samma tabell i ett query?

Nja jag missförstår nog vad du vill ha, men du kan ju göra såna här saker t ex: SELECT * FROM Data AS Idag, Data AS Igår, Data AS Förrgår WHERE Idag.Date= datum AND Igår.Date=datum-1 osv...

[sodjan]

Det är kanske enklare att se vad du vill ha om du kan presentera
ett hypotetiskt tabellutseende och ett resultatet.

[PopUnoNkoK]

Här har vi en bild

(Lånad bild)

Den vågräta axeln där nere har jag inga datum, utan bara timmar.

Nu skulle jag alltså vilja lägga till en eller två till linjer på samma graf.

Typ



Men datan till de olika strecken ska alltså vara från samma kolumn i tabellen (Temp kolumnen) Men från olika tider.
Så att ett av strecken i grafen ovan representerar de senaste 24 timmarna. (0-24 timmar tillbaka) Det andra strecket representerar Ytterligare 24 timmar tillbaka (24 -48 timmar tillbaka). Och sedan eventuellt ett tredje streck.

Hmmm... Jag lyckas verkligen inte beskriva så bra. Vi får se om det har klarnat för er nu. =)

[johano]

Ok, här är ett försök (gjort i SQLServer, men tror det borde funka i mySQL också)

Gissar också att du vill aggregera värdena per tim-nivå, detta görs med AVG(..) och GROUP BY på timmen.
Den första kladdiga deriverade tabellen med alla SELECT UNION är bara till för att få 24 rader, en per timme.
Detta gör att du alltid får en rad för varje timme även om din Temptabell skulle sakna värden för någon timme.

Du skulle kunna skapa en tabell HRS med kolumnen H (int) och sen skriva in värdena 0 => 23 i den så kan
du skippa hela denna konstruktion.

Den sista kluriga sorteringen är till för att få ordningen på timmar att börja med nuvarande klockslag och sen "wrappa runt" vid 23 så att timmar med "lägre" nummer kommer sist, t.ex. kl 07 igår ska hamna efter kl 09 idag.

Ta det för vad det är värt.


Antaget är att din tabell heter "Temp":

Kod: Markera allt

select HRS.h, AVG(LAST24.temp) as "LAST24", AVG(LAST48.temp) as "LAST48", AVG(LAST72.temp) as "LAST72"
from
(
  -- detta är ett litet hack för att få en rad för varje timme, även om det saknas data i Temp-tabellen för timmen ifråga...
  select 0 union  select 1 union select 2 union select 3 union select 4union select 5union select 6union select 7 union select 8 union select 9 union select 10 union select 11 union 
  select 12 union select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union select 19 union select 20 union select 21 union select 22 union select 23
) as HRS(h)
left join
(
	select DATEPART(hh, datum) as "h", temp from Temp where DATEDIFF(hh, GETDATE(), datum ) BETWEEN -23 AND 0
) as LAST24(h, temp) on HRS.h = LAST24.h
left join
(
	select DATEPART(hh, datum) as "h", temp from Temp where DATEDIFF(hh, GETDATE(), datum ) BETWEEN -47 AND -24
) as LAST48(h, temp)  on HRS.h = LAST48.h
left join
(
	select DATEPART(hh, datum) as "h", temp from Temp where DATEDIFF(hh, GETDATE(), datum ) BETWEEN -71 AND -48
) as LAST72(h, temp)  on HRS.h = LAST72.h
GROUP BY HRS.h
ORDER BY CASE WHEN HRS.h-DATEPART(hh, GETDATE()) >= 0 THEN HRS.h-DATEPART(hh, GETDATE()) ELSE 24+HRS.h-DATEPART(hh, GETDATE()) END

/johan

[Nerre]

Du vill alltså att:

Mån 8:00 A
Mån 16:00 B
Mån 24:00 C
Tis 8:00 D
Tis 16:00 E
Tis 24:00 F
Ons 8:00 G
Ons 16:00 H
Ons 24:00 I

blir

8:00 A D G
16:00 B E H
24:00 C F I

[johano]

Exakt så tolkade jag också att resultatet skulle bli

/johan

[adent]

Det allra enklaste är att hantera det programmatiskt.

Gör tre queries och spara datat i tre olika arrayer och skicka sen in det till grafritarfunktionen.


MVH: Mikael

[Nerre]

Ja, den lösningen hanterar ju även situtationen där det nån dag kanske saknas data från ett visst klockslag eller där klockslagen inte är exakt samma. SQL-lösningen kräver ju att det finns data för exakt samma klockslag alla dagar.

[Pinkevin]

Jag tror att det du önskar kallas för en pivot-tabell. Operationen som Nerre visar är åtminstone en pivotering.
Tyvärr känner jag inte till MySQL syntax för det, men Google kanske kanske hjälpa dig.

[Millox]

En månad sen men men, bättre sent än aldrig.

Förutsättningar:
Tabell
Id int p_key auto_increment,
Date datetime,
Sensor int,
Value float

Villkor:
Det måste finnas ett värde för varje tillfälle i tabellen under den aktuella dagen.

Select a.date, a.value, b.value, c.value from
Tabell a left join tabell b on a.date > b.date - 24 h - tolerans and a.date < b.date - 24 h + tolerans left join tabell c on a.date > c.date -48 h - tolerans and a.date < c.date - 48 h + tolerans where <villkor>

Toleransen sätter du baserat på vad du har för noggrannhet isamplingen. Om det alltid är identiskt lika ner på sekundnivå kan du bara använda = istället för två villkor med < > mellan.

Hur du implementerar -24h eller -48h beror av din dialekt, date_sub kanske det är i mysql och denna använder du även för toleransen.

Efter jag skrev allt kom jag på att du kanske vill ha join-villkor även på sensor-id, men det kan du nog ordna själv antar jag.

Left join tar alltså alla värden ur vänstra tabellen och joinar med de värden som matchar ur högertabellen. Finns det inget värde i högertabellen sätts den kolumnen till null.

Self-join är något att googla på