MySQL Query begrenzen aan inputkant (met Where?)

[ericplan]

Na een hoop gezoek (hier en op Stackoverflow) en gefröbel is het me gelukt een werkende query met running totals te maken. De basis is een database met tellerstanden die regel voor regel afgevraagd worden. Het verschil is de opbrengst per 5 minuten en het maximumvermogen. De running total geeft de dagopbrengst. De tabel heet logger, de meetwaarden S0_1 enz. en Datum_Dag is een datetime waarde, die elke vijf minuten 1x voorkomt.
Met de functie EXPLAIN ben ik erachter waarom deze query zo traag is, eerst worden alle 440.000 records gelezen en pas op de output wordt het Datum-filter gebruikt. Dat moet natuurlijk aan de inputkant gebeuren, alleen lukt dat me niet om dat werkend te krijgen. Ik dacht iets gevonden te hebben met http://stackoverflow.com/...4418/mysql-subquery-limit

code:

DELETE 
FROM posts 
WHERE id not in (
      SELECT * FROM (
            SELECT id 
            FROM posts 
            ORDER BY timestamp desc limit 0, 15
      ) 
      as t);

maar ik krijg deze constructie niet werkend in mijn query. Ik zie door de haakjes het bos niet meer en zou graag een tip of oplossingsrichting krijgen. Onderstaand mijn (voor leesbaarheid iets vereenvoudigde) query.

code:

select t1.Datum_Dag as Date1, 
(t2.S0_1 - t1.S0_1) as `101`, (t2.S0_1 - t1.S0_1) as `201` , @running_total1 := 12 as `301`,
(t2.S0_2 - t1.S0_2) as `102`, (t2.S0_2 - t1.S0_2) as `202` , @running_total2 := 13 as `302`,
(t2.S0_3 - t1.S0_3) as `103`, (t2.S0_3 - t1.S0_3) as `203` , @running_total3 := 14 as `303`,
@prev_day <> DATE(t1.Datum_Dag) , @prev_day := DATE(t1.Datum_Dag) , @prev_day := DATE(t1.Datum_Dag)  
from    (select @rownumt1:=@rownumt1+1 samplenr, Datum_Dag, S0_1, S0_2, S0_3, T_1 from logger, (select @rownumt1:=0) dummy1 order by Datum_Dag)t1,
        (select @rownumt2:=@rownumt2+1 samplenr, Datum_Dag, S0_1, S0_2, S0_3, T_1 from logger, (select @rownumt2:=0) dummy2 order by Datum_Dag)t2 
cross JOIN (SELECT @prev_day:=-1 , @running_total1 := 0, @running_total2 := 0, @running_total3 := 0 ) r
Where t2.samplenr = t1.samplenr +1 and t2.S0_1 - t1.S0_1 !=0 and  Datum_Dag Between '2017-04-16 00:05:00' AND '2017-04-21 23:55:00' 
ORDER BY `Date1`

En een stukje van de database:

code:

