У меня есть несколько таблиц с частично перекрывающимися значениями TSTZRANGE
, и мне нужно JOIN
их так, чтобы "точки останова" создавались для каждой границы UPPER
и LOWER
для каждого диапазона.
Некоторые примеры данных:
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY,
foo TEXT,
active_timespan TSTZRANGE
);
INSERT INTO
foo (foo, active_timespan)
VALUES
('One',TSTZRANGE('2015-01-01 00:00:00-00','2015-01-01 06:00:00-00')),
('Two',TSTZRANGE('2015-01-01 12:00:00-00','2015-01-01 18:00:00-00'));
CREATE TABLE bar (
bar_id SERIAL PRIMARY KEY,
bar TEXT,
active_timespan TSTZRANGE
);
INSERT INTO
bar (bar, active_timespan)
VALUES
('Alpha',TSTZRANGE('2015-01-01 03:00:00-00','2015-01-01 09:00:00-00')),
('Bravo',TSTZRANGE('2015-01-01 15:00:00-00','2015-01-01 21:00:00-00'));
Желаемый результат:
active_timespan | foo | bar
------------------------------------------------------|------|------
'["2015-01-01 00:00:00-00","2015-01-01 03:00:00-00"]' | One | Null
'["2015-01-01 03:00:00-00","2015-01-01 06:00:00-00"]' | One | Alpha
'["2015-01-01 06:00:00-00","2015-01-01 09:00:00-00"]' | Null | Alpha
'["2015-01-01 12:00:00-00","2015-01-01 15:00:00-00"]' | Two | Null
'["2015-01-01 15:00:00-00","2015-01-01 18:00:00-00"]' | Two | Bravo
'["2015-01-01 18:00:00-00","2015-01-01 21:00:00-00"]' | Null | Bravo
Текущий подход:
В настоящее время я использую сложную систему cte
, в которой я выбираю нужные мне данные из каждой таблицы в ее собственную cte
, а затем UNION
все отдельные значения диапазона UPPER
и LOWER
из каждого запроса, оставляя мне список TIMESTAMPTZ
значений. Затем я использую оконную функцию LEAD
для создания новых значений TSTZRANGE
. Как только я закончу все это, я присоединю исходный cte
к новому списку значений TSTZRANGE
.
Обновлять
Текущий шаблон запроса ниже:
WITH
cte_foo AS (
SELECT
foo_id,
foo,
active_timespan
FROM
foo
WHERE
active_timespan && $1
)
, cte_bar AS (
SELECT
bar_id,
bar,
active_timespan
FROM
bar
WHERE
active_timespan && $1
)
-- continue for each table to be joined...
-- , cte_baz AS (
-- SELECT
-- baz_id,
-- baz,
-- active_timespan
-- FROM
-- baz
-- WHERE
-- active_timespan && $1
-- )
, cte_times AS (
SELECT DISTINCT
UNNEST(
ARRAY[
LOWER(cte_foo.active_timespan),
UPPER(cte_foo.active_timespan)
]
) AS breakpoint
FROM
cte_foo
UNION
SELECT DISTINCT
UNNEST(
ARRAY[
LOWER(cte_bar.active_timespan),
UPPER(cte_bar.active_timespan)
]
) AS breakpoint
FROM
cte_bar
-- continue for each table to be joined...
-- UNION
-- SELECT DISTINCT
-- UNNEST(
-- ARRAY[
-- LOWER(cte_baz.active_timespan),
-- UPPER(cte_baz.active_timespan)
-- ]
-- ) AS breakpoint
-- FROM
-- cte_baz
)
, cte_timespans AS (
SELECT
TSTZRANGE(
sub_times.breakpoint,
LEAD(sub_times.breakpoint, 1, 'infinity'::TIMESTAMPTZ) OVER (ORDER BY sub_times.breakpoint)
) AS timespan
FROM
(
SELECT DISTINCT
cte_times.breakpoint
FROM
cte_times
) AS sub_times
ORDER BY
timespan
)
SELECT
cte_timespans.timespan,
cte_foo.foo,
cte_bar.bar
FROM
cte_timespans
LEFT OUTER JOIN cte_foo ON cte_timespans.timespan && cte_foo.active_timespan
LEFT OUTER JOIN cte_bar ON cte_timespans.timespan && cte_bar.active_timespan
-- continue for each table to be joined...
-- LEFT OUTER JOIN cte_baz ON cte_timespans.timespan && cte_baz.active_timespan
ORDER BY
cte_timelines.timespan
Производительность явно низкая, особенно когда я присоединяюсь к нескольким таблицам (в некоторых случаях более десяти). Это лучший способ подойти к этой ситуации?
Принятие желаемого за действительное:
Было бы здорово, если бы у PostgreSQL был какой-то RANGE JOIN
— что-то вроде:
SELECT
*
FROM
foo
FULL OUTER RANGE JOIN bar ON foo.active_timespan && bar.active_timespan
explain analyze
) на ваших реальных данных. Тип данных диапазона также может быть проиндексирован: postgresql.org/docs /current/static/ Вы пробовали это? - person a_horse_with_no_name   schedule 22.09.2015cte
(описанного в документации как ограничения оптимизации). Они также удаляют все индексы после начальногоSELECT
. - person losthorse   schedule 22.09.2015full outer join
вернет все строки из обеих таблиц, что невозможно оптимизировать. База данных должна прочитать все строки из foo и все строки из bar. - person a_horse_with_no_name   schedule 22.09.2015gist
присутствует на каждомTSTZRANGE
- person losthorse   schedule 22.09.2015range_a NOT IN range_b
может быть 0,1 или 2 диапазона, а результатомtuple*tuple
может быть 0 или 1 кортеж. Таким образом, либо вам придется рассматривать разделенные диапазоны отдельно, либо вам придется пройти через границы, как это делает @byMike. - person joop   schedule 22.09.2015&&
) нельзя использовать дляfull join
прямо сейчасFULL JOIN is only supported with merge-joinable or hash-joinable join conditions
, что оставляет мало места для оптимизации. - person pozs   schedule 22.09.2015