Как выбрать активную цену и предыдущую?

Question

[Андрей Мохов]

Дано: таблица с ценами на товары:
id - идентификатор товара
active_from - дата начала активности цены
active_to - срок до которого цена актуальна (может быть null, тогда считается бессрочная)
price - цена

требуется запросом вытащить цены товаров на конкретную дату (но в целом достаточно на текущий момент) и предыдущую цену, чтобы показать снизилась ли цена товара

Важно!
нужно не только актуальную, но и предыдущую.
интервалы активности могут пересекаться по разному, например: был товар у которого цена не менялась пол года, пришли менеджеры и решили сделать скидку до конца месяца, а после истечения скидки чтобы цена вернулась на прежнюю позицию, а за неделю до истечения срока ставят цену ещё ниже и до конца следующего месяца

допустим есть такие данные:

id | active_from | active_to  | price
 1 |  2019-01-01 |       null |   200
 1 |  2019-10-01 |       null |   150
 1 |  2019-11-01 | 2020-11-30 |   100
 
 2 |  2019-01-01 |       null |   200
 2 |  2019-06-01 | 2019-08-31 |   150
 2 |  2019-07-01 | 2019-09-30 |   140
 2 |  2019-11-01 |       null |   100

таким образом для первого товара в период с января по сентябрь цена 200, предыдущая: null
в октябре цена 150, предыдущая 200
в ноябре цена 100, предыдущая 150
начиная с декабря цена 150, предыдущая 100 (в декабре закончилась акция на товар и активной становится предыдущая активная цена)

для второго товара:
с января 200, null
в июне 150, 200
с июля по сентябрь 140, 150
в октябре 200, 140
с ноября 100, 200

пример запроса:

WITH
    prices (row_id, product_id, active_from, active_to, price) AS (
        VALUES
        (1, 1, '2019-01-01'::date, null, 200),
        (2, 1, '2019-10-01'::date, null, 150),
        (3, 1, '2019-11-01'::date, '2019-11-30'::date, 100),
        (4, 2, '2019-01-01'::date, null, 200),
        (5, 2, '2019-06-01'::date, '2019-08-31'::date, 150),
        (6, 2, '2019-07-01'::date, '2019-09-30'::date, 140),
        (7, 2, '2019-11-01'::date, null, 100),
        (8, 3, '2019-01-01'::date, null, 200),
        (9, 3, '2019-06-01'::date, '2019-10-31'::date, 150),
        (10, 3, '2019-07-01'::date, '2019-09-30'::date, 140),
        (11, 3, '2019-11-01'::date, null, 100)
    )
SELECT DISTINCT ON (product_id) *, lag("price") OVER (PARTITION BY "product_id" ORDER BY "active_from")
FROM prices
WHERE now() BETWEEN "active_from" AND coalesce("active_to", now())
ORDER BY product_id, "active_from" DESC

беда запроса в том, что отсекаются цены с истёкшими интервалами, поэтому предыдущую цену вытянуть не могу

Answer 1

[Андрей Мохов]

как-то так решается задача, идея в том, чтобы избавиться от столбца active_to дополнив изначальные данные строками:

WITH
    "prices" (row_id, product_id, active_from, active_to, price) AS (
        VALUES
        (1, 1, '2019-01-01'::date, null, 200),
        (2, 1, '2019-10-01'::date, null, 150),
        (3, 1, '2019-11-01'::date, '2019-11-30'::date, 100),
        (4, 2, '2019-01-01'::date, null, 200),
        (5, 2, '2019-06-01'::date, '2019-08-31'::date, 150),
        (6, 2, '2019-07-01'::date, '2019-09-30'::date, 140),
        (7, 2, '2019-11-01'::date, null, 100),
        (8, 3, '2019-01-01'::date, null, 200),
        (9, 3, '2019-06-01'::date, '2019-10-31'::date, 150),
        (10, 3, '2019-07-01'::date, '2019-09-30'::date, 140),
        (11, 3, '2019-11-01'::date, null, 100)
    )
SELECT
    u."row_id",
    u."product_id",
    u."active_from",
    u."active_to",
    u."price",
    lag(u."price") OVER (PARTITION BY u."product_id" ORDER BY u."effective_date") as "previous_price",
    u."effective_date"
