选择要查询的字段

SELECT m.category, # 应用类别 # 计算中位数决策时间并转换为无符号整数 CAST( PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY TIMESTAMPDIFF(SECOND, a.time, d.time)) AS UNSIGNED ) AS median_decision_time

从app表中获取数据

FROM app a

将app表中的package_name与mapping_table表中的package_name关联

JOIN mapping_table m ON a.package_name = m.package_name

从app表中获取下载操作中最早的记录

JOIN ( 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 a.action_type = 'itemShow'

按应用类别分组

GROUP BY m.category

对这段代码的重要部分进行注释SELECT mcategory CAST PERCENTILE_CONT05 WITHIN GROUP ORDER BY TIMESTAMPDIFFSECOND atime dtime AS UNSIGNED AS median_decision_time FROM app a JOIN mapping_table m O