时间戳

首页 > 解决方案 > 在最近一次购买之前选择最近一次购买

sql - 在最近一次购买之前选择最近一次购买

问题描述

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

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')
),

步骤 2. 创建 CPG 类别的采购表：

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

步骤 3.Left 加入：这是我卡住的地方。我想制作一张在购买 CPG 之前最近购买过 OTHER 的表格。即，输出应如下所示：

others_category  count_distinct_customers
Electronics            1
Books                  1

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

标签： sqlsql-serversql-server-2017

解决方案

这就是我在 SQL Server 2017 中的做法，但是，我不确定这是否会在 2005 年工作（不幸的是，就像我说的，我不再有 2005 年的测试环境了）。我认为 APPLY是在 SQL Server 2008 中添加的。当然，“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;

推荐阅读