*二、分析工具应用(选做加分题) 1、工作中用到mysgl服务器,具体信息如下: log info为用户登录信息表,其中UID为用户唯一识别ID,channelLID为渠道ID. log_date为用户登录日期 pay_info为用户付费信息表,其中UID为用户唯一识别ID,channel ID为渠道ID, payment为用户付费金额,paid_state为用户付款状态(1为成功,0为失败) log_info与pay_info中同一UID为同一用户,同一channelID为同一渠道 现需求为提取每个渠道下的登录用户数量以及付费超过6元的用户数量,并计算付费率(付费率=付费用户数量/登录用户数量,此处付费用户数量定为付费超过6元的用户数量),输出结果格式如result表,请写出 SQL查询语句(给出必要注释)
时间: 2023-04-05 18:05:03 浏览: 72
SELECT
log_info.channelLID AS 渠道ID,
COUNT(DISTINCT log_info.UID) AS 登录用户数量,
COUNT(DISTINCT CASE WHEN pay_info.payment > 6 AND pay_info.paid_state = 1 THEN pay_info.UID END) AS 付费用户数量,
COUNT(DISTINCT CASE WHEN pay_info.payment > 6 THEN log_info.UID END) AS 付费超过6元的用户数量,
COUNT(DISTINCT CASE WHEN pay_info.paid_state = 1 THEN log_info.UID END) AS 付款成功的用户数量,
COUNT(DISTINCT CASE WHEN pay_info.paid_state = 0 THEN log_info.UID END) AS 付款失败的用户数量,
COUNT(DISTINCT log_info.UID) / COUNT(DISTINCT CASE WHEN pay_info.payment > 6 THEN log_info.UID END) AS 付费率
FROM
log_info
LEFT JOIN pay_info ON log_info.UID = pay_info.UID AND log_info.channelLID = pay_info.channelLID
GROUP BY
log_info.channelLID
ORDER BY
log_info.channelLID ASC;
-- 注释:
-- 1. 使用 LEFT JOIN 连接 log_info 和 pay_info 表,以 UID 和 channelLID 作为连接条件
-- 2. 使用 COUNT(DISTINCT ...) 计算不重复的用户数量
-- 3. 使用 CASE WHEN ... THEN ... END 进行条件判断
-- 4. 付费率的计算使用 COUNT(DISTINCT ...) / COUNT(DISTINCT ...)
-- 5. 按照渠道ID升序排序
-- 6. 输出结果格式如result表