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

Дано: таблица с ценами на товары:
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


беда запроса в том, что отсекаются цены с истёкшими интервалами, поэтому предыдущую цену вытянуть не могу
  • Вопрос задан
  • 143 просмотра
Решения вопроса 1
@mokhovcom Автор вопроса
как-то так решается задача, идея в том, чтобы избавиться от столбца 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"
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
trapwalker
@trapwalker
Программист, энтузиаст
Ну так а в чем у вас проблема-то? Вы в принципе не знаете как запрос писать и это задание такое?
Если так, то данный ресурс не для этого.
Если у вас конкретный вопрос, то заайте его конкретно. Что именно у вас не получется?
Не ясно как сформулировать рамочные условия с интервалами, пересекающими конкретную дату?
Не понятно как сгруппировать интервалы по товарам?
Не знаете про coalesce?
Запутались с граничными точками интервалов?
В чем проблема?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы