Autor Zpráva
Dave159
Profil *
Mám tabulku v DB s periodickými (perioda hodina) časovými záznamy, kde občas nějaké chybějí.
Jakým sql dotazem lze najít polohu a délku nejdelší časové mezerymezi řádky, když v tabulce je čas v sloupci typu datetime
juriad
Profil
http://sqlfiddle.com/#!2/18b4a/2
Vypíše takové řádky, jejichž předchůdce je vzdálenější než 61 minut.
Kajman
Profil
juriad:
Pro tabulky s mnoha a mnoha řádky to bude docela vražedný dotaz :-)

Dave159:
Co používáte za databázový systém? Kolik řádků je v té tabulce k prohledání?
juriad
Profil
Kajman:
Pokud existuje horní odhad na mezeru, lze přidat podmínku do joinu, pak může uvažovat jen lineární počet řádků místo kvadratického.
Předpokládám, že je to adhoc dotaz, který Dave159 spustí ručně jen občas.

Rychlejší (snad) řešení by šlo založit na seřazení a použití proměnných ve stylu Některé časteji řešené dotazy pro MySQL - FAQ » Číslování řádků přímo v mysql.
Nebo využít znalosti sekvence ID. (Hledání takových id, ke kterým neexistuje záznam o 1 menší, nebo přímo rozdíl času proti záznamu s id o 1 menším.)
Dave159
Profil *
Takže skutečně to je adhoc (nejspíš jen ted jednou spuštěný) dotaz, v oné tabulce je cca 300 000 řádků a jako databázový stroj používám Microsoft SQL Server Compact - dědictví po předchozím vývojáři. Běží to v C#.

Já myslel že je SQL jednoho db stroje stejné jako SQL jiného (proto jsem se ptal tady) ale asi není:
Ten dotaz padal na TIMESTAMPDIFF, který jsem nahradil její obdobou DATEDIFF. Dále jsem nahradil tab2.id za tab2.date - řádky žádné id nemají a strukturu db nemůžu upravit. Ted se mu nelíbí ten vyraz predchozi v klausuly having - snaží se to vyhodnotit jako sloupec, který nenajde.

Horní odhad na mezeru neexistuje - právě to se tímto dotazem snažím zjistit.

Asi na to SQL CE stačit nebude, tak to udělám v C#, tak že postupně projdu všechny řádky a budu si počítat rozdíly mezi řádky - a pamatovat si ten největší (jeho délku,počátek,konec...)
Kajman
Profil
Některé databáze umí analytické dotazy, které se na to hodí.

V MSSQL CE nebudout, ale mohlo by jít něco jako
SELECT TOP 1 t1.date - (SELECT Max(date)
                        FROM   tab AS t2
                        WHERE  t2.date < t1.date) mezera,
             t1.*
FROM   tab AS t1
ORDER  BY 1 DESC 
ale nemusí to být svižné na takovém počtu záznamů. S indexem nad tím sloupcem s časem by mohl být dotaz celkem efektivní.
Dave159
Profil *
Takže nakonec jsem to skutečně udělal jak jsem psal na konci [#5]. Tedy dotazem SELECT date FROM Data WHERE stanice_id= @id a iterováním nad výsledky. Tím postupem jsem se dostal na celkový čas 3s, po aplikování indexu na sloupec použitý v where -stanice_id, jsem čas srazil na 0,6s tedy čas naprosto v pohodě (je včetně otevření připojení, vybrání výsledku a výpis největší mezery pro každou stanici).
Zároveň jsem se naučil sílu indexů - úspora 4/5 původního času.
Mimochodem největší mezera byla cca 200 dní - technické problémy s jednou stanicí.
Kajman
Profil
Dave159:

Jen pozor, že v tom dotaze z [#7] musí být i order by date, abyste mohl porovnávat jen ty dva řádky za sebou.
Dave159
Profil *
Data do te tabulky jsou vkládána uz seřazená takze order by date by neměl být potřeba nebo je?
juriad
Profil
Pokud nezadás klauzuli ORDER BY, může databaze vrátit záznamy v libovolném pořadí. Může to být pořadí uložení záznamů, ale také nemusí.
Dave159
Profil *
Aha to jsem nevěděl - radši to tam přidám (včetně indexu na onom sloupci)

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: