| Autor | Zpráva | ||
|---|---|---|---|
| Dave159 Profil * |
#1 · Zasláno: 6. 3. 2014, 16:31:12
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 |
#2 · Zasláno: 6. 3. 2014, 17:50:09
http://sqlfiddle.com/#!2/18b4a/2
Vypíše takové řádky, jejichž předchůdce je vzdálenější než 61 minut. |
||
| Kajman Profil |
#3 · Zasláno: 6. 3. 2014, 20:37:25
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 * |
#5 · Zasláno: 6. 3. 2014, 21:26:57
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 |
||
| Dave159 Profil * |
#7 · Zasláno: 8. 3. 2014, 20:58:10
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 |
#8 · Zasláno: 8. 3. 2014, 21:13:20
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 * |
#9 · Zasláno: 8. 3. 2014, 21:18:15
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 * |
#11 · Zasláno: 8. 3. 2014, 22:47:47
Aha to jsem nevěděl - radši to tam přidám (včetně indexu na onom sloupci)
|
||
|
Časová prodleva: 10 let
|
|||
0