选择最新购买之前的最新购买

Question

我有一个表，看起来像这样：

customer_id  purchase_date  category
1            9/1/2018       Electronics
1            9/2/2018       Art
1            9/3/2018       Books
1            9/5/2018       CPG
2            9/2/2018       Books
2            9/4/2018       Electronics
2            9/20/2018      CPG     

从这里，我试图推导出在购买CPG之前的最新购买。为了进一步解释，下面是我的步骤：

步骤1.创建一个不属于CPG类别的采购表：

WITH OTHERS AS(
    SELECT customer_id,
           category as others_category,
           purchase_date
    FROM orders o
    WHERE category IN ('Electronics', 'Books', 'Art')
),

Step2.创建CPG类采购表：

CPG AS( 
    SELECT customer_id,
           category as cpg_category,
           purchase_date
    FROM orders o
    WHERE category = 'CPG'
)

步骤3. 3.Left :这就是我被卡住的地方。我想制作一个表格，其中包含在购买CPG之前最近的其他购买。也就是说，输出应该如下所示：

others_category  count_distinct_customers
Electronics            1
Books                  1

理想情况下，我不希望使用CTE。SQL类型为SQL Server 2017。

Answer 1

这就是我在SQL Server 2017中如何做到这一点，然而，我不确定这是否会在2005年工作(不幸的是，就像我说的，我不再有2005年的测试环境)。我认为APPLY是在SQL Server2008中添加的。当然，"VTE“在2005年不会起作用，因为VALUES构造函数子句是在2008年添加的(如果我没记错的话)，但是，您至少有一个表可以用来测试：

WITH VTE AS(
    SELECT V.customer_id,
           CONVERT(date,V.purchase_date,101) AS purchase_date,
           V.category
    FROM (VALUES(1,'9/1/2018 ','Electronics'),
                (1,'9/2/2018 ','Art'),
                (1,'9/3/2018 ','Books'),
                (1,'9/5/2018 ','CPG'),
                (2,'9/2/2018 ','Books'),
                (2,'9/4/2018 ','Electronics'),
                (2,'9/20/2018','CPG')) V(customer_id,purchase_date,category))
SELECT V2.category,
       COUNT(DISTINCT V2.customer_id) AS DistinctCustomers
FROM VTE V1
     CROSS APPLY (SELECT TOP 1
                         customer_id,
                         purchase_date,
                         category
                  FROM VTE ca 
                  WHERE ca.customer_id = V1.customer_id
                    AND ca.purchase_date < V1.purchase_date
                  ORDER BY ca.purchase_date DESC) V2

WHERE V1.category = 'CPG'
GROUP BY V2.category;

Answer 2

套用一下，您只想要紧跟"CPG购买“(由同一客户？)的购买。

分析函数LEAD()允许您查找"following“行中的内容，而不必将数据连接回自身。

WITH
  orders_with_lookup AS
(
  SELECT
    *,
    LEAD(category) OVER (PARTITION BY customer_id ORDER BY purchase_date)   AS customers_next_purchase_category
  FROM
    orders
)
SELECT
  category,
  COUNT(DISTINCT customer_id)   AS count_distinct_customers
FROM
  orders_with_lookup
WHERE
  customers_next_purchase_category = 'CPG'
GROUP BY
  category
ORDER BY
  category

Answer 3

您可以使用just (从SQL Server 2005开始提供)将每个CPG订单与之前购买的其他订单连接起来，然后您就可以只返回其他订单数据。

我已经添加了一个DISTINCT，所以如果两个CPG订单具有相同的其他以前的订单(因为它们之间没有输入其他订单)，那么它只返回一次。

   SELECT DISTINCT others.*
   FROM orders cpg
        OUTER APPLY (SELECT top 1 others.*
                     FROM orders as others
                     WHERE category <> 'CPG' and
                           others.purchase_date < cpg.purchase_date
                           order by others.purchase_date desc) as others
   WHERE category = 'CPG'