SQL汇总数据与连接对汇总的影响
聚集函数
有时我们需要汇总数据而不是把数据实际检索出来。
比如:
确定表中行数。
获得表中某些行的和
函 数 | 说 明 |
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
一:AVG()
使用AVG函数返回表中某列的平均价格:
SELECT AVG(prod_price) AS avg_price FROM Products;
所有产品的平均价格。
使用AVG函数返回表中特定行的平均价格:
SELECT AVG(prod_price) AS avg_price FROM Products where vend_id='DLL01';
供应商id为‘DLL01’的产品的平均价格。
说明:AVG()函数自动忽略列值为NULL的行。
二:COUNT()
count(*) 对表中的行的数目进行计数,不管列中包含的是空值(NULL)还是非空值。
count(column) 忽略NULL值
使用count(*) 函数
SELECT COUNT(*) AS num_cust FROM Customers
使用count(cust_email) 函数
select count(cust_email) as num_cust from Customers
只计算cust_email列有值的行进行计数。
三:SUM()
返回两个列的值相乘后相加后的结果。
select sum(item_price*quantity) as total_price from orderitems where order_num=20005;
本例说明sum(函数)可在多个列中进行计算
说明:SUM()函数自动忽略列值为NULL的行。
四,五:MAX(),MIN()
聚集不同的值:
以上5个聚集函数均可以如下使用:
对所有行执行计算,默认为ALL。
只包含不同的值,指定DISTINCT参数。
表连接后对汇总结果的影响:
两个表连接,根据连接字段是否为主键,主要分为以下3种情况:
1.主键连接主键:
学员ID | 学员姓名 |
1 | 张三 |
2 | 李四 |
A表
学员ID | 学员性别 |
1 | 男 |
2 | 女 |
B表
A表记录了学员ID和学员姓名。B表记录了学员ID和学员性别。当将A表和B表使用学员ID连接起来后,此时属于主键连接主键的情况。一般这种情况不太会出现,更常见到的是两个表已经合在一块。
2.非主键连接非主键:
订单id(主键) | 区域id | 订单金额 |
1 | a | 100 |
2 | a | 100 |
3 | c | 100 |
4 | d | 100 |
订单表A
订单id(主键) | 产品id(主键) | 区域id | 订单金额 |
1 | a-1 | a | 50 |
1 | a-2 | a | 50 |
2 | a-1 | a | 100 |
3 | c-1 | c | 100 |
4 | d-1 | d | 100 |
订单详情表B
当将订单表A和订单详情表B使用区域ID连接起来后:
订单id A | 区域id A | 订单金额 A | 订单id B | 产品id B | 区域id B | 订单金额 B |
1 | a | 100 | 1 | a-1 | a | 50 |
1 | a | 100 | 1 | a-2 | a | 50 |
1 | a | 100 | 2 | a-1 | a | 100 |
2 | a | 100 | 1 | a-1 | a | 50 |
2 | a | 100 | 1 | a-2 | a | 50 |
2 | a | 100 | 2 | a-1 | a | 100 |
3 | c | 100 | 3 | c-1 | c | 100 |
4 | d | 100 | 4 | d-1 | d | 100 |
连接前:订单表A的订单金额汇总值是:400元。订单详情表B的订单金额汇总值是:400元。
连接后:“订单金额 A ”列:800元。“订单金额 B”列:600元。
连接后的汇总值都比连接前的汇总值多,这是因为区域id出现了重复值的现象。因此为了保证数据的准确性,我们一般不会这样连接。
3.非主键连接主键:
当将订单表A和订单详情表B使用订单ID连接起来后:
订单id A | 区域id A | 订单金额 A | 订单id B | 产品id B | 区域id B | 订单金额 B |
1 | a | 100 | 1 | a-1 | a | 50 |
1 | a | 100 | 1 | a-2 | a | 50 |
2 | a | 100 | 2 | a-1 | a | 100 |
3 | c | 100 | 3 | c-1 | c | 100 |
4 | d | 100 | 4 | d-1 | d | 100 |
连接后“订单金额 B”列:400元。“订单金额 A”列:500元。
采用非主键连接的表最终关于“订单金额 B”列的汇总值是正确的。而"订单金额 A"列的汇总值是错误的。
结论:两个表之间关联,关联条件一般是用一个表的主键,另一个表的非主键进行关联。关联之后用之前使用非主键关联的表下的字段做汇总值,而另一个表的字段做维度。