假设您有足够的行,则可以使用窗口函数。使用此技巧均匀分布10%的样本:
select t.* from (select t.*, row_number() over (partition by account order by seqnum_within_account) as seqnum from (select t.*, row_number() over (partition by account, dept order by (select null)) as seqnum_within_account, count(*) over (partition by account) as cnt from t ) t ) t where seqnum * 10 <= cnt;
这是做什么的?最里面的 select 为每个帐户中的每个部门分配一个序号。接下来是在每个帐户中分配序列号,因此它在各部门之间均匀分布。因此,如果有n个部门,前n个值来自不同的部门,下一个n等等(假设有足够的行)。
select
决赛 where 需要10%的样本。
where
注意:您可以添加 where account = ??? 如果您只想为一个帐户提供信息,请访问最里面的子查询。
where account = ???
使用Row_Number()窗口函数
;with cte as ( select * , ROW_NUMBER() over (partition by dept order by AccountNo) AS Rn , count(AccountNo) over (partition by dept order by dept) AS Cnt from #tt ) select * from cte where Rn<=(1.0*Cnt)/10