英文:
Calculate a value with multiple columns in a each record
问题
在下面的表格中，我需要检查MainPrice列是否小于或等于Price01、Price02、Price03到Price06。
例如，考虑第一条记录：
MainPrice <= Price01 (22.99 <= 10.92)
结果是false，所以接下来应该检查Price02，同样是false。
MainPrice <= Price05 (22.99 <= 63.37)
满足条件，所以我需要选择Price05和MW05以及之前的值，即Price04和Price05。
现在有了MW05、Price05、MW04、Price04和MainPrice的值。
使用这些值，我需要使用以下公式进行计算：
(((MainPrice - Price04) * (MW05 - MW04)) / (Price05 - Price04)) + MW04
(((22.99 - 16.03) * (521 - 521)) / (63.37 - 16.03)) + 521
是否可以在一个查询中完成这个操作？
我的方法是使用CASE语句，并创建一个函数来返回计算后的值。
SELECT
CalculatedMW = CASE
WHEN Price01 >= MainPrice
THEN MW01
WHEN Price02 >= MainPrice
THEN dbo.calculate(MainPrice, MW02, MW01, Price02, Price01)
WHEN Price03 >= MainPrice
THEN dbo.calculate(MainPrice, MW03, MW02, Price03, Price02)
WHEN Price04 >= MainPrice
THEN dbo.calculate(MainPrice, MW04, MW03, Price04, Price03)
WHEN Price05 >= MainPrice
THEN dbo.calculate(MainPrice, MW05, MW04, Price05, Price04)
WHEN Price06 >= MainPrice
THEN 0
END
FROM
dbo.Pricing
英文:
I'm trying to perform a calculation in a select query on below table:
| MainPrice | Mw01 | Price01 | Mw02 | Price02 | Mw03 | Price03 | Mw04 | Price04 | Mw05 | Price05 | Mw06 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 22.9 | 379 | 10.92 | 464 | 12.42 | 464 | 16.03 | 521 | 16.03 | 521 | 63.37 | 521 |
In this table, I have a column MainPrice which I need to check if it is less than or equal to Price01, Price02, Price03 through Price06.
Example considering first record:
MainPrice <= Price01 (22.99 <= 10.92)
is false, so then it should next check against Price02, which is again false.
MainPrice <= Price05 (22.99 <= 63.37)
satisfies the condition so I need to pick Price05 & MW05 with previous values which is are Price04 & Price05.
So now have values MW05, Price05, MW04, Price04 & MainPrice.
With these values I need to do a calculation using this formula:
(((MainPrice - Price04) * (MW05 - MW04)) / (Price05 - Price04)) + MW04
(((22.99 - 16.03) * (521 - 521)) / (63.37 - 16.03)) + 521
Is it possible to do this in one select query?
My approach: I'm doing using cases and created a function which will return a calculated value.
SELECT
CalculatedMW = CASE
WHEN Price01 >= MainPrice
THEN MW01
WHEN Price02 >= MainPrice
THEN dbo.calculate(MainPrice, MW02, MW01, Price02, Price01)
WHEN Price03 >= MainPrice
THEN dbo.calculate(MainPrice, MW03, MW02, Price03, Price02)
WHEN Price04 >= MainPrice
THEN dbo.calculate(MainPrice, MW04, MW03, Price04, Price03)
WHEN Price05 >= MainPrice
THEN dbo.calculate(MainPrice, MW05, MW04, Price05, Price04)
WHEN Price06 >= MainPrice
ELSE 0
END
FROM
dbo.Pricing
答案1
得分: 3
以下是已翻译的代码部分：
select MainPrice,
((MainPrice - pp) * (m - mm)) / (p - pp) + mm res
from yourtable
cross apply (
select top(1) *
from (
select *, lag(p) over(order by n) pp, lag(m) over(order by n) mm
from (
values
(1, Price01, MW01),
(2, Price02, MW02),
(3, Price03, MW03),
(4, Price04, MW04),
(5, Price05, MW05),
(6, Price06, MW06)
) t(n,p,m)
) t
where t.p <= MainPrice
order by n
) t
请注意，我只翻译了代码部分，没有包括问题的回答或其他内容。
英文:
You can unpivot pairs of (Price__, MW__) and select your parameters with CROSS APPLY
select MainPrice,
((MainPrice - pp) * (m - mm)) / (p - pp) + mm res
from yourtable
cross apply (
select top(1) *
from (
select *, lag(p) over(order by n) pp, lag(m) over(order by n) mm
from (
values
(1, Price01, MW01),
(2, Price02, MW02),
(3, Price03, MW03),
(4, Price04, MW04),
(5, Price05, MW05),
(6, Price06, MW06)
) t(n,p,m)
) t
where t.p <= MainPrice
order by n
) t
Not sure what if the first pair satisfies the condition t.p <= MainPrice. You may need to change your formula for this terminal case.
答案2
得分: 0
以下是代码部分的翻译：
One way is first to UNPIVOT the data, and it's a kind of more complicated as we have two pairs of columns. So, having this data:
一种方法是首先对数据进行UNPIVOT操作，由于有两组列，这种方法较为复杂。因此，有了以下数据：
DROP TABLE IF EXISTS #Pricing;
CREATE TABLE #Pricing
(
[MainPrice] MONEY NOT NULL,
[Price01] MONEY NOT NULL,
[MW01] INT NOT NULL,
[Price02] MONEY NOT NULL,
[MW02] INT NOT NULL,
[Price03] INT NOT NULL,
[MW03] INT NOT NULL,
[Price04] MONEY NOT NULL,
[MW04] INT NOT NULL,
[Price05] MONEY NOT NULL,
[MW05] INT NOT NULL,
[Price06] MONEY NOT NULL,
[MW06] INT NOT NULL,
[Price07] MONEY NOT NULL,
[MW07] INT NOT NULL,
[Price08] MONEY NOT NULL,
[MW08] INT NOT NULL,
[Price09] MONEY NOT NULL,
[MW09] INT NOT NULL,
[Price10] MONEY NOT NULL,
[MW10] INT NOT NULL,
[Price11] MONEY NOT NULL,
[MW11] INT NOT NULL,
[Price12] MONEY NOT NULL,
[MW12] INT NOT NULL
);
创建表 #Pricing
(
[MainPrice] MONEY NOT NULL,
[Price01] MONEY NOT NULL,
[MW01] INT NOT NULL,
[Price02] MONEY NOT NULL,
[MW02] INT NOT NULL,
[Price03] INT NOT NULL,
[MW03] INT NOT NULL,
[Price04] MONEY NOT NULL,
[MW04] INT NOT NULL,
[Price05] MONEY NOT NULL,
[MW05] INT NOT NULL,
[Price06] MONEY NOT NULL,
[MW06] INT NOT NULL,
[Price07] MONEY NOT NULL,
[MW07] INT NOT NULL,
[Price08] MONEY NOT NULL,
[MW08] INT NOT NULL,
[Price09] MONEY NOT NULL,
[MW09] INT NOT NULL,
[Price10] MONEY NOT NULL,
[MW10] INT NOT NULL,
[Price11] MONEY NOT NULL,
[MW11] INT NOT NULL,
[Price12] MONEY NOT NULL,
[MW12] INT NOT NULL
);
-- Insert data
INSERT INTO #Pricing (MainPrice, Price01, MW01, Price02, MW02, Price03, MW03, Price04, MW04, Price05, MW05, Price06, MW06, Price07, MW07, Price08, MW08, Price09, MW09, Price10, MW10, Price11, MW11, Price12, MW12)
VALUES
(100.00, 50.00, 2, 70.00, 5, 500.00, 3, 90.00, 10, 80.00, 8, 70.00, 6, 50.00, 2, 60.00, 4, 80.00, 7, 90.00, 10, 100.00, 12, 80.00, 9),
(120.00, 60.00, 3, 80.00, 6, 600.00, 4, 100.00, 11, 90.00, 9, 80.00, 7, 60.00, 3, 70.00, 5, 90.00, 8, 100.00, 11, 120.00, 13, 100.00, 10),
(140.00, 70.00, 4, 90.00, 7, 700.00, 5, 110.00, 12, 100.00, 10, 90.00, 8, 70.00, 4, 80.00, 6, 100.00, 9, 110.00, 12, 140.00, 14, 120.00, 11),
(160.00, 80.00, 5, 100.00, 8, 800.00, 6, 120.00, 13, 110.00, 11, 100.00, 9, 80.00, 5, 90.00, 7, 110.00, 10, 120.00, 13, 160.00, 15, 140.00, 12);
-- 插入数据
INSERT INTO #Pricing (MainPrice, Price01, MW01, Price02, MW02, Price03, MW03, Price04, MW04, Price05, MW05, Price06, MW06, Price07, MW07, Price08, MW08, Price09, MW09, Price10, MW10, Price11, MW11, Price12, MW12)
VALUES
(100.00, 50.00, 2, 70.00, 5, 500.00, 3, 90.00, 10, 80.00, 8, 70.00, 6, 50.00, 2, 60.00, 4, 80.00, 7, 90.00, 10, 100.00, 12, 80.00, 9),
(120.00, 60.00, 3, 80.00, 6, 600.00, 4, 100.00, 11, 90.00, 9, 80.00, 7, 60.00, 3, 70.00, 5, 90.00, 8, 100.00, 11, 120.00, 13, 100.00, 10),
(140.00, 70.00, 4, 90.00, 7, 700.00, 5, 110.00, 12, 100.00, 10, 90.00, 8, 70.00, 4, 80.00, 6, 100.00, 9, 110.00, 12, 140.00, 14, 120.00, 11),
(160.00, 80.00, 5, 100.00, 8, 800.00, 6, 120.00, 13, 110.00, 11, 100.00, 9, 80.00, 5, 90.00
<details>
<summary>英文:</summary>
One way is first to UNPIVOT the data, and it's a kind of more complicated as we have two pairs of columns. So, having this data:
DROP TABLE IF EXISTS #Pricing;
CREATE TABLE #Pricing
(
[MainPrice] MONEY NOT NULL,
[Price01] MONEY NOT NULL,
[MW01] INT NOT NULL,
[Price02] MONEY NOT NULL,
[MW02] INT NOT NULL,
[Price03] INT NOT NULL,
[MW03] INT NOT NULL,
[Price04] MONEY NOT NULL,
[MW04] INT NOT NULL,
[Price05] MONEY NOT NULL,
[MW05] INT NOT NULL,
[Price06] MONEY NOT NULL,
[MW06] INT NOT NULL,
[Price07] MONEY NOT NULL,
[MW07] INT NOT NULL,
[Price08] MONEY NOT NULL,
[MW08] INT NOT NULL,
[Price09] MONEY NOT NULL,
[MW09] INT NOT NULL,
[Price10] MONEY NOT NULL,
[MW10] INT NOT NULL,
[Price11] MONEY NOT NULL,
[MW11] INT NOT NULL,
[Price12] MONEY NOT NULL,
[MW12] INT NOT NULL
);
-- Insert data
INSERT INTO #Pricing (MainPrice, Price01, MW01, Price02, MW02, Price03, MW03, Price04, MW04, Price05, MW05, Price06, MW06, Price07, MW07, Price08, MW08, Price09, MW09, Price10, MW10, Price11, MW11, Price12, MW12)
VALUES
(100.00, 50.00, 2, 70.00, 5, 500.00, 3, 90.00, 10, 80.00, 8, 70.00, 6, 50.00, 2, 60.00, 4, 80.00, 7, 90.00, 10, 100.00, 12, 80.00, 9),
(120.00, 60.00, 3, 80.00, 6, 600.00, 4, 100.00, 11, 90.00, 9, 80.00, 7, 60.00, 3, 70.00, 5, 90.00, 8, 100.00, 11, 120.00, 13, 100.00, 10),
(140.00, 70.00, 4, 90.00, 7, 700.00, 5, 110.00, 12, 100.00, 10, 90.00, 8, 70.00, 4, 80.00, 6, 100.00, 9, 110.00, 12, 140.00, 14, 120.00, 11),
(160.00, 80.00, 5, 100.00, 8, 800.00, 6, 120.00, 13, 110.00, 11, 100.00, 9, 80.00, 5, 90.00, 7, 110.00, 10, 120.00, 13, 160.00, 15, 140.00, 12);
We can use the code above:
WITH DataSource AS
(
SELECT *
FROM
(
SELECT MainPrice
,Price01,Price02,Price03,Price04,Price05,Price06,Price07,Price08,Price09,Price10,Price11,Price12
,'Price' AS [type]
FROM #Pricing
UNION ALL
SELECT MainPrice
,CAST(MW01 AS MONEY), CAST(MW02 AS MONEY), CAST(MW03 AS MONEY), CAST(MW04 AS MONEY), CAST(MW05 AS MONEY), CAST(MW06 AS MONEY), CAST(MW07 AS MONEY), CAST(MW08 AS MONEY), CAST(MW09 AS MONEY), CAST(MW10 AS MONEY), CAST(MW11 AS MONEY), CAST(MW12 AS MONEY)
,'MW'
FROM #Pricing
) DS
UNPIVOT
(
[value] FOR [Column] IN ([Price01], [Price02], [Price03], [Price04], [Price05], [Price06], [Price07], [Price08], [Price09], [Price10], [Price11], [Price12])
) PVT
)
SELECT DS1.MainPrice
,DS1.[value] AS [Price]
,DS2.[value] AS [Mw]
,ROW_NUMBER() OVER (PARTITION BY DS1.MainPrice ORDER BY DS1.[column]) AS [RowID]
FROM DataSource DS1
INNER JOIN DataSource DS2
ON DS1.[MainPrice] = DS2.MainPrice
AND RIGHT(DS1.[Column], 2) = RIGHT(DS2.[Column], 2)
WHERE DS1.[type] ='price'
AND DS2.[type]= 'MW'
ORDER BY DS1.MainPrice, [RowID]
to get this:
[![enter image description here][1]][1]
Now, we are only interested in rows where `MainPrice` is smaller then `Price`. So, just adding `WHERE` clause:
...
SELECT DS1.MainPrice
,DS1.[value] AS [Price]
,DS2.[value] AS [Mw]
,ROW_NUMBER() OVER (PARTITION BY DS1.MainPrice ORDER BY DS1.[column]) AS [RowID]
FROM DataSource DS1
INNER JOIN DataSource DS2
ON DS1.[MainPrice] = DS2.MainPrice
AND RIGHT(DS1.[Column], 2) = RIGHT(DS2.[Column], 2)
WHERE DS1.[type] ='price'
AND DS2.[type]= 'MW'
AND DS1.MainPrice < DS1.[value]
and get:
[![enter image description here][2]][2]
Now, you can perform your calculations. But, there can be several rows for which the criteria is true. A way to handle this is the above to be in subquery and filter only where RowID=1. Alternative is something like this:
SELECT TOP 1 WITH TIES DS1.MainPrice
,DS1.[value] AS [Price]
,DS2.[value] AS [Mw]
FROM DataSource DS1
INNER JOIN DataSource DS2
ON DS1.[MainPrice] = DS2.MainPrice
AND RIGHT(DS1.[Column], 2) = RIGHT(DS2.[Column], 2)
WHERE DS1.[type] ='price'
AND DS2.[type]= 'MW'
AND DS1.MainPrice < DS1.[value]
ORDER BY ROW_NUMBER() OVER (PARTITION BY DS1.MainPrice ORDER BY DS1.[column]) ASC
[1]: https://i.stack.imgur.com/j0VcC.png
[2]: https://i.stack.imgur.com/Cl9gW.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论