Функция для инкапсуляции логики поиска
Я хочу написать функцию Postgres, которая ищет значение столбца в заданной таблице.
Пример настройки:
CREATE SCHEMA IF NOT EXISTS example;
CREATE TABLE example.price_table (
symbol text NOT NULL,
time_of timestamptz NOT NULL,
price float8 null,
volume float8 null );
CREATE TABLE example.interesting_times (
symbol text NOT NULL,
time_of timestamptz NOT NULL );
Образец данных
INSERT INTO example.price_table VALUES
('GME', '2016-01-01 00:00:00', 10.0, 10),
('GME', '2016-01-02 00:00:00', 11.0, 3),
('GME', '2016-01-03 00:00:00', 9.0 , 7),
('GME', '2016-01-03 00:40:00', 10.5, 76),
('GME', '2016-01-04 00:00:00', 11.0, 10),
('BBBY', '2016-01-01 00:00:00', 20.0, 5),
('BBBY', '2016-01-02 02:30:00', 2123.0, 1),
('BBBY', '2016-01-02 03:30:00', 2133.0, 1),
('BBBY', '2016-01-02 04:30:00', 2144.0, 100),
('BBBY', '2016-01-03 00:00:00', 29.0, 999),
('BBBY', '2016-01-03 03:40:00', 20.5, 1),
('BBBY', '2016-01-03 04:40:00', 20.6, 54),
('BBBY', '2016-01-04 00:00:00', 21.0, 34),
('BBBY', '2016-01-06 00:00:00', 666.0, 1);
INSERT INTO example.interesting_times VALUES
('GME', '2016-01-01 00:00:00'),
('GME', '2016-01-02 00:30:00'),
('GME', '2016-01-03 05:00:00'),
('GME', '2016-01-03 00:40:00'),
('GME', '2016-01-04 00:00:00'),
('BBBY', '2016-01-01 01:00:00'),
('BBBY', '2016-01-02 00:00:00'),
('BBBY', '2016-01-03 00:00:00'),
('BBBY', '2016-01-03 07:40:00'),
('BBBY', '2016-01-04 00:00:00');
При запросе таблицы interesting_times я хочу добавить самую раннюю рыночную цену из таблицы markout_price_table, как минимум на 3 часа, но не более чем на 3 часа + 24 часа в будущее. В идеале я бы просто вызвал функцию markout_price_table, например:
select symbol
, time_of
, markout_price_table( symbol
, time_of
, 'price'
, '3 hours'
, '24 hours') as price_3_hours_later
from example.interesting_times
order by symbol, time_of;
Я мог бы получить разное время разметки, добавив:
markout_price_table(symbol, time_of, 'price', '6 hours', '24 hours') as price_6_hours_later`
или
markout_price_table(symbol, time_of, 'volume', '0 hours', '24 hours') as volume_at_time
Можно ли инкапсулировать логику в такой функции? Если да, то будет ли это так же быстро, как загрузка всех данных и локальное слияние?
Я могу получить желаемый результат из вышеизложенного, используя несколько слияний, но мне это не нравится как решение, поскольку я хотел бы инкапсулировать хорошую логику для слияний, а не копировать и вставлять приведенное ниже много раз в каждый запрос.
with tabA as (
select a.symbol as symbol
, a.time_of as time_of
, b.price as price_3_hours_later
, b.time_of - (a.time_of + '3 hours') as timeliness
from example.interesting_times a
left join example.price_table b
on (a.symbol = b.symbol)
and (a.time_of + '3 hours' < b.time_of)
and (a.time_of + '3 hours' + '24 hours' > b.time_of)
order by b.time_of - (a.time_of + '3 hours')
)
, tabB as (
select symbol
, time_of
, price_3_hours_later
, timeliness
, min(timeliness) over w1 as min_tim
from tabA
window w1 as (partition by symbol, time_of order by timeliness)
)
select symbol
, time_of
, price_3_hours_later
from tabB
where (timeliness = min_tim)
or (timeliness is null)
order by symbol, time_of;
Итак, результат должен выглядеть так:
Symbol time_of price_3_hours_later
BBBY 2016-01-01 01:00:00.000 +0000 2123.0
BBBY 2016-01-02 00:00:00.000 +0000 2133.0
BBBY 2016-01-03 00:00:00.000 +0000 20.5
BBBY 2016-01-03 07:40:00.000 +0000 21.0
BBBY 2016-01-04 00:00:00.000 +0000 NULL
GME 2016-01-01 00:00:00.000 +0000 11.0
GME 2016-01-02 00:30:00.000 +0000 9.0
GME 2016-01-03 00:40:00.000 +0000 11.0
GME 2016-01-03 05:00:00.000 +0000 11.0
GME 2016-01-04 00:00:00.000 +0000 NULL
Спасибо @Belayer. Я сейчас добавил это
Ответы 2
Вы можете сократить этот запрос и обернуть его функцией : демо в db<>fiddle
create function markout_price_table(
p_symbol text, p_time_of timestamptz, min_after interval, max_after interval)
returns float8 language sql return (
select price from example.price_table
where symbol=p_symbol
and time_of between p_time_of+min_after
and p_time_of+max_after
order by time_of limit 1);
И это будет работать именно так, как вы хотели:
select symbol
, time_of
, markout_price_table(symbol, time_of, '3 hours', '27 hours') as price_3_hours_later
from example.interesting_times
order by symbol, time_of;
Поскольку вы поставили price прямо в имени функции, я не думаю, что имеет смысл передавать 'price' в качестве параметра.
Если бы вы хотели, чтобы функция динамически выбирала столбец price, это немного усложнило бы ситуацию. Вам придется сделать ее полиморфной функцией и передать в нее образец значения, чтобы она могла определить тип возвращаемого значения во время вызова.
Спасибо, в моем случае это работает. Но эффективно ли это? Меня беспокоит то, что в случае, если в таблице 5000 строк, будет выполнено 5000 различных SQL-запросов, и это будет очень медленно. Я надеялся, что можно будет иметь версию на основе массива, чтобы она могла выполняться быстро.
Вы мало что можете сделать с запросом сам по себе. Производительность всегда будет зависеть от конкретного профиля ваших данных. Если у вас его нет, индекс покрытия, предложенный Эрвином, позволяет выполнить это 300ms в тесте с поиском 5 тысяч точек времени для 40 символов в наборе из 500 тысяч торговых записей для 80 символов: демо . Половина этой суммы, если вы не используете перенос функций и вместо этого встраиваете запрос. Поскольку функция имеет from и order by, она не является автоматически встраиваемой.
Функции на основе массивов иногда имеют смысл в качестве специального обходного пути, решающего очень специфическую категорию проблем, но я не думаю, что это один из таких сценариев. В большинстве случаев вам действительно лучше объявить то, что вы хотите, в простых терминах SQL и позволить переписчику, планировщику и оптимизатору запросов PostgreSQL придумать, как получить это для вас как можно быстрее. При этом, если у вас есть набросок какой-либо функции pl/pgsql, с помощью которой вы пытались решить эту проблему, всегда интересно взглянуть на нее.
Можно решить с помощью одного простого оператора SQL с использованием подзапроса LATERAL:
SELECT i.symbol, i.time_of, p.price_3_hours_later
FROM interesting_times i
LEFT JOIN LATERAL (
SELECT p.price AS price_3_hours_later
FROM price_table p
WHERE p.symbol = i.symbol
AND p.time_of >= i.time_of + interval '3h'
AND p.time_of <= i.time_of + interval '27h'
ORDER BY p.time_of
LIMIT 1
)p ON true
ORDER BY i.symbol, i.time_of;
Для быстрой работы нужен индекс price_table (symbol, time_of). В идеале:
CREATE INDEX ON price_table (symbol, time_of) INCLUDE (price);
Если вы настаиваете, вы можете обернуть это в функцию SQL:
CREATE OR REPLACE FUNCTION markout_price_table(_start interval, _duration interval)
RETURNS TABLE (symbol text
, time_of timestamptz
, price float8
)
LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT i.symbol, i.time_of, p.price_3_hours_later
FROM interesting_times i
LEFT JOIN LATERAL (
SELECT p.price AS price_3_hours_later
FROM price_table p
WHERE p.symbol = i.symbol
AND p.time_of >= i.time_of + _start
AND p.time_of <= i.time_of + _start + _duration
ORDER BY p.time_of
LIMIT 1
)p ON true
ORDER BY i.symbol, i.time_of;
$func$;
Вызов:
SELECT * FROM markout_price_table('3h', '24h');
Зегарек уже предоставил действительную версию функции. Но обязательно добавьте соответствующие метки функций, чтобы не снижать производительность:
CREATE OR REPLACE FUNCTION markout_price(_symbol text, _time_of timestamptz, _start interval, _duration interval)
RETURNS float8
LANGUAGE sql STABLE STRICT PARALLEL SAFE AS -- !
$func$
SELECT p.price
FROM price_table p
WHERE p.symbol = _symbol
AND p.time_of >= _time_of + _start
AND p.time_of <= _time_of + _start + _duration
ORDER BY p.time_of
LIMIT 1;
$func$;
Вызов:
SELECT i.symbol, i.time_of
, markout_price(i.symbol, i.time_of, '3 h', '24 h') AS price_3_hours_later
FROM interesting_times i;
так будет ли это так же быстро, как загрузка всех данных и локальное слияние?
На самом деле любое решение (включая решение Зегарека) будет значительно быстрее, если у вас есть этот индекс.
Связанный:
Пожалуйста, дополните свой вопрос ожидаемыми/желаемыми результатами выборки данных. Опубликовать в виде форматированного текста, без изображений. Если вам нужно хорошее место для строительства в формате, см. Разумные решения.