`logger` 
(`Datum_Dag`, `S0_1`, `S0_2`, `S0_3`, `S0_4`, `S0_5`, `S0_6`, `S0_7`, `S0_8`, `S0_9`, `T_1`) 
('2017-04-24 13:05:00', '8843.759', '8707.556', '1057.483', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.193', '128'),
('2017-04-24 13:10:00', '8843.777', '8707.573', '1057.486', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.327', '133'),
('2017-04-24 13:15:00', '8843.796', '8707.590', '1057.490', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.399', '125'),
('2017-04-24 13:20:00', '8843.816', '8707.609', '1057.494', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.471', '124'),
('2017-04-24 13:25:00', '8843.837', '8707.629', '1057.499', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.541', '128'),
('2017-04-24 13:30:00', '8843.850', '8707.641', '1057.502', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.619', '127'),
('2017-04-24 13:35:00', '8843.864', '8707.654', '1057.505', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.697', '130'),
('2017-04-24 13:40:00', '8843.875', '8707.665', '1057.507', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.777', '129'),
('2017-04-24 13:45:00', '8843.884', '8707.672', '1057.509', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.860', '129'),
('2017-04-24 13:50:00', '8843.894', '8707.682', '1057.511', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1831.941', '127'),
('2017-04-24 13:55:00', '8843.908', '8707.695', '1057.514', '41.122', '542.702', '9313.000', '1544.879', '1537.714', '1832.018', '133'),

[Verwijderd]

LIMIT is om het absolute aantal records dat teruggegegeven wordt te beperken - handig voor als je iets met een pager wilt, bijvoorbeeld.

In jouw sitautie spelen er andere dingen. Ten eerste gebruik je in je FROM-clause twee keer een SELECT zonder WHERE (dus alle records ophalen), en vervolgens laat je daar een CROSS JOIN op los (vermenigvuldig aantal records met 4). Van dat resultaat wordt vervolgens met de WHERE die verderop staat het aantal records beperkt.

Kijk eens naar INNER JOIN, en handel de running totals en dergelijke lekker in je code af. Controleer ook even op er op de juiste kolommen indexen staan - ontbreken daarvan maakt alles dramatisch trager.

[johnkeates]

Dit ziet er uit als time series data, is dat niet meer iets voor een database die daar goed in is?

[ericplan]

johnkeates schreef op zondag 30 april 2017 @ 16:23:
Dit ziet er uit als time series data, is dat niet meer iets voor een database die daar goed in is?

Ik zit vast aan mysql, het draait bij een externe partij waar ik wel over mysql-databases kan beschikken, niet over andere smaken.

[Dido]

Je hebt je query iets vereenvoudigd voor leesbaarheid, maar leesbaar is hij niet. Het is me niet duidelijk wat je met deze query wilt bereiken.

Ten eerste selecteer je 101, 102, 103 die hetzelfde zijn als 201, 202, 203.
Ten tweede, wat doet @prev_day <> DATE(t1.Datum_Dag) in je select? Da's een boolean.
Ten derde, waarom gebruik je implicit join clauses in plaats van explicit (select from A join B ON a.id = b.a_id)?
Ten vierde, een cross join is een red flag, of je die nou per ongeluk (select * from A, of expliciet toevoegt. Hoeft geen probleem te zijn, maar het kan vaak anders.
Ten vijfde vraag ik me af of je voor de leesbaarheid ergens een group by hebt weggelaten.

Ik vermoed dat dit met duidelijke requirements een stuk eenvoudiger kan. Het zou best kunnen dat je sowieso sneller af bent met een paar losse queries dan proberen alles in 1 enkele ingwikkelde query te gooien. Je lijkt in je query al bezig te zijn met hoe je de data wilt presenteren en dat is eigenlijk altijd een red flag. Data ophalen en data presenteren zijn verschillende sporten!

[ericplan]

Simpel gezegd is het doel om twee opeenvolgende meterstanden te vergelijken en daar een paar berekeningen op los te laten, zoals het vermogen berekenen en een running total per dag per kWh-meter. Het is de bedoeling dat die berekende gegevens in een andere database komen, waarbij lege regels geschrapt worden e.d. Deze code was beschikbaar, vandaar dat ik er mee verder gegaan ben, maar ik begin te begrijpen dat het anders moet.

[Biersteker]

Heb je dan niet meer aan zoiets?
http://stackoverflow.com/...ate-lag-function-in-mysql
(of in postgresql lag function)

[ Voor 13% gewijzigd door Biersteker op 30-04-2017 17:54 ]

[ericplan]

Op basis van Biersteker aan de slag:

code:

SELECT t1.S0_1, t1.Datum_Dag,
       (SELECT t2.S0_1 - t1.S0_1 FROM logger AS t2 WHERE t2.Datum_Dag > t1.Datum_Dag ORDER BY t2.datum_Dag LIMIT 1) AS Wp
        FROM logger AS t1

Query voert snel uit, maar begrenzen of bladeren loopt fout. Zodra ik onderstaande code toevoeg zegt EXPLAIN dat het aantal rijen begrensd is tot circa 3200, maar bij de echte query krijg ik een time-out, ook als ik naar het laatste record wil. Het lijkt alsof de begrenzing niet werkt.

code:

SELECT t1.S0_1, t1.Datum_Dag,
       (SELECT t2.S0_1 - t1.S0_1 FROM logger AS t2 WHERE t2.Datum_Dag > t1.Datum_Dag ORDER BY t2.datum_Dag LIMIT 1) AS Wp
        FROM logger AS t1
        Where Datum_Dag Between '2017-04-16 00:05:00' AND '2017-04-21 23:55:00'

[MSalters]

"de meetwaarden S0_1 enz."

Dan verbaast het me niet dat je een probleem hebt. Genummerde kolommen zijn vrijwel altijd een indicatie dat je een tabel design probleem hebt, en dan heb je ook altijd problemen met queries.

Ik zit ook al even naar die query te staren, maar zelfs met die query én je beschrijving heb ik nog steeds geen enkel idee wat je eigenlijk probeert te doen. Zo'n opmerking als "De basis is een database met tellerstanden die regel voor regel afgevraagd worden" helpt niet echt; databases werken niet "regel voor regel".

[ericplan]

MSalters schreef op woensdag 3 mei 2017 @ 11:20:
"de meetwaarden S0_1 enz."

Dan verbaast het me niet dat je een probleem hebt. Genummerde kolommen zijn vrijwel altijd een indicatie dat je een tabel design probleem hebt, en dan heb je ook altijd problemen met queries.

Ik zit ook al even naar die query te staren, maar zelfs met die query én je beschrijving heb ik nog steeds geen enkel idee wat je eigenlijk probeert te doen. Zo'n opmerking als "De basis is een database met tellerstanden die regel voor regel afgevraagd worden" helpt niet echt; databases werken niet "regel voor regel".

De meetwaarden komen uit een 9-kanaals registratie-apparaat voor S0-devices (http://www.emuag.ch/en/data-loggers/emu-s0-impuls-logger/), zoals watermeters, warmtemeters, kWh-meters. De kanalen in dat apparaat zijn genummerd S0_1 t/m S0_9.

Die waarden worden per 5 minuten weggeschreven naar een csv-bestand en van daaruit in een database gezet die dezelfde namen hanteert als het bronapparaat. Met regel voor regel bedoel ik een datum/tijdcombinatie met bijbehorende meterstanden.

Drie kanalen van deze meter zijn gebruikt voor het meten van opbrengst van de PV-panelen. De overige kanalen houden andere dingen bij zoals waterverbruik, maar dat wil ik niet per se op ieder tijdstip kunnen zien.

Voor de opbrengst van de PV-panelen wordt het programma ZonPHP gebruikt. Dat is destijds als hobbyproject gebouwd door http://www.slaper.be/index.php

Het programma wordt niet meer onderhouden, werkt met MySQL, maar niet met MySQLi. Werkt met PHP 5.x maar niet met PHP 7.x en presenteert de grafieken met Flash (OpenFlashCharts), niet met HTML5.

De makers van het programma hadden geen verstand van SQL, vandaar dat de database veel groter is dan nodig en veel redundantie bevat. Ik heb er ook (te) weinig verstand van, maar probeer iets te maken, waarmee ik m'n oude data kan migreren naar een nieuwe omgeving en weer even vooruit kan.

Daarbij zit ik vast aan het gereedschap dat mijn webhoster nu beschikbaar heeft en dat is MySQLi en PHP7.

Uit jullie antwoorden begrijp ik dat ik het beste aan de slag kan met een Inner Join. Daar heb ik inmiddels ook wat over opgezocht en dat ga ik dus maar proberen. Maar het is voor mij onbekend terrein en lastige materie.

[Orion84]

Dus je hebt een tabel met meterstanden (meerdere meters, een regel per 5 minuten) en die wil je omrekenen naar de opbrengst per 5 minuten (dus verschil tussen vorige meterstand en huidige meterstand) en dan nog iets van een dagtotaal en een max. opbrengst?

Is het dan niet veel handiger om gewoon de hele dag binnen te halen uit de database en daar in een for loopje doorheen te wandelen, waarbij je de meterstanden omrekent naar opbrengst en terwijl je er doorheen loopt ook een max. en totaal bijhouden?

[ericplan]

Orion84 schreef op woensdag 3 mei 2017 @ 14:39:
Dus je hebt een tabel met meterstanden (meerdere meters, een regel per 5 minuten) en die wil je omrekenen naar de opbrengst per 5 minuten (dus verschil tussen vorige meterstand en huidige meterstand) en dan nog iets van een dagtotaal en een max. opbrengst?

Is het dan niet veel handiger om gewoon de hele dag binnen te halen uit de database en daar in een for loopje doorheen te wandelen, waarbij je de meterstanden omrekent naar opbrengst en terwijl je er doorheen loopt ook een max. en totaal bijhouden?

Er zijn een paar sites die live-data weergeven. ZonPHP synchroniseert met PVOutput en je kan dus live je opbrengst volgen.

De query heeft twee doelen:

• Het eenmalig migreren van de bestaande database (mag in hapklare brokken)
• Produceren van live-data per 5 minuten

[Orion84]

Maar ben je nu aan het proberen van scratch iets te schrijven? Is het niet veel makkelijker om gewoon ZonPHP aan te passen zodat het met PHP7 en MySQLi werkt? Dat verandert namelijk op zich niks aan de queries volgens mij?

[BlueZero]

Maar welke gegevens probeer je precies uit deze database te halen. Ik kan wel een paar oplossingen bedenken maar weet niet precies wat je allemaal zou willen zien.

Vb. opbrengst per dag:

MySQL:

SELECT 
    DATE(Datum_Dag) as dag,
    MAX(S0_1) - MIN(S0_1) as opbrengst
FROM logger 
GROUP BY DATE(Datum_Dag);

Vb. opbrengst per uur:

MySQL:

SELECT 
    DATE(Datum_Dag) as dag,
    HOUR(Datum_Dag) as hour,
    MAX(S0_1) - MIN(S0_1) as opbrengst
FROM logger 
GROUP BY DATE(Datum_Dag), HOUR(Datum_dag);

Vb. gemiddelde per 5 minuten per dag:

MySQL:

SELECT 
    DATE(Datum_Dag) as dag,
    (MAX(S0_1) - MIN(S0_1)) / COUNT(*) as gem
FROM logger 
GROUP BY DATE(Datum_Dag);

In principe is jouw running total vrij simpel omdat je zeker weet dat de meterstand altijd oploopt (ga ik vanuit). Dus je kunt gewoon een MAX() - MIN() doen.

[Verwijderd]

ericplan schreef op woensdag 3 mei 2017 @ 14:23:
Het programma wordt niet meer onderhouden, werkt met MySQL, maar niet met MySQLi. Werkt met PHP 5.x maar niet met PHP 7.x en presenteert de grafieken met Flash (OpenFlashCharts), niet met HTML5.

Maak eens een kopie, en doe een globale zoek en vervang waarbij je 'mysql_' vervangt door 'mysqli_' - goede kans dat het dan weer werkt!

Waarom? In PHP zijn de mysql_* functies de manier waarop je de MyQSL database benaderd. Die library is verouderd, en daarom in PHP7 helemaal uitgeschakeld. De mysqli_* functies doen hetzelfde, maar het is een andere, veiliger library die wel ondersteund wordt. Het heeft dus niks met de database zelf te maken.

Dan heb je je grafieken nog niet opgelost, maar wel je database.

[Aetos]

@BlueZero

Mis je zo niet het verbruik tussen de laatste meting en de meting van de volgende dag/ uur?

[ericplan]

BlueZero schreef op woensdag 3 mei 2017 @ 15:50:
Maar welke gegevens probeer je precies uit deze database te halen. Ik kan wel een paar oplossingen bedenken maar weet niet precies wat je allemaal zou willen zien.

In principe is jouw running total vrij simpel omdat je zeker weet dat de meterstand altijd oploopt (ga ik vanuit). Dus je kunt gewoon een MAX() - MIN() doen.

Het gaat om deze drie waarden:

code:

concat(round(3600000*(t2.S0_1 - t1.S0_1)/TIME_TO_SEC(TIMEDIFF( t2.Datum_Dag,t1.Datum_Dag )))) as `101`, round(1000*(t2.S0_1 - t1.S0_1)) as `201` ,
@running_total1 := if(@prev_day <> DATE(t1.Datum_Dag), 0,@running_total1 + round(1000*(t2.S0_1 - t1.S0_1))) as `301`,

t2.Datum_Dag en t1.Datum_Dag zijn in deze code opeenvolgende tijdstippen, in principe vijf minuten, maar bij fouten (zoals een stroomstoring) mogelijk anders.

101 is het vermogen in W,
201 is de Wh per 5 minuten
301 is de Wh per dag

[BlueZero]

ericplan schreef op woensdag 3 mei 2017 @ 16:09:
[...]

101 is het vermogen in W,
201 is de Wh per 5 minuten
301 is de Wh per dag

In principe heb je 301 met mijn vorige query, echter waarom maak je geen Trigger op een INSERT die een paar velden vult met het vermogen en een running total voor de huidige dag.

Ander optie is het doorlopen van een resultset en ondertussen een variabele zetten:

MySQL:

set @prev_s0_1 := 0.0;
set @prev_time := '2017-04-24 00:00:00';
set @running_total := 0.0;
set @wh_5min := 0.0;

SELECT 
    concat(round(3600000*(S0_1 - @prev_s0_1)/TIME_TO_SEC(TIMEDIFF( Datum_Dag,@prev_time )))) as '101',  
    @wh_5min := round(1000*(S0_1 - @prev_s0_1)) as `201`,
    IF(DAY(Datum_Dag) != DAY(@prev_time), @running_total = @wh_5min, @running_total := @running_total + @wh_5min) as running_total,
    Datum_Dag,
    @prev := S0_1 as current,
        @prev_time := Datum_Dag
FROM logger 
WHERE Datum_Dag 
Between '2017-04-24 00:05:00' AND '2017-04-26 23:55:00';

Bovenstaande code kan netter, namelijk initiele prev_s01 zetten kan beter alsook de prev_time maar ik ga er vanuit dat dat wel moet lukken. Ik heb de code ook niet getest maar dit zou ongeveer moeten werken.

[ Voor 55% gewijzigd door BlueZero op 03-05-2017 16:51 ]

[ericplan]

Ik ga hiermee aan de slag, dank. Ben helaas te druk met werk nu, zal wel weekend worden voor het eerste probeersel.