Query for Min, Max, Avg, and Last Value in TSQL

问题:

I am trying to find Max, min, avg and last value of a column in single query. Platform: SQL Server 2012

Sample Table:

SN  Month  Acc  Bal
------------------------
1     7    101   1,000/-
2     7    101   1,500/-
3     7    101   1,700/-
4     8    101   1,200/-
5     8    101   900/-
6     9    101   2,500/-

Query I wrote:

select 
    [Month], [Acc],
    min(Bal) as MinBal,
    avg(Bal) as AvgBal,
    max(Bal) as MaxBal
    --, ??? for as LastBal
from 
    MyTable
Group By 
    [Month], [Acc]

Query with Last_Value returns all records instead of aggregated records

select 
    [Month], [Acc],
    min(Bal) as MinBal,
    avg(Bal) as AvgBal,
    max(Bal) as MaxBal,
    LAST_VALUE(Bal) OVER (partition by [Acc] order by [Month]) as LastBal
from 
    MyTable
Group By 
    [Month], [Acc], Bal

Also including last_value(bal) is generating an error with bal required on group by list

Column 'Bal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

回答1:

Please try this solution-

DATA Generation

CREATE TABLE Alls
(
     SN INT
    ,[Month] INT
    ,Acc INT
    ,Bal INT
)
GO

INSERT INTO Alls VALUES
(1,  7,    101,   1000),
(2,  7,    101,   1500),
(3,  7,    101,   1700),
(4,  8,    101,   1200),
(5,  8,    101,   900),
(6,  9,    101,   2500)
GO

SOLUTION

SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM Alls
ORDER By SN

OUTPUT

sn          Acc         Month       Bal         MinBal      AvgBal           MaxBal      lastVal
----------- ----------- ----------- ----------- ----------- ---------------- ----------- -----------
1           101         7           1000        1000        1400.000000      1700        1700
2           101         7           1500        1000        1400.000000      1700        1700
3           101         7           1700        1000        1400.000000      1700        1700
4           101         8           1200        900         1050.000000      1200        900
5           101         8           900         900         1050.000000      1200        900
6           101         9           2500        2500        2500.000000      2500        2500

(6 rows affected)

IF you only need acc,month and other aggregate columns then use below-

SOLUTION

SELECT Acc,[Month],MAX(MinBal)MinBal,MAX(AvgBal)AvgBal,MAX(MaxBal)MaxBal,MAX(lastVal)lastVal
FROM
(
    SELECT sn,Acc,[Month] ,Bal
    , MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
    , AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
    , MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
    , FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
    FROM Alls
)u GROUP BY Acc,[Month]

OUTPUT

Acc         Month       MinBal      AvgBal           MaxBal      lastVal
----------- ----------- ----------- ---------------- ----------- -----------
101         7           1000        1400.000000      1700        1700
101         8           900         1050.000000      1200        900
101         9           2500        2500.000000      2500        2500

(3 rows affected)

回答2:

select * 
from 
( SELECT sn, Acc, [Month], Bal
       , MIN(Bal) OVER(PARTITION BY Acc, [Month]) MinBal
       , AVG(Bal) OVER(PARTITION BY Acc, [Month]) AvgBal
       , MAX(Bal) OVER(PARTITION BY Acc, [Month]) MaxBal
       , row_number() OVER(PARTITION BY Acc, [Month] ORDER BY SN DESC) as rn 
) tt
where rn = 1
ORDER By sn

回答3:

You can achieve as below:

select 
 tt.Month
, tt.Acc
, min(Bal) as MinBal
, avg(Bal) as AvgBal 
, max(Bal) as MaxBal
, latest.balance
FROM #tbl1 as tt 
JOIN (
  SELECT 
  id
  ,month
  ,acc
  ,bal as balance
FROM #tbl1 AS t1
WHERE id = (SELECT MAX(id)
                 FROM #tbl1 AS t2 
                 WHERE t1.month = t2.month 
                   AND t1.acc = t2.acc 
                 GROUP BY month, acc)
) as latest
on tt.month = latest.month 
                   AND tt.acc = latest.acc 
Group By tt.Month, tt.Acc, latest.balance


DROP TABLE #tbl1