如何在电子商务数据库中找到日期之间的差异并找到首次购买

下面的方法设计得相对容易理解。还有其他方法(例如，窗口函数)可能会稍微更有效;但这使得它在你当前的SQL技能水平下很容易维护。

请注意，下面的SQL命令是相互构建的(所以答案在最后)。下面是一个数据库<>

它基于一个简单的查询(我们将把它用作子查询)，该查询查找每个客户的第一个order_purchase_timestamp。

SELECT customer_id, MIN(order_purchase_timestamp) AS first_purchase_date
FROM orders
GROUP BY customer_id

接下来是DATEDIFF来查找两个日期之间的差。

然后，您可以使用上面的子查询来获取每行的第一个日期-然后查找日期差异，例如，

SELECT orders.customer_id, 
orders.order_purchase_timestamp,
first_purchases.first_purchase_date,
DATEDIFF(month, first_purchases.first_purchase_date, orders.order_purchase_timestamp) AS age_by_month
FROM   orders
INNER JOIN
(SELECT customer_id, MIN(order_purchase_timestamp) AS first_purchase_date
FROM orders
GROUP BY customer_id
) AS first_purchases ON orders.customer_id = first_purchases.customer_id

• 注意- DATEDIFF有一个'gotcha'，得到大多数人，但对你有好处-当比较月份时，它忽略天组件，例如，如果发现月份的差异，1月1日和1月31日之间的月份有0差异。另一方面，1月31日和2月1日之间的1个月将有差异。然而，我认为这实际上是你想要的!

但是，当客户在一个月内进行多次购买时，上面的代码会重复(每次购买都有一行)。相反，我们可以按该月进行分组，然后只记录该月的第一次购买。

一个"直接"的方法是对YEAR(orders.order_purchase_timestamp)和MONTH(orders.order_purchase_timestamp)进行分组。然而，我在下面使用了一个小技巧——使用EOMONTH来查找这个月的最后一天。EOMONTH对该月中的任何日期返回相同的日期;因此，我们可以据此分组。

最后，您可以添加WHERE表达式和ORDER BY来获得您所要求的结果(在两个日期之间)

SELECT orders.customer_id, 
MIN(orders.order_purchase_timestamp) AS order_purchase_timestamp,
first_purchases.first_purchase_date,
DATEDIFF(month, first_purchases.first_purchase_date, EOMONTH(orders.order_purchase_timestamp)) AS age_by_month
FROM   orders
INNER JOIN
(SELECT customer_id, MIN(order_purchase_timestamp) AS first_purchase_date
FROM orders AS orders_ref
GROUP BY customer_id
) AS first_purchases ON orders.customer_id = first_purchases.customer_id
WHERE  orders.order_purchase_timestamp BETWEEN '20161001' AND '20180930'
GROUP BY orders.customer_id, first_purchases.first_purchase_date, EOMONTH(orders.order_purchase_timestamp)
ORDER BY order_purchase_timestamp;

结果-注意它们与您的结果不同，因为您希望最早的日期是1/10/2016。

customer_id   order_purchase_timestamp   first_purchase_date        age_by_month
1             2016-10-04 00:00:00.000    2016-09-04 00:00:00.000    1

编辑:因为别人会这么做的!您可以通过一次读取来完成此操作，这可能会运行得更快一些。它也短了一点，但在我看来更难理解。

下面使用窗口函数来计算客户最早的购买，以及每个月最早的购买(并使用DISTINCT而不是GROUP BY)。这样，它只执行DATEDIFF来计算差值。

WITH monthly_orders AS
(SELECT DISTINCT orders.customer_id, 
MIN(orders.order_purchase_timestamp) OVER (PARTITION BY orders.customer_id, EOMONTH(orders.order_purchase_timestamp)) AS order_purchase_timestamp,
MIN(orders.order_purchase_timestamp) OVER (PARTITION BY orders.customer_id) AS first_purchase_date
FROM   orders)
SELECT *, DATEDIFF(month, first_purchase_date, order_purchase_timestamp) AS age_by_month
FROM   monthly_orders
WHERE  order_purchase_timestamp BETWEEN '20161001' AND '20180930';

还要注意这两个字段以及您使用的日期或日期时间字段的类型-如果您有日期时间而不仅仅是日期，BETWEEN '20161001' AND '20180930'与>= '20161001' AND < '20181001'是不一样的