Производительность PostgreSQL: запрос, чтобы найти акции, достигающие 52-недельного максимума (объединяя строки максимальных значений)
У меня очень простая структура базы данных с ценами на акции на конец дня, которые выглядят примерно так:
finalyzer_pricedata=> \d pdEndOfDayPricEentity
Table "public.pdendofdaypriceentity"
Column | Type | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------
id | uuid | | not null |
close | numeric(19,2) | | not null |
day | date | | not null |
instrument_id | uuid | | not null |
(instrument_id - уникальный идентификатор акции)
Теперь я хочу выбрать все instrument_id которые достигли своего 52-недельного максимума на текущей неделе. (т.е. все акции, где close столбец за последние 7 дней выше чем за 52 недели до этого)
Я перепробовал много разных подходов: group by и max(), select selected on, оконные функции (row_number), но мне не удалось получить его ниже 150 секунд. Мой лучший (и самый простой) подход в настоящее время:
select CAST(weekHigh.instrument_id AS VARCHAR) instrumentId,
weekHigh.maxClose weekHighValue,
yearHigh.maxClose yearHighValue,
yearHigh.maxDay yearHighDay
from
(select distinct on (eod.instrument_id) instrument_id,
eod.close maxClose,
eod.day as maxDay
from pdendofdaypriceentity eod
where eod.day BETWEEN (CAST('2018-11-12' AS date) - interval '52 weeks') AND (CAST('2018-11-12' AS date) - interval '1 day')
order by eod.instrument_id, close desc) yearHigh
inner join (select eod.instrument_id instrument_id, max(eod.close) maxClose
from pdendofdaypriceentity eod
where eod.day BETWEEN CAST('2018-11-12' AS date) AND CAST('2018-11-18' AS date)
group by eod.instrument_id) weekHigh
on weekHigh.instrument_id = yearHigh.instrument_id
where weekHigh.maxClose > yearHigh.maxClose;
Я очень хорошо знаю, что есть десятки подобных вопросов, но эти подходы привели меня к рабочему решению, но ни один из них не помог мне улучшить производительность. Таблица содержит 10 миллионов строк из разных 28000 акций. И это только станет больше. Есть ли способ реализовать это требование с помощью второго запроса без денормализации? Любой вид индексов и т. Д. Будет хорошо, очевидно.
План запроса для вышеуказанного подхода:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=148153.45..1136087.99 rows=6112 width=74) (actual time=3056.748..144632.288 rows=411 loops=1)
Hash Cond: (eod.instrument_id = eod_1.instrument_id)
Join Filter: ((max(eod_1.close)) > eod.close)
Rows Removed by Join Filter: 27317
-> Unique (cost=0.56..987672.73 rows=18361 width=26) (actual time=2.139..141494.533 rows=28216 loops=1)
-> Index Scan using test3 on pdendofdaypriceentity eod (cost=0.56..967290.80 rows=8152771 width=26) (actual time=2.117..79396.893 rows=8181608 loops=1)
Filter: ((day >= '2017-11-13 00:00:00'::timestamp without time zone) AND (day <= '2018-11-11 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 1867687
-> Hash (cost=147923.68..147923.68 rows=18337 width=48) (actual time=2793.633..2793.639 rows=27917 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1739kB
-> HashAggregate (cost=147556.94..147740.31 rows=18337 width=48) (actual time=2301.968..2550.387 rows=27917 loops=1)
Group Key: eod_1.instrument_id
-> Bitmap Heap Scan on pdendofdaypriceentity eod_1 (cost=2577.01..146949.83 rows=121422 width=22) (actual time=14.264..1146.610 rows=115887 loops=1)
Recheck Cond: ((day >= '2018-11-12'::date) AND (day <= '2018-11-18'::date))
Heap Blocks: exact=11992
-> Bitmap Index Scan on idx5784y3l3mqprlmeyyrmwnkt3n (cost=0.00..2546.66 rows=121422 width=0) (actual time=12.784..12.791 rows=115887 loops=1)
Index Cond: ((day >= '2018-11-12'::date) AND (day <= '2018-11-18'::date))
Planning time: 13.758 ms
Execution time: 144635.973 ms
(19 rows)
мои текущие (в основном случайные) показатели:
Indexes:
"pdendofdaypriceentity_pkey" PRIMARY KEY, btree (id)
"ukcaddwp8kcx2uox18vss7o5oly" UNIQUE CONSTRAINT, btree (instrument_id, day)
"idx5784y3l3mqprlmeyyrmwnkt3n" btree (day)
"idx5vqqjfube2j1qkstc741ll19u" btree (close)
"idxcaddwp8kcx2uox18vss7o5oly" btree (instrument_id, day)
"test1" btree (close DESC, instrument_id, day)
"test2" btree (instrument_id, day, close DESC)
"test3" btree (instrument_id, close DESC)
3 ответа
Попробуйте следующий запрос
select weekHigh.instrument_id,
weekHigh.maxClose weekLowValue,
yearHigh.maxClose yearLowValue
from (
select instrument_id,
max(eod.close) maxClose
from pdendofdaypriceentity eod
where eod.day BETWEEN (CAST('2018-11-12' AS date) - interval '52 weeks') AND (CAST('2018-11-12' AS date) - interval '1 day')
group by eod.instrument_id
) yearHigh
inner join (
select eod.instrument_id instrument_id, max(eod.close) maxClose
from pdendofdaypriceentity eod
where eod.day BETWEEN CAST('2018-11-12' AS date) AND CAST('2018-11-18' AS date)
group by eod.instrument_id
) weekHigh on weekHigh.instrument_id = yearHigh.instrument_id
where weekHigh.maxClose > yearHigh.maxClose;
с указателем pdendofdaypriceentity(day, instrument_id, close), Обратите внимание, что отсутствует maxDay что у вас есть в вашем запросе.
Можно добавить maxDay другим присоединиться к pdendofdaypriceentityОднако я бы начал с запроса выше без distinct on а также order by в первом подзапросе.
Потенциально запрос может стать намного быстрее, если вы запрашиваете фиксированные окна, а не динамический диапазон.
Например
- Этот год (календарь)
- Этот квартал
- Этот месяц
Это потому, что он позволяет вам использовать индекс, который может найти максимальное закрытие для инструмента, не считывая диапазон значений.
Например, если мы хотим найти самое высокое значение в 2017 году
Индекс первого дня
|day|instrument|close|
|31-12-2016|1|12.00|
|01-01-2017|1|19.00| <-- start scan here
...
|06-01-2017|1|31.00| <-- highest
...
|31-12-2017|1|11.00| <-- end scan here
|01-01-2018|1|13.00|
Замечания: close эффективно неупорядочен, следовательно, сканирование
Годовой индекс
|extract(year from day)|instrument|close|day|
|2016|1|12.00|31-12-2016|
|2017|1|31.00|06-01-2017| <-- highest close for that year at the top
...
|2017|1|19.00|01-01-2017|
...
|2017|1|11.00|31-12-2017|
|2018|1|13.00|01-01-2018|
Таким образом, вы можете сканировать в 365 раз меньше записей. И вы все еще можете запросить день.
Примечание: ваш фильтр должен использовать ту же функцию даты, что и индекс
Вы бы хотели провести анализ данных где-нибудь еще? Размеры, которые вы упоминаете, не так уж и хороши для разумного подхода R или Python. Приведенный ниже пример работает с таблицей из 20 миллионов строк (28 000 идентификаторов приборов с 720 наблюдениями в каждой) и требует ±1 секунды на текущем Macbook Pro. Я произвел некоторые ложные данные, чтобы пойти с этим.
С R и data.table:
# Filter data for the past 52 weeks
result <-
data[day >= max(day) - 52*7 & day <= max(day)]
# Get all instances where close was at max
result_52max <-
result[result[, .I[close == max(close)], by = instrument_id]$V1]
# Get all instances where this happened last week
result_7max <-
result_52max[day >= max(day) - 7 & day <= max(day)]
Среднее время выполнения: <1 секунда.
репродукция
Данные
# Attention: takes a couple of minutes
library(data.table)
set.seed(1701)
data <- NULL
id <- 1
n <- 1000
obs <- 720
for(j in 1:28){
dt <- NULL
dates <- seq.Date(from = as.Date("2017-01-01"), by = "day", length.out = obs)
for(i in (1+(j-1)*n):(n*j)){
start <- sample(1:200, 1)
dt <- rbindlist(list(dt,
data.table(id = id:(id+obs-1),
close = abs(start + cumsum(sample(c(-1, 1), obs, TRUE))),
day = dates,
instrument_id = rep(i, obs))))
id <- id+obs
}
data <- rbindlist(list(data, dt))
}
Состав
> str(data)
Classes ‘data.table’ and 'data.frame': 20160000 obs. of 4 variables:
$ id : int 1 2 3 4 5 6 7 8 9 10 ...
$ close : num 27 26 27 28 27 28 29 28 29 30 ...
$ day : Date, format: "2017-01-01" "2017-01-02" "2017-01-03" ...
$ instrument_id: int 1 1 1 1 1 1 1 1 1 1 ...
- attr(*, ".internal.selfref")=<externalptr>
Участок для первых пяти идентификаторов инструментов
library(ggplot2)
ggplot(subset(data, data$instrument_id <= 5),
aes(day, close, color = as.factor(instrument_id),
group = as.factor(instrument_id))) +
geom_line()