公共表表达式内外的SQL分区数据 [英] SQL Partition data within and outside of a Common Table Expression

问题描述

我正在使用SQL Server 2012，我具有以下示例结果集。带有Tot前缀的列是从左侧的列派生的。

I am using SQL Server 2012 I have the following sample result set. The columns with the Tot Prefix are derived from the columns to the left.

我想做的是给定日期和给定AssetStyle的数据分区。基于此标准，我要对利润和ACB求和。然后，我想将这两个总和的结果除以总的返回数。示例数据如下。

What I am trying to do is partition data for a given date, and a given AssetStyle. Based on this criteria I want to sum profit and sum ACB. I then want to divide the results of these two sums for a total return number. The sample data is below.

ValuationDate   CustodianaccountNum AssetStyle  AssetClass  Profit  ACB NetReturn   TotProfit   TotACB  TotReturn
3/31/2007   1111    All Cap US All Cap  2552.55337  1069804.43  0.002386    719511.4487 7859959.87  0.091541364
3/31/2007   2222    All Cap US All Cap  1085.121285 1547961.89  0.000701    719511.4487 7859959.87  0.091541364
3/31/2007   3333    All Cap US All Cap  715873.774  3298000 0.217063    719511.4487 7859959.87  0.091541364
3/31/2007   4444    All Cap US All Cap  0   1944193.55  0   719511.4487 7859959.87  0.091541364
4/30/2007   1111    All Cap US All Cap  1374.387675 554859.78   0.002477    404343.9809 5803741.618 0.069669535
4/30/2007   2222    All Cap US All Cap  8041.548001 1038959.69  0.00774 404343.9809 5803741.618 0.069669535
4/30/2007   3333    All Cap US All Cap  -714.739726 554922.1475 -0.001288   404343.9809 5803741.618 0.069669535
4/30/2007   4444    All Cap US All Cap  395642.785  3655000 0.108247    404343.9809 5803741.618 0.069669535
3/31/2008   1111    Alt. Investment Strategies  Alternative Investments -6701.16054 1445773.579 -0.004635   -46461.95138    2709953.979 -0.017144923
3/31/2008   2222    Alt. Investment Strategies  Alternative Investments -2859.33957 37854.99999 -0.075534   -46461.95138    2709953.979 -0.017144923
3/31/2008   3333    Alt. Investment Strategies  Alternative Investments 0   247481.1    0   -46461.95138    2709953.979 -0.017144923
3/31/2008   4444    Alt. Investment Strategies  Alternative Investments -36901.45127    978844.3    -0.037699   -46461.95138    2709953.979 -0.017144923

我正在尝试获得此结果

WITH DATA AS
(
SELECT 
  ValuationDate, 
  CustodianaccountNum,
  AssetStyle, 
  AssetClass, 
  Profit, 
  ACB, 
  NetReturn, 
  SUM(Profit) OVER  (PARTITION by  AssetStyle, ValuationDate) AS TotProfit,
  SUM(ACB) OVER  (PARTITION by  AssetStyle, ValuationDate) AS TotACB
FROM 
  [PWM_Performance].[dbo].[BucketPerformance2]
)
SELECT 
  *, 
  SUM(TotProfit/TotACB) OVER  (PARTITION by  AssetStyle, ValuationDate) AS TotReturn
FROM data
  WHERE Totacb > 1
  ORDER BY AssetStyle, ValuationDate

我正在获得TotProfit和totACB的预期结果，但是我没有为TotReturn获得正确的结果。示例数据显示了正确的结果，但是我得到的值不是我在分区语句中指示的数据子集的总和。

I am getting the expected result for TotProfit and totACB, but I am not getting the correct result for TotReturn. The sample data shows the correct result but I am getting different values that aren't the sum of the data subsets I have indicated in my partition statement.

我正在使用CTE，因为我认为我无法从前面的两个利润和ACB的总和中得出计算结果。

I am using a CTE because I don't think I can derive a calculation from the two preceding derived sums of profit and ACB.

推荐答案

WITH DATA AS
(
SELECT 
  ValuationDate, 
  CustodianaccountNum,
  AssetStyle, 
  AssetClass, 
  Profit, 
  ACB, 
  NetReturn, 
  SUM(Profit) OVER (PARTITION BY  AssetStyle, ValuationDate) AS TotProfit,
  SUM(ACB) OVER (PARTITION BY  AssetStyle, ValuationDate) AS TotACB
FROM PWM_Performance.dbo.BucketPerformance2
)
SELECT 
  *, 
  TotProfit / TotACB AS TotReturn
FROM data
WHERE TotACB > 1
ORDER BY AssetStyle, ValuationDate

您已经掌握了正确的分子和分母。样式和日期分区分别具有四行，每行均具有相同的值。因此，当您第二次对相同的分区求和时，它只是一遍又一遍地添加相同的比率：除法是完成计算所需的全部。而且，如果您没有对 TotACB 进行过滤，则可以取消CTE。

You already had the right numerator and denominator in hand. The partitions in style and date each have four rows all having the same values. So when you summed over identical partitions a second time it was simply adding the same ratio over and over: division was all you needed in order to finish the calculation. And had you not been filtering on TotACB you could have eliminated the CTE.

另一个答案在以下方面起作用代数，但它正在做额外的工作。作为示例，它不仅要直接吐出1/2，还必须算出（1 + 1 + 1 + 1）/（2 + 2 + 2 + 2）。性能可能最终成为一个因素，考虑到浮点的性质，结果中肯定会有差异的可能。

The other answer works in terms of the algebra but it's doing extra work. Instead of just directly spitting out 1/2, as an example, it has to work out (1+1+1+1) / (2+2+2+2). Performance might eventually be a factor and there is definitely some potential for differences in the result given the nature of floating point.

这篇关于公共表表达式内外的SQL分区数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！