SQL进阶——GROUP BY 和 PARTITION BY

本文介绍了使用Oracle数据库进行数据排名的方法,包括rank(), dense_rank() 和 row_number() 函数的应用,并展示了如何通过求余数实现数据抽样。通过具体实例,读者可以了解这些SQL技巧在实际场景中的运用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

运行环境 win10 / oracle(DB11g)

一、rank()dense_rank()row_number()

-- 创表
create table teams_5
(
    member varchar(6),
    team   varchar(4),
    age   SMALLINT
)
;

insert into teams_5 values ('大木', 'A', 28);
insert into teams_5 values ('一件', 'A', 19);
insert into teams_5 values ('新特', 'A', 23);
insert into teams_5 values ('山田', 'B', 40);
insert into teams_5 values ('九本', 'C', 30);
insert into teams_5 values ('乔天', 'D', 28);
insert into teams_5 values ('野野宫', 'D', 28);
insert into teams_5 values ('加藤', 'D', 24);
insert into teams_5 values ('新城', 'D', 22);

-- 排序
select member, team, age,
       rank() over(partition by team order by age desc) rn,
       dense_rank() over(partition by team order by age desc) dense_rn,
       row_number() over(partition by team order by age desc) row_num 
  from teams_5
 order by team, rn
;

结果:

MEMBER TEAM        AGE         RN   DENSE_RN    ROW_NUM
------ ---- ---------- ---------- ---------- ----------
大木   A            28          1          1          1
新特   A            23          2          2          2
一件   A            19          3          3          3
山田   B            40          1          1          1
九本   C            30          1          1          1
乔天   D            28          1          1          1
野野宫 D            28          1          1          2
加藤   D            24          3          2          3
新城   D            22          4          3          4

rn 位次有跳动,row_num连续

二、用求余数分组 (抽样)

-- 从原来的表中抽出(大约)五分之一的数据
select *
  from sometbl
 where mod(seq, 5) = 0
;

--如果没有连续编号,用row_number()函数生成
select * 
  fromselect col,
                row_number() over(order by col) as seq
          from somtbl)
 where mod(seq, 5) = 0
 ;

内容多来自 《SQL进阶教材》,仅做笔记。

### 关于SQL中 `GROUP BY` `PARTITION BY` 的高级教程 #### 使用场景对比 `GROUP BY` 子句用于汇总数据并返回每个分组的结果集。这意味着对于每一个唯一的组合,只会有一条记录被返回。而窗口函数中的 `PARTITION BY` 则允许在同一查询结果集中保留所有的原始行数,并基于指定列对这些行进行分区,在不减少总行数的情况下执行聚合操作。 例如,当统计员工工资时: - 如果使用 `GROUP BY`, 将得到每部门平均薪资的一览表; - 若采用带有 `PARTITION BY` 的窗口函数,则可以在保持原表格结构的同时增加一列表示每位成员所在部门内的相对位置或排名等附加信息[^1]。 #### 实际应用案例 考虑有一个名为 `sales` 的销售记录表,其中包含销售人员的名字 (`name`)、所属区域(`region`)以及销售额(`amount`)三个字段。现在想要了解各地区内不同人员的具体表现情况。 ##### 方法一:利用 `GROUP BY` ```sql SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region; ``` 这段代码会给出各个地区的总体业绩概况,但丢失了个人层面的信息。 ##### 方法二:借助 `PARTITION BY` 为了既能看到整体也能关注个体贡献度,可以这样写: ```sql SELECT name, region, amount, AVG(amount) OVER (PARTITION BY region) as avg_regional_sale FROM sales; ``` 此语句不仅提供了每个人的销售金额,还额外计算出了他们各自所处区域内所有人的平均成绩作为参照标准[^2].
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Scc_hy

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值