FROM (
    SELECT "u".*,
        coalesce("u"."row_id" = lead("u"."row_id") OVER (PARTITION BY "u"."product_id" ORDER BY "u"."effective_date"), FALSE) AS "exclude_row"
    FROM (
        SELECT "p1".*, "p1"."active_from" AS "effective_date"
        FROM "prices" "p1"
        UNION ALL
        (
            SELECT DISTINCT ON ("prev"."product_id") "prev".*, ("p"."active_to" + '1 day'::interval)::date AS "effective_date"
            FROM "prices" "p"
            LEFT JOIN "prices" "prev" ON "prev"."product_id" = "p"."product_id"
                AND "p"."active_to" > "prev"."active_from"
                AND "p"."active_to" < coalesce("prev"."active_to", now())
            WHERE "p"."active_to" IS NOT NULL
            ORDER BY "prev"."product_id", "prev"."active_from" DESC
        )
    ) "u"
) u
WHERE NOT u."exclude_row"
ORDER BY "product_id", "effective_date"

Answer 2

[Сергей П]

Ну так а в чем у вас проблема-то? Вы в принципе не знаете как запрос писать и это задание такое?
Если так, то данный ресурс не для этого.
Если у вас конкретный вопрос, то заайте его конкретно. Что именно у вас не получется?
Не ясно как сформулировать рамочные условия с интервалами, пересекающими конкретную дату?
Не понятно как сгруппировать интервалы по товарам?
Не знаете про coalesce?
Запутались с граничными точками интервалов?
В чем проблема?

Comments

[Андрей Мохов]

а вы попробуйте запрос написать, чтобы понять, что это совсем не тривиальная задача, утрите мне нос

[Сергей П]

Андрей Мохов, о, я и не такие запросы писал. Вы меня на понт не берите. Если уже пытались решить задачу -- приводите пример, который у вас не заработал.

[Сергей П]

. Андрей Мохов, ну, ок, написал. Запрос, в общем-то. тривиальный. В чем проблема была?
Вы бы не ленились лучше и подготовили SQL с тестовыми данными для тестирования запроса и показали бы свои наработки, тут бы вас и исправили. А так вон в абьюз уже два рапорта улетело за задание.

[Андрей Мохов]

Сергей Паньков, ну так покажите свой запрос...

вот мой набор тестовых данных, но запрос кривой, т.к. выбираю не актуальную цену, а последнюю по дате активности

WITH
    prices (row_id, product_id, active_from, active_to, price) AS (
        VALUES
        (1, 1, '2019-01-01'::date, null, 200),
        (2, 1, '2019-10-01'::date, null, 150),
        (3, 1, '2019-11-01'::date, '2019-11-30'::date, 100),
        (4, 2, '2019-01-01'::date, null, 200),
        (5, 2, '2019-06-01'::date, '2019-08-31'::date, 150),
        (6, 2, '2019-07-01'::date, '2019-09-30'::date, 140),
        (7, 2, '2019-11-01'::date, null, 100),
        (8, 3, '2019-01-01'::date, null, 200),
        (9, 3, '2019-06-01'::date, '2019-10-31'::date, 150),
        (10, 3, '2019-07-01'::date, '2019-09-30'::date, 140),
        (11, 3, '2019-11-01'::date, null, 100)
    )
SELECT DISTINCT ON (product_id) *, lag("price") OVER (PARTITION BY "product_id" ORDER BY "active_from")
FROM prices
ORDER BY product_id, "active_from" DESC

[Сергей П]

Андрей Мохов, а почему просто не ограничить даты условиями вида:

active_from <= :qdate
AND (
  active_to IS NULL 
  OR active_to > :qdate
)

По товару можно добавить группировку, а какую вы будете использовать аггрегирующую функцию по цене - не важно, потому что интервалы. наверно, не пересекаются.

[Андрей Мохов]

Сергей Паньков, во-первых: интервалы пересекаются, об этом в условии задачи сказано; во-вторых: группировка мне ничего не даст, т.к. в условиях задачи сказано, что для текущей цены нужно ещё и предыдущую цену взять.
А теперь по поводу понтов: а такие запросы я так понимаю не писали, покажите пожалуйста агрегирующую функцию

