计算应用展示到下载的中位决策时间 SQL 查询

SELECT -- 选择需要查询的列 m.category, -- 将计算出的中位数转换为无符号整数形式输出 CAST( PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY TIMESTAMPDIFF(SECOND, a.time, d.time)) AS UNSIGNED ) AS median_decision_time FROM -- 从 app 表中查询数据，使用 mapping_table 表中的 package_name 关联 app a JOIN mapping_table m ON a.package_name = m.package_name JOIN ( -- 在 app 表中查找每个用户对每个应用下载的最早时间 SELECT userid, package_name, MIN(time) AS time FROM app WHERE action_type = 'download' GROUP BY userid, package_name ) AS d ON a.userid = d.userid AND a.package_name = d.package_name WHERE -- 只查询 action_type 为 'itemShow' 的数据 a.action_type = 'itemShow' GROUP BY -- 按照分类进行分组 m.category.