根据SQL Server中其他列的值在列中进行除法

Question

在解决这个问题上有些困难。

store key  promotion key  sales year  average weekly sales          *Desired Output Column*
1               1           2007             100                        1 (100 / by itself)
1               1           2008             120                        1 (120 / by itself)
1               2           2008             240                        2 (240 / 120)
1               3           2007              50                        0.5 (50 / 100)
1               4           2007              70                        0.7 (70 / 100)
1               5           2007              80                        0.8 (80 / 100)
1               3           2008              180                       1.5 (180 / 120)
2               1           2009              150                       1 (150 / by itself)
2               2           2009              200                       1.3 (200 / 150)
2               3           2007              300                       Null (no baseline value in 2007)

每个门店关键字和销售年份都有一个由促销关键字1指示的基准值。所有其他促销关键字都反映了我希望划分为相同门店关键字和销售年度的基准值的条目。

因此，在上面的示例中，我希望所有具有商店关键字1和2007年的条目除以该年和商店的基准值(即，促销关键字1对应于同一商店关键字1和销售年度2007)。类似地，具有商店关键字1和2008年的所有条目除以该年的基准值(商店1和销售年度2008的促销关键字1)。同样，对于商店2，我希望促销密钥2的商店密钥2和年份2009除以基准值(促销密钥1、商店密钥2和年份2009)。由于2007年存储密钥2没有基准值(升级密钥1)，因此该条目将生成null。

Answer 1

我认为你想要：

select t.*,
       (avgweeklysales  * 1.0 /
        max(case when promotionkey = 1 then avgweeklysales end) over (partition by storekey, salesyear)
       ) as desired_output
from t;

Answer 2

这与戈登的想法非常相似，但我使用的是FIRST_VALUE

WITH YourTable AS(
    SELECT V.StoreKey,
           V.PromotionKey,
           V.SalesYear,
           V.AvgSales
    FROM (VALUES(1,1,2007,100),
                (1,1,2008,120),
                (1,2,2008,240),
                (1,3,2007,50),
                (1,4,2007,70),
                (1,5,2007,80),
                (1,3,2008,180),
                (2,1,2009,150),
                (2,2,2009,200),
                (2,3,2007,300))V(StoreKey,PromotionKey,SalesYear,AvgSales))
SELECT YT.StoreKey,
       YT.PromotionKey,
       YT.SalesYear,
       YT.AvgSales,
       (YT.AvgSales * 1.) / FIRST_VALUE(CASE YT.PromotionKey WHEN 1 THEN YT.AvgSales END) OVER (PARTITION BY YT.StoreKey, YT.SalesYear ORDER BY YT.PromotionKey) AS DesiredColumn
FROM YourTable YT
ORDER BY YT.StoreKey,
         YT.PromotionKey,
         YT.SalesYear;