Oracle数据库实现内部分组的简单方法（oracle 内部分组）

Oracle数据库实现内部分组的简单方法

在Oracle数据库中，分组是一种经常使用的聚合函数，用于将数据进行汇总和计算。通常情况下，分组是根据指定属性对数据集合进行分组，然后对每个组进行聚合计算。但是，在一些特殊情况下，我们需要对数据集合内部进行分组，以实现更精细的聚合计算。本文将介绍一种简单的方法，在Oracle数据库中实现内部分组。

让我们看一个简单的示例数据集合，包含学生姓名、班级名称和学科成绩。

CREATE TABLE student_scores
(
    student_name  varchar2(20),
    class_name    varchar2(20),
    subject       varchar2(20),
    score         number(5,2)
);
INSERT INTO student_scores VALUES ('张三', '一班', '语文', 85);
INSERT INTO student_scores VALUES ('张三', '一班', '数学', 90);
INSERT INTO student_scores VALUES ('张三', '一班', '英语', 80);
INSERT INTO student_scores VALUES ('李四', '一班', '语文', 75);
INSERT INTO student_scores VALUES ('李四', '一班', '数学', 85);
INSERT INTO student_scores VALUES ('王五', '二班', '语文', 90);
INSERT INTO student_scores VALUES ('王五', '二班', '数学', 92);
INSERT INTO student_scores VALUES ('赵六', '二班', '英语', 80);

我们的目标是按照学生姓名和班级名称进行内部分组，计算每个学生在本班内的平均成绩和排名。为此，我们需要进行以下步骤。

步骤一：计算每个学生在每个班级内的平均成绩。

SELECT
    student_name,
    class_name,
    AVG(score) AS avg_score
FROM
    student_scores
GROUP BY
    student_name,
    class_name;

这样，我们就得到了每个学生在每个班级内的平均成绩，得到以下结果。

STUDENT_NAME  CLASS_NAME  AVG_SCORE
------------  ---------- ---------
赵六              二班         80
李四              一班         80
张三              一班         85
王五              二班         91

步骤二：计算每个学生在本班内的排名。具体方法是，先将每个学生在每个班级内的平均成绩按照降序排列，然后使用Rank()函数计算排名。注意，由于Rank()函数会跳过相同的排名，因此我们需要使用Dense_Rank()函数。

SELECT
    student_name,
    class_name,
    AVG(score) AS avg_score,
    Dense_Rank() OVER(PARTITION BY class_name ORDER BY AVG(score) DESC) AS rank
FROM
    student_scores
GROUP BY
    student_name,
    class_name;

这样，我们就得到了每个学生在本班内的平均成绩和排名，得到以下结果。

STUDENT_NAME  CLASS_NAME  AVG_SCORE  RANK
------------  ----------  ---------  ----
赵六              二班         80         3
王五              二班         91         1
张三              一班         85         1
李四              一班         80         2

综上所述，我们使用以上两步骤，就实现了在Oracle数据库中对数据集内部进行分组的简单方法。当然，如果数据集更为复杂和庞大，我们还可以使用其他高级分析函数（如Lag()和Lead()函数）和数据透视表（Pivot和Unpivot操作）进行更为详尽和灵活的分组计算。

完整代码如下：

CREATE TABLE student_scores
(
    student_name  varchar2(20),
    class_name    varchar2(20),
    subject       varchar2(20),
    score         number(5,2)
);
INSERT INTO student_scores VALUES ('张三', '一班', '语文', 85);
INSERT INTO student_scores VALUES ('张三', '一班', '数学', 90);
INSERT INTO student_scores VALUES ('张三', '一班', '英语', 80);
INSERT INTO student_scores VALUES ('李四', '一班', '语文', 75);
INSERT INTO student_scores VALUES ('李四', '一班', '数学', 85);
INSERT INTO student_scores VALUES ('王五', '二班', '语文', 90);
INSERT INTO student_scores VALUES ('王五', '二班', '数学', 92);
INSERT INTO student_scores VALUES ('赵六', '二班', '英语', 80);
SELECT 
    student_name, 
    class_name, 
    AVG(score) AS avg_score, 
    Dense_Rank() OVER(PARTITION BY class_name ORDER BY AVG(score) DESC) AS rank
FROM 
    student_scores 
GROUP BY 
    student_name, 
    class_name;