Grouping with GROUP BY

Databases & SQL Lesson 8 2:05 English narration · English + 中文 subtitles burned in

space play · ←/→ 5s · j/l 10s · f fullscreen · ,/. speed

Chapters

Transcript

Last lesson an aggregate folded the whole table into one number. 上一课里，聚合函数把整张表折成了一个数字。

Usually you want one number per something — per form, per customer, per month. 而通常你想要的是"每一个什么"对应一个数字—— 每个班一个、每个客户一个、每个月一个。

GROUP BY does the first half of that: it sorts the rows into piles that share a value. GROUP BY 做的是这件事的前一半：它把行分成一堆一堆，同一堆里的值相同。

Three students in 11A, two in 11B, so two piles. 11A 有三个学生，11B 有两个，所以是两堆。

And notice that nothing has been summarised yet — every original row is still there. 再注意：目前还什么都没有被汇总——原来的每一行都还在。

Then the aggregates run — but once per pile instead of once for the table. 然后聚合函数开始运行——但是每一堆各跑一次，而不是整张表跑一次。

Three 11A rows become one row saying three students, average ninety point three three. 三行 11A 变成一行，说：三个学生，平均分 90.33。

Two 11B rows become one row of their own. 两行 11B 变成它们自己的一行。

That is the shape to hold on to: one row per group, and the column you grouped by is what names each one. 这就是要记住的形状：每组一行，而你用来分组的那一列，正是给每一行命名的东西。

Now the distinction people ask about. 现在说大家会问的那个区别。

There are two filters, and they run at different moments. 有两个过滤器，而它们在不同的时刻运行。

WHERE drops rows first, before anything is grouped. WHERE 先丢掉行，在任何分组发生之前。

Then the groups are made. 然后各个组被建立起来。

Then HAVING drops whole groups. 然后 HAVING 丢掉整个整个的组。

That order is why WHERE cannot test COUNT star — at the moment WHERE runs, no group exists yet to count. 这个先后顺序就是为什么 WHERE 不能测试 COUNT(*)—— 在 WHERE 运行的那一刻，还没有任何一个组可以去数。

Here it is doing its job. 这是它在干活的样子。

After grouping there are two rows, one per form. 分组之后有两行，每个班一行。

Add HAVING COUNT star at least three and the 11B group goes, because it only has two students. 加上 HAVING COUNT(*) >= 3，11B 那一组就没了，因为它只有两个学生。

Note that it is removed as a GROUP — both of its rows disappear together, which is exactly what a WHERE could not have done. 注意它是作为一个"组"被移除的—— 它的两行是一起消失的，而这恰恰是 WHERE 做不到的事。

Four things to take with you. 带走四点。

One: GROUP BY gives one summary row per group. 第一：GROUP BY 让每一组得到一行汇总。

Two: WHERE filters rows before the groups are made. 第二：WHERE 在分组建立之前过滤行。

Three: HAVING filters groups afterwards, and it can test COUNT star. 第三：HAVING 在之后过滤组，而且它可以测试 COUNT(*)。

Four: any plain column beside an aggregate must be in GROUP BY. 第四：任何和聚合函数并排的普通列，都必须写进 GROUP BY。

Now run the tasks below. 现在去做下面的题。