sql 分组取每组的前n条或每组的n%(百分之n)的数据
sql keyword:
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