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.