Блог

Сценарий:

Таблица «HIST»

ОЖИДАЕМЫЙ РЕЗУЛЬТАТ:

Я хочу отобразить разницу между строками в столбце «ЗНАЧЕНИЕ», группируемом по RID, где HIST_DATE находится между «2020-03-31» и «2021-04-01».

Затем перенесите различные значения в столбцы «VALUE_OLD» И «VALUE_NEW».

Примечание : Предполагая, что между этими датами может быть только 2 разных значения

Oracle SQL

Другой вариант-использовать функции аналитики ОТСТАВАНИЯ/ЛИДОВ :

• ЗАДЕРЖКА возвращает значение из предыдущей строки таблицы.
• ЛИД возвращает значение из следующей строки таблицы.

 select rid
    , LAG(hist_date) OVER(partition by rid order by hist_date ) as HIST_DATE_OLD 
    , hist_date  as HIST_DATE
    , LAG(value) OVER(partition by rid order by hist_date ) as VALUE_OLD 
    , value as VALUE_NEW
from hist

 

 select rid
    , lead(hist_date) OVER(partition by rid order by hist_date desc) as HIST_DATE_OLD 
    , hist_date  as HIST_DATE
    , lead(value) OVER(partition by rid order by hist_date desc) as VALUE_OLD 
    , value as VALUE_NEW
from hist
order by rid, hist_date
 

Затем вы можете отфильтровать и отформатировать в соответствии с вашими потребностями

 select rid, VALUE_OLD, VALUE_NEW from (
    select rid
       , TO_CHAR(lead(hist_date) OVER(partition by rid order by hist_date desc), 'YYYY-MM-DD') as HIST_DATE_OLD 
       , TO_CHAR(hist_date , 'YYYY-MM-DD') as HIST_DATE
       , lead(value) OVER(partition by rid order by hist_date desc) as VALUE_OLD 
       , value as VALUE_NEW
    from hist
    where HIST_DATE between '2020-03-31' and '2021-04-01'
    and   rid = 1  
order by rid, hist_date
)
where HIST_DATE_OLD IS NOT NULL

RID VALUE_OLD VALUE_NEW 
--- --------- --------- 
  1 V113      V114          
 

 select * from (
    select rid
       , TO_CHAR(lead(hist_date) OVER(partition by rid order by hist_date desc), 'YYYY-MM-DD') as HIST_DATE_OLD 
       , TO_CHAR(hist_date , 'YYYY-MM-DD') as HIST_DATE
       , lead(value) OVER(partition by rid order by hist_date desc) as VALUE_OLD 
       , value as VALUE_NEW
    from hist
order by rid, hist_date
)
where HIST_DATE between '2020-03-31' and '2021-04-01'
    and   rid = 1  

RID HIST_DATE_OLD HIST_DATE  VALUE_OLD VALUE_NEW 
--- ------------- ---------- --------- --------- 
  1 2020-03-08    2020-04-11 V112      V113      
  1 2020-04-11    2021-03-15 V113      V114      
 

позаботьтесь о фильтре внутри и снаружи основного ВЫБОРА

• снаружи вы выполняете фильтрацию после выполнения операции ЗАДЕРЖКИ/ОПЕРЕЖЕНИЯ (выше)
• внутри вы выполняете фильтрацию перед выполнением операции ЗАДЕРЖКИ/ОПЕРЕЖЕНИЯ (ниже).

 RID HIST_DATE_OLD HIST_DATE  VALUE_OLD VALUE_NEW 
--- ------------- ---------- --------- --------- 
  1               2020-04-11           V113      
  1 2020-04-11    2021-03-15 V113      V114      
 

Предполагая, что между этими датами может быть только 2 разных значения

  select distinct RID, 
        first_value(VALUE) over(order by HIST_DATE) VALUE_OLD,
        first_value(VALUE) over(order by HIST_DATE desc) VALUE_NEW
 from HIST
 where HIST_DATE between  date '2020-03-31' and date '2021-04-01'

    
 

Вам придется:

1. Найдите последнюю дату hist_date для каждого rid (новые значения)
2. Найдите последнюю дату истории для каждого rid (oldvals), которая предшествует самой последней (newvals)
3. Прочитайте таблицу hist, чтобы найти новые значения для каждого rid
4. Прочитайте таблицу hist, чтобы найти предыдущие значения для каждого rid

Вы всегда показываете два последних значения каждого rid, независимо от того, есть ли другие более старые значения.

 SELECT
    n.rid,
    o.value AS value_old,
    n.value AS value_new
FROM
    (
        SELECT
            rid,
            MAX(hist_date) AS DATE
        FROM
            hist
        GROUP BY
            rid
    )
    newvals,
    (
        SELECT
            hist.rid,
            MAX(hist_date) AS DATE
        FROM
            hist,
            (
                SELECT
                    rid,
                    MAX(hist_date) AS DATE
                FROM
                    hist
                GROUP BY
                    rid
            )
            newvals
        WHERE
            hist.rid=newvals.rid
        AND hist.hist_date<newvals.date
        GROUP BY
            hist.rid
    )
    oldvals,
    hist n,
    hist o
WHERE
    n.rid=o.rid
and n.rid=newvals.rid
and n.rid=oldvals.rid
AND n.hist_date=newvals.date
AND o.hist_date=oldvals.date