refer: https://www.cnblogs.com/by-dream/p/9403984.html
Out1 = SELECT QueryId, DocId, Rating, ROW_NUMBER() OVER (PARTITION BY QueryId ORDER BY Score DESC) AS ScoreRankPosition, ROW_NUMBER() OVER (PARTITION BY QueryId ORDER BY Rating DESC) AS RatingRankPosition FROM In1; Out2 = SELECT * FROM Out1;
T1 = SELECT QueryId, DocId, Util.CaculateDCG(ScoreRankPosition, Rating) AS DCG FROM In1 WHERE Convert.ToDouble(ScoreRankPosition) <=1; T2 = SELECT QueryId, DocId, Util.CaculateDCG(RatingRankPosition, Rating) AS IDCG FROM In1 WHERE Convert.ToDouble(RatingRankPosition) <=1; T3 = SELECT QueryId, SUM(DCG) AS DCG GROUP BY QueryId FROM T1; T4 = SELECT QueryId, SUM(IDCG) AS IDCG GROUP BY QueryId FROM T2; T5 = SELECT T4.QueryId, Util.CaculateNDCG(T3.DCG, T4.IDCG) AS NDCG FROM T4 INNER JOIN T3 ON T3.QueryId == T4.QueryId;Out1 = SELECT AVG(NDCG) AS NDCG FROM T5; Out2 = SELECT * FROM Out1;