如何计算“阶梯式”？ SQL中的价格？ [英] How can I calculate a "stepped" price in SQL?
问题描述
嘿，
我的数据库中有几个产品。一些是乐队的价格，其他的是基于数量的阶梯乐队的价格：
绑定（非步进）
productA：1-5 = 25英镑，6-10英镑= 22英镑
所以，如果你买5，那么所有都是25英镑（125英镑），但如果你买6则所有都是22英镑（132英镑）
步进条带
productB：1-5 =£105,6-10 =£100
所以如果你买6 ，前5个是105英镑，但第6个（或第7个，第8个等）是100英镑
5 * 105 +（6-5）* 100
是否有整洁 * 这样做？
谢谢^ _ ^
Andy
Hey,
I have a couple of products in my db. some are priced in bands and others are priced in stepped bands based on quantity:
banding (not stepped)
productA: 1-5 = £25, 6-10 = £22
so if you buy 5 then all are £25 (£125) but if you buy 6 then all are £22 (£132)
stepped banding
productB: 1-5 = £105, 6-10 = £100
so if you buy 6, the first 5 are £105 but the 6th (or 7th, 8th etc) are at £100
5*105+(6-5)*100
is there a "neat"* way to do this?
thanks ^_^
Andy
* Neat：
1.布置整齐;良好的秩序。
2.完成或表现出技能或效率。
1.arranged in a tidy way; in good order.
2.done with or demonstrating skill or efficiency.
我尝试过：
所以这就是我到目前为止：
What I have tried:
So here is what I have so far:
select
c.company_id,
c.company_name,
b.pricing_price,
p.product_name,
l.licence_quantity,
b.pricing_price * l.licence_quantity as company_cost
from company c
inner join licence l on c.company_id = l.licence_companyid
inner join product p on l.licence_productid = p.product_id
inner join price_banding b on l.licence_productid = b.pricing_productid
where
b.pricing_band_min<=l.licence_quantity and
(b.pricing_band_max >= l.licence_quantity or b.pricing_band_max is null) and
b.pricing_stepped = 0
我在视图中得到定价范围qtys：
I get the pricing band qtys in a view:
with banding as(
select
p.pricing_id,
p.pricing_productid,
p.pricing_band_min,
p2.pricing_band_min-1 as pricing_band_max,
p.pricing_price,
p.pricing_stepped,
row_number() over (partition by p.pricing_productid,p.pricing_band_min order by p2.pricing_band_min-1) as num
from pricing p
left outer join pricing p2 on p.pricing_productid = p2.pricing_productid and p.pricing_band_min<p2.pricing_band_min
)
select
p.pricing_id,
p.pricing_productid,
p.pricing_band_min,
p.pricing_band_max,
p.pricing_price,
p.pricing_stepped
from banding p
where p.num = 1
推荐答案
嘿Andy，
我很难想到任何非常简洁的价格条带形式（步进而非步进）。
（整洁）解决方案可能是通过描述一个另一方面的定价类型。这样做可以使您的数据更好，并使价格计算变得更好。当然，你仍然可以在外部/表示层中用步进/非步进来描述它。
这就是我的意思：想象一下以下步进绑定：
产品A：
1-5 = 25
6 = 7
7-10 = 22
这与你的例子中的价格完全相同（6是132 = 5 * 25 + 1 * 7）但数据以阶梯式条带的形式描述。
完成后，SQL成为绑定的标准连接。
希望这会有所帮助！ ？
Jon
Hey Andy,
I find it hard to think of anything very neat with the distinctly different forms of price banding (stepped and not stepped).
The (neat) solution might be by describing one type of pricing in the terms of the other. Doing this would neaten your data and make the price calculations nice. You can still, of course, in the external / presentation layer describe it in terms of stepped / not stepped.
Here's what I mean: Imagine the following stepped banding:
product A:
1-5 = 25
6 = 7
7-10 = 22
This works out exactly the same total as the price in your example (6 are 132 = 5*25+1*7) but the data is described in the terms of a stepped banding.
Once this is done, the SQL becomes standard joins on a banding.
Hope this helps!?
Jon
如果我已正确理解您的问题，并假设您的价格区间定义明确 （没有重叠或间隙），那么这样的事情应该有效：
If I've understood your question correctly, and assuming your price-bands are well defined (no overlaps or gaps), then something like this should work:
WITH prices As
(
-- Not stepped:
-- * Only a single price band should match;
-- * Total price = quantity * price;
SELECT
l.licence_companyid,
l.licence_productid,
l.licence_quantity,
b.pricing_price,
b.pricing_price * l.licence_quantity As company_cost
FROM
licence As l
INNER JOIN price_banding As b
ON b.pricing_productid = l.licence_productid
WHERE
b.pricing_stepped = 0
And
b.pricing_band_min <= l.licence_quantity
And
(b.pricing_band_max >= l.licence_quantity Or b.pricing_band_max Is Null)
UNION ALL
-- Stepped:
-- * Match all price bands that apply (min < quantity);
-- * Total price = sum of [quantity for this band] * price;
SELECT
l.licence_companyid,
l.licence_productid,
Max(l.licence_quantity),
Max(b.pricing_price),
Sum(b.pricing_price * CASE
WHEN b.pricing_band_max Is Null THEN 1 + l.licence_quantity - b.pricing_band_min
WHEN b.pricing_band_max >= l.licence_quantity THEN 1 + l.licence_quantity - b.pricing_band_min
ELSE 1 + b.pricing_band_max - b.pricing_band_min
END)
FROM
licence As l
INNER JOIN price_banding As b
ON b.pricing_productid = l.licence_productid
WHERE
b.pricing_stepped = 1
And
b.pricing_band_min <= l.licence_quantity
GROUP BY
l.licence_companyid,
l.licence_productid
)
SELECT
c.company_id,
c.company_name,
b.pricing_price,
p.product_name,
b.licence_quantity,
b.company_cost
FROM
prices As b
INNER JOIN company As c
ON c.company_id = b.licence_companyid
INNER JOIN product As p
ON p.product_id = b.licence_productid
;
如果这不对，你可以创建一个 SQL小提琴 [ ^ ]带有一些虚拟样本数据来演示？
If that's not right, can you create a SQL Fiddle[^] with some dummy sample data to demonstrate?
这篇关于如何计算“阶梯式”？ SQL中的价格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
