用开窗函数优化下面这段mysql代码:select behavior_type, count(distinct user_id) as 用户数, count(distinct user_id)/(select count(distinct user_id) from userbehavior_new where behavior_type='pv') as 转化率 from userbehavior_new group by behavior_type order by behavior_type desc;
时间: 2024-04-09 12:28:20 浏览: 91
开窗函数.sql
使用开窗函数优化后的MySQL代码如下:
```sql
SELECT behavior_type,
COUNT(DISTINCT user_id) OVER () AS 用户数,
COUNT(DISTINCT user_id) / COUNT(DISTINCT CASE WHEN behavior_type = 'pv' THEN user_id END) OVER () AS 转化率
FROM userbehavior_new
GROUP BY behavior_type
ORDER BY behavior_type DESC;
```
优化点:
1. 使用COUNT(DISTINCT user_id) OVER ()将总用户数计算为一个窗口函数,以避免子查询。
2. 使用COUNT(DISTINCT CASE WHEN behavior_type = 'pv' THEN user_id END) OVER ()作为分母,计算转化率,避免子查询。
3. 保持原有的GROUP BY和ORDER BY子句不变。
阅读全文