SQL

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+

select a1.machine_id, round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
from Activity as a1 join Activity as a2
on a1. machine_id = a2.machine_id and a1.process_id = a2.process_id 
where a1.activity_type = 'start' and a2.activity_type = 'end'
group by a1.machine_id

如果SELECT中只使用了a1.machine_id，数据库可以推断出分组列是machine_id，因此允许省略GROUP BY中的a1.前缀

inner join¶

定义：INNER JOIN 只返回两个表中满足连接条件的匹配行。

outer join¶

类型：
- LEFT OUTER JOIN（或 LEFT JOIN）：返回左表的所有行，即使右表中没有匹配项。
- RIGHT OUTER JOIN（或 RIGHT JOIN）：返回右表的所有行，即使左表中没有匹配项。
  - FULL OUTER JOIN（,）：返回两个表的所有行，无论是否有匹配项（MySQL 不支持 FULL OUTER JOIN，但可通过 UNION 实现）。

特性	`CROSS JOIN`	逗号（`,`）
可读性	更清晰，明确表示是笛卡尔积操作。	不够直观，容易与其他操作混淆。
现代 SQL 标准	符合现代 SQL 标准，推荐使用。	是传统写法，不推荐在现代 SQL 中使用。
与其他 JOIN 的兼容性	可以与其他 JOIN（如 `INNER JOIN`、`LEFT JOIN` 等）混合使用。	不能与其他 JOIN 混合使用。
可扩展性	更易于扩展和维护。	可读性和维护性较差。

having¶

having 一般和 group by 一起使用，因为 where 不能对分组的结果进行约束（其在 group by）之前作用。having 对 group by 的结果进行作用

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount > 300;

至少有 5 名直属下属的经理

首先使用连接每组下属和经理，随后使用 having 筛选出分组中的 count

select b.name 
from Employee a, Employee b
where a.managerId = b.id
group by b.id 
having count(*) >= 5;

学生们参加各科测试的次数

此处用到了多维 group by

Students
+---------------+---------+         
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+

Subjects
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+

Examinations
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+

select stu.student_id, stu.student_name, sub.subject_name, ifnull(grouped.cnt, 0) as attended_exams
from 
    Students stu
    cross join 
    Subjects sub
    left join
    (
        select student_id, subject_name, count(*) as cnt
        from Examinations
        group by student_id, subject_name
    )grouped
    on stu.student_id = grouped.student_id and sub.subject_name = grouped.subject_name
order by stu.student_id, sub.subject_name;

求比率¶

使用 avg 来计算 1 和 0 的均值

确认率

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+

action = 'confirmed' 是一个条件表达式，返回布尔值：
- 如果 action 等于 'confirmed'，结果为 1（真）。
- 否则，结果为 0（假）。
AVG() 函数会对这些 1 和 0 求平均值，结果就是 action = 'confirmed' 的行所占的比例。

select s.user_id,
round(ifnull(avg(action = 'confirmed'), 0), 2) as confirmation_rate
from Signups s left join Confirmations c
on s.user_id = c.user_id
group by s.user_id;

TopK 问题¶

CREATE TABLE employees(
    `employee_id` int(6) NOT NULL auto_increment, -- '员工编号'
    `salary` double(10,2) DEFAULT NULL,-- '月薪'
    `department_id` int(6) DEFAULT NULL, -- '部门ID'
    PRIMARY KEY(`employee_id`)
);

求各个部门工资前三名的员工 id：

select employee_id
from employees as e1
where (
    select count(*) 
    from employees as e2
    where e1.department_id = e2.department_id
    and e1.salary < e2.salary
) <= k - 1
order by department_id, salary desc;

注意，这里的 where 关联子查询中 e1 指的其实并不是表 e1 ，而是外层 select 尝试的每一行

注意，这里的不能在最后写 group by department_id，因为这里 select 的是 employee_id，而不是某个聚合函数