Dotaz: prekryvanie datumov a medzery v intervaloch

[jankoh]

zdravim,

pre zjednodusenie majme nasledujucu tabulku:

insert into hodnoty (id,hodnota,datum_od,datum_do) values
	 (1,3.300,'2018-01-01','2019-12-31'),
	 (1,3.600,'2020-01-01','2020-11-31'),
	 (1,3.850,'2021-01-01','2021-12-31'),
	 (1,4.000,'2022-01-01','2022-12-31'),
	 (1,4.350,'2023-01-01','2023-12-31'),
	 (1,4.700,'2024-01-01','3333-03-03'),
	 (2,3.300,'2018-01-01','2019-12-31'),
	 (2,3.600,'2020-01-01','2020-12-31'),
	 (2,3.850,'2021-01-01','2021-12-31'),
	 (2,4.000,'2022-01-01','2022-12-31');
	 (2,4.350,'2023-01-01','2023-12-31'),
	 (2,4.700,'2024-01-01','3333-03-03'),
	 (3,2.848,'2018-01-01','2018-08-31'),
	 (3,2.943,'2018-09-01','2018-12-31'),
	 (3,3.188,'2019-01-01','2019-12-31'),
	 (3,3.555,'2020-01-01','2020-12-31'),
	 (3,3.819,'2020-12-01','2021-12-31'),
	 (3,3.961,'2022-01-01','2022-12-31'),
	 (3,4.291,'2023-01-01','2023-12-31'),
	 (3,4.597,'2024-01-01','3333-03-03');

su v nej id ktore maju urcitu casovu platnost. je mozne priamo v sql zistit, ci sa pre jednotlive id datumove intervaly neprekryvaju(t.j. ci id nema v dany datum viacero moznych hodnot) a zaroven ci nie su v intervaloch medzery(t.z. ci neexistuje datum pre ktory id nema ziadnu hodnotu)?

v priklade ako najmensi mozny datum je 2018-01-01 a najvyssi 3333-03-03.

pre id=1 napr. chybaju hodnoty pre december 2020. pre id=3 su zase pre interval datumov december 2020 platne 2 hodnoty(3.555 aj 3.819 co je nespravne).

je mozne tieto chyby v integrite dat nejako lahko vyselektovat?

dakujem.

Odpovědi

[Vtipnéř]

Taky je tam neexistující datum '2020-11-31', to by se mělo taky zjistit?

[jankoh]

2020-11-31? pre ktore id?

[xxxs]

druhy zaznam: (1,3.600,'2020-01-01','2020-11-31'),

[X]

Je to jen vzorovy nastrel => je to uplne jedno.

[xxxs]

pytal sa na to, tak dostal odpoved.

[jankoh]

no tak konkretne ten datum 2020-11-31 interval od 2020-01-01 do 2020-11-31 pokryva ci nie? pre id=1 chyba cely december(cize od 2020-12-01 do 2020-12-31), ale to som pisal v zadani.

[čavo]

Tak otázka bola, či sa majú detegovať aj neexistujúce dátumy, lebo jedenásty mesiac má len 30 dní.

[jankoh]

ahaaa...jasne, to je moja chyba v zadani, to som prehliadol. taketo chyby to hladat nemusi, predpoklad je, ze datumove intervaly su zadane korektne a db nedovoli zapisat neexistujuci datum.

[jankoh]

asi to nema priamo v sql nejake jednoduche riesenie, asi bude pohodlnejsie exportnut data a tu kontolou vykonat v nejakom python scripte.

[xxxs]

asi. mozno by sa to dalo v sql rozbit na jednotlive datumy k danemu id. potom by si vedel najst duplicity a porovnanim s tabulkou s komletnymi datumami by si videl, co ti chyba.

[X]

Nesla by pouzit na kazdy sloupec LAG() funkce? Co je to vubec za databazovy system?

[jankoh]

mariadb. ale v podstate mi slo o univerzalne pouzitelne riesenie.

za ten LAG() dik, zajtra to preskumam podrobnejsie, ale mohol by to byt spravny smer.

medzitym ma napadlo take primitivne riesenie: pouzit viacere docasne tabulky. do jednej si vypocitat na zaklade min(datum_od) a max(datum_do) pre kazde id celkovy pocet dni. do druhej si pre kazde id vypocitat pocet dni pre kazdy interval a spravit sum() jednotlivych intervalov. a potom porovnat. ak sa rovnaju tak je to ok. ak celkovy pocet dni bude vacsi ako sucet intervalov tak nejaky datum chyba, ak mensi tak sa prekryvaju.

[ttttttttttt]

Překrývající intervaly můžeš najít tak, že vyzkoušíš všechny dvojice, jestli se překrývají. Dá se to udělat i líp, tohle je spíš na jednorázový test.

select *
from 
  hodnoty h1
  join hodnoty h2 on daterange(h1.datum_od, h1.datum_do, '[]') && daterange(h2.datum_od, h2.datum_do, '[]') and h1.id < h2.id;