[Андрей Мохов]

Сергей Паньков, если я добавляю в запрос указанное вами условие, то из выборки исключаются строки в которых есть active_to, в этом случае я не могу получить предыдущую цену, например:

...
WHERE now() BETWEEN "active_from" AND coalesce("active_to", now())

исключаются строки 3, 5, 6, 9, 10, а они нужны, чтобы на текущую дату узнать предыдущую

PS в моих данных выше я поменял 2020 год на 2019

[Сергей П]

Андрей Мохов, так, без паники. Я. действительно, не обратил внимания, что интервалы пересекаются.
Странно. То есть более поздний интрвал имеет бОльший приоритет, чем более ранний?
Мне кажется стоило отдельно упомянуть. что интервалы могут пересекаться и включать друг-друга, но приоритет по дате открытия интервала. Однако, согласен, это моя невнимательность.

Вы попытались решить задачу оконной функцией и это, действительно, довольно проблематично в данных обстоятельствах.
Тут бы подошло решение в виде храниой процедуры со стеком.
"Скобочная" вложенность этих интервалов может быть очень большой и чистый SQL лохо приспособлен для таких контекстно зависимых задач.

[Сергей П]

Андрей Мохов, добавьте, пожалуйста, в текст вопроса шаблон SQL с таблицей тестовых данных. Такие задачки народ с бОльшим удовольсивем берётся решать, когда нужно лишь поиграться с SQL.
Если бы вы приложили еще и ссылочку на онлайн REPL постгреса, чтобы не поднимать локальную базу для попробовать, вообще было бы круто

[Андрей Мохов]

Сергей Паньков,

о есть более поздний интрвал имеет бОльший приоритет, чем более ранний?

именно так, т.к. сидят менеджеры и для товара у которого цена не менялась пол года ставят на какие-нибудь праздники акцию, следовательно: интервал вышел, цена должна вернуться прежняя, а бывает ещё и так:
завезли допустим арбузы, видят, что уходят плохо, ставят до конца лета скидку, а в последнюю неделю ещё один интервал добавляют, в общем по задаче пересекаться могут и так и эдак, приоритетом считается, что актуальна та цена, дата активности которой позже

в виде хранимой процедуры со стеком

мало подходит такое решение, т.к. нужно на лету считать и быстро, а данных много

[Сергей П]

Андрей Мохов, а если предыдущую цену брать отдельным вложенным селектом?

[Андрей Мохов]

Сергей Паньков, думал об этом, но с условиями выборки беда

пока вижу только вариант "дополнить" данные как-то так:

WITH prices AS (...)
SELECT ...
FROM prices
UNION ALL
SELECT active_to as active_from, ...
FROM prices
WHERE active_to IS NOT NULL

чтобы избавиться от столбца active_to

надежда была на то, что может кто уже сталкивался с подобным и есть "проторенная" дорожка

[Сергей П]

Андрей Мохов,

в виде хранимой процедуры со стеком

мало подходит такое решение, т.к. нужно на лету считать и быстро, а данных много

Тут как раз не проблема. Хранимка будет работать в потоке как специфическая оконная функция. На реальных данных она не будет работать заметно медленнее, чем обычный запрос.
Не так уж много интервалов пересекаются с конкретной датой. А из стека предыдущий интервал брать даже быстрее, чем сабселектом. Так что присмотритесь к хранимкам.

Кстати, есть еще вариант. Сделать дополнительную таблицу, где хранить "раскрытые скобки", то есть непересекающиеся упрощенные интервалы. Так вы перенесёте вычислительную сложность из чтения (которое часто) на запись (которая сравнительно редко).
При модифиуации вашей сложно-интервальной таблицы удаляете и пересчитываете упрощенные плоские интервалы в кэше. Там же можно и предыдущие значения цен хранить готовенькими.
Денормализация и некоторая избыточность, зато на чтение будет просто реактивной и в любой момент можно перестроить плоскую таблицу.

[Андрей Мохов]

Сергей Паньков, денормализация это крайний вариант, т.к. придётся писать кучу триггеров, это у меня как запасной вариант, у задачи сроки чутка меньше, потому хотел обойтись запросом
своя агрегирующая функция тоже как вариант, но пока раздумываю по тем же соображениям