SQL练习

多字段分组和排序

需求

对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量

正确语句

select gender,university,count(1) as user_num,
avg(active_days_within_30) as avg_active_day,
avg(question_cnt) as avg_question_cnt 
from user_profile 
group by gender,university 
order by gender asc,university asc

应该就是先根据性别分，然后再在性别基础上不同大学再分出来
order by也是 先根据第一个字段排序完成后，分出了不同的组，再在对应组内进行第二个字段的排序（后一次排序在自己组内进行不会更改组间顺序）

ONLY_FULL_GROUP_BY

在 MySQL 5.7 及以上版本，默认启用了 ONLY_FULL_GROUP_BY 模式，它用于 强制 SQL 语句符合 SQL 标准的分组规则。
当你的 SELECT 语句包含了 非聚合列（即没有使用 GROUP BY 或 聚合函数 计算），而这些列没有出现在 GROUP BY 中时，就会触发这个错误。例如：

SELECT university, question_cnt
FROM user_profile
GROUP BY university;

这样的话北京大学有n个学生
n个学生的question_cnt不同，它就不知道返回哪个数据

解决方法

正确解决：按需求将select字段改为聚合函数，因为分组本来就是查不同组内的聚合信息

错误解决方法:groupy by 后加字段，这种解决没有任何意义，因为查询结果基本没有意义，下面看案例

这种查询结果没什么意义
A中分出5 和 10 但是不知道5有几个人 10有几个人
仅能知道A中有5和10

特殊情况，非聚合字段也可以出现在select中

当不会发生歧义的时候，即唯一对应，非聚合字段也可以出现在select中
比如下面这个 group by后面只有r.region_id，但是select了r.region_name等非聚合字段
因为一个id对应一个name对应一个url对应一个ids，所以不会违反ONLY_FULL_GROUP_BY的规则

举个例子哈，比如上个例子的A大学对应的question_cnt只能是5
所以你group by university查出来和group by university,question_cnt是一样的
都是A,5

<select id="rentDetail" parameterType="map" resultType="map">
        SELECT
            r.region_id AS regionId,
            r.region_name AS regionName,
            r.ecology_ids AS ecologyIds,
            IFNULL(r.img_url, '') AS imgUrl,
            COUNT(re.resource_id) AS total,
            IFNULL(SUM(re.area_size), 0) AS areaSize,
            SUM(IF(re.is_rented = '30', 1, 0)) AS isRentedCount,
            SUM(IF(re.is_rented = '20', 1, 0)) AS unRentedCount,
            SUM(IF(re.is_rented = '22', 1, 0)) AS lockCount,
            SUM(IF(DATEDIFF(re.end_time, NOW()) BETWEEN 0 AND re.warning_days, 1, 0)) AS toExpireCount
        FROM
            m_region r
        LEFT JOIN m_resources re ON re.region_id = r.region_id AND re.status = 0
        WHERE
            r.operator_id = #{operatorId}
        <if test="type != null and type != ''"> and re.type = #{type}</if>
        AND
            r.region_id IS NOT NULL
        GROUP BY
            r.region_id
    </select>

数据库表

where只能筛选原数据

需求

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

错误语句

select university,avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt 
from user_profile 
where avg_question_cnt<5 or avg_answer_cnt <20
group by university;

错误原因：where只能对于原数据进行筛选，而avg，count等属于聚合函数不属于原数据，不能被where筛选，如果需要筛选需要用到分组后筛选having
正确语句

~from user_profile 
group by university 
having avg_question_cnt<5 or avg_answer_cnt <20

多表查询FROM JOIN ON

需求：统计每个学校的答过题的用户的平均答题数


错误语句

select u.university,
	count(1)/distinct qd.device_id
  from user_profile u,question_practice_detail qd 
  where qd.device_id = u.device_id 
  group by university 

错误点
1.我们想要统计的是不同distinct 的device的数量而不是统计所有结果
所以我们使用count(distinct qd.device_id)可以完成统计不同设备的总个数
2. from user_profile u,question_practice_detail qd 这种写法是隐式连接（老旧写法），应该用 JOIN，改为FROM user_profile u JOIN question_practice_detail qd ON qd.device_id = u.device_id
其实就是把左连接/右连接写法统一普通多表查询
正确语句

SELECT 
    u.university, 
    COUNT(1) / COUNT(DISTINCT qd.device_id) AS avg_questions_per_user
FROM user_profile u
JOIN question_practice_detail qd 
    ON qd.device_id = u.device_id
GROUP BY u.university;