Darbe iš nutolusių įrenginių kelis kartus per valanda surenkam įvairius duomenis. Kiekviena surinkimo faktą (surinko ar nesurinko duomenis) taip pat fiksuojame ir saugome DB. Kažkada iškilo poreikis grafiškai pasižiūrėti, kaip vis dėl to mūsų įrenginiai veikia, t. y. kaip kokybiškai teikia mums duomenis. Tam pasirašiau paprastą užklausą, kuri tiesiog suskaičiuoja, kiek kiekvieną valandą buvo sėkmingų duomenų surinkimo iteracijų. Kaip ir viskas gerai, tačiau bėda kyla, jei konkrečią valandą nebuvo nei vienos sėkmingos iteracijos. Tada duomenys visai dingsta (žr. nuotrauką viršuje), o norėjosi bent null pamatyti.
Nieko nelaukęs susigooglinau šaunų pavyzdėlį, kuriame panaši problema buvo išspręsta hierarchine užklausa panaudojant WITH ir CONNECT BY konstrukcijas (čia jau mano šiek tiek adaptuotas variantas):
WITH parameters AS ( SELECT TO_DATE('2014-03-01', 'yyyy-mm-dd') AS START_DAY , TO_DATE('2014-03-02', 'yyyy-mm-dd') AS END_DAY FROM DUAL ) , hour_range AS ( SELECT START_DAY + (LEVEL-1)/24 AS DT FROM PARAMETERS CONNECT BY LEVEL <= (END_DAY - START_DAY + 1)*24 ) SELECT TO_char(DT, 'YYYY-MM-DD HH24') as laikas FROM HOUR_RANGE ORDER BY 1
Kaip veikia CONNECT BY yra šauniai aprašyta čia, todėl per daug neišsiplėsiu ir geriau parodysiu, kaip prieš tai aprašytą užklausą pritaikiau savo reikmėms. Po kiek užsitęsusio „būrimo” gavosi štai tokia užklausa:
with dummy_date AS ( SELECT TO_char(TO_DATE('2014-08-26', 'yyyy-mm-dd'), 'YYYY-MM-DD HH24') as dien, TO_char(TO_DATE('2014-08-26', 'yyyy-mm-dd') + (LEVEL-1)/24, 'YYYY-MM-DD HH24') AS DT FROM dual CONNECT BY LEVEL <= (TO_DATE('2014-08-27', 'yyyy-mm-dd') - TO_DATE('2014-08-26', 'yyyy-mm-dd') + 1)*24 ) ,iteracijos as ( SELECT TO_char(trunc(DUOM_SUR_ITERACIJOS.DSI_SURINKIMO_DATA, 'DD'), 'YYYY-MM-DD HH24') AS diena, TO_char(trunc(DUOM_SUR_ITERACIJOS.DSI_SURINKIMO_DATA, 'HH'), 'YYYY-MM-DD HH24') AS data, count(dsi_id) as iter FROM DUOM_SUR_ITERACIJOS WHERE DUOM_SUR_ITERACIJOS."DSI_SURINKIMO_DATA" >= '2014-08-26' and DUOM_SUR_ITERACIJOS."DSI_SURINKIMO_DATA" <= '2014-08-27' and DUOM_SUR_ITERACIJOS."DSI_RNG_ID"= 383 and DSI_SU_KLAIDOMIS is null group by trunc(DUOM_SUR_ITERACIJOS.DSI_SURINKIMO_DATA, 'DD'), trunc(DUOM_SUR_ITERACIJOS.DSI_SURINKIMO_DATA, 'HH') ) select dummy_date.dien as diena ,dummy_date.dt as data ,iteracijos.iter as iter from dummy_date left join iteracijos on iteracijos.data=dummy_date.dt order by diena, data
Užklausos vykdymo metu, „virtualioje lentelėje” dummy_date, pagal prieš tai pateiktą pavyzdį, yra sukuriami maždaug tokie dienos (diena su laiku 00:00) ir datos (diena su valanda) įrašai:
DIEN DT ------------- -------------- 2014-08-26 00 2014-08-26 00 2014-08-26 00 2014-08-26 01 2014-08-26 00 2014-08-26 02 2014-08-26 00 2014-08-26 03 ... ... 2014-08-26 00 2014-08-27 20 2014-08-26 00 2014-08-27 21 2014-08-26 00 2014-08-27 22 2014-08-26 00 2014-08-27 23
Su elementariu select, „virtualioje lentelėje” iteracijos yra sukuriami tokie įrašai (apačioje). Čia paprasčiausiai su group by ir count yra suskaičiuojama, kiek per kiekvieną valandą buvo įvykdyta teisingų iteracijų. Įrašai niekaip nesurūšiuoti, nes rūšiavimas atliekamas paskutinėje select užklausoje.
DIENA DATA ITER ------------- -------------- ---- 2014-08-26 00 2014-08-26 10 4 2014-08-26 00 2014-08-26 13 4 2014-08-26 00 2014-08-26 07 3 2014-08-26 00 2014-08-26 04 1 2014-08-26 00 2014-08-26 08 3 2014-08-26 00 2014-08-26 00 4 2014-08-26 00 2014-08-26 02 1 2014-08-26 00 2014-08-26 11 3 2014-08-26 00 2014-08-26 12 4 2014-08-26 00 2014-08-26 14 3 2014-08-26 00 2014-08-26 01 4 2014-08-26 00 2014-08-26 09 2 2014-08-26 00 2014-08-26 03 4
Paskutinis select’as tiesiog iš „virtualios lentelės” dummy_date paima dienos ir datos stulpelių eilutes ir prie jų su left join prijungia „virtualios lentelės” iteracijos stulpelio ITER reikšmes. Gaunasi iš pat pradžių pageidautas rezultatas:
DIENA DATA ITER ------------- -------------- ---- 2014-08-26 00 2014-08-26 00 4 2014-08-26 00 2014-08-26 01 4 2014-08-26 00 2014-08-26 02 1 2014-08-26 00 2014-08-26 03 4 2014-08-26 00 2014-08-26 04 1 2014-08-26 00 2014-08-26 05 null 2014-08-26 00 2014-08-26 06 null 2014-08-26 00 2014-08-26 07 3 2014-08-26 00 2014-08-26 08 3 2014-08-26 00 2014-08-26 09 2 2014-08-26 00 2014-08-26 10 4 2014-08-26 00 2014-08-26 11 3 2014-08-26 00 2014-08-26 12 4 2014-08-26 00 2014-08-26 13 4 2014-08-26 00 2014-08-26 14 4
Rezultatas tarp paprastos („virtuali lentelė” iteracijos) ir hierarchinės užklausos daug akivaizdesnis išpiešus užklausų rezultatus grafiškai. Paprastai ištraukus duomenis, atrodo, kad įrenginys veikia praktiškai be sutrikimų. Duomenis ištraukus „gudresniu” būdu, aiškiai matome duomenų perdavimo trūkius.
Atgalinis pranešimas: Klaidingų duomenų paieška Tabibitosan metodu | Paulius Bautrėnas