在这篇文章中，我们将为您详细介绍MSSqlServer基础学习系列———聚合函数的内容，并且讨论关于聚合函数sql语句的相关问题。此外，我们还会涉及一些关于$Django聚合函数、分组查询、F,Q查询

在这篇文章中，我们将为您详细介绍MSSql Server基础学习系列———聚合函数的内容，并且讨论关于聚合函数sql语句的相关问题。此外，我们还会涉及一些关于$Django 聚合函数、分组查询、F,Q查询、orm字段以及参数、15-MySQL-Ubuntu - 数据表的查询 - 聚合函数 (四)、ClickHouse 聚合函数、Django 第十课 5.【聚合函数】的知识，以帮助您更全面地了解这个主题。

• MSSql Server基础学习系列———聚合函数（聚合函数sql语句）
• $Django 聚合函数、分组查询、F,Q查询、orm字段以及参数
• 15-MySQL-Ubuntu - 数据表的查询 - 聚合函数 (四)
• ClickHouse 聚合函数
• Django 第十课 5.【聚合函数】

MSSql Server基础学习系列———聚合函数（聚合函数sql语句）

微软的sql Server中默认集成了很多的聚合函数，使用这些聚合函数可以大大减轻我们的sql语句编写量。

-常用聚合函数
--在聚合函数中，为NULL的记录不参与计算，count(*)除外
--MAX
select MAX(Cid) as '最大编号' from dbo.NewCourse 
where Cperiod<6
--min
select MIN(cid) as 最小编号 from dbo.NewCourse 
--sum
select SUM(Cperiod) from  dbo.NewCourse 
--avg
--计算平均值时，被除数未计算为NULL的记录，会造成结果的不准备
select AVG(Cperiod) from dbo.NewCourse 

--计算平均值建议时候语句
select SUM(Cperiod)/COUNT(*) from dbo.NewCourse
select AVG(isnull(Cperiod,0))from dbo.NewCourse
--函数isnull()字段，如果为空返回值)用于处理null值
--count 
--计算行数
select COUNT(*) from dbo.NewCourse
where Cperiod<5
--count()后面括号的内容最好是*，统计的时候包含Null的记录
--如果是count(列名)则不会统计列值为null的记录

select * from dbo.NewCourse
where Cid in(1,2)
--between还可用于日期形式的查询语句
select * from dbo.Teacher
where Tbiethday between '2010-10-10' and '2015-10-10'

$Django 聚合函数、分组查询、F,Q查询、orm字段以及参数

15-MySQL-Ubuntu - 数据表的查询 - 聚合函数 (四)

聚合函数

(1) count () 统计总数

查询男性总数

select count (*) as 男性 from students where gender=1;

(2) max () 最大值

查询男性中年龄最大值

select max (age) as 男性最大年龄 from students where gender=1;

(3) min () 最小值

查询女性中身高最小值

select min (height) as 女性最矮身高 from students where gender=2;

(4) sum () 求和

查询男性年龄总和

select sum (age) as 男性年龄总和 from students where gender=1;

(5) avg () 平均值

查询男性的年龄平均值

select avg (age) as 男性的年龄平均值 from students where gender=1;

ClickHouse 聚合函数

count

返回记录条数。

如

SELECT count() FROM table

注：如果求 COUNT(DISTINCT x)，则使用 uniq 函数

any(x)

返回遇到的第一个值

备注：待补充

anyHeavy(x)

通过 heavy hitters 算法，得到一个经常出现的值.

示例

SELECT anyHeavy(AirlineID) AS res FROM ontime

anyLast(x)

返回最后遇到的值

min(x)

返回最小值

max(x)

返回最大值

argMin(arg,val)

TBD

argMax(arg,val)

TBD

sum(x)

求和

仅对数值有效

sumWithOverflow(x)

求和

如果超过上限，则报错

sumMap(key,value)

对于相同的 key，对其 value 求和

示例

输入

CREATE TABLE sum_map( date Date, timeslot DateTime, statusMap Nested( status UInt16, requests UInt64 ) ) ENGINE = Log; INSERT INTO sum_map VALUES (''2000-01-01'', ''2000-01-01 00:00:00'', [1, 2, 3], [10, 10, 10]), (''2000-01-01'', ''2000-01-01 00:00:00'', [3, 4, 5], [10, 10, 10]), (''2000-01-01'', ''2000-01-01 00:01:00'', [4, 5, 6], [10, 10, 10]), (''2000-01-01'', ''2000-01-01 00:01:00'', [6, 7, 8], [10, 10, 10]); SELECT timeslot, sumMap(statusMap.status, statusMap.requests) FROM sum_map GROUP BY timeslot

查询

┌────────────timeslot─┬─sumMap(statusMap.status, statusMap.requests)─┐ │ 2000-01-01 00:00:00 │ ([1,2,3,4,5],[10,10,20,10,10]) │ │ 2000-01-01 00:01:00 │ ([4,5,6,7,8],[10,10,20,10,10]) │ └─────────────────────┴──────────────────────────────────────────────┘

