Hledání chybějících záznamů – MS SQL – Fórum – Programujte.com

Ahoj,

mám v tabulce log měření, kde má při správné funkci být každou celou sekundu jeden záznam. Potřebuji v tabulce najít chybějící záznamy a vypsat jejich datum a čas.

Na vysvětlení: pokud dojde k selhání v 16:55:48, budou existovat záznamy s časem 16:55:47 a 16:55:49. Záznam pro 16:55:48 nebude existovat. Potřebuji tyto "díry" najít a vypsat.

V C# bych to zvládl, ale jak na to v MS SQL mne vůbec nenapadá.

hu

test

Se mi tu nedari vlozit prispevek, od vcera. Porad to pise zpracovava, zpracovava a pak to napise, ze vyprsela captcha :) Zkusim kopii z mailu. Zajimave, ze test prosel. Asi se to sekne na nejakem znaku. A to sem to mel puvodne pekne v code.

>- vybrat vse a seradit podle casu
>- udelat rozdil casu s nasledujicim radkem nebo predchozim radkem
>
>Resil bych to asi tak, ze vyberu tabulku 2x. Ocisluji radky pro prvni vyber 1,2,3 a pro druhy jako 2, 3, 4. A pak to cele zgrupovat podle toho cisla a udelat rozdil casu a kde having/where je ruzny od 1. Jen pro mssql neznam ty prikazy, ale asi nejak takto:
>
>SELECT *, SUM(cas) FROM
>(
>SELECT *, +cas AS cas, COUNT(*) as x FROM tab ORDER BY cas DESC
>UNION
>SELECT *, -cas AS cas, COUNT(*)+1 as x FROM tab ORDER BY cas DESC
>)
>GROUP BY x
>HAVING SUM(cas)>1
>
>Radky tam budou 2x.
>Kazdy prvni bude mit x = n, duplicitni bude mit x = n+1.
>Takze zgrupovanim podle x ziskas propojeni obou radku.
>A pro SUM potrebujes jen, aby jeden mel cas kladny a druhy zaporny. Predpokladam, ze UM pak casy odecte.

Jo, mozna by to chtelo nejdrive z orderovat a pak ocislovat radky. Nejsem si ted jisty, ktere z toho se provede drive. Jde o to, aby oba ty vybery byli stejne, ve stejnem poradi, jen druhy musi mit cislovani radku +1, aby se spojili spravne radky.
SELECT *, count(*) AS x FROM (SELECT ... ORDER BY ...)

SQL umí vypsat to, co v databázi je. Neumí vypisovat co v databázi není. Proto bych to řešil založením pomocné tabulky, do které bych nějakým programem nebo skriptem naplnil, v jakých časech očekávám výsledky.

Pomocná tabulka VZOR, sloupec CAS
Tabulka obsahuje seznam časů, ke kterým má existovat měření.
CAS
---
16:55:47
16:55:48
16:55:49

Tvoje tabulka LOGMERENI, sloupec START
START
-----
16:55:47
16:55:49

SELECT CAS
FROM VZOR
LEFT OUTER JOIN LOGMERENI ON LOGMERENI.START = VZOR.CAS
WHERE LOGMERENI.START IS NULL

Tento příkaz vypíše časy, ke kterým chybí měření:
16:55:48

Ale teda jestli Ti měření nepoběží celý den, tak dostaneš 3600 záznamů, že v danou vteřinu měření neproběhlo.
V tomhle je lepší Peterovo řešení. Ale ten tam zas nemá vyřešené okrajové podmínky - kdyby Ti měření celý den neběželo (neměl bys vůbec žádný záznam), tak Ti to Peterovo řešení nic nenajde.

#5 RomanZ
Jen poznámka: 3600 záznamů za hodinu. Za den by jich bylo 24x tolik.

Každopádně jde o hledání krátkých výpadků, které představují většinou 1 záznam.

hu

Jo, to máš vlastně pravdu, to vrátí spoustu řádků, není to praktické.

To Peterovo řešení mne vedlo ještě k jednomu nápadu: zjistit subselectem nejbližší další záznam a vrátit jen ty, kde je později než za vteřinku. Výhoda je že pro každý výpadek se vrací jen jeden řádek a navíc zobrazuje čas pro začátek a konec výpadku. Nevýhoda jsou samozřejmě ty okrajové podmínky (co když není žádný záznam, nebo )

SELECT CAS, (select min(log2.cas) from logmereni log2 where LOG1.CAS < log2.cas) CAS2
FROM LOGMERENI LOG1
WHERE (rozdilcasu(CAS,CAS2) > 1) OR (CAS2 IS NULL)

Poznámky: malými písmeny jsem napsal subselect, aby to bylo čitelnější, ale na case sensitive databázi to takhle nebude fungovat. A pro rozdilcasu() si musíš najít odpovídající ekvivalent pro Tvou databázi - prostě nějaký výraz, který dovede vrátit rozdíl časů v sekundách. Kontrola (CAS2 IS NULL) tam je kvůli nahlášení posledního výpadku, který ještě neskončil (nemá za sebou  navazující úspěšný záznam měření).

RomanZ: To je lepsi.
Mne zas pri tom tvem napadlo, ze tu tabulku nemusis ukladat, muzes ji vytvarel pro zadany interval casu pomoci selectu. K tomu najoinovat tu puvodni a pomoci where odfiltrovat vyplnene radky. Neco jako, ale, zas nevim, jak to zapsat... :)

SELECT B.a, B.b, B.cas, casFrom+count(*) AS casX FROM tab A
 LEFT JOiN tab B on B.cas = A.casFrom+count(*)
WHERE B.cas IS NULL

Jakoze bys vyuzil puvodni tabulku jako zdroj radku a omezil to jen podminkami, ze chces data za poslednich 7 dni nebo mensi cislo, kde celkovy pocet radku urcite preahne pocet pro 24 hodin. Ale, z puvvodni tabulky by nevytahl nic a pridal tam jen sloupce z druhe. Ale to je mozna rychlejsi vytahnout tabulku 2x.
 

A jinak souhlasim, ze kdyby server vypadl, tak i tohle i me predchozi reseni nemusi vrati ani radek :) Takze by byla asi lepsi procedura.

Jako, to me reseni by slo pouzit, ale bylo by nutne udelat si pomocnou tabulku, kterou pak pouzije pro SELECT. Ale pak nemuze vybirat vetsi interval, treba u prechodneho rok nebo tak neco. Videl jsem to na nejakem forku s tou pomocnou tabulkou, tak mi to prislo jako zajimave.

Nevím jestli splňuje zadání, ale pokud by vám stačil seznam děr a jejich velikost, tak by mohlo být zajímavé použít funkci LAG, která umí vrátit "předchozí" hodnotu. Tak by se dal udělat rozdíl času mezi řádky (seřazenými).

Výhody:
- databáze by to měla zpracovat jedním(seřazeným) průchodem tabulkou.
- v případě že bude chybět třeba několik dní/měsíců dat tak to bude odpověď z databáze jen jedna řádka.(případně by mělo jít velikost díry filtrovat)
- V případě že v tabulkce je více senzorů odlišených dle nějakého ID senzoru, tak dá se použít PARTITION BY a spočítat více senzorů jedním dotazem,
 

Nevýhody
- Výstupem nejsou jednotlivé chybějící záznamy ale asi by se asi museli "dopočítat" v aplikaci.

#10 Ovrscout
Zapomněl jsem napsat, že s MSSQL jsem už dlouho nedělal a nejsem na ni zrovna expert.
Raděj si zkontrolujte/nastudujte dokumentaci k této window funkci přímo v mssql dokumentaci (správné verze) :)