通过将 XML 列中的 UnitPrice 和 Quantity 相乘来对 TotalPrice 进行 SQL 查询-解网

问：

我需要查询 SQL Server 数据库表，但我无法控制此数据库的设计有多糟糕。我想要从如下所示的数据中获得截至今天的最后 X 天的实际每日总资金（日期以 UTC 存储）：

SQL Server 表包含如下行：

Customer   Customer1
CreateDate 2023-09-11 8:00 am
Items      (column contains XML)
             <ArrayOfItems>
               <Item>
                 <Quantity>5</Quantity>
                 <UnitPrice>3.00</UnitPrice>
               </Item>
             </ArrayOfItems>

Customer   Customer2
CreateDate 2023-09-11 10:00 am
Items      (column contains XML)
             <ArrayOfItems>
               <Item>
                 <Quantity>1</Quantity>
                 <UnitPrice>5.50</UnitPrice>
               </Item>
               <Item>
                 <Quantity>4</Quantity>
                 <UnitPrice>4.00</UnitPrice>
               </Item>
             </ArrayOfItems>

Customer   Customer3
CreateDate 2023-09-12 4:00 pm
Items      (column contains XML)
             <ArrayOfItems>
               <Item>
                 <Quantity>2</Quantity>
                 <UnitPrice>5.00</UnitPrice>
               </Item>
               <Item>
                 <Quantity>3</Quantity>
                 <UnitPrice>3.50</UnitPrice>
               </Item>
             </ArrayOfItems>

我希望结果是每天的总数量和价格。例如，我希望输出为：

DateCreated   TotalQuantity    TotalPrice
-----------------------------------------
2023-09-11         10             36.50
2023-09-12          5             20.50

显然，如果在数据输入时计算总数量并放入自己的列中，那会更好，这样我就可以用总和进行简单的查询。唉，它不是那样设计的，我被我所拥有的东西困住了。UnitPrice

这是我到目前为止所做的工作（注意：在此查询中，我在 DateADD 部分输入了“2”的年龄，该部分获取最近两天的数据。我可以将其更改为过去的任意天数，例如，我可以通过在其中输入“30”来获得过去 30 天，或者我可以使用其他日期算法。日期不是我需要帮助的，而是我需要帮助的 TotalPrice）：

SELECT
    CONVERT(date, CreateDate) AS DateCreated,
    SUM (Items.value('sum(/ArrayOfItems/Item/Quantity)', 'int') ) AS TotalQuantity
FROM
    Invoices
WHERE
    DATEADD(day, 2, CreateDate) > GetUtcDate()
GROUP BY
    CONVERT(date, CreateDate)

上面的 SQL 查询只让我走了一半。它获取前两列，但不获取最后一列。我不知道如何让它乘以每个项目的时间，然后将每天的所有时间相加。它必须一行一行地做，因为每一行都是不同的。到目前为止，我有这个，它让我得到了数量，但不是当天的总价。我如何获得它？QuantityUnitPriceUnitPrice

我尝试过搜索，但没有一个搜索结果给我这个确切的场景。我敢肯定以前一定有人问过这个问题，但我找不到它，至少，不是这个确切的场景。我没有看到任何东西告诉我如何像这样迭代每天的行。

DateCreated   TotalQuantity 
---------------------------
2023-09-11         10       
2023-09-12          5

SQL 服务器 xml

在提出问题时，您需要提供一个最小的可重现示例：（1） DDL 和样本数据填充，即 CREATE 表加上 INSERT T-SQL 语句。（2）你需要做什么，即逻辑和你的代码尝试在T-SQL中实现它。（3）期望的输出，基于上面 #1 中的示例数据。（4）您的 SQL Server 版本（SELECT @@version;）。

0赞 TFabris 9/13/2023

