mysql - 如何计算每个用户mysql的timediff
问题描述
假设我有这样的数据表
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'
我如何获得每笔交易和每个用户的时间差异,所以结果就像这样
MAX MIN AVERAGE MEDIAN
3 1 3 3
解释:
- 从“2020-01-03”到“2020-01-06”(3 天)时,users_id 12 中的最大 timediff
- 当“2020-01-02”和“2020-01-03”之间的交易时,users_id 13 中的 timediff 的最小值发生
- 平均值为 3(users_Id 12 中的 2 天 + users_Id 12 中的 3 天 + users_Id 13 中的 1 天)/users_id 的计数(12 和 13)
解决方案
你可以使用这样的东西(不计算中位数):
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 上的这个答案使用类似的东西。如您所见,查询变得非常混乱。没有类似SUM
或AVG
在 MySQL 上获取中位数的函数。
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
推荐阅读
- c# - 如何将数据从本地机器(计算机)推送到 Azure Table
- python - Add filters to query dynamically using pyscopg2
- python - 无法在 django ''function' 对象中调用对象方法没有属性 'objects'”
- javascript - 为什么悬停选择器不适用于类?
- excel - 如何让文本框显示结果
- sql - 语法返回额外 NULLS 的情况
- excel - 一个宏运行,另一个不运行
- vue.js - 如何仅在某些组件中加载本地 JavaScript 文件
- javascript - 表单始终使用 Javascript 确认提交
- c++ - malloc、free、多指针,它是如何工作的?