Например, у меня есть такая таблица:
CREATE TABLE sales (
id int NOT NULL PRIMARY KEY,
sku text NOT NULL,
date date NOT NULL,
amount real NOT NULL,
CONSTRAINT date_sku UNIQUE (sku,date)
)
Можно ли как-то проверить для каждого sku, если каждые 2 дня средние продажи больше, чем, например, 14 проданных товаров. Я хочу найти диапазоны дат, процент и сумму, проданную в те дни.
например, для артикула B в моем примере было продано 15 at 2022-01-01
и 20 at 2022-01-02
, а среднее значение за эти 2 дня составляет 17,5, что больше, чем 14, поэтому оно появится в моем результате, и изменение составит 17,5 / 14 = 1,25.
Снова на следующие 2 дня у нас есть 20 at 2022-01-02
и 13 at 2022-01-03
. Следовательно, среднее значение равно 16,5, что больше 14, и оно появится в результате.
но для 13 at 2022-01-03
и 12 at 2022-01-04
и в среднем около 12,5. Поскольку 12,5 не больше 14, оно не будет отображаться в результате.
мой желаемый результат с примером суммы 14:
sku start_date end_date amount_sold change_rate
B 2022-01-01 2022-01-02 17.5 1.25
B 2022-01-02 2022-01-03 16.5 1.17
D 2022-01-01 2022-01-02 28 2
Я пытался использовать CASE WHEN
, но знаю, что это не сработает для больших данных, таких как один год:
SELECT *
FROM (
SELECT sku,
AVG(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-02' THEN amount END) AS first_in,
AVG(CASE WHEN date BETWEEN '2022-01-02' AND '2022-01-03' THEN amount END) AS second_in,
AVG(CASE WHEN date BETWEEN '2022-01-03' AND '2022-01-04' THEN amount END) AS third_in
FROM sales
GROUP BY sku
) AS t
WHERE first_in > 14
OR second_in > 14
OR third_in > 14
Привет, что означает «сумма 14»?
@Atmo извините за запутанный вопрос. я отредактировал
@NickW 14 - это просто число, которое использовалось в расчетах и было введено пользователем.
Вы можете создать временную таблицу (или cte), для каждого дня вы должны хранить среднее значение этого дня и следующего дня. Затем запросите временную таблицу.
Как правило, используйте LEAD
(или LAG
) для извлечения данных из следующей или предыдущей записи. По крайней мере, это то, что я сделал до того, как вы попросили, возможно, несколько дней. Другие оконные функции подходят для ваших нужд, если вы хотите более 1 дня:
SELECT *, averageamount/14
FROM (
SELECT sku, date,
MAX(date) OVER w AS nextdate,
AVG(amount) OVER w AS averageAmount
FROM sales
WINDOW w AS (PARTITION BY sku ORDER BY date RANGE BETWEEN '0 day' PRECEDING AND '2 days' FOLLOWING )
) s
WHERE averageAmount > 14
Это выше выбирает все диапазоны длиной до 3 дней (дни D
, D+1
и D+2
). Вы можете удалить диапазоны длиной менее 3 дней, добавив дополнительное условие:
AND nextdate >= date + interval '2 days'
Это будет работать только в течение 1 дня после. Можно ли как-то расширить это, например, каждые 3 дня в среднем?
Отредактировал ответ, чтобы сделать его более универсальным.
Я просмотрел ваши данные и понятия не имею, какими должны быть значения в
amount_sold
(или группировке) иchange_percentage
. Почему бы вам не отредактировать свой вопрос, чтобы объяснить это? И что вы пробовали до сих пор?