avg(x)

平均值

uniq(x)

求近似的排重值, 适用于数值，字符串，日期，日期时间，以及 multiple 和 tuple 参数

使用了自适合抽样算法：在 65535 下非常准确和高效。

uniqCombined(x)

求近似的排重值，适用于数值，字符串，日期，日期时间，以及 multiple 和 tuple 参数

使用了组合的三个算法：数组，hash 表和 HyperLogLog 表。

内存消耗比 uniq 要小几倍，同时精度高几倍，但是性能一般比 uniq 慢，虽然有时也会快。最大支持到 96KiB 个状态.

uniqHLL12(x)

使用 HyperLogLog 算法来做排重值的近似计算，支持 2.5KB 个状态.

uniqExact(x)

计算精确的排重值.

groupArray(x), groupArray(max_size)(x)

创建一个数组值

我自己的示例

:) select str_nest.y from schema_nested; SELECT str_nest.y FROM schema_nested ┌─str_nest.y───────────────┐ │ [''search'',''mock1@a.com''] │ │ [''image'',''mock1@a.com''] │ │ [''video'',''mock2@a.com''] │ │ [''image'',''mock2@a.com''] │ └──────────────────────────┘ ┌─str_nest.y──────────────────────────────┐ │ [''search'',''mock1@a.com'',''news''] │ │ [''image'',''mock1@a.com'',''search'',''news''] │ └─────────────────────────────────────────┘ ┌─str_nest.y─────────────────────────────┐ │ [''video'',''mock2@a.com''] │ │ [''image'',''mock2@a.com'',''music'',''news''] │ └────────────────────────────────────────┘ 8 rows in set. Elapsed: 0.003 sec.

:) select groupArray(str_nest.y) from schema_nested SELECT groupArray(str_nest.y) FROM schema_nested ┌─groupArray(str_nest.y)────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ [[''search'',''mock1@a.com''],[''image'',''mock1@a.com''],[''video'',''mock2@a.com''],[''image'',''mock2@a.com''],[''search'',''mock1@a.com'',''news''],[''image'',''mock1@a.com'',''search'',''news''],[''video'',''mock2@a.com''],[''image'',''mock2@a.com'',''music'',''news'']] │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.003 sec.

:) select groupArray(3)(str_nest.y) from schema_nested SELECT groupArray(3)(str_nest.y) FROM schema_nested ┌─groupArray(3)(str_nest.y)──────────────────────────────────────────────────┐ │ [[''search'',''mock1@a.com''],[''image'',''mock1@a.com''],[''video'',''mock2@a.com'']] │ └────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.002 sec.

groupArrayInsertAt

TBD

groupUniqArray(x)

生成排重的数组，内存消耗与 uniqExact 方法相同

:) select groupUniqArray(str_nest.y) from schema_nested SELECT groupUniqArray(str_nest.y) FROM schema_nested ┌─groupUniqArray(str_nest.y)────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ [[''image'',''mock2@a.com'',''music'',''news''],[''image'',''mock1@a.com''],[''image'',''mock2@a.com''],[''video'',''mock2@a.com''],[''image'',''mock1@a.com'',''search'',''news''],[''search'',''mock1@a.com''],[''search'',''mock1@a.com'',''news'']] │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec.

quantile(level)(x)

估算百分位点值.

level 是一个 0 至 1 之间的常数，但是不要为 0 或者 1. 0 或者 1 时，使用 min,max 方法最合适.

quantileDeterministic(level)(x, determinator)

TBD

quantileTiming(level)(x)

TBD

quantileTimingWeighted(level)(x, weight)

TBD

quantileExact(level)(x)

TBD

quantileExactWeighted(level)(x, weight)

TBD

quantileTDigest(level)(x)

TBD

median

求中位数

每一个百分位点方法，都对应着一个求中位数的方法，如

median, medianDeterministic, medianTiming, medianTimingWeighted, medianExact, medianExactWeighted, medianTDigest

quantiles(level1, level2, ...)(x)

求多个百分位点

也对应着一系列的方法 quantiles, quantilesDeterministic, quantilesTiming, quantilesTimingWeighted, quantilesExact, quantilesExactWeighted, quantilesTDigest

varSamp(x)

求方差，结果是随机变量的方差的无偏估计

varPop(x)

求总体方差

stddevSamp(x)

为 varSamp(x) 的平均根

stddevPop(x)

为 varPop(x) 的平均根

topK

使用了 Filtered Space-Saving 算法来计算 topK

同时使用了 Parallel Space Saving 算法来进行 reduce and combine.

topK(N)(column)

:) select topK(3)(str_nest.y) from schema_nested SELECT topK(3)(str_nest.y) FROM schema_nested ┌─topK(3)(str_nest.y)────────────────────────────────────────────────────────┐ │ [[''video'',''mock2@a.com''],[''search'',''mock1@a.com''],[''image'',''mock1@a.com'']] │ └────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec.