A jestli něco nechybí můžeš zjistit tak, že porovnáš součty délek intervalů s intervalem (nejmenší datum, největší). V mariadb možná bude potřeba napsat vlastní podmínky na porovnání intervalů, nevím, jestli je tam na to funkce.

[jankoh]

mozno primitivne, ale funkcne riesenie. davam to sem, ak by to niekto povazoval za uzitocne.

with t1 as(
  select
    id,
    datediff(max(datum_do), min(datum_od)) + 1 as celkovy_pocet_dni
  from
    hodnoty
  group by
    id
),
t2 as(
  select
    id,
    sum(datediff(datum_do, datum_od) + 1) as sucet_intervalov
  from
    hodnoty
  group by
    id
),
t3 as (
  select
    t1.id as id,
    celkovy_pocet_dni - sucet_intervalov as rozdiel
  from
    t1
    left join t2 on t2.id = t1.id
  having
    rozdiel != 0
)
select
  id,
  case
    when rozdiel < 0 then 'duplicita datumov'
    when rozdiel > 0 then 'chybajuci interval datumov'
  end as chyba
from
  t3
order by
  t3.id

[tttttttttttt]

Tohle jen kontroluje, že celková délka souhlasí, ale překryvy / chybějící dny to neodhalí. Součet může sedět, i když se intervaly překrývají:

(1,1.000,'2018-01-01','2018-12-31'),
(1,1.000,'2018-01-01','2018-12-31'),
(1,1.000,'2020-01-01','2020-12-31')

[jankoh]

mas pravdu, musim na to teda inak. dik.

[jankoh]

-- kontrola ci datum_do nie je mensi ako datum_od
select
  *,
  datediff(h.datum_do, h.datum_od) as pocet_dni
from
  hodnoty as h
having
  pocet_dni < 0
order by
  h.id,
  h.datum_od,
  h.datum_do;

-- kontrola ci intervaly datumov na seba plynulo nadvazuju
with t1 as (
  select
    h.id,
    lag(h.datum_do, 1) over (
      PARTITION BY h.id
      order by
        h.id,
        h.datum_od,
        h.datum_do
    ) as datum_pred,
    h.datum_od,
    h.datum_do,
    lead(h.datum_od, 1) over (
      PARTITION BY h.id
      order by
        h.id,
        h.datum_od,
        h.datum_do
    ) as datum_po
  from
    hodnoty as h
  order by
    h.id,
    h.datum_od,
    h.datum_do
)
select
  id,
  datum_pred,
  case
    when datediff(datum_od, datum_pred) is NULL then 'OK'
    when datediff(datum_od, datum_pred) = 1 then 'OK'
    else 'CHYBA'
  end as stav_1,
  datum_od,
  datum_do,
  case
    when datediff(datum_po, datum_do) is NULL then 'OK'
    when datediff(datum_po, datum_do) = 1 then 'OK'
    else 'CHYBA'
  end as stav_2,
  datum_po
from
  t1
having
  stav_1 = 'CHYBA'
  or stav_2 = 'CHYBA'

skusil som to vyriesit pomocou LAG a LEAD. rozdelil som to do dvoch krokov. najskor sa skontroluje ci su v poriadku jednotlive datumove intervaly. a v druhom kroku sa skontroluje ci intervaly pre jednotlive id na seba nadvazuju a neprekryvaju sa. vie mi to niekto skontrolovat ci tam zase nie je nejaka chyba? nejaky pripad pri ktorom by kontrola zlyhala?

[Tomas]

překryvy:

select 
   A.*
   ,B.*
from hodnoty A
join hodnoty B on A.id = b.id and A.datum_do >= B.datum_od and A.datum_od <= B.datum_do

řešení vychází z následujícího obrázku, který ukazuje možné překryvy

            A
    +----------------+
    o                x
 o     x  o    x  o     x
 +-----+  +----+  +-----+
    B       B        B

Díry v jistém smyslu jsou problematičtější, protože vždycky budete mít díru. A to na začátku a na konci.

select
  A.*
  ,min(B.datum_od)
from hodnoty A
join hodnoty B on A.id = B.id and A.datum_do < B.datum_od
group by A.*
having A.datum_do + interval '1' day < min(B.datum_od)

Rychlejší řešení děr je pomocí analytických funkcí. Konkrétně funkce LAG a LEAD

[cbrpnk]

SELECT id, hodnota, datum_od, datum_do
FROM (
   SELECT id, hodnota, datum_od, datum_do,
          LEAD(datum_od) OVER (PARTITION BY id ORDER BY datum_od) AS next_datum_od
   FROM hodnoty
) AS t
WHERE datum_do >= next_datum_od; 

SELECT id, hodnota, datum_od, datum_do
FROM (
  SELECT id, hodnota, datum_od, datum_do,
         LAG(datum_do) OVER (PARTITION BY id ORDER BY datum_od) AS prev_datum_do
  FROM hodnoty
) AS t
WHERE datum_od > DATE_ADD(prev_datum_do, INTERVAL 1 DAY);