假如你的老板想知道哪些项目还处于活跃状态,哪些项目已经停止了。他让你生成一个每个项目最后一个bug提交日期的报表。你查询根据product分组的dete_report的最大值,如下:

product latest bug_id
USQL 2010-06-01 1234
Open RoundFile 2010-02-16 3456
ReConsider 2010-01-01 5678
    但是你对比全量数据,发现这个结果并不准确。
product latest bug_id memo
USQL 2009-12-19 1234 这个bug_id…
USQL 2010-06-01 2248 与这个日期不匹配
Open RoundFile 2010-02-16 3456 这里又正确
ReConsider 2010-01-01 5678 这里又正确

目的:获取每组的最大值

    group by 确实很强大,他将复杂的报表生成过程简化到只用一个关键字解决。但是我们要认清group by什么能做,什么不能做。我们分析造成上面的原因。
    如果我们只需要获取每一个项目和最后一个bug提交的时间。那么可以使用以下语句,他会返回正确的结果。

1
2
3
select product_id, max(date_report) as latest
from bug join bug_product using (bug_id)
group by product_id;

    接下来我们对以上语句,进行扩展,让它返回最后一个提交bug的id。

1
2
3
select product_id, max(date_report) as latest, bug_id
from bug join bug_product using (bug_id)
group by product_id;

    然而这个查询在不同的数据库中,要么返回一个错误的结果,要么是一个错误的语法。那么我们本章的目的就是要执行一个不仅返回每组最大值的查询,同时也要返回这个值的其他字段。

反模式:引用非分组列

    造成这个反模式的根本原因就是程序员对于SQL中分组查询逻辑的普遍误解。

单值规则

    属于一个组的所有行,他们group by关键字所指的那个字段都是一样的。对于以下查询,每一组都会返回一条且仅返回一条,这就是单值规则。

1
2
3
select product_id, max(date_report) as latest
from bug join bug_product using (bug_id)
group by product_id;

    跟在select之后的每一列,对于每一组来说都必须返回且仅返回一个值,无论这一组匹配多少行。在这个查询中max()返回每一组最大date_report,它也是遵守单值规则的,所以这个查询返回的结果是正确的。但是你想在这个sql中返回其他的字段,如下:

1
2
3
select product_id, max(date_report) as latest, bug_id
from bug join bug_product using (bug_id)
group by product_id;

    在这个例子中,在根据product进行的分组中,每一组将有多个bug_id,也是就是说一个product有多个bug_id,那么它不符合单值规则,数据库就不知道它显示哪一个值是正确的。大多数数据库在这样情况下则会抛出错误,或者给出一个错误的值。

我想要的查询

    常见的误解就是程序员任务SQL会猜测你需要在报表中显示哪个bug_id,大多数人认为如果查询得到最大值,那么查询返回结果的其他列就会是对应的最大值所在的行的那些值。不幸的是,SQL并没有那么智能,原因如下:

  1. 如果两个bug的date_report值相同并且这两个值就是这一组中的最大值,那么哪个bug_id应该显示到报表中呢?
  2. 如果聚合函数没有匹配到任意一行,那么将显示哪个bug_id,如sum(), count(), avg()。
  3. 如果使用了两个聚合函数,那么SQL该显示哪个bug_id。
    1
    2
    3
    select product_id, max(date_report) as latest, min(date_report) as earliest, bug_id
    from bug join bug_product using (bug_id)
    group by product_id;

    这就是为什么单值规则为何如此重要。所以如果没有单值规则,数据库可靠性会降低,这意味着同样的查询可能是合理的,也可能是不合理的,而唯一的标准竟然是数据的状态!!!

如何识别反模式

    对于大多数数据库,当你违背了单值原则,会立刻返回一个错误。
USQL
    对于mysql和sqlite,有所不同,mysql在有歧义的列上会返回第一行,而sqlite会返回最后一行,其排序规则是实际的物理存储顺序来决定的。

合理使用反模式

    如上一小节提到mysql和sqlite不严谨之处,有时我们也可以利用它得到一些便利。

