sql 分组取每组的前n条或每组的n%（百分之n)的数据

sql 关键字：

SELECT * ,ROW_NUMBER() OVER(partition by b.UserID order by b.UserID ) rn from b

执行结果：按UserID分组查询出每组条数，rn从1递增

UserID | rn

1 1

1 2

1 3

2 1

2 2

按要求我们要查询分组后每个user 5%的数据

1.用户分组计算5%数量

with temp as

(

--当数量<1的时候就取一条，且四舍五入取整

SELECT b.UserID,cast(ROUND(CASE WHEN COUNT(1)*0.05<1 THEN 1 ELSE COUNT(1)*0.05 END,0) AS INT) SelCnt from b

)

2.关联临时表取数

SELECT * ,ROW_NUMBER() OVER(partition by b.UserID order by b.UserID ) rn from b

inner join temp on b.UserID=temp.UserID

where b.rn<=temp.SelCnt