SQL查询来查找组by子句中所有行的总和和行的部分总和 [英] SQL query to find the sum of all rows and sum of portions of rows within a group by clause

问题描述

我试图为SQL Server 2008数据库构建查询。该查询将对SID和日期列进行分组并累计Profit列。该查询还需要查找具有相同单位的所有行在一天结束时的利润总和。在这种情况下，单位值1在12:54 PM从-1切换到1并保持不变，直到当天下午1:15的最后一个记录为止。

 
  
 SID日期利润单位RowID 
 --------- ---------------------------------------------- 
 1 7/26/10 1:15 PM -25 1 1 
 1 7/26/10 12:54 PM -12.5 1 2 
 1 7/26/10 12:54 PM 0 0 3 
 1 7/26/10 12:54 PM -125 -1 4 
 1 7/26/10 12:00 PM 100 -1 5 
 1 7/26/10 10:31 AM -50 -1 6 
  
 

具有0个单位的行表示切换发生。第三行的单位不总是变为0。例如，

 
  
 SID日期利润单位
 ------- ------------------------------------------------- 
 1 7/26/10 2:15 PM -37.5 -1 
 1 7/26/10 2:06 PM -125 -1 
 1 7/26/10 2:00 PM  - 12.5 -1 
 1 7/26/10 2:00 PM 0 0 
 1 7/26/10 2:00 PM -75 1 
 1 7/26/10 12:45 PM -12.5 1 
 1 7/26/10 12:45 PM 0 0 
 1 7/26/10 12:45 PM -125 -1 
 1 7/26/10 12:00 PM 100 -1 
 1 7/26/10 9:55 AM -50 -1 
  
 

第一个数据集的输出如下所示：

 
  
 SID日期TotalProfit ProfitforEndofDayUnits 
 -------------------------------------------- ----------- 
 1 7/26/10 -112.5 -37.5 
  
 

谢谢。

解决方案

  declare @T table（
 [SID] int，
 [Date] datetime，
利润费用，
单位int）
 
插入@T值
（1，'7/26/10 2:15 PM'，-37.5，-1），
（1，'7/26/10 2:06 PM '，-125，-1），
（1，'7/26/10 2:00 PM'，-12.5，-1），
（1，'7/26/10 2： （1，'7/26/10 2:00 PM'，-75,1），
（1，'7/26/10 12:45），
 PM'，-12.5,1），
（1，'7/26/10 12:45 PM'，0,0），
（1，'7/26/10 12:45 PM '，-125，-1），
（1，'7/26/10 12:00 PM'，100，-1），
（1，'7/26/10 9:55 AM'，-50，-1），
（2，'7/26/10 1:15 PM'， -  25,1），
（2，'7/26/10 12： （2，'7/26/10 12:54 PM'，0,0），
（2，'7/26/10 12:54 PM'，-125，-1），
（2，'7/26/10 12:00 PM'，100，-1），
（2，'7/26 / 10 10:31 AM'，-50，-1）
 
 
; cte为
（
选择
 [SID]，
 cast（[Date] as Date）as [Date]，
 Profit，
单位，
 row_number（）over（由SID分区，cast（[Date]为Date）
 order by [Date] desc）从@T 
）
选择
 C1。[SID]，
 C1。[Date]，
总和（C1.Profit）为TotalProfit，
 sum（C2.Units = C1.Units和
 C1.rn时的情况） S.rn 
然后C1.Profit 
 else 0.0 
 end）ProfitforEndofDayUnits 
从cte作为C1 
内部连接cte作为C2 
在C1上[SID ] = C2。[SID]和
 C1。[Date] = C2。[Date]和
 C2.rn = 1 
 cross apply 
（select min（C3.rn ）作为cb的
作为C3 
其中C1。[SID] = C3。[SID]和
 C1。[Date] = C3。[Date]和
 C3。单位<> C2.Units）as S 
 group by C1。[SID]，C1。[Date] 
  

结果：

pre $ $ c $ s SID日期TotalProfit ProfitforEndofDayUnits
--------- - ---------- --------------------- ----------------- ------
1 2010-07-26 -337,50 -175.0000
2 2010-07-26 -112,50 -37.5000

