在TSQL中查询最小值，最大值，平均值和最后一个值 [英] Query for Min, Max, Avg, and Last Value in TSQL

问题描述

我正在尝试在单个查询中查找列的最大，最小，平均和最后一个值。
平台：SQL Server 2012

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

示例表：

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/-

我写的查询：

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

使用 Last_Value 的查询返回所有记录，而不是汇总记录

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

还包括 last_value（bal）生成一个错误，要求按列表进行分组

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

选择列表中的 Bal列无效，因为其中不包含

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

推荐答案

请尝试以下解决方案-

数据生成

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

解决方案

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

输出

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)

如果只需要acc，month和其他汇总列，则在下面使用-

解决方案

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]

输出

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)

这篇关于在TSQL中查询最小值，最大值，平均值和最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！