oracle 子查询、Group by和窗口函数

• 子查询：一个查询（外部查询）内部嵌套另一个查询（内部查询）。
• Group by和窗口函数的区别：

  • GROUP BY：

    • 用于将结果集中的数据分组，并对每个组进行汇总计算。
    • 汇总计算包括聚合函数（如 SUM(), AVG(), COUNT(), MAX(), MIN()）的应用。
    • 结果集中每个分组只会保留一行，汇总计算的结果将代替原始行（原始数据会丢失）。

  • 窗口函数：

    • 在保留结果集的原始行的情况下，对每一行进行计算。
    • 能够对数据进行排名、移动平均等复杂的分析。
    • 计算时会考虑窗口范围内的其他行，但不会改变结果集的行数（原始数据不会丢失）。
• 各自的用途：

  • GROUP BY：适用于需要进行数据汇总和分组的场景，如计算总和、平均值、计数等。

  • 窗口函数：适用于需要在原始数据基础上进行复杂分析的场景，如排名、移动平均、逐行比较等。

• 示例1：给定一张学生学科成绩表base_student_grade,字段id表示学生学号，name为姓名，subject表示学科，grade为某学科成绩。使用子查询和窗口函数查询每个学生成绩最高的学科和分数。

select id,name,subject,grade
from (
    select id,name,subject,grade,
        row_number() over(partition by id,name order by grade desc) as rn
    from base_student_grade
) where rn=1

1. row_number() ：窗口函数。为结果集中的每一行生成一个唯一的行号。
2. partition by：将数据按照id,name进行分区，即将具有相同id,name的行分为同一个分区。
3. order by grade desc：每个分区按照grade降序排列。
4. rn=1：选取每个分区内的第一行，rn=2：选取每个分区内的第二行，rn<=2：选取每个分区内的前两行。

• 示例2：将每个部门的员工薪资总和计算出来，并按部门分组。

​SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;