@YitzhakKhabinsky - 对不起，我不知道该怎么做。我以为我已经在描述中给出了最少的信息。:(

答：

1赞 Charlieface 9/13/2023 #1

在转换为正确的类型后，可以使用 XQuery 的 FLWOR 表达式循环访问和乘法值

Select
    Convert(date,CreateDate) as DateCreated,
    SUM ( Items.value('
      sum(
        for $i in /ArrayOfItems/Item
        return xs:int(($i/Quantity/text())[1]) * xs:decimal(($i/UnitPrice/text())[1])
      )', 'decimal(18,2)') ) AS TotalQuantity
from
    Invoices
where
    CreateDate > DateADD(day, -2, GETUTCDATE())
group by
    Convert(date, CreateDate)

db<>fiddle

请注意对子句的更改，以启用要使用的索引。WHERE

另一种方法是切碎节点，并分别对它们求和Item

Select
    Convert(date,CreateDate) as DateCreated,
    SUM (
      x.Item.value('(Quantity/text())[1]','int') *
      x.Item.value('(UnitPrice/text())[1]', 'decimal(18,2)')
    ) AS TotalQuantity
from
    Invoices
CROSS APPLY Items.nodes('/ArrayOfItems/Item') x(Item)
where
    CreateDate > DateADD(day, -2, GETUTCDATE())
group by
    Convert(date, CreateDate)

非常感谢这些例子，第一个看起来像是我所期望/希望找到的那种东西。我将尝试你的例子以及伊扎克·哈宾斯基的例子，看看哪一个最适合我。这两种方法（使用 FLWOR 迭代与粉碎 XML）似乎都应该适用于我的目的，一旦我能够尝试它们，我就会 ping 回来。

0赞 TFabris 9/14/2023

FLWOR 版本似乎非常适合我的目的，而且在性能方面，它非常快。接下来，我将尝试切碎示例。

0赞 TFabris 9/14/2023

您的 Shredding 版本也可以工作并产生类似的输出，尽管它似乎比 FLWOR 版本慢（尽管我没有执行多次测试以确保性能）。一个有趣的副作用是，FLWOR 和 shredding 版本都会导致看似随机顺序的输出，而不是我的原始代码生成的按日期排序的输出（只有项目计数）。不知道为什么，但是，理论上我应该能够用一个简单的“排序依据”语句来修复它，对吧？

0赞 TFabris 9/14/2023

“order by”语句解决了这个问题，但是当我在大型数据集上运行 FLWOR 版本和 Shred 版本时，我注意到它之间的细微差异。总价格产出的差异从几美分到几美元不等。不确定该信任哪一个。

0赞 Charlieface 9/14/2023

可能是轻微的舍入错误。也许提高 to 的大小或其他东西。除非您指定，否则您不能依赖排序，它可能是并行化的。decimaldecimal(38,19)ORDER BY

2赞 Yitzhak Khabinsky 9/13/2023 #2

请尝试以下解决方案。

它使用 XQuery 方法并粉碎 XML。.nodes().value()

CTE 后的数据以矩形/关系格式出现。剩下的就是基本分组和所需列的 SUM（）。

SQL格式

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, CreateDate DATE, Items XML);
INSERT @tbl (CreateDate, Items) VALUES
('2023-09-11', N'<ArrayOfItems>
    <Item>
        <Quantity>5</Quantity>
        <UnitPrice>3.00</UnitPrice>
    </Item>
</ArrayOfItems>'),
('2023-09-11', N'<ArrayOfItems>
    <Item>
        <Quantity>1</Quantity>
        <UnitPrice>5.50</UnitPrice>
    </Item>
    <Item>
        <Quantity>4</Quantity>
        <UnitPrice>4.00</UnitPrice>
    </Item>
</ArrayOfItems>'),
('2023-09-12', N'<ArrayOfItems>
    <Item>
        <Quantity>2</Quantity>
        <UnitPrice>5.00</UnitPrice>
    </Item>
    <Item>
        <Quantity>3</Quantity>
        <UnitPrice>3.50</UnitPrice>
    </Item>
</ArrayOfItems>');
-- DDL and sample data population, end

WITH rs AS
(
    SELECT t.ID, t.CreateDate 
        , c.value('(Quantity/text())[1]', 'INT') AS Quantity
        , c.value('(UnitPrice/text())[1]', 'MONEY') AS UnitPrice
        , c.value('(Quantity/text())[1]', 'INT') * c.value('(UnitPrice/text())[1]', 'MONEY') AS TotalPrice
    FROM @tbl AS t
    CROSS APPLY Items.nodes('/ArrayOfItems/Item') AS t1(c)
)
-- SELECT * FROM rs  -- uncomment this line to see the data
SELECT CreateDate
    , SUM(Quantity) AS TotalQuantity
    , SUM(TotalPrice) AS TotalPrice
FROM rs
GROUP BY rs.CreateDate;

输出

创建日期	总数量	总价
2023-09-11	10	36.50
2023-09-12	5	20.50

@YitzhakKhabinsky - 非常感谢这个深思熟虑的例子。您能解释一下为什么粉碎XML的SELECT语句中包含“[1]”吗？这是否意味着它只选择数组中的第一个条目，因此如果有多个条目，则会遗漏其他项目？不过，您输出中的数字看起来是正确的，所以它必须以某种方式工作，我只是不明白如何。

0赞 Yitzhak Khabinsky 9/14/2023

@TFabris，XQuery 方法需要一个单例，这就是我们使用“[1]”的原因。不用担心，所有数据都是从切碎的 XML 中出来的。您可以取消注释，并暂时注释掉下面的聚合部分，以查看完整的数据。.value()SELECT * FROM rs

上一个：如何在 SQL Server 中获取 xml 列的值

下一个：在 SQL 中将带有嵌套 XML 的列转换为表格格式？

通过将 XML 列中的 UnitPrice 和 Quantity 相乘来对 TotalPrice 进行 SQL 查询

SQL query for TotalPrice by multiplying UnitPrice and Quantity which are inside a XML column

评论

评论

评论