with norms as ( select v, sum(w_td * w_td) as w2 from data group by v)select x.v as ego,y.v as v,nx.w2 as x2, ny.w2 as y2, sum(x.w_td * y.w_td) as innerproduct, sum(x.w_td * y.w_td) / sqrt(nx.w2 * ny.w2) as cosinesimilarityfrom data as xjoin data as y on (x.base=y.base)join norms as nx on (nx.v=x.v)join norms as ny on (ny.v=y.v)where x.v < y.vgroup by 1,2,3,4order by 6 desc
产量
ego|v |x2 |y2 |innerproduct|cosinesimilarity--------------------------------------------------99 |1234|41.0|38.0|23.0 |0.5826987807288609
