如何出取分布最均的一组数据?

[giftzheng]

在四个位置
随机分布1-10的数据
如
1,6,8,9
1,2,3,4
1,3,5,7
5,6,7,8
...
如何出取分布最均的一组数据
1,3,5,7

没sql 讲下思路也行

[dawugui]

1,6,8,9 后一个减去前一个的数为2,2,2,然后对这三个数求方差,越小越好?我试试看.

[dawugui]

--有三行数据方差为0,此法不可用.
create table tb(val1 int,val2 int,val3 int,val4 int)
insert into tb values(1,6,8,9) 
insert into tb values(1,2,3,4) 
insert into tb values(1,3,5,7) 
insert into tb values(5,6,7,8) 
go

select * , v1 = val2-val1 , v2 = val3-val2 , v3 = val4-val3 from tb
drop table tb

/*
val1        val2        val3        val4        v1          v2          v3          
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           6           8           9           5           2           1
1           2           3           4           1           1           1
1           3           5           7           2           2           2
5           6           7           8           1           1           1

（所影响的行数为 4 行）
*/

[dawugui]

如果再判断有两数小于等于5,有两数大于5,这样可以吧?

[dawugui]

create table tb(val1 int,val2 int,val3 int,val4 int)
insert into tb values(1,6,8,9) 
insert into tb values(1,2,3,4) 
insert into tb values(1,3,5,7) 
insert into tb values(5,6,7,8) 
go

select * from tb where (val2-val1)*(val2-val1) + (val3-val2)*(val3-val2) + (val4-val3)*(val4-val3) in
(
  select min(v1) from
  (
    select * , 
      v1 = (val2-val1)*(val2-val1) + (val3-val2)*(val3-val2) + (val4-val3)*(val4-val3),
      v2 = case when val1 < 5 then 1 else 0 end + case when val2 < 5 then 1 else 0 end + case when val3 < 5 then 1 else 0 end +case when val4 < 5 then 1 else 0 end from tb
  ) t
  where v2 = 2
) 
and case when val1 < 5 then 1 else 0 end + case when val2 < 5 then 1 else 0 end + case when val3 < 5 then 1 else 0 end +case when val4 < 5 then 1 else 0 end = 2

drop table tb

/*
val1        val2        val3        val4        
----------- ----------- ----------- ----------- 
1           3           5           7

（所影响的行数为 1 行）
*/