如何为每个用户的mysql计数timediff [英] how to count timediff for each users mysql

问题描述

假设我有这样的数据表

ID  users_Id   createdAt
1   12         '2020-01-01'
2   12         '2020-01-03'
3   12         '2020-01-06'
4   13         '2020-01-02'
5   13         '2020-01-03'  

我如何获取每个交易和每个用户的timediff，所以结果就像这样

how do i get the timediff for every transaction and every users so the results are just like this

MAX   MIN   AVERAGE    MEDIAN
3     1     3          3 

说明:

• timediff的最大值发生在从2020年1月3日到2020年1月6日(3天)之间的users_id 12中.
• 在"2020-01-02"和"2020-01-03"之间进行交易时，user_id 13中发生的时间差异最小.
• 平均数为3(users_Id 12天2天+ users_Id 12天3天+ users_Id 13天1天)/users_id的数量(12和13)

推荐答案

您可以使用类似这样的方法(不计算中位数):

You can use something like this (without computing the median):

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 order_status_id in (4, 5, 6, 8)
) DiffTable
WHERE diff IS NOT NULL

---

在MySQL上计算中位数要复杂得多.但是，您可以在StackOverflow上的此答案中基于使用类似这样的内容.如您所见，查询变得非常混乱.在MySQL上没有像SUM或AVG这样的函数来获取中位数.

---

The median is much more complicated to compute on MySQL. But you can use something like this based on this answer on StackOverflow. As you can see the query get very messy. There is no function like SUM or AVG on MySQL to get the median.

SELECT MIN(DiffTable.diff) AS `MIN`, MAX(DiffTable.diff) AS `MAX`, SUM(DiffTable.diff) / COUNT(DISTINCT user_id) AS `AVG`, MIN(median.diff) AS `MEDIAN`
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 order_status_id in (4, 5, 6, 8)
) DiffTable, (
  SELECT m1.diff 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 order_status_id in (4, 5, 6, 8)
  ) m1, (
    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 order_status_id in (4, 5, 6, 8)
  ) m2
  WHERE m1.diff IS NOT NULL AND m2.diff IS NOT NULL
  GROUP BY m1.diff
  HAVING SUM(SIGN(1-SIGN(m1.diff-m2.diff))) = (COUNT(*)+1)/2
) median
WHERE DiffTable.diff IS NOT NULL

在dbfiddle.uk上的演示

这篇关于如何为每个用户的mysql计数timediff的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！