Блог

Я столкнулся с проблемой разделения метрики на несколько диапазонов. Чтобы дать вам некоторый контекст, давайте рассмотрим этот пример, где у нас есть определенное количество заказов на одного клиента. Теперь клиент может заказать n количество продуктов. Давайте предоставим клиенту определенную скидку в зависимости от количества заказов. Скидки предлагаются на основе многоуровневой модели. Я оставляю несколько категорий продуктов, чтобы упростить его. Вот несколько примеров таблиц.

Таблица заказов

 Customer    |   order_no
----------------------------
Customer1   |   400
Customer2   |   1200
Customer3   |   40
Customer4   |   2000
Customer5   |   700
  

Таблица многоуровневых цен

 Tier   | lower_th | higer_th | price |
--------------------------------------
Tier1  | 0        | 250      | 50    |
TIer2  | 251      | 500      | 45    |
Tier3  | 501      | 1000     | 40    |
TIer4  | 1001     | 10000    |  30   |
  

Пример1: я хочу иметь возможность взимать с Customer1 50 долларов США за 250 заказов и 45 долларов США за остальные 150 продуктов из общего количества 400.

Пример2: я хочу иметь возможность взимать с Customer5 50 долларов за 250 заказов и 45 долларов за еще 250 и 40 долларов за остальные 200 продуктов из общего количества 700.

Как мне добиться этого в PostgreSQL? Мой вывод должен быть следующим для Customer1. Каков наилучший способ разделить общее количество заказов и присоединить его к уровням ценообразования, чтобы получить соответствующую сумму?

 Customer  | order_no | charges |
--------------------------------
Customer1 | 250      | 50      |
Customer1 | 150      | 45      |
  

Вы можете думать о своих уровнях как об интервалах.

Два интервала [a1, b1] и [a2, b2] пересекаются, когда

 a1 <= b2 AND b1 >= a2
  

Количество заказов — это еще один интервал, который всегда начинается с 1.

Ваши два интервала: уровни [lower_th, higer_th] и порядки [1, order_no] .

Запрос представляет собой простое соединение с использованием этого выражения пересечения:

 SELECT *
    ,CASE WHEN O.order_no > T.higer_th
    THEN T.higer_th - T.lower_th   1 -- full tier
    ELSE O.order_no - T.lower_th   1
    END AS SplitOrderNumbers
FROM
    Orders AS O
    INNER JOIN Tiers AS T
        -- ON 1 <= T.higer_th AND O.order_no >= T.lower_th
        ON O.order_no >= T.lower_th
ORDER BY
    O.Customer
    ,T.lower_th
;
  

На самом деле вам не нужна 1 <= T.higer_th часть, потому что она всегда истинна, поэтому выражение становится простым O.order_no >= T.lower_th .

Кроме того, обычно лучше хранить интервалы как [closed; open) . Обычно это упрощает арифметику, аналогично тому, почему большинство языков программирования имеют индексы массива, начинающиеся с 0, а не с 1. Ваши интервалы кажутся [closed; closed] . В этом случае вам нужно установить lower_th значение 1 , а не 0 и иметь 1 в вычислениях.

При такой корректировке выборки данных этот запрос выдает следующий результат:

  ----------- ---------- ------- ---------- ---------- ------- ------------------- 
| Customer  | order_no | Tier  | lower_th | higer_th | price | SplitOrderNumbers |
 ----------- ---------- ------- ---------- ---------- ------- ------------------- 
| Customer1 |      400 | Tier1 |        1 |      250 | 50.00 |               250 |
| Customer1 |      400 | Tier2 |      251 |      500 | 45.00 |               150 |
| Customer2 |     1200 | Tier1 |        1 |      250 | 50.00 |               250 |
| Customer2 |     1200 | Tier2 |      251 |      500 | 45.00 |               250 |
| Customer2 |     1200 | Tier3 |      501 |     1000 | 40.00 |               500 |
| Customer2 |     1200 | Tier4 |     1001 |    10000 | 30.00 |               200 |
| Customer3 |       40 | Tier1 |        1 |      250 | 50.00 |                40 |
| Customer4 |     2000 | Tier1 |        1 |      250 | 50.00 |               250 |
| Customer4 |     2000 | Tier2 |      251 |      500 | 45.00 |               250 |
| Customer4 |     2000 | Tier3 |      501 |     1000 | 40.00 |               500 |
| Customer4 |     2000 | Tier4 |     1001 |    10000 | 30.00 |              1000 |
| Customer5 |      700 | Tier1 |        1 |      250 | 50.00 |               250 |
| Customer5 |      700 | Tier2 |      251 |      500 | 45.00 |               250 |
| Customer5 |      700 | Tier3 |      501 |     1000 | 40.00 |               200 |
 ----------- ---------- ------- ---------- ---------- ------- ------------------- 
  

Для данных о ценах я бы использовал такую таблицу, чтобы упростить обслуживание данных

 create table pricing_data
(
   high_limit int, 
   price numeric
);
  

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

 create view pricing as
  select coalesce(lag(high_limit) over (order by high_limit), 0) as last_limit,
         high_limit, price
    from pricing_data;
  

Это упрощает разбивку на уровни ценообразования:

 select o.customer, 
       least(o.order_no - p.last_limit, p.high_limit - p.last_limit) as order_no,
       p.price as charges
  from orders o
  join pricing p on p.last_limit < o.order_no
 order by o.customer, p.price desc
;
  

Результат:

 customer    order_no    charges
Customer1    250            50
Customer1    150            45
Customer2    250            50
Customer2    250            45
Customer2    500            40
Customer2    200            30
Customer3     40            50
Customer4    250            50
Customer4    250            45
Customer4    500            40
Customer4   1000            30
Customer5    250            50
Customer5    250            45
Customer5    200            40
14 rows
  

Скрипка здесь.