如何查询用户第二次与第三次资料编辑的时间差中位数
2026-8-12
原始数据表
| uuid | user_id | user_created | profile_edited |
|---|---|---|---|
| 1c5d134c | user_3 | 2022-11-10T19:09:05+00:00 | 2022-11-18T18:00:05+00:00 |
| 1c5d134b | user_3 | 2022-11-10T19:09:05+00:00 | 2022-11-15T18:00:05+00:00 |
| 1c5d134a | user_3 | 2022-11-10T19:09:05+00:00 | 2022-11-14T18:00:05+00:00 |
| 1c5d434a | user_1 | 2022-11-10T19:09:05+00:00 | 2022-11-13T19:09:05+00:00 |
| 1c8b424b | user_1 | 2022-11-10T19:09:05+00:00 | 2022-11-10T16:09:05+00:00 |
| 1c8b424c | user_2 | 2022-11-01T19:09:05+00:00 | 2022-11-19T19:09:05+00:00 |
| 1c8b424a | user_2 | 2022-11-01T19:09:05+00:00 | 2022-11-22T19:09:05+00:00 |
| 1c8b424b | user_2 | 2022-11-01T19:09:05+00:00 | 2022-11-24T19:09:05+00:00 |
| 1c5d434c | user_1 | 2022-11-10T19:09:05+00:00 | 2022-11-18T19:09:05+00:00 |
| 1c5d434e | user_1 | 2022-11-10T19:09:05+00:00 | 2022-11-16T19:09:05+00:00 |
| 1c5d434c | user_1 | 2022-11-10T19:09:05+00:00 | 2022-11-14T19:09:05+00:00 |
用户第二次与第三次资料编辑时间统计
| user_id | second_edit | third_edit | difference |
|---|---|---|---|
| user_1 | 2022-11-13T19:09:05+00:00 | 2022-11-14T19:09:05+00:00 | 24 hours |
| user_2 | 2022-11-22T19:09:05+00:00 | 2022-11-24T19:09:05+00:00 | 48 hours |
| user_3 | 2022-11-15T18:00:05+00:00 | 2022-11-18T18:00:05+00:00 | 72 hours |
时间差中位数查询语句
针对24、48、72这三个时间差计算中位数，可使用以下SQL查询：
通用排序法
适用于任意数量的时间差，自动计算中位数：
WITH time_diffs AS ( SELECT 24 AS hour_diff UNION ALL SELECT 48 UNION ALL SELECT 72 ) SELECT hour_diff AS median_hours FROM ( SELECT hour_diff, ROW_NUMBER() OVER (ORDER BY hour_diff) AS row_num, COUNT(*) OVER () AS total_rows FROM time_diffs ) ranked_diffs WHERE row_num = CEIL(total_rows / 2);
简化查询法
已知仅有三个数值时，可直接选取中间值：
SELECT hour_diff AS median_hours FROM (VALUES (24), (48), (72)) AS diffs(hour_diff) ORDER BY hour_diff LIMIT 1 OFFSET 1;
两种方法都会返回48作为中位数，对应48小时。
内容的提问来源于stack exchange，提问作者akyayik
相关产品推荐
相关产品推荐