1
2
3
select b.report_by, a.account_name 
from bug b join account a on b.report_by = a.account_name
group by b.report_by;

    以上查询虽然违背了单值原则,但是它返回的结果确是正确的,因为report_by 和 account_name 是一对一的关系。

解决方案

    解决这个反模式的方法就是写不带歧义的SQL,下面将一一说明。

只查询功能所依赖的列

    最直接的解决办法就是将有歧义的列排除在外。很多时候那些额外的列,我们并不见得用得到。

使用关联子查询
1
2
3
4
5
6
7
select bp1.product_id, b1.date_report as latest, b1.bug_id
from bug b1 join bug_product bp1 using (bug_id)
where not exists(
select * from bug b2 join bug_product bp2 using (bug_id)
where bp1.product_id on bp2.product_id
and b1.date_report < b2.date_report
);

    这个查询简单易懂,但是它的效率是很低的。

使用衍生表

    使用衍生表比使用关联子查询效率要更高,就是先group by得到一个临时表,然后关联这个临时表

1
2
3
4
5
6
7
8
select m.product_id, m.latest, max(b1.bug_id) as latest_bug_id
from bug b1 join bug_product bp1 using(bug_id)
join (
select bp2.product_id, max(b2.date_report) as latest
from bug b2 join bug_product bp2 using(bug_id)
group by bp2.product_id
) m on bp1.product_id = m.product_id and b1.date_report = m.latest
group by m.product_id, m.latest;
使用join

    你可以创建一个外联结查询去匹配那些不存在的记录。如果匹配不存在就会返回NULL,因此,如果查询结果返回了NULL,就知道没有找到相应的记录

1
2
3
4
5
6
7
8
9
select bp1.product_id, b1.date_report as latest, b1.bug_id
from bug b1 join bug_product bp1 on b1.bug_id = bp1.bug_id
left outer join (
bug as b2 join bug_product as bp2 on b2.bug_id = bp2.bug_id
) on (
bp1.product_id = bp2.product_id and b1.date_report < b2.date_report
or b1.date_report = b2.data_report and b1.bug_id < b2.bug_id
)
where b2.bug_id is null;

    你理解这个sql需要花点时间,但是它是一个很重要的工具,它适用于大量数据查询并且可伸缩性比较关键时。

对额外的列使用聚合函数

    你可以使用另一个聚合函数,从而让它遵守单值规则

1
2
3
4
select product_id, max(date_report) as latest,
max(bug_id) as latest_bug_id
from bug join bug_product using(bug_id)
group by product_id;
连接同组所有值

    最后还有一个聚合函数可以用来处理bug_id并避免单值规则。mysql和sqlite提出一个叫做group_concat()的函数,它能将这一组中所有值连在一起作为单值返回。默认情况下使用逗号分割。

1
2
3
select product_id, max(date_report), group_concat(bug_id)
from bug join bug_product using(bug_id)
group by product_id;
product latest bug_id
USQL 2010-06-01 1234,2248
Open RoundFile 2010-02-16 3456,4077,5150
ReConsider 2010-01-01 5678,8063
    但是它不会告诉你哪个bug_id对应最新bug,他还有一个缺点,它并非SQL标准函数。其他数据库并不支持这个函数。有些数据库支持自定义函数,如postgreSQL你可以这样写:
1
2
3
4
5
6
7
8
9
10
11
create AGGREGATE GROUP_ARRAY(
BASETYPE = ANYELEMENT,
SFUNC = ARRAY_APPEND,
STYPE = ANYARRAY,
INITCOND = '{}'
);

select product_id, max(date_report) as latest,
ARRAY_TO_STRING(GROUP_ARRAY(BUG_ID), ',') as bug_id_list
from bug join bug_product using(bug_id)
group by product;
    另一些数据库不支持自定义函数,那么你只能写存储过程遍历一个非分组的查询结果,手动将每个值连在一起。

总结

    牢记单值规则,避免模棱两可的查询。