我正在使用Microsoft SQL Server Management Studio。我正在尝试衡量电子商务网站的客户保留率。
为此,我需要四个值:
- 第一个月
order_purchase_timestamp
age_by_month
first_purchase
age_by_month
和first_purchase
的值在我的数据库中是而不是,我想计算它们。
在我的数据库中,我有customer_id
和order_purchase_timestamp
。first_purchase
应该是order_purchase_timestamp
的最早示例,我只需要月份和年份。age_by_month
应该是从first_purchase
到order_purchase_timestamp
的月份差。
我只想衡量每个月的客户保留率,所以如果在同一个月进行了两次购买,它不应该显示。
日期在2016 - 10 - 01到2018 - 09 - 30之间.它应该是由order_purchase_timestamp
排序的
一个例子
| 客户标识|订单_购买_时间戳|
| - ------| - ------|
| 1个|2016年9月4日|
| 第二章|2016年9月5日|
| 三个|2016年9月5日|
| 三个|2016年9月15日|
| 1个|2016年10月4日|
到
| 客户标识|首次购买|年龄_按_月|订单_购买_时间戳|
| - ------| - ------| - ------| - ------|
| 1个|2016年至2009年|无|2016年9月4日|
| 第二章|2016年至2009年|无|2016年9月5日|
| 三个|2016年至2009年|无|2016年9月5日|
| 1个|2016年至2009年|1个|2016年10月4日|
我所做的
SELECT
customer_id, order_purchase_timestamp
FROM
orders
WHERE
(order_purchase_timestamp BETWEEN '2016-10-01' AND '2016-12-31')
OR (order_purchase_timestamp BETWEEN '2017-01-01' AND '2017-03-31')
OR (order_purchase_timestamp BETWEEN '2017-04-01' AND '2017-06-30')
OR (order_purchase_timestamp BETWEEN '2017-07-01' AND '2017-09-30')
OR (order_purchase_timestamp BETWEEN '2017-10-01' AND '2017-12-31')
OR (order_purchase_timestamp BETWEEN '2018-01-01' AND '2018-03-31')
OR (order_purchase_timestamp BETWEEN '2018-04-01' AND '2018-06-30')
OR (order_purchase_timestamp BETWEEN '2018-07-01' AND '2018-09-30')
ORDER BY
order_purchase_timestamp
本来我打算按季度来做,但现在我想按月来做。
2条答案
按热度按时间kmpatx3s1#
下面的方法被设计成相对容易理解。还有其他方法(例如窗口函数)可能稍微更有效;但这使得以您当前的SQL技能水平进行维护变得很容易。
请注意,下面的SQL命令是相互构建的(所以答案在最后)。为了沿着,下面是一个db<>fiddle,其中包含。
它基于一个简单的查询(我们将使用它作为子查询),该查询查找每个客户的第一个order_purchase_timestamp。
接下来是DATEDIFF,查找两个日期之间的差异。
然后,您可以使用上面的内容作为子查询,获取每一行的第一个日期-然后查找日期差,例如,
但是,当客户在一个月内有多次购买时(每次购买占一行),上面的操作会重复。相反,我们可以使用GROUP BY按月份进行分组,然后只取当月的第一次购买。
一个“直接”的方法是按YEAR(orders.order_purchase_timestamp)和MONTH(orders.order_purchase_timestamp)分组。但是,我使用了下面的小技巧-使用EOMONTH查找月份的最后一天。EOMONTH返回该月份中任何日期的相同日期;因此,我们可以按此分组。
最后,您可以添加WHERE表达式和ORDER BY以获得您所要求的结果(在两个日期之间)
结果-请注意,它们与您的结果不同,因为您希望最早日期为2016年1月10日。
**编辑:**因为其他人也会这样做!你可以用一次通读来完成,这样可能会运行得更快一些。它也有点短--但是更难理解imo。
下面的代码使用windows functions来计算客户的最早购买量和每个月的最早购买量(并且使用DISTINCT而不是GROUP BY),然后使用DATEDIFF来计算差额。
但是请注意,这在结果中有一个不同之处,如果您在一个月内有2个订单,并且您的最小日期过滤器在到之间(例如,订单在15/10和20/10,而您的最小日期是16/10),那么行 * 将不会 * 被包括在内,因为该月最早的购买超出了过滤器范围。
还要注意这两种情况以及您使用的日期或日期时间字段的类型-如果您有日期时间而不仅仅是日期,那么
BETWEEN '20161001' AND '20180930'
与>= '20161001' AND < '20181001'
是不同的n7taea2i2#
下面是一个简短的查询,它可以满足您的所有需求(所使用方法的描述是内联的):