SQL 常用数学函数和聚合函数

一、常用数学函数

求绝对值 ABS()
随机数rand()

select cast( floor(rand()*1000000) as int)  -- 结果为6位内整数
select cast( floor(rand()*1000000) as CHAR(6)) -- 结果转换为字符

四舍五入 ROUND()

向上取整 ceiling()

向下取整 floor()

Power()取数值表达式的幂值；例：power(5,2)返回25（5^2）

二、聚合函数

聚合函数：在实际应用中，聚合函数常和分组函数group by结合使用，用来查询.where 子句的作用对象一般只是行，用来作为过滤数据的条件。；
1、 select 语句的选择列表（子查询或外部查询）
2、having 子句；
3、compute 或 compute by 子句中等；

1、AVG() 平均值 默认忽略 NULL

select  AVG(cfildname)  from ctable                --忽略 NULL
select  AVG(isnull(cfildname,0))  from ctable   -- NULL 作零计算

2、SUM() 求和
3、Max() 最大值
4、Min() 最小值
5、Count( ) 计数

--计数两种用法，必需要包含一个参数：字段名或 *号
select Count(cfildname)  from ctable    --忽略字段 NULL 值
select Count(*)      from ctable         --对表中行数进行计数  包括 NULL 值的行

三、其他聚合函数

6、 count_big() 返回指定组中的项目数量。
7、 grouping() 产生一个附加的列。判断是否是合计行，

当用cube或rollup运算符添加行时，输出值为1；

当所添加的行不是由cube或rollup产生时，输出值为0.

8、binary_checksum() 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。
9、checksum_agg() 返回指定数据的校验值，空值被忽略。
判断列是否变动过。

select prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no

10、checksum() 返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。

11、stdev()返回给定表达式中所有值的统计标准偏差。

select stdev(prd_no) from sales

12、stdevp() 返回给定表达式中的所有值的填充统计标准偏差。

select stdevp(prd_no) from sales

13、 var() 返回给定表达式中所有值的统计方差。

14、 varp()返回给定表达式中所有值的填充的统计方差。

select varp(prd_no) from sales

例：对文本列求和，使用cast(）数据转换

select SUM(cast(changeData as numeric(12,0))) from testTable where departmentID='REACH & DEVELOPMENT';

例：
对行字段 通过 union all 转换为列，使用聚合函数求和、最大值、最小值、平均值

-- 临时表（ [_1]、[_2]、。。。[_5]）5个数值型字段
select SUM([_1]) as nSum ,MIN([_1]) as nMin,Max([_1]) as nMax,avg([_1]) as navg

from (
select _1 as _1

from dbo.##pivot_1370_5R10NYR40_01 
where iautoid = 100

union all
select _2 
from dbo.##pivot_1370_5R10NYR40_01 
where iautoid = 100

union all


select _3 
from dbo.##pivot_1370_5R10NYR40_01 
where iautoid = 100

union all 

select [_4]
from dbo.##pivot_1370_5R10NYR40_01 
where iautoid = 100

union all

select [_5] 
from dbo.##pivot_1370_5R10NYR40_01 
where iautoid = 100

) t1

--如果帐户固定为101,102
select 商品编号,
       sum(case 帐户 when 101 then 数量 else 0 end) [101],
       sum(case 帐户 when 102 then 数量 else 0 end) [102]
from tb 
group by 商品编号
 
--如果帐户固定不固定
declare @sql varchar(8000)
set @sql = 'select 商品编号 '
select @sql = @sql + ' , max(case 帐户 when ' + cast(帐户 as varchar) + ' then 数量 else 0 end) [' + cast(帐户 as varchar) + ']'
from (select distinct 帐户 from tb) as a
set @sql = @sql + ' from tb group by 商品编号'
exec(@sql)