首页
随机页面
分类
查看“Django/docs/3.2.x/ref/contrib/postgres/aggregates”的源代码
来自菜鸟教程
Django/docs/3.2.x/ref/contrib/postgres/aggregates / ←
PostgreSQL 特定的聚合函数 — Django 文档
跳转至：
导航
、
搜索
因为以下原因，您没有权限编辑本页：
您请求的操作仅限属于该用户组的用户执行：
用户
您可以查看和复制此页面的源代码。
{{DISPLAYTITLE:PostgreSQL 特定的聚合函数}} <div id="module-django.contrib.postgres.aggregates" class="section"> <span id="postgresql-specific-aggregation-functions"></span> = PostgreSQL 特定的聚合函数 = 这些功能可从 <code>django.contrib.postgres.aggregates</code> 模块获得。 它们在 [https://www.postgresql.org/docs/current/functions-aggregate.html PostgreSQL 文档] 中有更详细的描述。 <div class="admonition note"> 笔记 所有函数都没有默认别名，因此您必须明确提供一个。 例如： <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">>>> SomeModel.objects.aggregate(arr=ArrayAgg('somefield')) {'arr': [0, 1, 2]}</syntaxhighlight> </div> </div> </div> <div class="admonition-common-aggregate-options admonition"> 常见的聚合选项 所有聚合都有 [[../../../models/querysets#aggregate-filter|filter]] 关键字参数。 </div> <div id="general-purpose-aggregation-functions" class="section"> == 通用聚合函数 == <div id="arrayagg" class="section"> === ArrayAgg === <dl> <dt>''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">ArrayAgg</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expression</span></span>'', ''<span class="n"><span class="pre">distinct</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="n"><span class="pre">ordering</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">()</span></span>'', ''<span class="o"><span class="pre">**</span></span><span class="n"><span class="pre">extra</span></span>''<span class="sig-paren">)</span></dt> <dd><p>返回连接到数组中的值列表，包括空值。</p> <dl> <dt><span class="sig-name descname"><span class="pre">distinct</span></span></dt> <dd><p>一个可选的布尔参数，用于确定数组值是否不同。 默认为 <code>False</code>。</p></dd></dl> <dl> <dt><span class="sig-name descname"><span class="pre">ordering</span></span></dt> <dd><p>字段名称的可选字符串（带有指示降序的可选 <code>"-"</code> 前缀）或指定结果列表中元素顺序的表达式（或字符串和/或表达式的元组或列表） .</p> <p>例子：</p> <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">'some_field' '-some_field' from django.db.models import F F('some_field').desc()</syntaxhighlight> </div> </div></dd></dl> </dd></dl> </div> <div id="bitand" class="section"> === BitAnd === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">BitAnd</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expression</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="o"><span class="pre">**</span></span><span class="n"><span class="pre">extra</span></span>''<span class="sig-paren">)</span> : 返回所有非空输入值的按位 <code>AND</code> 的 <code>int</code>，如果所有值都为空，则返回 <code>None</code>。 </div> <div id="bitor" class="section"> === BitOr === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">BitOr</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expression</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="o"><span class="pre">**</span></span><span class="n"><span class="pre">extra</span></span>''<span class="sig-paren">)</span> : 返回所有非空输入值的按位 <code>OR</code> 的 <code>int</code>，如果所有值都为空，则返回 <code>None</code>。 </div> <div id="booland" class="section"> === BoolAnd === <dl> <dt>''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">BoolAnd</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expression</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="o"><span class="pre">**</span></span><span class="n"><span class="pre">extra</span></span>''<span class="sig-paren">)</span></dt> <dd><p>如果所有输入值都为真，则返回 <code>True</code>，如果所有值都为空或没有值，则返回 <code>None</code>，否则返回 <code>False</code>。</p> <p>用法示例：</p> <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">class Comment(models.Model): body = models.TextField() published = models.BooleanField() rank = models.IntegerField() >>> from django.db.models import Q >>> from django.contrib.postgres.aggregates import BoolAnd >>> Comment.objects.aggregate(booland=BoolAnd('published')) {'booland': False} >>> Comment.objects.aggregate(booland=BoolAnd(Q(rank__lt=100))) {'booland': True}</syntaxhighlight> </div> </div></dd></dl> </div> <div id="boolor" class="section"> === BoolOr === <dl> <dt>''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">BoolOr</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expression</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="o"><span class="pre">**</span></span><span class="n"><span class="pre">extra</span></span>''<span class="sig-paren">)</span></dt> <dd><p>如果至少有一个输入值为真，则返回 <code>True</code>，如果所有值都为空或没有值，则返回 <code>None</code>，否则返回 <code>False</code>。</p> <p>用法示例：</p> <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">class Comment(models.Model): body = models.TextField() published = models.BooleanField() rank = models.IntegerField() >>> from django.db.models import Q >>> from django.contrib.postgres.aggregates import BoolOr >>> Comment.objects.aggregate(boolor=BoolOr('published')) {'boolor': True} >>> Comment.objects.aggregate(boolor=BoolOr(Q(rank__gt=2))) {'boolor': False}</syntaxhighlight> </div> </div></dd></dl> </div> <div id="jsonbagg" class="section"> === JSONBAgg === <dl> <dt>''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">JSONBAgg</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expressions</span></span>'', ''<span class="n"><span class="pre">distinct</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="n"><span class="pre">ordering</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">()</span></span>'', ''<span class="o"><span class="pre">**</span></span><span class="n"><span class="pre">extra</span></span>''<span class="sig-paren">)</span></dt> <dd><p>将输入值作为 <code>JSON</code> 数组返回。</p> <dl> <dt><span class="sig-name descname"><span class="pre">distinct</span></span></dt> <dd><div class="versionadded"> <p><span class="versionmodified added">3.2 版中的新功能。</span></p> </div> <p>一个可选的布尔参数，用于确定数组值是否不同。 默认为 <code>False</code>。</p></dd></dl> <dl> <dt><span class="sig-name descname"><span class="pre">ordering</span></span></dt> <dd><div class="versionadded"> <p><span class="versionmodified added">3.2 版中的新功能。</span></p> </div> <p>字段名称的可选字符串（带有指示降序的可选 <code>"-"</code> 前缀）或指定结果列表中元素顺序的表达式（或字符串和/或表达式的元组或列表） .</p> <p>示例与 [[#django.contrib.postgres.aggregates.ArrayAgg.ordering|ArrayAgg.ordering]] 相同。</p></dd></dl> </dd></dl> </div> <div id="stringagg" class="section"> === StringAgg === <dl> <dt>''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">StringAgg</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">expression</span></span>'', ''<span class="n"><span class="pre">delimiter</span></span>'', ''<span class="n"><span class="pre">distinct</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>'', ''<span class="n"><span class="pre">ordering</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">()</span></span>''<span class="sig-paren">)</span></dt> <dd><p>返回连接成字符串的输入值，由 <code>delimiter</code> 字符串分隔。</p> <dl> <dt><span class="sig-name descname"><span class="pre">delimiter</span></span></dt> <dd><p>必需的参数。 必须是字符串。</p></dd></dl> <dl> <dt><span class="sig-name descname"><span class="pre">distinct</span></span></dt> <dd><p>一个可选的布尔参数，用于确定连接的值是否不同。 默认为 <code>False</code>。</p></dd></dl> <dl> <dt><span class="sig-name descname"><span class="pre">ordering</span></span></dt> <dd><p>字段名称的可选字符串（带有指示降序的可选 <code>"-"</code> 前缀）或指定结果字符串中元素顺序的表达式（或字符串和/或表达式的元组或列表） .</p> <p>示例与 [[#django.contrib.postgres.aggregates.ArrayAgg.ordering|ArrayAgg.ordering]] 相同。</p></dd></dl> </dd></dl> </div> </div> <div id="aggregate-functions-for-statistics" class="section"> == 统计聚合函数 == <div id="y-and-x" class="section"> === y 和 x === 所有这些函数的参数 <code>y</code> 和 <code>x</code> 可以是字段名称或返回数字数据的表达式。 两者都是必需的。 </div> <div id="corr" class="section"> === Corr === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">Corr</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则以 <code>float</code> 或 <code>None</code> 的形式返回相关系数。 </div> <div id="covarpop" class="section"> === CovarPop === <dl> <dt>''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">CovarPop</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">sample</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">False</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span></dt> <dd><p>如果没有任何匹配的行，则以 <code>float</code> 或 <code>None</code> 的形式返回总体协方差。</p> <p>有一个可选参数：</p> <dl> <dt><span class="sig-name descname"><span class="pre">sample</span></span></dt> <dd><p>默认情况下，<code>CovarPop</code> 返回总体总体协方差。 但是，如果 <code>sample=True</code>，返回值将是样本总体协方差。</p></dd></dl> </dd></dl> </div> <div id="regravgx" class="section"> === RegrAvgX === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrAvgX</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则以 <code>float</code> 或 <code>None</code> 的形式返回自变量 (<code>sum(x)/N</code>) 的平均值。 </div> <div id="regravgy" class="section"> === RegrAvgY === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrAvgY</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则以 <code>float</code> 或 <code>None</code> 的形式返回因变量 (<code>sum(y)/N</code>) 的平均值。 </div> <div id="regrcount" class="section"> === RegrCount === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrCount</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 返回两个表达式都不为空的输入行数的 <code>int</code>。 </div> <div id="regrintercept" class="section"> === RegrIntercept === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrIntercept</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则返回由 <code>(x, y)</code> 对确定的最小二乘拟合线性方程的 y 截距作为 <code>float</code> 或 <code>None</code>。 </div> <div id="regrr2" class="section"> === RegrR2 === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrR2</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则以 <code>float</code> 或 <code>None</code> 的形式返回相关系数的平方。 </div> <div id="regrslope" class="section"> === RegrSlope === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrSlope</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则返回由 <code>(x, y)</code> 对确定的最小二乘拟合线性方程的斜率作为 <code>float</code> 或 <code>None</code>。 </div> <div id="regrsxx" class="section"> === RegrSXX === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrSXX</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则返回 <code>sum(x^2) - sum(x)^2/N</code>（自变量的“平方和”）作为 <code>float</code> 或 <code>None</code>。 </div> <div id="regrsxy" class="section"> === RegrSXY === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrSXY</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则返回 <code>sum(x*y) - sum(x) * sum(y)/N</code>（独立时间因变量的“乘积之和”）作为 <code>float</code> 或 <code>None</code>。 </div> <div id="regrsyy" class="section"> === RegrSYY === ; ''<span class="pre">class</span>'' <span class="sig-name descname"><span class="pre">RegrSYY</span></span><span class="sig-paren">(</span>''<span class="n"><span class="pre">y</span></span>'', ''<span class="n"><span class="pre">x</span></span>'', ''<span class="n"><span class="pre">filter</span></span><span class="o"><span class="pre">=</span></span><span class="default_value"><span class="pre">None</span></span>''<span class="sig-paren">)</span> : 如果没有任何匹配的行，则返回 <code>sum(y^2) - sum(y)^2/N</code>（因变量的“平方和”）作为 <code>float</code> 或 <code>None</code>。 </div> </div> <div id="usage-examples" class="section"> == 使用示例 == 我们将使用此示例表： <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">| FIELD1 | FIELD2 | FIELD3 | |--------|--------|--------| | foo | 1 | 13 | | bar | 2 | (null) | | test | 3 | 13 |</syntaxhighlight> </div> </div> 以下是一些通用聚合函数的示例： <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">>>> TestModel.objects.aggregate(result=StringAgg('field1', delimiter=';')) {'result': 'foo;bar;test'} >>> TestModel.objects.aggregate(result=ArrayAgg('field2')) {'result': [1, 2, 3]} >>> TestModel.objects.aggregate(result=ArrayAgg('field1')) {'result': ['foo', 'bar', 'test']}</syntaxhighlight> </div> </div> 下一个示例显示了统计聚合函数的用法。 将不描述基础数学（例如，您可以在 [https://en.wikipedia.org/wiki/Regression_analysis wikipedia] 中阅读相关内容）： <div class="highlight-default notranslate"> <div class="highlight"> <syntaxhighlight lang="python">>>> TestModel.objects.aggregate(count=RegrCount(y='field3', x='field2')) {'count': 2} >>> TestModel.objects.aggregate(avgx=RegrAvgX(y='field3', x='field2'), ... avgy=RegrAvgY(y='field3', x='field2')) {'avgx': 2, 'avgy': 13}</syntaxhighlight> </div> </div> </div> </div> <div class="clearer"> </div> [[Category:django 3.2.x 文档]]
返回至“
PostgreSQL 特定的聚合函数 — Django 文档
”。
