Postgresql Fill Gaps - Матрица последней доступной информации для данной ветви

У меня есть таблица с индексами импортируемых файлов, с датами и ветвями каждого импортированного файла.

Теперь мне нужно объединить несколько ветвей, чтобы мне приходилось дублировать информацию из некоторых ветвей, когда у нас выходной, чтобы данные были согласованными, в основном мне нужно заполнить эти пробелы самой последней доступной информацией.

Я пытался сделать несколько самостоятельных объединений с ранжированием, чтобы переключаться между ними и получать предыдущие данные, но это не сработало.

У меня есть таблица:

rundate, branch, imported 
2015-04-01, PL1, TRUE 
2015-04-01, ES1, TRUE 
2015-04-01, CZ4, TRUE 
2015-04-02, PL1, TRUE 
2015-04-02, ES1, TRUE 
2015-04-02, CZ4, TRUE   
2015-04-03, ES1, TRUE 
2015-04-03, CZ4, TRUE

В этом примере я хотел бы сделать запрос, который возвращает:

gap_date, branch, real_date
2015-04-03, PL1, 2015-04-02

Эта таблица довольно мала (пара тысяч строк), поэтому производительность не должна быть большой проблемой.

Есть идеи, как мне этого добиться?

Теперь я использую функцию, которая получает даты rundate и ответвления от промежутка в качестве параметров и отвечает на последнюю дату перед датой, переданной в качестве параметра (используя max(rundate) где rundate <= '$1')

Спасибо!

Источник

3 ответа

Решение

Ты можешь использовать outer join, subquery а также cross join:

Схема:

create table tbl(rundate date, 
                 branch varchar(10), 
                 imported bool);
insert into tbl values('2015-04-01', 'PL1', TRUE),
('2015-04-01', 'ES1', TRUE), 
('2015-04-01', 'CZ4', TRUE), 
('2015-04-02', 'PL1', TRUE), 
('2015-04-02', 'ES1', TRUE), 
('2015-04-02', 'CZ4', TRUE),  
('2015-04-03', 'ES1', TRUE), 
('2015-04-03', 'CZ4', TRUE);

Запрос:

select q.rundate as gap_date,q.branch,
       (select max(tt.rundate) 
        from tbl tt
        where tt.rundate<q.rundate and tt.branch=q.branch)
        as real_date
from tbl t
right outer join(
         select rundate,branch from (
                select distinct rundate from tbl) t1
                cross join (
                select distinct branch from tbl)t2
          )q 
on t.rundate=q.rundate and t.branch=q.branch
where t.branch is null

Результат:

gap_date    branch  real_date
2015-04-03  PL1     2015-04-02
Источник

Это carthesian product of both domains решение от @voycheck за исключением записей, которые на самом деле существуют.

WITH br AS (
        SELECT DISTINCT branch AS branch FROM tbl
        )
, mima AS (
        SELECT MIN(rundate) as mi
             , MAX(rundate) as ma
        FROM tbl)
, rng AS (
        SELECT generate_series( mima.mi, mima.ma, '1 day'::interval)::date AS rundate
        FROM mima
        )
SELECT * FROM rng
JOIN br ON NOT EXISTS ( -- cartesian product EXCEPT
        SELECT *
        FROM tbl t
        WHERE t.branch = br.branch
        AND t.rundate = rng.rundate
        )
        ;
Источник

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

CREATE TEMPORARY TABLE _matrix
SELECT
t1.rundate,
t2.branch
(SELECT DISTINCT rundate FROM yourtable) t1,
(SELECT DISTINCT branch FROM yourtable) t2

Затем вы можете найти пропуски, используя операторы LEFT JOIN и WHERE ... IS NULL:

SELECT
m.rundate,
m.branch
FROM _matrix m
LEFT JOIN yourtable yt ON(yt.rundate = m.rundate AND yt.branch = m.branch)
WHERE yt.rundate IS NULL

Конечно, то же самое может быть достигнуто без временных таблиц - используя только подзапрос. Особенно, когда производительность не имеет решающего значения.

Источник
Другие вопросы по тегам