提问人：Mich39 提问时间：11/5/2023 最后编辑：Dale KMich39 更新时间：11/6/2023 访问量：84
如何检索特定日期的最后一个已知值？
How to retrieve the last known value for specific date?
问：
我有两个表：ProductSales 和 ProductPrice
产品销售
| 销售日期 | 售中产品展示 | 销售数量 |
|---|---|---|
| 2023-10-01 | 产品1 | 150 |
| 2023-10-01 | 产品2 | 224 |
| 2023-10-03 | 产品1 | 91 |
| 2023-10-05 | 产品3 | 317 |
| 2023-10-05 | 产品4 | 1200 |
| 2023-10-06 | 产品1 | 11 |
| 2023-10-09 | 产品1 | -25 |
| 2023-10-09 | 产品2 | 190 |
| 2023-10-13 | 产品1 | 601 |
| 2023-10-15 | 产品2 | -400 |
| 2023-10-15 | 产品3 | 35 |
产品价格
| 价格日期 | 产品 | 价格 |
|---|---|---|
| 2023-10-01 | 产品1 | 104 |
| 2023-10-01 | 产品2 | 53 |
| 2023-10-01 | 产品3 | 210 |
| 2023-10-01 | 产品4 | 78 |
| 2023-10-09 | 产品1 | 100 |
| 2023-10-09 | 产品2 | 50 |
| 2023-10-09 | 产品3 | 211 |
| 2023-10-09 | 产品4 | 75 |
| 2023-10-15 | 产品2 | 55 |
| 2023-10-15 | 产品3 | 212 |
现在我必须计算每个产品的月销售额
select SaleProduct, sum(SaleQuantity*isnull(Price,0)) as MonthlySales
from ProductSales s
left outer join ProductPrice p
on s.SaleDate = p.PriceDate and s.SaleProduct = p.Product
where saledate between '2023-10-01' and ' 2023-10-31'
group by SaleProduct
但问题是，并非每个销售日期我都有相应的价格日期，并且按日期加入有时会给我零价格。如果是这样，我必须从销售日期之前的最后已知日期开始计算价格。
如何在查询中实现这一点？
答：
1赞
Chris Maurer
11/5/2023
#1
Select ps.SaleProduct,ps.SaleQuantity*pp.Price
From ProductSales ps Inner Join ProductPrice pp
On ps.SaleProduct=pp.Product and
pp.PriceDate=(Select max(x.PriceDate) From ProductPrice x
Where x.Product=ps.SaleProduct and x.PriceDate<=ps.SaleDate
)
当您在内部查询中引用外部查询值时，它称为相关子查询。
评论
0赞
nbk
11/5/2023
这会产生错误，因为产品不是销售的一部分
0赞
Mich39
11/5/2023
谢谢。这就是我所寻找的，从内部查询引用外部表。生成的查询是：选择 ps。SaleProduct，sum（ps.销售数量*pp.Price） 作为 MonthlySales from ProductSales ps Inner Join ProductPrice pp On ps.SaleProduct=pp。产品和pp。PriceDate=（从 ProductPrice x 中选择 max（x.PriceDate），其中 x.Product=ps。SaleProduct 和 x.PriceDate<=ps。SaleDate ） 按 ps 分组。售中产品展示
0赞
marcothesane
11/6/2023
#2
您曾经使用过带有大写首字母的产品名称，在其他情况下使用小写首字母。我把所有的东西都放在“标题”上，首字母大写。
我尝试了另一种方法：在子查询中使用 向产品价格添加一列等于同一产品的下一个产品价格日期，这会导致缺少的下一个日期被最大日期值替换，并使用复杂的连接将销售表与该子查询连接起来，该子查询涉及产品的等谓词和三个日期的范围谓词：LEAD(pricedate,1,'9999-12-31') OVER()
WITH
-- your input, not part of end query ..
productsales(SaleDate,SaleProduct,SaleQuantity) AS (
SELECT CAST('2023-10-01' AS DATE),'Product1',150
UNION ALL SELECT CAST('2023-10-01' AS DATE),'Product2',224
UNION ALL SELECT CAST('2023-10-03' AS DATE),'Product1',91
UNION ALL SELECT CAST('2023-10-05' AS DATE),'Product3',317
UNION ALL SELECT CAST('2023-10-05' AS DATE),'Product4',1200
UNION ALL SELECT CAST('2023-10-06' AS DATE),'Product1',11
UNION ALL SELECT CAST('2023-10-09' AS DATE),'Product1',-25
UNION ALL SELECT CAST('2023-10-09' AS DATE),'Product2',190
UNION ALL SELECT CAST('2023-10-13' AS DATE),'Product1',601
UNION ALL SELECT CAST('2023-10-15' AS DATE),'Product2',-400
UNION ALL SELECT CAST('2023-10-15' AS DATE),'Product3',35
)
,
productprice(PriceDate,Product,Price) AS (
SELECT CAST('2023-10-01' AS DATE) ,'Product1',104
UNION ALL SELECT CAST('2023-10-01' AS DATE) ,'Product2',53
UNION ALL SELECT CAST('2023-10-01' AS DATE) ,'Product3',210
UNION ALL SELECT CAST('2023-10-01' AS DATE) ,'Product4',78
UNION ALL SELECT CAST('2023-10-09' AS DATE) ,'Product1',100
UNION ALL SELECT CAST('2023-10-09' AS DATE) ,'Product2',50
UNION ALL SELECT CAST('2023-10-09' AS DATE) ,'Product3',211
UNION ALL SELECT CAST('2023-10-09' AS DATE) ,'Product4',75
UNION ALL SELECT CAST('2023-10-15' AS DATE) ,'Product2',55
UNION ALL SELECT CAST('2023-10-15' AS DATE) ,'Product3',212
)
-- end of input
-- real query starts here, replace following comma wiht "WITH"
,
w_next_pricedt AS (
SELECT
pricedate AS price_from_dt
, LEAD(pricedate,1,'9999-12-31') OVER(PARTITION BY product ORDER BY pricedate) AS price_to_dt
, product
, price
FROM productprice
)
SELECT
saleproduct
, SUM(SaleQuantity*ISNULL(Price,0)) AS monthlysales
FROM productsales s
JOIN w_next_pricedt p
ON p.product=s.saleproduct
AND s.saledate >= p.price_from_dt
AND s.saledate < p.price_to_dt
WHERE s.saledate BETWEEN '2023-10-01' AND ' 2023-10-31'
GROUP BY saleproduct
ORDER BY saleproduct
| 售中产品展示 | 月销售额 |
|---|---|
| 产品1 | 83808 |
| 产品2 | -628 |
| 产品3 | 73990 |
| 产品4 | 93600 |
0赞
Saikat
11/6/2023
#3
这是解决方案。我不确定你是否需要这样的东西。此解决方案应适用于 SQL Server。
with salesData as
(
select
* ,
row_number() over (partition by a.saleDate , a.saleProduct , a.saleQuantity , b.product order by b.priceDate desc) as seq
from productSales as a inner join productPrices as b on
a.saleProduct = b.Product and a.saleDate >= b.priceDate
)
select
a.saleProduct ,
datename(month , saleDate) as [month] ,
sum(saleQuantity*unitPrice) as totalPrice
from salesData as a
where seq = 1
group by a.saleProduct , datename(month , saleDate);
0赞
Thorsten Kettner
11/6/2023
#4
您有一个价格历史记录表。例如，它显示 product1 在 2023-10-01 到 2023-10-08 的日期范围内成本为 104。在 2023 年 10 月 9 日，价格更改为 100，因此从那时起一直有效。
您希望选择所有销售，然后仅将一个价格联接到交易记录中，即销售日期的当前价格。为此，您需要将横向连接 （） 与子句结合使用。CROSS APPLYTOP
select
s.saleproduct,
sum(s.salequantity * pp.price) as monthly_sales
from productsales s
cross apply
(
select top(1) *
from productprice p
where p.product = s.saleproduct
and p.pricedate <= s.saledate
order by p.pricedate desc
) pp
where s.saledate between cast('2023-10-01' as date)
and cast('2023-10-31' as date)
group by s.saleproduct
order by s.saleproduct;
演示：https://dbfiddle.uk/OfBrHFp7（感谢 Luuk 为小提琴提供输入数据。
评论