covarSamp(x,y)

计算协方差

covarPop(x,y)

计算总体协方差

corr(x,y)

计算 Pearson 相关系数

含参聚合函数

学习自官方文档

sequenceMatch(pattern)(time, cond1, cond2, ...)

按顺利先后匹配事件

• pattern: 类似正则表达式的匹配规则
• time: 事件发生时间
• cond1, cond2: 最多 32 个，来标识条件是否满足

返回值

• 0：不匹配
• 1: 匹配

示例

sequenceMatch (''(?1).*(?2)'')(EventTime, URL LIKE ''%company%'', URL LIKE ''%cart%'')

是否存在事件链，即先访问了 company 的事件，再访问了 cart 的事件

也可以通过别的聚合方法来表示

minIf(EventTime, URL LIKE ''%company%'') < maxIf(EventTime, URL LIKE ''%cart%'')

sequenceCount(pattern)(time, cond1, cond2, ...)

逻辑与 sequenceMatch 函数一样，但是返回值为 chain 的编号.

TBD

uniqUpTo(N)(x)

返回 N 个排重值数目，如果实际值大于 N，则返回 N+1

聚合函数后缀

-If

任何聚合函数都可以通过增加后缀 If, 来增加一个外部参数条件. 聚合函数仅处理满足条件的记录行. 如果条件一次也没有触发，则返回默认值(通常是 0 或者是空字符串)

这样可以一次处理多个聚合条件，而不需要转换为子查询或者 JOIN 来计算.

示例

sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond)

-Array

任何聚合函数都可以通过增加后缀 Array, 来使原来的参数类型 T 变为新的参数类型 Array(T)。

在处理时，原来的聚合函数会依次处理 Array 中的每一项.

示例

sumArray(arr) : 对 Arrays 中的所有元素进行求和，即 sum(arraySum(arr))

uniqArray(arr): 对 Arrays 中的所有元素进行排重值，即 uniq(arrayJoin(arr))

-If 和 -Array 可以组合使用，但是 Array 必须在前，If 在后。例如

uniqArrayIf(arr, cond)

quantilesTimingArrayIf(level1, level2)(arr, cond)

-State

返回的不是结果值，返回的是中间状态. 这个是与 AggregatingMergeTree 来配合使用的.

-Merge

聚合函数会把中间状态会为参数，进行 Merge，来完成聚合，返回最终的结果值.

-MergeState

与 -Merge 类似，但是返回的不是结果值，而是类似于 -State 的中间状态.

-ForEach

将对 table 使用的聚合函数，转换为对数组的聚合函数。对数组的每一项进行处理，返回一个结果数组。

如：

sumForEach([1,2],[3,4,5],[6,7])

结果为

[10,13,5]

Django 第十课 5.【聚合函数】

1：所有的聚合函数都是放在 ‘django.db.models’ 下面

2：聚合函数不能单独执行，需要放在一些可以执行聚合函数的方法下面中去执行。比如 ‘aggregate’。示例代码如下：

result = Book.objects.aggretate(Avg("price"))

3: 聚合函数执行完成后，给这个聚合函数的值去个名字，取名字的规制，默认是 ‘field__【聚合函数名】’ 形成的。比如以上代码形成的名字叫做‘price__avg’ 。如果不想使用默认的名字，那么可以在使用聚合函数的时候传递关键字参数进去，参数的名字就是聚合函数执行完成的名字。示例代码如下：

result = Book.objects.aggretate(avg=Avg("price"))

#以上传递了关键字参数 ‘avg=Avg("price")’ ，那么以后 ‘Avg’聚合函数执行完成的名字就叫做‘avg’

4：‘aggretate’：这个方法不会返回一个‘QuerySet’对象，而是返回一个字典，这个字典中的key就是聚合函数的名字。值就是聚合函数执行后的结果

5：order_by：指定将查询的结果根据某个字段进行排序。如果要倒叙排序，那么可以在这个字段的前面加一个负号。

articles = Article.objects.order_by("create_time")
articles = Article.objects.order_by("-create_time")

一定要注意的一点是，多个order_by，会把前面排序的规制给打乱，而使用后面的排序方式。

articles = Article.objects.order_by("create_time").order_by("auther_name")

他会根据作者的名字进行排序，而不是使用文章的创建时间。当然，也可以在模型定义的在 ‘meta’类中定义‘ordering’来指定默认的排序方式。

Class Meta:
　　db_table = ''book_order'' #数据库表名
　　ordering = [''create_time'', ''-price''] #默认数据库排序

今天的关于MSSql Server基础学习系列———聚合函数和聚合函数sql语句的分享已经结束，谢谢您的关注，如果想了解更多关于$Django 聚合函数、分组查询、F,Q查询、orm字段以及参数、15-MySQL-Ubuntu - 数据表的查询 - 聚合函数 (四)、ClickHouse 聚合函数、Django 第十课 5.【聚合函数】的相关知识，请在本站进行查询。