-partition by 和 group by 的联系与区别
- partition by 和 group by 均可实现分组功能
- partition by 返回分组结果中的每个值,而group by常返回特定聚合函数的一个结果
- partition by 常与 row_number() 结合使用
-partition by 和 group by 的联系与区别示例理解
- 创建表testtable,并创建三个字段之后插入三行值,结果如下:
select *
from testtable as t;
-实现group by分组查询 和 partition by 分区查询
select t.id,t.nm,sum(t.cnt) as NMSUM
from testtable as t
group by t.nm;
select t.id,t.nm,sum(t.cnt) over (partition by t.nm order by t.cnt ) as NMSUM
from testtable as t;
结果分别如下:
-partition by 和 row_number() 结合使用示例:
(关于row_number()的用法可参考笔记#数据库知识点-窗口函数 rank() dense_rank() row_number())
select t.id,t.nm,row_number() over (partition by t.nm order by t.cnt ) as NMSUM
from testtable as t;
结果如下: