假如你的老板想知道哪些项目还处于活跃状态，哪些项目已经停止了。他让你生成一个每个项目最后一个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;
    另一些数据库不支持自定义函数，那么你只能写存储过程遍历一个非分组的查询结果，手动将每个值连在一起。

总结

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