I am trying to build a query for a SQL Server 2008 database. The query would group the SID and Date column and sum the Profit column. The query also needs to find the sum of Profit for all rows having the same units toward the end of a day. in this case, the units value of 1 switched from -1 at 12:54PM to 1 and remained the same until the last record for that day which is at 1:15PM.

SID Date               Profit    Units   RowID
-------------------------------------------------------
1   7/26/10 1:15 PM     -25          1        1
1   7/26/10 12:54 PM    -12.5        1        2
1   7/26/10 12:54 PM    0            0        3
1   7/26/10 12:54 PM    -125         -1       4
1   7/26/10 12:00 PM    100          -1       5
1   7/26/10 10:31 AM    -50          -1       6

The row with 0 units indicates that the switch happened. The Units does not always change to 0 on the third row. For example,

SID Date             Profit     Units
--------------------------------------------------------
1   7/26/10 2:15 PM    -37.5        -1
1   7/26/10 2:06 PM    -125         -1
1   7/26/10 2:00 PM    -12.5        -1
1   7/26/10 2:00 PM    0             0
1   7/26/10 2:00 PM    -75           1
1   7/26/10 12:45 PM   -12.5         1
1   7/26/10 12:45 PM    0            0
1   7/26/10 12:45 PM   -125         -1
1   7/26/10 12:00 PM    100         -1
1   7/26/10 9:55 AM     -50         -1

The output from the first data set would look like:

SID      Date     TotalProfit    ProfitforEndofDayUnits 
-------------------------------------------------------
1        7/26/10      -112.5                  -37.5

Thanks.

解决方案

declare @T table( 
  [SID] int,
  [Date] datetime,
  Profit money,
  Units int)

insert into @T values
(1,   '7/26/10 2:15 PM',    -37.5,        -1),
(1,   '7/26/10 2:06 PM',    -125,         -1),
(1,   '7/26/10 2:00 PM',    -12.5,        -1),
(1,   '7/26/10 2:00 PM',    0,             0),
(1,   '7/26/10 2:00 PM',    -75,           1),
(1,   '7/26/10 12:45 PM',   -12.5,         1),
(1,   '7/26/10 12:45 PM',    0,            0),
(1,   '7/26/10 12:45 PM',   -125,         -1),
(1,   '7/26/10 12:00 PM',    100,         -1),
(1,   '7/26/10 9:55 AM',     -50,         -1),
(2,   '7/26/10 1:15 PM',     -25,          1),
(2,   '7/26/10 12:54 PM',    -12.5,        1),
(2,   '7/26/10 12:54 PM',    0,            0),
(2,   '7/26/10 12:54 PM',    -125,         -1),
(2,   '7/26/10 12:00 PM',    100,          -1),
(2,   '7/26/10 10:31 AM',    -50,          -1)


;with cte as
(
  select
    [SID],
    cast([Date] as Date) as [Date],
    Profit,
    Units,
    row_number() over(partition by SID, cast([Date] as Date) 
                      order by [Date] desc) as rn
  from @T
)
select 
  C1.[SID],
  C1.[Date],
  sum(C1.Profit) as TotalProfit,
  sum(case when C2.Units = C1.Units and
                C1.rn < S.rn 
        then C1.Profit
        else 0.0
      end) ProfitforEndofDayUnits
from cte as C1
  inner join cte as C2
    on C1.[SID] = C2.[SID] and
       C1.[Date] = C2.[Date] and
       C2.rn = 1
  cross apply
    (select min(C3.rn) as rn
     from cte as C3
     where C1.[SID] = C3.[SID] and
           C1.[Date] = C3.[Date] and
           C3.Units <> C2.Units) as S      
group by C1.[SID], C1.[Date]

Result:

SID         Date       TotalProfit           ProfitforEndofDayUnits
----------- ---------- --------------------- -----------------------
1           2010-07-26 -337,50               -175.0000
2           2010-07-26 -112,50               -37.5000

这篇关于SQL查询来查找组by子句中所有行的总和和行的部分总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！