主页 / dba / 问题 / 176653
Accepted
Ryan
Asked: 2017-06-20 07:06:50 +0800 CST

计算带有新项目的订单

  • 772

场景：找出每个工厂中的任何客户，这些客户有三个或更多订单，其中包含他们以前从未购买过的新商品。例如，约翰有：

Order A: Item 1, Item 2
Order B: Item 2, Item 3, Item 4
Order C: Item 3

订单 B 包含约翰以前从未购买过的商品，而订单 C 则没有。

换句话说：从每个客户的第二个订单开始，我们想知道他们有多少订单，其中至少有一件商品以前从未出现在他们的订单中。

表架构：请参阅SQL Fiddle。RelatedRecordID 将多个记录绑定到一个订单中。

我的方法：另请参阅上面的 SQL Fiddle。这种方法有缺陷且不稳健。在前面John的例子中，这个方法会给出2个增量，但是因为订单C放在B之后，所以实际上只有一个增量。如果订单 B 不包含项目 3，则其优先级不会影响结果。

有人能想出解决办法或新方法吗？

sql-server sql-server-2008-r2

4 个回答

  1. Best Answer

    CTE 首先找到第一个订单，而不是每件商品都被购买（每个客户和工厂），然后我们简单地计算不同的订单 ID（并减去 1，因为第一个订单不计算在内）：

    with cta as
(
  select 
      FactoryID, CustomerID,  
      min(RelatedRecordID) as MinRelatedRecordID
  from 
      Table1
  group by 
      FactoryID, CustomerID, ItemID
)
select 
    FactoryID, CustomerID, 
    count(distinct MinRelatedRecordID)-1 as OrdersWithNewItems 
from 
    cta 
group by 
    FactoryID, CustomerID 
having 
    count(distinct MinRelatedRecordID) > 3 ;

    在SQLfiddle.com中测试

    • 3

  2. 我不确定性能，恕我直言，它至少需要一个索引([FactoryID], [CustomerID], [RelatedRecordID])

    首先，我筛选了那些拥有 3 个或更多订单的客户，然后我对每个订单的不同行进行了编号，([RelatedRecordID])以避免最终结果出现第一个项目，并且我计算了该项目被购买了多少次。

    最后它返回那些已经购买过一次的物品。

    WITH NOrders AS
(
    -- customers that have 3 or more orders
    SELECT FactoryID, CustomerID, RelatedRecordID, count(*) AS NumOrders
    FROM   Table1
    GROUP BY FactoryID, CustomerID, RelatedRecordID
    HAVING count(*) >= 3
)
, Items AS
(
    -- item that appears once and not on the first line
    SELECT t1.[RecordNo], t1.[FactoryID], t1.[CustomerID], t1.[ItemID], t1.[RelatedRecordID],
           ROW_NUMBER() OVER (PARTITION BY t1.[FactoryID], t1.[CustomerID], t1.[RelatedRecordID] 
                        ORDER BY t1.[FactoryID], t1.[CustomerID], t1.[RelatedRecordID], t1.[RecordNo]) AS RNOrder,
           COUNT(ItemID) OVER (PARTITION BY t1.[FactoryID], t1.[CustomerID], t1.[ItemID]) CItems
    FROM       Table1 t1
    INNER JOIN NOrders
    ON         NOrders.FactoryID = t1.FactoryID
    AND        NOrders.CustomerID = t1.CustomerID
)
SELECT [RecordNo], [FactoryID], [CustomerID], [ItemID]
FROM   Items
WHERE  CItems = 1 
AND    RNOrder > 1

GO

    记录号 | 厂号 | 客户ID | 商品编号
------: | ------: | ----------: | -----:
       5 | 1 | 1 | 4个

    dbfiddle在这里

    • 1

  3. 就像一个想法：

    with sub as (
select
t.*,
coalesce((select itemid from table1 t_in where t_in.recordno < t.recordno and t_in.itemid=t.itemid),-1) flag
from table1 t)

select 
relatedrecordid, min(flag)
from sub



group by relatedrecordid

    所有标有 -1 的订单都包含新项目。

    • 0

  4. 此查询基于您的示例数据。假设 RelatedRecordID 是购买的顺序。

    ;WITH cte_OrderCounts AS (SELECT [FactoryID], [CustomerID]
                              , COUNT(DISTINCT [RelatedRecordID]) AS OrderCounts
                          FROM Table1
                          GROUP BY [FactoryID], [CustomerID]
), cte AS (SELECT *
               , DENSE_RANK() OVER (PARTITION BY [FactoryID], [CustomerID] ORDER BY [RelatedRecordID]) AS CustomerOrderRank
               , ROW_NUMBER() OVER (PARTITION BY [FactoryID], [CustomerID], [ItemID] ORDER BY [RelatedRecordID]) AS CustomerPurchaseItem
           FROM Table1
)
SELECT * 
FROM cte a
INNER JOIN cte_OrderCounts b
    ON b.FactoryID = a.FactoryID
    AND b.CustomerID = a.CustomerID
WHERE a.CustomerOrderRank > 1
AND a.CustomerPurchaseItem = 1
AND b.OrderCounts >= 3;
    • 0

相关问题