AdventureWorks dbo.ufnGetProductListPrice女士

Question

我正在研究SQL AdventureWorks 2014，为我们公司建立内部dbase模型。

我通常在Postgres工作，我试图“理解”Ms存储过程.-)但是.

商店的程序dbo.ufnGetProductListPrice在我看来很奇怪。在这里可以找到SQL代码：(https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/functions/dbo_ufnGetProductListPrice_116.html)

 CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;

    SELECT @ListPrice = plph.[ListPrice] 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @ListPrice;
END;

该函数使用了以下两个表：

• Production.Product

• Production.ProductListPriceHistory - (https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/tables/Production_ProductListPriceHistory_159.html)

我特别怀疑的是：

1. 函数得到参数@ProductID。相同的@ProductID用作Production.Product的主键和表Production.ProductListPriceHistory的主键的一部分，因此，当我们可以直接在ProductListPriceHistory.ProductID上测试ProductID时，在Product.ProductID = ProductListPriceHistory.ProductID上进行连接似乎没有帮助。

为什么要创建这样的连接？似乎没有help...

将给定的@Orderdate日期时间作为第二个参数在联接中签入

1. ，条件为

和@OrderDate之间的plph.StartDate和聚合(plph.EndDate，转换(日期时间，'99991231'，112))；-确保我们得到所有的价格！

但是，如果我们调用@Orderdate = 1/1/2022的存储过程，

• 考虑到EndDate可能为NULL，
• 和我们可以在ProductListPriceHistory.StartDate中使用@ProductID拥有两条记录，第一条与StartDate=1/1/2020和第二条StartDate=1/1/2021，

这样的“中间”条件应该是两者相匹配的，显然我们会期待最后一种情况.是个bug吗?

Answer 1

您说得对，这段代码中有许多严重的缺陷，我建议您找到更好的教程。

我注意到了每一个缺陷的评论

CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money]   -- money is a bad data type due to rounding problems, decimal should be used
                  -- scalar UDFs are very slow, this should be a inline Table Valued Function
AS 
BEGIN
    DECLARE @ListPrice money;

    SELECT @ListPrice = plph.[ListPrice]    -- as you point out, there should be some kind of aggregation
    FROM [Production].[Product] p           -- as you point out: join is unnecessary
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
                           -- BETWEEN should never be used on dates, use ">= AND <"
            AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112));   -- ISNULL is better for performance than COALESCE

    RETURN @ListPrice;
END;

一个更好的功能是

CREATE FUNCTION dbo.ufnGetProductListPrice (
  @ProductID int,
  @OrderDate datetime
)
RETURNS TABLE
AS RETURN

SELECT TOP (1)
  plph.ListPrice
FROM Production.ProductListPriceHistory plph 
WHERE plph.ProductID = @ProductID 
  AND @OrderDate >= plph.StartDate
  AND @OrderDate < ISNULL(plph.EndDate, CONVERT(datetime, '99991231', 112))
ORDER BY
  plph.StartDate DESC;

假设有可能出现多个活跃价格(我认为在AdventureWorks中是不可能的)，那么您还需要TOP (1)和ORDER BY plph.StartDate DESC。如果这是不可能的，那么你可以忽略这一点。

而不是用标量UDF来做这件事。

SELECT p.*, dbo.ufnGetProductListPrice(p.ProductId, GETDATE())
FROM Production.Product p;

你用APPLY来做电视节目

SELECT p.*, plp.*
FROM Production.Product p
OUTER APPLY dbo.ufnGetProductListPrice(p.ProductId, GETDATE()) plp;

OUTER APPLY的工作方式类似于LEFT JOIN，这意味着如果没有行，就可以得到NULL。

Answer 2

@Charlieface谢谢你的回复。关于函数ufnGetProductStandardCost的另一个注意事项

AdventureWorks存储历史零售价和CostPrices，以便在特定时间找到特定产品的价格(列表、成本)。这就是为什么我对数据库的这一部分感兴趣

它甚至为成本价格ufnGetProductStandardCost https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/functions/dbo_ufnGetProductStandardCost_117.html提供了“双重”功能。

有同样的错误:-(

我认为ufnGetProductListPrice(ProductID，OrderDate)的思想是，如果在给定的时间价格不存在，则报告单一价格或零。

不是一张桌子或一张桌子。

让我们回想一下我之前想过的那个案子

EndDate=null

• one

• search OrderDate 1/1/2022

• one record with StartDate=1/1/2020，OrderDate record with StartDate=1/1/2021，EndDate=null

履行这一职能的一个想法是：

使用子查询为给定的@ProductId

• use (例如@OrderDate=1/1/2019)

)查找MAX() StardDate<@OrderDate，以便在外部查询中获取对应的ListPrice，或者如果@OrderDate之前没有StartDate ListPrice，则返回NULL (例如，@OrderDate=1/1/2019))

你觉得这个查询怎么样？有什么改善吗？

CREATE FUNCTION dbo.ufnGetProductListPrice (
  @ProductID int,
  @OrderDate datetime
)
AS 
BEGIN

DECLARE @ListPrice money;

SELECT @ListPrice= plph.ListPrice
FROM Production.ProductListPriceHistory plph 
WHERE plph.ProductID = @ProductID 
  AND plhp.StartDate = (
        SELECT MAX(StardDate)
        FROM Production.ProductListPriceHistory plph1
        WHERE plph1.ProductID = @ProductID
            AND plhp1.StartDate <=  @OrderDate)         
RETURN @ListPrice;
END;