Средняя таблица истории запасов
У меня есть таблица, которая отслеживает изменения в запасах по времени для некоторых магазинов и продуктов. Значение является абсолютным запасом, но мы добавляем новую строку только при изменении запаса. Этот дизайн должен был держать стол маленьким, потому что ожидается, что он будет быстро расти.
Это пример схемы и некоторые тестовые данные:
CREATE TABLE stocks (
id serial NOT NULL,
store_id integer NOT NULL,
product_id integer NOT NULL,
date date NOT NULL,
value integer NOT NULL,
CONSTRAINT stocks_pkey PRIMARY KEY (id),
CONSTRAINT stocks_store_id_product_id_date_key
UNIQUE (store_id, product_id, date)
);
insert into stocks(store_id, product_id, date, value) values
(1,10,'2013-01-05', 4),
(1,10,'2013-01-09', 7),
(1,10,'2013-01-11', 5),
(1,11,'2013-01-05', 8),
(2,10,'2013-01-04', 12),
(2,11,'2012-12-04', 23);
Мне нужно иметь возможность определить средний запас между начальной и конечной датами для каждого продукта и магазина, но моя проблема в том, что простая функция avg() не учитывает, что запас остается неизменным между изменениями.
Я хотел бы что-то вроде этого:
select s.store_id, s.product_id , special_avg(s.value)
from stocks s where s.date between '2013-01-01' and '2013-01-15'
group by s.store_id, s.product_id
в результате получается что-то вроде этого:
store_id product_id avg
1 10 3.6666666667
1 11 5.8666666667
2 10 9.6
2 11 23
Чтобы использовать функцию усреднения SQL, мне нужно было бы "распространить" вперед во времени предыдущее значение для store_id и product_id, пока не произойдет новое изменение. Есть идеи, как этого добиться?
3 ответа
Особая трудность этой задачи: вы не можете просто выбрать точки данных в пределах своего временного диапазона, но должны дополнительно рассмотреть самую последнюю точку данных перед временным диапазоном и самую раннюю точку данных после временного диапазона. Это варьируется для каждой строки, и каждая точка данных может существовать или не существовать. Требует сложного запроса и затрудняет использование индексов.
Вы можете использовать типы диапазонов и операторы (Postgres 9.2+), чтобы упростить вычисления:
WITH input(a,b) AS (SELECT '2013-01-01'::date -- your time frame here
, '2013-01-15'::date) -- inclusive borders
SELECT store_id, product_id
, sum(upper(days) - lower(days)) AS days_in_range
, round(sum(value * (upper(days) - lower(days)))::numeric
/ (SELECT b-a+1 FROM input), 2) AS your_result
, round(sum(value * (upper(days) - lower(days)))::numeric
/ sum(upper(days) - lower(days)), 2) AS my_result
FROM (
SELECT store_id, product_id, value, s.day_range * x.day_range AS days
FROM (
SELECT store_id, product_id, value
, daterange (day, lead(day, 1, now()::date)
OVER (PARTITION BY store_id, product_id ORDER BY day)) AS day_range
FROM stock
) s
JOIN (
SELECT daterange(a, b+1) AS day_range
FROM input
) x ON s.day_range && x.day_range
) sub
GROUP BY 1,2
ORDER BY 1,2;
Обратите внимание, я использую имя столбца day вместо date, Я никогда не использую базовые имена типов в качестве имен столбцов.
В подзапросе sub Я выбираю день из следующего ряда для каждого элемента с помощью оконной функции lead(), используя встроенную опцию, чтобы обеспечить "сегодня" по умолчанию, где нет следующей строки.
С этим я формирую daterange и сопоставить его с вводом с помощью оператора перекрытия&&, вычисляя результирующий диапазон дат с помощью оператора пересечения*,
Все диапазоны здесь с эксклюзивной верхней границей. Вот почему я добавляю один день к диапазону ввода. Таким образом, мы можем просто вычесть lower(range) от upper(range) чтобы получить количество дней.
Я предполагаю, что "вчера" - последний день с достоверными данными. "Сегодня" все еще может измениться в реальном приложении. Следовательно, я использую "сегодня" (now()::date) как исключительная верхняя граница для открытых диапазонов.
Я предоставляю два результата:
your_resultсогласен с вашими результатами.
Вы делите на количество дней в вашем диапазоне дат безоговорочно. Например, если элемент указан только за последний день, вы получите очень низкое (вводящее в заблуждение!) "Среднее".my_resultвычисляет одинаковые или более высокие числа.
Я делю на фактическое количество дней, в течение которого товар указан в списке. Например, если элемент указан только за последний день, я возвращаю указанное значение как среднее.
Чтобы понять разницу, я добавил количество дней, в течение которых товар был указан: days_in_range
Индекс и производительность
Для таких данных старые строки обычно не меняются. Это послужило бы отличным аргументом для материализованного представления:
CREATE MATERIALIZED VIEW mv_stock AS
SELECT store_id, product_id, value
, daterange (day, lead(day, 1, now()::date) OVER (PARTITION BY store_id, product_id
ORDER BY day)) AS day_range
FROM stock;
Затем вы можете добавить индекс GiST, который поддерживает соответствующий оператор&&:
CREATE INDEX mv_stock_range_idx ON mv_stock USING gist (day_range);
Большой тестовый кейс
Я провел более реалистичный тест с 200k строк. Запрос с использованием MV был примерно в 6 раз быстрее, что, в свою очередь, было в ~ 10 раз быстрее, чем запрос @Joop. Производительность сильно зависит от распределения данных. MV помогает больше всего с большими таблицами и высокой частотой записей. Кроме того, если в таблице есть столбцы, которые не относятся к этому запросу, MV может быть меньше. Вопрос стоимости против прибыли.
Я поместил все опубликованные решения (и адаптировал) в большую скрипку, чтобы поиграть:
SQL Fiddle с большим контрольным примером.
SQL Fiddle только с 40k строками - чтобы избежать тайм-аута на sqlfiddle.com
Это довольно быстро и грязно: вместо того, чтобы делать неприятную интервальную арифметику, просто присоединитесь к календарной таблице и сложите их все.
WITH calendar(zdate) AS ( SELECT generate_series('2013-01-01'::date, '2013-01-15'::date, '1 day'::interval)::date )
SELECT st.store_id,st.product_id
, SUM(st.zvalue) AS sval
, COUNT(*) AS nval
, (SUM(st.zvalue)::decimal(8,2) / COUNT(*) )::decimal(8,2) AS wval
FROM calendar
JOIN stocks st ON calendar.zdate >= st.zdate
AND NOT EXISTS ( -- this calendar entry belongs to the next stocks entry
SELECT * FROM stocks nx
WHERE nx.store_id = st.store_id AND nx.product_id = st.product_id
AND nx.zdate > st.zdate AND nx.zdate <= calendar.zdate
)
GROUP BY st.store_id,st.product_id
ORDER BY st.store_id,st.product_id
;
Этот ответ основан на подразумеваемой идее, что вы ищете среднее значение по дням, поэтому каждый день считается новой строкой. Хотя это может быть обработано в других механизмах SQL в виде строк, это было проще реализовать, выделив Среднее (сумма (значение)/ количество (значение)) и экстраполировав его на число дней с этим значением. используя формат таблицы и эту цель, я придумал это решение ( SQLFiddle)
select store_id, product_id, CASE WHEN sum(nextdate-date) > 0 THEN sum(Value*(nextdate-date)) / sum(nextdate-date) END as Avg_Value
from (
select *
, (
select value
from stocks b
where a.store_id = b.store_id
and a.product_id = b.product_id
and a.date >= b.date
order by b.date
limit 1
)*1.0 "value"
, coalesce((
select date
from stocks b
where a.store_id = b.store_id
and a.product_id = b.product_id
and a.date < b.date
order by b.date
limit 1
),case when current_date > '2013-01-12' then '2013-01-12' else current_date end) nextdate
from (
select store_id, product_id, min(case when date < '2013-01-07' then '2013-01-07' else date end) date
from stocks z
where date < '2013-01-12'
group by store_id, product_id
) a
union all
select store_id, product_id, date, value*1.0 "value"
, coalesce((
select date
from stocks b
where a.store_id = b.store_id
and a.product_id = b.product_id
and a.date < b.date
order by b.date
limit 1
),case when current_date > '2013-01-12' then '2013-01-12' else current_date end) nextdate
from stocks a
where a.date between '2013-01-07' and '2013-01-12'
) t
group by store_id, product_id
;
Запрос принимает первое вхождение каждого магазина / продукта перед параметром запуска ('2013-01-07'), и заменяет параметр как дату, если она больше, чем записанная дата таблицы, выбирает значение для этой ранней записи и дату первого изменения в таблице после параметра start, и сохраняет следующую дату ограниченной конечный параметр ('2013-01-12'). Вторая часть запроса объединения захватывает все изменения между двумя параметрами и следующим изменением или текущей датой, оба из которых ограничены конечным параметром. Наконец, вычисление выполняется для результатов, где значения умножаются на разницу дат при их суммировании, деленную на сумму дней между датами. Поскольку все даты ограничены в запросе, среднее значение будет усреднено по точному окну, которое передается в качестве параметров.
Не учитывая все это на PostgreSQL, я советую, если вы планируете реализовать это в функции, скопировав этот запрос и заменив все экземпляры '2013-01-07' с вашим именем параметра запуска и всеми экземплярами '2013-01-12' с вашим именем конечного параметра даст вам результаты, которые вы ищете для любого данного окна даты.
Изменить: Если вы хотите получить среднее значение за другую единицу времени, просто замените два экземпляра nextdate-date с любым расчетом интервала даты, который вы ищете. nextdate-date возвращает количество дней между двумя.