sql - 蜂巢查询以查找客户从当前交易日期起过去 10 天内的交易总数

Question

如何获取客户自上次交易日起最近 10 天内的交易次数。例子：

Name  Type     Trx_date
Cody  sale  08/05/2018 12:11:43
Cody  sale  08/01/2018 07:51:49
Cody  sale  08/01/2018 07:51:49
Cody  sale  08/01/2018 07:51:49
Cody  sale  07/28/2018 03:21:32
Cody  sale  02/25/2018 05:19:34
Cody  sale  01/20/2018 06:15:41
Laim  sale  08/06/2018 11:41:23
Laim  sale  08/01/2018 07:51:49
Laim  sale  08/01/2018 07:51:49
Laim  sale  07/30/2018 22:58:13
Laim  sale  05/19/2018 03:21:02
Laim  sale  02/25/2018 09:19:34
Laim  sale  01/29/2018 07:15:41

结果必须是：

Name  Type     Last_Trx_date           10d_Count
Cody  sale   08/05/2018 12:11:43          5
Laim  sale   08/06/2018 11:41:23          4

标签： sql

Answer 1

这将有助于

SELECT
Name,
SUBSTRING_INDEX(GROUP_CONCAT(Trx_date ORDER BY Trx_date DESC),',',1) AS last_transaction_date,
COUNT(*)
FROM 
tableName
WHERE Trx_date >= (NOW() - INTERVAL 10 DAY)
GROUP BY Name;