sql - 将一个数字分成多个波段

Question

我一直在处理将指标分成几个波段的问题。为了给您提供一些背景信息，让我们以每个客户有一定数量的订单为例。现在，客户可以订购 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 的 250 个订单收取 50 美元，对总共 400 种产品中的其余 150 种产品收取 45 美元。
示例 2:我希望能够向 Customer5 收取 250 美元的 250 美元订单，另外 250 美元的 45 美元以及 700 件中其余 200 件产品的 40 美元。
我如何在 PostgreSQL 中实现这一点？我的输出需要是 Customer1 的以下内容。拆分订单总数并将其加入定价层以获得相应金额的最佳方法是什么？

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

Answer 1

您可以将您的层视为间隔。
两个区间 [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 |
+-----------+----------+-------+----------+----------+-------+-------------------+