今天的企业从越来越大的数据集中生成有用的信息,这就是为什么趋势分析、预测和智能业务报告仪表板有很高的需求。战略和战术计划人员需要这些信息来对其业务的未来做出决策,这也会导致依赖于他们用来存储数据的关系数据库。

那么如何将存储在数据库表中的数据转换为这种类型的知识呢?SQL中的分析函数允许您对数据库中的记录运行计算。许多人认为SUM函数是最有用的SQL命令之一,因为它可以帮助您在列中添加值或从另一个SQL表达式中添加结果。在这篇文章中,我们将介绍几种求和的方法。

SQL SUM做什么?

SUM是组成聚合函数组的SQL函数之一。我们通常在SQL中使用的许多函数只对单行进行操作。聚合函数或组函数对多行进行操作,允许用户以不同于数据值在数据库中显示的方式操作和显示数据值。例如,如果程序员使用提供算术操作(如SUM、COUNT和AVG)的函数,然后将它们应用于多行数据,那么结果将是一个会计算但不会实际存储在数据库中的值。

完整的SQL训练营2021:从零到英雄

最后更新2020年10月

畅销书
  • 83课
  • 所有级别
4.7 (97704)

成为SQL专家!|由穆Portilla

探索课程

这是一个重要的概念,因为它意味着您不能简单地从数据库中存储和检索数据。相反,组函数可以对一组行和一组数据值应用数学操作,并派生出以前未知的信息。这样做的价值在于,程序员可以通过应用SQL的内置算术函数从现有数据中生成信息和知识。

聚合函数处理使用groupby进行分组的数据。多组函数(如SUM)可以提供整个数据集或单个组的汇总总数。这听起来可能不是什么惊天动地的事情,但这是提供商业分析的第一步。例如,数据库可以为业务提供答案,例如,“总费用或每个部门的工资是多少。”

通过使用基本的多组函数(如SUM和COUNT),您可以获得上述业务问题的答案。其他单组函数提供最小值和最大值和方差。根据这些值,您可以计算出标准偏差,这是真正的业务智能和分析的基础。企业需要的不仅仅是静态信息。它需要分析。那么数据库如何为更详细的问题提供实时答案呢?销售总量是多少,销售排名是多少,每个渠道的销售与上周,上个月,前月,去年这个时候的偏差是多少?这些都是企业需要实时回答的问题。

要对这些类型的复杂问题建模,然后将它们实现为标准SQL查询,需要执行许多步骤。值得庆幸的是,已经有了执行这些分析函数的SQL扩展。使用SQL SUM函数也比使用编程语言查询数据并循环结果以生成和更有效。这样做还需要向应用程序返回更多数据,而应用程序只需要一个简单的值。让我们看看SUM函数的语法。

SQL SUM语法

下面是SUM语句的基本语法:

SELECT SUM(column_name or expression) FROM table_name WHERE条件:

列必须是数字列,如果包含SQL表达式,则必须返回数字值列表。这是基本的语法,但是还有更多的方法可以在查询中使用SUM。一种方法是使用SUM和OVER。

SELECT column_1, column_2, SUM (column_2) OVER (ORDER BY column_1) running_total FROM table_name ORDER BY column_1;

扩展OVER被添加到上面查询中的SUM计算中。在本例中,SUM作为一个分析函数工作,ORDER BY子句指示将在该分析和函数上执行的数据范围(窗口或行)。这个查询将返回三列。第一列告诉您将处理数据的顺序,而第二列显示将汇总的数据。在第三列中,您将发现第二列的运行总数。

与SUM一起使用的另一个SQL语句是GROUP BY语句。这将允许我们根据另一列的值对一列的值求和。下面是用于这种查询类型的语法:

SELECT column_1, SUM (column_2), column_one_sum FROM table_name GROUP BY column_1;

现在您已经了解了如何使用SUM,下面让我们看一些示例数据、查询和结果,以了解SUM如何处理实际数据。

SQL中使用SUM的例子

对于这些示例,我们将假设我们有一个名为orders的数据库表,其中包含如下所示的数据。

Id department_id customer_name pre_order 费用
1 107 行为 20000 1000
2 107 银色 40000 5000
3. 107 靶心 70000 10000
4 108 公式4 1000 500
5 108 电视媒体 20000 2000
6 109 X-sell 40000 4000
7 109 YCT 20000 1000

现在让我们看看在SQL查询中使用SUM的各种方法。它真的很灵活。

SQL SUM的基本使用

所以使用SUM来查询我们上面看到的数据的最基本的查询是这样的:

SELECT SUM(pre_order) total FROM orders;

这将返回一行结果。

总计
211000

使用SUM和OVER

如果我们在查询中使用OVER语句生成pre_order列和customer_name的运行总数,它将如下所示。

SELECT pre_order, SUM (pre_order) OVER (ORDER BY customer_name) running_total FROM orders ORDER BY customer_name;

