SQLPARTITIONBY用法小结

SQLPARTITIONBY用法小结需要先看清适用场景和关键步骤，避免只记结论却忽略实际限制。

一、前言

关于同表分组，今天发现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 COUNT3FROM TableA A

使用PARTITION BY：

SELECT *, count(1) OVER  (PARTITION BY COL1) AS COUNT1count(1) OVER  (PARTITION BY COL1,COL2)  AS COUNT2count(1) OVER  (PARTITION BY COL1,COL2,COL3)  AS COUNT3FROM TableA A

代码一下清爽了。

三、与group by 对比

实现计数

SELECT *, count(1) OVER  (PARTITION BY COL1) AS COUNT1FROM TableA A

相当于

SELECT *, (SELECT COUNT(1) TableA B  where A.COL1=B.COL1 group by  COL1) AS COUNT1FROM 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 ratioFROM 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 作为 总条数的取值。