在分区上找到共同的价值

Jordan1200 发表于 Dev

乔丹1200

我有这样一张桌子：

Loan_Num asset LTV
1         20    0.2
2         20    0.2
3         20    0.12 
4         20    0.2
5         10    0.3
6         10    0.3
7         10    0.22 
8         10    0.3

我想按资产组在此表中添加一个公用值。

Loan_Num asset LTV   cV
1         20    0.2  0.2
2         20    0.2  0.2
3         20    0.12 0.2
4         20    0.2  0.2
5         10    0.3  0.3
6         10    0.3  0.3
7         10    0.22 0.3
8         10    0.3  0.3

有什么建议怎么做？有一个通用功能的内置功能吗？

马丁·史密斯

一种方法是

WITH CTE1
     AS (SELECT *,
                COUNT(*) OVER (PARTITION BY [asset], [LTV]) AS C
         FROM   YourTable),
     CTE2
     AS (SELECT *,
                RANK() OVER (PARTITION BY [asset] ORDER BY C DESC, [LTV] DESC) AS R
         FROM   CTE1)
SELECT [Loan_Num],
       [asset],
       [LTV],
       MAX(CASE
             WHEN R = 1
               THEN [LTV]
           END) OVER (PARTITION BY [asset]) AS cV
FROM   CTE2

演示版

尽管实际上这会稍微有点效率，因为它消除了排序

WITH CTE1
     AS (SELECT *,
                COUNT(*) OVER (PARTITION BY [asset], [LTV]) AS C
         FROM   YourTable),
     CTE2
     AS (SELECT *,
                MAX(C) OVER (PARTITION BY [asset]) AS MaxC
         FROM   CTE1)
SELECT [Loan_Num],
       [asset],
       [LTV],
       MAX(CASE
             WHEN C = MaxC
               THEN [LTV]
           END) OVER (PARTITION BY [asset]) AS cV
FROM   CTE2