sql 刷题
1、抽样取数
表结构为user_id,reg_time,age, 写一句sql按user_id随机抽样2000个用户 写一句sql取出按各年龄段(每10岁一个分段,如(0,10))分别抽样1%的用户
1)随机抽样2000个用户
select * from table order by rand() limit 2000
2)取出各年龄段抽样1%的用户
set @target = 0
set @count_user = 0
select @target:=@target+10 as age_right, *
from table as t1
where t1.age >=@target-10 and t1.age < (@target)
and t1.id in
(select floor(count(*)*0.1) from table as t2
where t1.age >=@target-10 and t1.age < (@target)
order by rand() limit ??)
(mysql下按百分比取数没有想到比较好的方法,因为limit后面不能接变量。想到的方法是先计算出每个年龄段的总数,然后计算出1%是多少,接着给每一行加一个递增+1的行标,当行标=1%时,结束)