这个查询将为我们的表返回以下结果:

customer_name pre_order running_total
行为 20000 20000
银色 40000 60000
靶心 70000 130000
公式4 1000 131000
电视媒体 20000 151000
X-sell 40000 191000
YCT 20000 211000

这个查询返回了一个更有用的数据集。我们现在有了客户的名字和预订的总数。

使用SUM和OVER和PARTITION BY

但是,如果我们想要查看每个客户的所有预购订单和每个部门运行的预购总量,那么必须使用GROUP BY或PARTITION子句来隔离每个组,并将SUM函数限制为每个数据组(部门)。

SELECT company_name, department_id, pre_order, SUM(pre_order) OVER (PARTITION BY department_id ORDER BY pre_order) department_total FROM orders ORDER BY department_id, preorders, company_name;

在这个SQL查询示例中,函数名是SUM,它应用于列预订单,这是基本语法。OVER告诉数据库,你想使用SUM作为一个分析函数,它将对SQL表达式产生的数据执行OVER:

PARTITION BY department_id ORDER BY pre_sales

查询结果如下所示。

customer_name department_id pre_order department_total
行为 107 20000 20000
银色 107 40000 60000
靶心 107 70000 130000
公式4 108 1000 1000
电视媒体 108 20000 21000
X-sell 109 40000 40000
YCT 109 20000 60000

现在每个部门都有一个运行总数。

使用SUM和GROUP BY

如果我们只想要部门的总和,我们可以在SQL中使用GROUP BY并编写一个基本查询。

SELECT department_id, SUM(pre_order) department_total FROM orders GROUP BY department_id;

我们将从该查询中获得的结果如下所示:

department_id department_total
107 130000
108 21000
109 60000

使用SUM和DISTINCT

你也可以在SUM函数中使用DISTINCT。结果是,它将删除列中所有重复的值。这是我们第一个简单的查询,它对每条记录中的pre_order列的值求和,并添加了DISTINCT子句:

SELECT SUM(DISTINCT pre_order) DISTINCT t_total FROM orders;

下面是该查询的结果:

distinct_total
131000

注意,没有DISTINCT子句的查询返回的值是211,000,但是现在有了DISTINCT子句,返回的值是131,000或80,000。这是因为pre_order列中重复了40,000次,重复了20,000次,总共是80,000。

使用SUM计算多个列上的组总数

您可能已经注意到订单表有另一个数字字段。假设我们不仅要计算一个部门的预订量的总和,还要计算百分比列的总和。我们可以使用orders表下面的查询来做到这一点。

SELECT department_id, SUM(pre_order) department_order_total, SUM(expense) department_expense_total FROM orders GROUP BY department_id;

这个查询将返回以下结果:

department_id department_order_total department_expense_total
107 130000 16000
108 21000 2500
109 60000 5000

在SUM函数中使用表达式

我们说过,SUM函数中使用的值可以是列或SQL表达式。如果我们想要一个扣除费用的订单总数,我们可以用一个表达式来做。我不知道这个查询是否有用,但它是可能的,它展示了如何使用SUM表达式。下面是查询:

SELECT pre_order, SUM (pre_order - expense) OVER (ORDER BY customer_name) running_total FROM orders ORDER BY customer_name;

查询的结果如下所示:

customer_name pre_order running_total
行为 20000 19000
银色 40000 54000
靶心 70000 114000
公式4 1000 114500
电视媒体 20000 132500
X-sell 40000 168500
YCT 20000 187500

使用SQL SUM与SQL COUNT和SQL AVG

还可以在SQL查询中组合聚合函数,以作用于同一组记录。如果我们想返回一个部门的客户数量以及预订和费用的总和,我们可以这样做。下面是这个查询:

SELECT department_id, SUM(pre_order) department_order_total, SUM(expense) department_expense_total, COUNT(customer_name) customer_count FROM orders GROUP BY department_id;

该查询的结果如下所示:

department_id department_order_total department_expense_total customer_count
107 130000 16000 3.
108 21000 2500 2
109 60000 5000 2

这意味着我们有了可以用来计算平均值的数字。这意味着我们现在可以编写这样的查询:

SELECT department_id, SUM(pre_order) department_order_total, SUM(expense) department_expense_total, COUNT(customer_name) COUNT, SUM(orders) / COUNT(customer_name) avg_orders, SUM(expense) / COUNT(customer_name) avg_expenses FROM orders GROUP BY department_id;

它将返回以下记录集:

department_id department_order_total department_expense_total avg_orders avg_expenses
107 130000 16000 3. 43333.33 5333.33
108 21000 2500 2 10500 1250
109 60000 5000 2 30000 2500

但我们不需要这样做,因为我们也可以使用AVG和COUNT来进行更简单的查询。下面的查询将返回与最后一个查询相同的结果。

