[[TableOfContents]

貉るる一 螳 [http]讌覓(http://www.sqler.com/bSQLQA/842640) 殊.
覩語 蟆 螳 企瓦.

覓語 #

る覯    蠏蟆    る蠍語   襷り    
E	        1	    50	        7	
D	        3	    80	        6	
B	        1	    30	        1	
F	        3	    100	        15	
A	        2	    100	        10	
C	        1	    40	        4	

 襭襯 螳讌螻 (蠍碁,螳螳谿....)襯 覦一 り .

覦一 豺 

豌覯讌, 覲襦 る 蠍語伎  觜訣襦  伎 覃,
覯讌, 企襯 覿襯 覲襦 觜訣蟆 覦磯伎 .

企 る 企
1. 蠏蟆(1~3 / 伎襦  企れ)
2. る蠍語/襷り = 蠍語 ( 蠍語願 讌ъ襦  企れ)

 螳讌襯 螻ろ.

蠏蟆 磯ジ  企 1~3蟾讌 伎 螻,   蠍語企 る蠍語伎 襷り襦 伎.

る 襦 讌 覈襯願れ..

覓朱 豕譬 狩 覿覿 襦蠏碁朱 蟲 蟆讌襷

豕 蠍磯蓋 貎朱Μ螳 碁 螳 讌 れ 
螻覿れ 譟一語 蟲 覺.

#

豕 覓語. 螳 伎 覡伎 覃 所 豕 .
--一危 襷り鍵
;with temp(る覯, 蠏蟆, る蠍語, 襷り)
as
(
select 'A', 2, 100, 10 union all
select 'B', 1, 30 , 1  union all
select 'C', 1, 40 , 4  union all
select 'D', 3, 80 , 6  union all
select 'E', 1, 50 , 7  union all
select 'F', 3, 100, 15
)
select * into #temp
from temp

select N'蠍碁' name into #worker union all
select N'螳螳谿' union all
select N'伎' 



--simulation
--similarity, euclidean distance
set nocount on
if object_id('tempdb.dbo.#result') is not null
        drop table #result

create table #result(
    る覯 nvarchar(200)
,   蠏蟆 int
,   る蠍語 int
,   襷り int
,    nvarchar(200)
)

declare 
    @i int
,   @dist float
,   @min_dist float
set @i = 1

while(@i <= 100)
begin
    if object_id('tempdb.dbo.#rand') is not null
        drop table #rand

    select
        a.る覯
    ,   a.蠏蟆
    ,   a.る蠍語
    ,   a.襷り
    ,   b.
    ,   b.num
    into #rand
    from (select *, row_number() over(order by newid()) - 1 num from #temp) a
        inner join (    
            select 
                name 
            ,   row_number() over(order by newid()) - 1 num
            ,   count(*) over() cnt
            from #worker 
        ) b
            on a.num % b.cnt = b.num

    declare
        @v1 float
    ,   @v2 float
    ,   @v3 float

    select 
        @v1 = sum(蠏蟆)
    ,   @v2 = sum(る蠍語)
    ,   @v3 = sum(襷り)
    from #rand
    where num = 0

    select @dist = sqrt(sum(n)) 
    from (
        select
            power(@v1 - sum(蠏蟆), 2) + power(@v2 - sum(る蠍語), 2) + power(@v3 - sum(襷り), 2) n
        from #rand
        where num <> 0
        group by
            num
    ) t

    if @i = 1 
    begin
        insert #result
        select
            る覯
        ,   蠏蟆
        ,   る蠍語
        ,   襷り
        ,   
        from #rand
        set @min_dist = @dist
    end else
    begin
        if @min_dist > @dist
        begin
            truncate table #result
            insert #result
            select
                る覯
            ,   蠏蟆
            ,   る蠍語
            ,   襷り
            ,   
            from #rand

            set @min_dist = @dist
        end
    end
    --print @i
    set @i = @i + 1
end

print @min_dist
select 
    る覯
,   蠏蟆
,   る蠍語 * 1.0 / 襷り [る蠍語/襷り]
,       
from #result
order by 

蟆郁骸
る覯    蠏蟆    る蠍語/襷り   
E	        1	    7.142857142857	    螳螳谿
D	        3	    13.333333333333	    螳螳谿
A	        2	    10.000000000000	    伎
C	        1	    10.000000000000	    伎
B	        1	    30.000000000000	    蠍碁
F	        3	    6.666666666666	    蠍碁