在SQL Server中，如果两个记录在组中具有相同的排名，如何对每个组中的记录进行排名，并检查其他变量以分配排名？

Question

我有一个数据集，它基本上告诉你每个零售商发送了多少优惠券，有多少人回复，每个经销商和每个优惠券的相应回复率。

我希望在每个经销商的响应率排名每个优惠券，如果两个优惠券有相同的响应率，那么我需要分配更好的排名，以优惠券是发送最多

这是我尝试过的脚本，但它的排名不正确

SELECT 
    DealerCode, Coupon_name, emailsent, responders, responserate,
    RN = RANK() OVER (PARTITION BY DealerCode, Coupon_name, responserate
                      ORDER BY DealerCode, responserate, emailsent)
FROM 
    table123 

这是意料之中的结果

RetailerCode    Coupon_name emailsent   responders  responserate    RN
----------------------------------------------------------------------
A1                Coupon 1     6              1     0.166666667      1
A1                Coupon 2     10             1     0.1              2
A1                Coupon 7     50             2     0.04             3
A1                Coupon 9     25             1     0.04             4
A2                Coupon 1     28             3     0.10714          2
A2                Coupon 4     12             0     0                3
A2                Coupon 3     1217           131   0.1076           1
A3                Coupon 2     63             10    0.1587           1
A3                Coupon 6     9              1     0.11111          2
A3                Coupon 7     3              0     0                3
A3                Coupon 8     2              0     0                4
A4                Coupon 4     174            22    0.1266782        3
A4                Coupon 3     1118           244   0.2182869        1
A4                Coupon 6     3091           420   0.135877         2
A5                Coupon 3     1227           78    0.06356962       2
A5                Coupon 2     780            50    0.064104         1
A5                Coupon 1     164            6     0.0365866        3

Answer 1

查看您的示例，您应该只使用DealerCode分区

  SELECT DealerCode, Coupon_name, emailsent, responders, responserate,
   RN = RANK()OVER(PARTITION BY DealerCode
   ORDER BY DealerCode,responserate,emailsent)
  FROM table123 

Answer 2

听起来像是想要PARTITION BY dealercode和coupon_name，ORDER BY responserate和emailsent (假设这包含发送优惠券的数字)，两者都是递减的。

SELECT dealercode,
       coupon_name,
       emailsent,
       responders,
       responserate,
       rn = rank() OVER (PARTITION BY dealercode,
                                      coupon_name
                         ORDER BY responserate DESC,
                                  emailsent DESC)
       FROM table123;

Answer 3

使用row_number

SELECT dealercode,
       coupon_name,
       emailsent,
       responders,
       responserate,
       row_number() OVER (PARTITION BY dealercode,
                                      coupon_name
                         ORDER BY responserate DESC,
                                    emailsent desc
                                  ) RN
       FROM table123;