Oracle SQL lekérdezés: az adatok legutóbbi módosításának megkeresése

Elkerülheti a többszörös találatot az asztalon és az összekapcsolásokat, ha mindkét késleltetési (vagy lead) számítást együtt végzi:

with t as (
  select id, pdate, data1, data2,
    lag(data1) over (partition by id order by pdate) as lag_data1,
    lag(data2) over (partition by id order by pdate) as lag_data2
  from table_x
),
u as (
  select t.*,
    case when lag_data1 is null or lag_data1 != data1 then pdate end as pdate1,
    case when lag_data2 is null or lag_data2 != data2 then pdate end as pdate2
  from t
),
v as (
  select u.*,
    rank() over (partition by id order by pdate1 desc nulls last) as rn1,
    rank() over (partition by id order by pdate2 desc nulls last) as rn2
  from u
)
select v.id,
  max(trunc(sysdate) - (case when rn1 = 1 then pdate1 end))
    as data1_last_update,
  max(trunc(sysdate) - (case when rn2 = 1 then pdate2 end))
    as data2_last_update
from v
group by v.id
order by v.id;

Feltételezem, hogy adatait Jun-2014, nem Jan-2013 számára szánta; és hogy a legutóbbi módosítási dátumokat hasonlítja össze az aktuális dátummal. A 10-Jun-2014 stb. használatára igazított adatokkal ez a következőt adja:

ID DATA1_LAST_UPDATE DATA2_LAST_UPDATE
-- ----------------- -----------------
A                  4                 2 
B                  2                 5 

Az első CTE (t) megkapja a tényleges táblázatadatokat, és két extra oszlopot ad hozzá, mindegyik adatoszlophoz egyet, késleltetést használva (ami megegyezik a csökkenő dátumok szerint rendezett lead).

A második CTE (u) két dátumoszlopot ad hozzá, amelyeket csak az adatoszlopok módosításakor állítanak be (vagy amikor először állítják be őket, arra az esetre, ha soha nem változtak volna). Tehát ha egy sorban data1 ugyanaz, mint az előző sorban, akkor a pdate1 üres lesz. Az első kettőt kombinálhatja a késleltetés számításának megismétlésével, de a kicsit felosztva hagytam, hogy egy kicsit világosabb legyen.

A harmadik CTE (v) olyan rangsort rendel ezekhez a pdate oszlopokhoz, hogy a legfrissebb legyen az első helyen.

Az utolsó lekérdezés pedig kiszámítja az aktuális dátum és a legmagasabb rangú (azaz a legutóbbi) változás közötti különbséget az egyes adatoszlopok esetében.

SQL Fiddle, beleértve az összes CTE-t külön-külön, így láthatja, mit csinálnak.

A lekérdezése nem a számomra megfelelő eredményeket adta, lehet, hogy kihagytam valamit, de az alábbi lekérdezéssel is a megfelelő eredményeket kaptam (ezt ellenőrizheti: SQLFiddle bemutató):

with ranked as (
                  select ID, 
                         data1,
                         data2,   
                         rank() over(partition by id order by pdate desc) r 
                  from table_x
               )
select id,
       sum(DATA1_LASTUPDATE) DATA1_LASTUPDATE,
       sum(DATA2_LASTUPDATE) DATA2_LASTUPDATE
from (
     -- here I get when data1 was updated
         select id, 
                count(1) DATA1_LASTUPDATE,
                0 DATA2_LASTUPDATE
           from ranked
     start with r = 1
    CONNECT BY (PRIOR data1 = data1)
           and PRIOR r = r - 1
      group by id
    union 
  -- here I get when data2 was updated
        select id, 
               0 DATA1_LASTUPDATE,
               count(1) DATA0_LASTUPDATE  
           from ranked
     start with r = 1
    CONNECT BY (PRIOR data2 = data2)
           and PRIOR r = r - 1
      group by id
)  
group by id