如何使用mysql找出每个用户上每个事务的时间差(当天)

Question

我有一个这样的表：

CREATE TABLE test (
ID SERIAL PRIMARY KEY,
user_id INT,
createdAt DATE,
status_id INT
);
INSERT INTO test VALUES
(1, 12, '2020-01-01', 4),
(2, 12, '2020-01-03', 7),
(3, 12, '2020-01-06', 7),
(4, 13, '2020-01-02', 5),
(5, 13, '2020-01-03', 6),
(6, 14, '2020-03-03', 8),
(7, 13, '2020-03-04', 4),
(8, 15, '2020-04-04', 7),
(9, 14, '2020-03-02', 6),
(10, 14, '2020-03-10', 5),
(11, 13, '2020-04-10', 8);

这是我的小提琴

在该表中，每个事务的id有id，user_id是用户，createdAt是事务发生的日期，status_id是每个事务的状态(在这种情况下，status_id4、5、6、8是已批准的事务(

我想找出在"2020-02-01"one_answers"2020-04-01"之间进行交易的每个用户的每个交易的最大、最小、平均不同日期，在该期间批准了>1笔交易

这是我的问题：

SELECT MIN(diff) AS `MIN`, MAX(diff) AS `MAX`, SUM(diff) / COUNT(DISTINCT user_id) AS `AVG`
FROM (
SELECT ID, user_id, DATEDIFF((SELECT t2.createdAt FROM test t2 WHERE t2.user_id = t1.user_id AND t1.createdAt <= t2.createdAt AND t2.id <> t1.id LIMIT 1), t1.createdAt) AS diff
FROM test t1
where 
status_id in (4, 5, 6, 8)
HAVING SUM(t1.user_id BETWEEN '2020-02-01' AND '2020-04-01')
AND SUM(t1.user_id >= '2020-02-01') > 1 
) DiffTable
WHERE diff IS NOT NULL

但据说：

在没有GROUP BY的聚合查询中，SELECT列表的表达式#1包含非聚合列"fidde_KDQIQQDMUZEIOVXFHRZPY.t1.ID"；这与sql_mode=only_full_group_by 不兼容

我该怎么办？

这是我的小提琴

预期结果

+-----+-----+---------+
| MAX | MIN | AVERAGE |
+-----+-----+---------+
|  36 |   1 |      22 |
+-----+-----+---------+

解释：

- the user_id who have approval transaction on 2020-02-01 until 2020-04-01 and user_id who have transaction more than 1 are user_id 13 & 14
- the maximum of different day on 2020-02-01 until 2020-04-01 are user_id 13 which the different day for each transaction happen in 2020-03-04 and doing next transaction again in 2020-04-10
- the minimum day of different day of each transaction are user_id 14 who doing transaction on 2020-03-02 and next transaction 2020-03-03
- average are 22 days (sum of different day on user_Id 13 & 14 / amount of user_id who fit on this condition) 

Answer 1

您需要在子查询之外执行GROUPing；子查询应仅用于将所选事务限制为具有所需CCD_ 7值和所需范围内的日期的那些事务。然后，您可以在外部查询中选择在该期间内有多笔交易的用户：

SELECT user_id,
COUNT(*) AS transactions, 
MIN(diff) AS `MIN`, 
MAX(diff) AS `MAX`, 
SUM(diff) / COUNT(diff) AS `AVG`
FROM (
SELECT user_id, DATEDIFF((SELECT MIN(t2.createdAt)
FROM test t2
WHERE t2.user_id = t1.user_id
AND t1.createdAt < t2.createdAt
AND t2.status_id in (4, 5, 6, 8)
), t1.createdAt) AS diff
FROM test t1
WHERE status_id in (4, 5, 6, 8)
AND createdAt BETWEEN '2020-02-01' AND '2020-04-01'
) DiffTable
WHERE diff IS NOT NULL
GROUP BY user_id
HAVING COUNT(*) > 1

输出(为您的小提琴(：

user_id     transactions    MIN     MAX     AVG
14          2               1       7       4.0000

dbfiddle 演示

如果您想要基于在此期间发生的所有事务而不是user_id的值，您可以简单地删除GROUP BY和HAVING子句：

SELECT COUNT(*) AS transactions, 
MIN(diff) AS `MIN`, 
MAX(diff) AS `MAX`, 
SUM(diff) / COUNT(diff) AS `AVG`
FROM (
SELECT user_id, DATEDIFF((SELECT MIN(t2.createdAt)
FROM test t2
WHERE t2.user_id = t1.user_id
AND t1.createdAt < t2.createdAt
AND t2.status_id in (4, 5, 6, 8)
), t1.createdAt) AS diff
FROM test t1
WHERE status_id in (4, 5, 6, 8)
AND createdAt BETWEEN '2020-02-01' AND '2020-04-01'
) DiffTable
WHERE diff IS NOT NULL

输出：

transactions    MIN     MAX     AVG
3               1       37      15.0000

dbfiddle 演示

注意，DATEDIFF计算中现有的子查询存在几个问题：没有ORDER BY的LIMIT不能保证给出预期的结果，并且status_id没有条件。我已经在更新的查询中修复了这两个问题。