SELECT department_id, SUM(pre_order) department_order_total, SUM(expense) department_expense_total, COUNT(customer_name) COUNT, AVG(orders) avg_orders, AVG(expense) avg_expenses FROM orders GROUP BY department_id;

设置SUM的范围

解析函数可能有也可能没有窗口子句——在这个上下文中窗口是解析函数使用的行范围。只返回pre_order列和的第一个查询没有使用窗口子句。在本例中,它将添加整个行集。

第二个示例返回每个客户的运行总数,默认情况下使用一个窗口。我们使用的ORDER BY子句触发了这个默认窗口。这意味着当前分区中的当前行和之前的行是要在计算中使用的行。因为在这个示例中没有编码PARTITION,所以累积SUM函数使用了整个列。

在第三个示例中,我们返回每个部门的运行总数。在这个例子中,有一个PARTITION,我们按部门对它进行分组。但是,我们没有设置窗口参数。可以使用前面的ROWS或RANGE子句设置窗口参数。

常见SQL SUM错误

最常见的错误之一是使用的SUM函数结果不是数字数据。有时,VARCHAR列中可能有数字数据。在对这些值使用SUM函数之前,必须首先将这些值转换为数字数据类型。

使用SUM函数时可能发生的另一个错误是数字溢出。它看起来是这样的:

算术溢出错误,将表达式转换为数据类型int。

顶尖发展课程bob外围官网

深度学习A-Z™:手动人工神经网络
基里尔·埃雷门科,哈德林·德·蓬特维斯,利朗西团队
4.5 (36565)
畅销书
完整的2021年颤振开发训练营与Dartbob外围官网
安吉拉Yu博士
4.7 (30966)
畅销书
数据科学课程2021:完整的数据科学训练营
365职业,365职业团队
4.6 (90693)
畅销书
虚幻引擎c++开发:学习c++和制作视频游戏
Ben Tristem, Sam Pattuzzi(游戏开发)电视团队Rob Brooks报道
4.6 (51240)
畅销书
终极MySQL训练营:从SQL初学者到专家
柯尔特·斯蒂尔,这是伊恩·斯库诺弗
4.6 (53535)
畅销书

更多的发展bob外围官网课程

这可能发生在一个运行了很长时间的查询中,然后某一天,它抛出一个错误。当列是整数类型且SUM函数的结果大于该类型的限制时,可能会发生这种情况。SUM函数将尝试以与列相同的类型返回结果。要修复此错误,必须首先将值转换为一个大整数。下面是一个例子:

SELECT department_id, SUM(CONVERT(BIGINT, pre_order)) department_total FROM orders GROUP BY department_id;

使用GROUP BY子句的SUM函数可能会遇到的另一个常见错误是:

列在选择列表中无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中。

这通常意味着您还没有为SUM函数设置窗口。在某些关系数据库中,可能触发这种错误的查询如下:

SELECT *, SUM(pre_order) total FROM orders;

有几种方法可以修复这个查询,具体取决于您试图对第一个查询做什么。如果您只想要pre_order列的完整和,您可以从查询中删除星号。

SELECT SUM(pre_order) total FROM orders;

如果是基于另一列求和,可以添加一个GROUP BY。这个查询看起来像这样:

SELECT department_id, SUM(pre_order) total FROM orders GROUP BY department_id;

您还可以通过使用OVER函数告诉数据库您的窗口就是整个表来修复查询。这个查询看起来像这样:

SELECT SUM(pre_order) OVER() total FROM orders;

结论

分析是详细报告所需的功能,是作战和战术计划和分析所需的信息来源。SUM函数是一个功能强大的分析命令,它将添加表列的值或SQL表达式的结果。当与ORDER BY语句、OVER语句、GROUP BY语句和PARTITION BY语句一起使用时,可以使用SUM计算不同行范围的和。

SQL顶级课程

SQL和PostgreSQL:完整的开发人员指南
Stephen主梁
4.7 (1340)
评价最高
完整的SQL训练营2021:从零到英雄
何塞Portilla
4.7 (97704)
畅销书
终极MySQL训练营:从SQL初学者到专家
柯尔特·斯蒂尔,这是伊恩·斯库诺弗
4.6 (53535)
畅销书
SQL - MySQL的数据分析和商业智能
365年职业生涯
4.6 (27320)
畅销书
高级SQL: SQL专家认证准备课程
8万多名学员,Code Star学院
4.4 (2089)
完整的SQL和数据库训练营:零到精通[2021]
Andrei Neagoie, Mo Binni
4.6 (1391)
SQL初学者:学习SQL使用MySQL和数据库设计
Tim Buchalka的学习编程学院,Jon Avis - SQL讲师
4.5 (9909)
SQL & PostgreSQL初学者:成为一个SQL专家
Jon Avis - SQL讲师
4.5 (3156)
畅销书

多个SQL课程

SQL学生也学习

让你的团队。领导行业。

使用Udemy for Business订阅在线课程库和数字学习工具。

请求一个演示