聚合数据的最佳方法-更新查询或子查询？

Question

我正在尝试将一些客户订单数据聚合到一个表中进行分析。数据是客户订购的产品数量，然后尝试确定订单是小订单、中型订单还是大订单，然后通过OrderSize确定他们购买的产品总数和订单成本。

小订单-1-2个产品

中级订单-3-4个产品

大订单- >=5产品

以下是数据：

CustomerID  OrderID   OrderSize OrderTotal
1           800       1         $20
2           801       1         $10
3           802       4         $85
1           803       1         $30
2           804       8         $120
3           805       1         $40

这是我正在尝试构建的表(只发布一个图像更容易)：

​

我可以像这样运行一个更新查询来填充表：

--构建表格--

CREATE TABLE Customers (
    CustomerID varchar(10) PRIMARY KEY,
    SmallOrderCount int,
    SmallOrderProducts int,
    SmallOrderTotal money,
    MedOrderCount int,
    MedOrderProducts int,
    MedOrderTotal money,
    LargeOrderCount int,
    LargeOrderProducts int,
    LargeOrderTotal money
);

--插入独特的客户--

INSERT INTO Customers
SELECT CustomerID FROM Orders GROUP BY CustomerID;

--更新以填充订单信息--

UPDATE Customers
SET SmallOrderCount = (SELECT count(*) FROM Orders WHERE OrderSize BETWEEN 1 AND 2 AND Orders.CustomerID = Customers.CustomerID);

UPDATE Customers 
SET SmallOrderProducts = (SELECT sum(OrderSize) FROM Orders WHERE OrderSize BETWEEN 1 AND 2 AND Orders.CustomerID = Customers.CustomerID);

UPDATE Customers 
SET SmallOrderTotal = (SELECT sum(OrderTotal) FROM Orders WHERE OrderSize BETWEEN 1 AND 2 AND Orders.CustomerID = Customers.CustomerID);

然后，我可以对剩下的6列重复此操作。

然而，这似乎有很多工作要做。有没有一种方法可以使用子查询填充我的Customer表，而这个子查询的工作量可能更少？或者我上面的方法是最直接的？

Answer 1

您可以在一个带有CASE语句的命令中插入它的所有：

INSERT INTO Customers
SELECT CustomerID
      ,sum(CASE WHEN OrderSize BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS SmallOrderCount
      ,sum(CASE WHEN OrderSize BETWEEN 1 AND 2 THEN OrderSize ELSE 0 END) AS SmallOrderProducts
      ,sum(CASE WHEN OrderSize BETWEEN 1 AND 2 THEN OrderTotal ELSE 0 END) AS SmallOrderTotal
      ,sum(CASE WHEN OrderSize BETWEEN 3 AND 4 THEN 1 ELSE 0 END) AS MedOrderCount
      ,sum(CASE WHEN OrderSize BETWEEN 3 AND 4 THEN OrderSize ELSE 0 END) AS MedOrderProducts
      ,sum(CASE WHEN OrderSize BETWEEN 3 AND 4 THEN OrderTotal ELSE 0 END) AS MedOrderTotal
      ,sum(CASE WHEN OrderSize > 4 THEN 1 ELSE 0 END) AS LargeOrderCount
      ,sum(CASE WHEN OrderSize > 4 THEN OrderSize ELSE 0 END) AS LargeOrderProducts
      ,sum(CASE WHEN OrderSize > 4 THEN OrderTotal ELSE 0 END) AS LargeOrderTotal
FROM   Orders
GROUP  BY CustomerID;

请参阅此。

Answer 2

一个insert应该足够了：

INSERT INTO Customers(CustomerID,SmallOrderCount,SmallOrderProducts,
   SmallOrderTotal)     
SELECT a.CustomerID, COUNT(a.*) as cnt, sum(a.OrderSize) as OrderSize,
    sum(a.OrderTotal) as OrderTotal 
   FROM Orders a
   WHERE a.OrderSize BETWEEN 1 AND 2 
   GROUP BY a.CustomerID

上面的查询将只插入订单大小在1到2之间的客户。如果您还需要插入其他客户，可以使用：

INSERT INTO Customers(CustomerID,SmallOrderCount,SmallOrderProducts,
   SmallOrderTotal)     
SELECT a.CustomerID, 
COUNT(CASE WHEN  a.OrderSize BETWEEN 1 AND 2  THEN 1 END) as cnt, 
sum(CASE WHEN  a.OrderSize BETWEEN 1 AND 2  THEN a.OrderSize ELSE 0 END) as OrderSize,
sum(CASE WHEN  a.OrderSize BETWEEN 1 AND 2  THEN a.OrderTotal ELSE 0 END ) as OrderTotal 
FROM Orders a
GROUP BY a.CustomerID

Answer 3

我只会为它创建一个查询(可以用作视图)，而不是整个新的持久化表。

WITH cteOrders AS (
    SELECT CustomerID,
        CASE WHEN OrderSize BETWEEN 1 AND 2 THEN OrderSize END SmallOrderProducts,
        CASE WHEN OrderSize BETWEEN 1 AND 2 THEN OrderTotal END SmallOrderTotal,
        CASE WHEN OrderSize BETWEEN 3 AND 4 THEN OrderSize END MediumOrderProducts,
        CASE WHEN OrderSize BETWEEN 3 AND 4 THEN OrderTotal END MediumOrderTotal,
        CASE WHEN OrderSize > 4 THEN OrderSize END LargeOrderProducts,
        CASE WHEN OrderSize > 4 THEN OrderTotal END LargeOrderTotal
    FROM Orders
)
SELECT CustomerID,
        COUNT(SmallOrderProducts) SmallOrderCount,
        COALESCE(SUM(SmallOrderProducts), 0) SmallOrderProducts,
        COALESCE(SUM(SmallOrderTotal), 0) SmallOrderTotal,
        COUNT(MediumOrderProducts) MediumOrderCount,
        COALESCE(SUM(MediumOrderProducts), 0) MediumOrderProducts,
        COALESCE(SUM(MediumOrderTotal), 0) MediumOrderTotal,
        COUNT(LargeOrderProducts) LargeOrderCount,
        COALESCE(SUM(LargeOrderProducts), 0) LargeOrderProducts,
        COALESCE(SUM(LargeOrderTotal), 0) LargeOrderTotal
    FROM cteOrders
    GROUP BY CustomerID