Как по конкретному дню извлечь значения из таблицы с диапазонами дат, используя VIEW?

Question

Недавно начала изучать представления в SQL и нашла интересную задачу, с которой возникли сложности.

У меня есть таблица id и их статусов, например, table_status:

ID  STATUS
 1  on
 2  off
 3  pause
 4  off

И есть вторая таблица истории изменения этих статусов, up_status:

ID  STATUS  UP
 1  on      26-APR-21
 1  off     25-APR-21
 1  off     24-JAN-21
 1  on      23-MAR-21
 2  on      22-APR-20
 3  off     26-APR-21
 3  on      25-MAR-21
 3  on      24-JAN-21
 3  pause   23-MAR-21
 3  off     22-APR-19
 4  off     25-APR-20
 4  on      21-APR-20

Вывод: id, status, actual_data.

Как используя VIEW и подавая на вход любую дату, получать актуальную на эту дату первую таблицу?

Единственное, что я изначально смогла добиться самостоятельно - это создать представление, которое уже с конкретной датой выдаёт по этому дню актуальные данные, правда с датами последнего изменения статуса:

CREATE VIEW actual AS (
    SELECT test.id, test.status, test.up 
    FROM (SELECT id, 
        status, 
        up, 
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY up DESC) as rn 
        FROM up_status
        WHERE up <= to_date('29.05.2021','DD.MM.YYYY')
        ) test 
    WHERE test.rn = 1 
);

Итог запроса:

ID  STATUS  UP
 1  off     26-APR-21
 2  on      22-APR-20
 3  off     26-APR-21
 4  off     25-APR-20

Но я прекрасно понимаю, что это неверное решение.

Думаю, что можно было бы сделать запрос, который возвращает ВСЕ дни, каким-то образом доставая их из диапазонов, и все статусы всех id по этим дням (у меня, к сожалению, не получилось пока что). Затем, обращаясь к этому представлению, можно было бы через WHERE подавать дату для фильтра. К сожалению, я не смогла найти хороший пример, который мне бы помог это сделать.

Поэтому, может кто-то помочь мне с примером, в котором показано, как достать все дни, или подсказать, что я могу использовать для менее громоздкого решения, при этом используя VIEW.

Answer 1

Можно создать view, которое будет для каждой записи хранить также дату, до которой статус действителен, т.е. по сути у нас будет интервал, когда запись активна:

CREATE VIEW up_status_activity AS (
SELECT id,
  status,
  up,
  LEAD(up) OVER(PARTITION BY id ORDER BY up ASC) - 1 as valid_until
  FROM up_status
);

Теперь можно для каждой даты найти записи, которые были активны на дату:

select id, status, up
FROM up_status_activity
where to_date('29.05.2021','DD.MM.YYYY') between up AND
   coalesce(valid_until, to_date('01.01.2100', 'DD.MM.YYYY'))

Answer 2

Вопрос, как используя VIEW и подавая на вход любую дату, получать [...]

То есть, упрощённо что-то такое:

CREATE VIEW actual (date_parametr) AS select ... where col = date_parametr

Но такая конструкция синтаксически недействительна - представление не может иметь параметры. Вопрос не новый, идее создать параметризированное представление, уже десятки лет.

Одно из решений - добавить заданные колонки в лист выборки представления, затем использовать их в фильтре запроса. Это решение уже предложено в ответе @RomanKonoval, просто повторю с незначительными улучшениями:

create or replace view statusact as (
select id, status, up,
    lead (up, 1, date'9999-12-31') over (
        partition by id order by up asc)-(1/24/60/60) till
from statusup);

with param (dt) as (
    select date'2021-05-29' from dual
) select id, status, to_char (dt, 'dd-Mon-rr') asofday
from statusact, param
where dt between up and till;

        ID STATU ASOFDAY  
---------- ----- ---------
         1 on    29-May-21
         2 on    29-May-21
         3 off   29-May-21
         4 off   29-May-21

Чтобы обойти отсутствие параметров в представлении, с которыми можно было бы, например, достичь лучшей производительности запроса, можно создать табличную функцию:

create or replace package pack as
    type statrec is record (id int, status varchar(8), dt date);
    type stattab is table of statrec;
    function getstatus (dt date) return stattab pipelined;
end;
/
create or replace package body pack as
    function getstatus (dt date) return stattab  pipelined is
    begin 
        for r in (  
            select id, status 
            from (select id, status, up, lead (up, 1, date'9999-12-31') over (
                    partition by id order by up asc)-(1/24/60/60) till
                from statusup) where dt between up and till) loop
            pipe row (statrec (r.id, r.status, dt));    
        end loop;        
        return;
    end;
end;
/

Такая функция является своего рода представлением в SQL запросе. Вот посмотрите, запрос выглядит точь-в-точь как с представлением, но только с параметром:

select id, status, to_char (dt, 'dd-Mon-rr') asofday
from pack.getstatus (date'2021-05-29');

        ID STATUS   ASOFDAY           
---------- -------- ------------------
         1 on       29-May-21         
         2 on       29-May-21         
         3 off      29-May-21         
         4 off      29-May-21

Все примеры и данные на db<>fiddle.

Есть ли другое или более новое решение?
В выражение для создания представлений клауза для опциональных параметров добавлена не будет. Архитекторы Oracle решили, что с введением SQL Macro (с версии 19.6), проблематика отсутствия параметров в представлении, решена. Подробнее, что это такое, с ссылкой на документацию и примером в этом ответе.

БЛОГ НА HUSL

Как по конкретному дню извлечь значения из таблицы с диапазонами дат, используя VIEW?

Ответы (2 шт):