[SQL] uitsluiten indien binnen 5 minuten opnieuw

[easydisk]

hallo,

Ik heb een tabel in een database waar ik SQL queries op af wil vuren,

code:

ID  datum                        object                 storing 
73745   11/12/2008 10:59:00 AM  000625  29570   
73746   11/12/2008 11:03:00 AM  000625  29542
73747   11/12/2008 11:15:00 AM  000625  29537

Nu wil ik alle regels selecteren waarbij ik het zo wil dat *per object* een storing (willekeurig) maar 1x geteld wordt in een periode van 5 minuten.

Uit bovenstaande wil ik ID 73745 en 73747 terug krijgen, want 73746 is namelijk binnen 5 minuten na de eerste storing (dat het een ander storings nhummer is maakt niet uit)

Hoe krijg ik dit voor elkaar ?

[Herko_ter_Horst]

Ik zou gewoon alle records opvragen en in de opvragende code de timestamps vergelijken en zodoende de records filteren.

Het kan ongetwijfeld ook helemaal in SQL (welk dialect/welke DB gebruik je?).

[Voutloos]

Groeperen op sommetje op het datetime field...

[pedorus]

Ik zou iets doen als:

SQL:

... where not exists (select * from tabelnaam as t2 where [minder dan 5 min verschil])

[Janoz]

@pedorus: Je criterium is niet helemaal correct. Storingen die binnen 5 minuten van elkaar vallen komen dan helemaal niet meer in het resultaat terug. Daarnaast 'lekt' je criterium ook. 3 storings meldingen met telkens 4 minuten ertussen worden ook samen genomen.

* Janoz is verder met Herko. Ik denk dat je dit makkelijker programmatisch op kunt lossen. Zorg dat je je gegevens in een handige volgorde binnenkrijgt (sorteren op object en tijd) en je kunt heel makkelijk door je resultset lopen en het resultaat opbouwen.

SQL is echt een set taal. Je kunt er heel krachtig verzamelingenlogica op toepassen. De resultaten die de topicstarter wil zijn daarentegen niet echt verzamelingoperaties.

[DigiK-oz]

Oracle heeft hiervoor de LAG en LEAD functies (kijken naar vorig resp volgend record). Of iets dergelijks ook in andere DB-engines zit weet ik niet.

[pedorus]

Janoz schreef op maandag 12 januari 2009 @ 14:55:
@pedorus: Je criterium is niet helemaal correct. Storingen die binnen 5 minuten van elkaar vallen komen dan helemaal niet meer in het resultaat terug.

Klopt, de omschrijving is niet helemaal ok. Ik bedoel dus iets als

SQL:

t1.datum between t2.datum and t2.datum+interval 5 minute

Daarnaast 'lekt' je criterium ook. 3 storings meldingen met telkens 4 minuten ertussen worden ook samen genomen.

Dat lijkt me ook 1 storing, hoewel het niet duidelijk is beschreven.

* Janoz is verder met Herko. Ik denk dat je dit makkelijker programmatisch op kunt lossen. Zorg dat je je gegevens in een handige volgorde binnenkrijgt (sorteren op object en tijd) en je kunt heel makkelijk door je resultset lopen en het resultaat opbouwen.

Of deze SQL-query te traag is hiervoor zie je snel genoeg, dat hangt van de database-engine af. Over het algemeen zou ik niet te veel data over willen pompen naar je applicatie.

[Janoz]

1x geteld wordt in een periode van 5 minuten.

Daaruit valt imho wel op te maken dat 0:00 en 0:04 als 1 storing wordt gezien, en 0:06 vervolgens weer als een losse storing wordt gezien.

Met je data-overpomp argument heb je daarentegen wel een punt, maar wanneer je daardoor genoodzaakt wordt om allemaal PL/SQL code te gaan kloppen.

[easydisk]

Helaas, er is wel Oracle maar daar kan dan geen gebruik van gemaakt worden. MySQL is het maximale wat ik kan bieden.

En daarnaast nog even ter verduidelijking: 3 storingen met elk 4 minuten er tussen geld als 2 storingen.

Maar het is idd een aardig sommetje... helaas zou 1 SQL query het makkelijkst zijn om dat de data daarna verder gaat in Excel.

[MrBucket]

Wat volgens mij wel haalbaar moet zijn is om met een cursor door je (op timestamp gesorteerde) dataset te lopen. In pseudo-code krijg je dan zoiets:

code:

declare myCursor cursor for select ... from... order by objectid, datum
declare datum, last_datum datetime
declare objectid, last_objectid int

fetch next from myCursor into datum, objectid
while /* has data */
begin
  if objectid <> last_objectid or datediff(datum, last_datum, minutes) >= 5
  begin
    /* Report current row, e.g. in temporary table. */
    set last_objectid = objectid
    set last_datum = datum
  end

  fetch next from myCursor into datum, objectid
end

Realiseer je overigens wel dat het resultaat er compleet anders uit kan zien, afhankelijk van met welke datum je begint te tellen (bijv. wanneer alle rijen exact 2 min. uit elkaar liggen).

[pedorus]

MrBucket schreef op maandag 12 januari 2009 @ 20:49:
Wat volgens mij wel haalbaar moet zijn is om met een cursor door je (op timestamp gesorteerde) dataset te lopen. In pseudo-code krijg je dan zoiets:

Ik vraag me daarbij enkel af of je die data makkelijk in Excel krijgt. De gewone mysql_query functies werken ook niet met resultsets van stored procedures (mysqli wel). Misschien dat dit dus beter even in een Excel VBA macro'tje kan..

Realiseer je overigens wel dat het resultaat er compleet anders uit kan zien, afhankelijk van met welke datum je begint te tellen (bijv. wanneer alle rijen exact 2 min. uit elkaar liggen).

Ik vraag me ook af of deze definitie echt zo handig is. Het is in ieder geval een goede manier om ervoor te zorgen dat een normaal sql-statement niet gaat werken.

[easydisk]

Heb het maar dmv een Excel dingetje gefilterd.

maar: stel dat ik heb dat elke storing die binnen 5 minuten van de vorige voorkomt, niet telt (dus stel 3 storingen, om de 4 minuten > alleen storing 1 telt) zou het dan wel lukken ?!

[Janoz]

Dan zou de 2e door Pedorus aangehaalde psuedo query wel werken. Bedenk echter wel dat in het geval van een storing om de 4 minuten dit zeurende geval niet meer in je rapportage terug komt. Ik vraag me af of dat de bedoeling is.

Als je toch je criteria aan wilt passen zodat de query makkelijker wordt dan kun je beter kiezen voor vaste intervallen. alle storingen binnen 0:00 - 0:05 worden samengenomen en 0:05 - 0:10 enz enz. Je hoeft de tabel dan niet meer met zichzelf te joinen, maar kunt gewoon groeperen op een bewerkte datum. Je krijgt dan wel dat storingen van 0:04 en 0:06 als verschillend worden gezien, maar dat is een keuze.