计算应用类别中位数决策时间 - SQL查询代码示例

-- 从app表中选择所有行，并与mapping_table表中的行进行连接，以便获取应用程序的类别信息
SELECT m.category,
-- 计算决策时间的中位数，并将结果转换为无符号整数
CAST( AVG(TIMESTAMPDIFF(SECOND, a.time, d.time)) AS UNSIGNED ) AS median_decision_time
-- 从app表中选择所有行，并与下载操作类型相应的行进行连接，以便获取每个用户和应用程序的第一次下载时间
FROM app a
JOIN mapping_table m ON a.package_name = m.package_name
JOIN (
-- 从app表中选择所有行，并按用户ID和应用程序名称分组，以获取每个用户和应用程序的第一次下载时间
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
-- 从app表中选择所有行，并与itemShow操作类型相应的行进行连接，以便获取每个类别的应用程序的中位数决策时间
WHERE a.action_type = 'itemShow'
GROUP BY m.category;

代码解释:

• SELECT m.category: 从mapping_table表中选择应用程序类别信息。
• CAST( AVG(TIMESTAMPDIFF(SECOND, a.time, d.time)) AS UNSIGNED ) AS median_decision_time: 计算每个用户在应用程序中从展示到下载的决策时间，并转换为无符号整数。
• FROM app a: 从app表中选择所有行，其中包含用户的操作信息。
• JOIN mapping_table m ON a.package_name = m.package_name: 将app表中的应用程序名称与mapping_table表中的应用程序名称进行匹配，以便获取应用程序的类别信息。
• 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': 筛选出app表中包含'itemShow'操作类型的所有行，表示用户查看应用程序信息。
• GROUP BY m.category: 根据应用程序类别对结果进行分组，以便计算每个类别的中位数决策时间。