SQL PARTITION BY用法小结_MsSqlserver

一、前言

关于同表分组，今天发现partition by 特好用，
相比于group by,贵在保留原表所有行+灵活计算

二、案例

对于同表tablea 的不同分组计数，如:

select *, 
(select count(1) tablea b  where a.col1=b.col1) as count1
(select count(1) tablea b  where a.col1=b.col1 and  a.col2=b.col2 ) as count2
(select count(1) tablea b  where a.col1=b.col1 and  a.col2=b.col2 and a.col3=b.col3) as count3
from tablea a

使用partition by：

select *, 
count(1) over  (partition by col1) as count1
count(1) over  (partition by col1,col2)  as count2
count(1) over  (partition by col1,col2,col3)  as count3
from tablea a

代码一下清爽了。

三、与group by 对比

实现计数

select *, 
count(1) over  (partition by col1) as count1
from tablea a

相当于

select *, 
(select count(1) tablea b  where a.col1=b.col1 group by  col1) as count1
from tablea a

四、妙用

4.1 接函数

select 
    *,
    -- 计算总销
    sum(amount) over (partition by col1 ) as total,
    -- 计算占比（保留2位小数）
    round(col3 / sum(col2 ) over (partition by col1 ) * 100, 2) as ratio
from tablea ;

4.2 行号

select 
    *,
   row_number() over (partition by col1 order by col2 desc) 
from tablea ;

多一个行号列 1、2、3、4、5

4.3 count 另类

count 会遍历所有数据，但是row_number()-1 只会取一条，
这未曾不是一种优化

select 
    *,
   row_number() over (partition by col1 order by col2 desc) -1  as count1 
from tablea ;
--备注
select 
    *,
   case when count1=0 then 1 else 2 end  as  '新产品'
from tablea ;

当然还可以 max(count1)-1 作为总条数的取值。

到此这篇关于sql partition by用法小结的文章就介绍到这了,更多相关sql partition by内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！