Joins with grouping
Databases & SQL Lesson 11 1:59 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Everything in this lesson you have already met.
这一课里的每样东西你都已经见过了。
A join makes one wide table.
连接做出一张更宽的表。
GROUP BY sorts that into piles.
GROUP BY 把它分成一堆一堆。
An aggregate turns each pile into one row each.
聚合函数把每一堆各变成一行。
And ORDER BY puts the answers in some order.
而 ORDER BY 把答案排出个顺序来。
The only new thing here is the sequence — and getting the sequence right is what makes a report come out.
这里唯一新的东西是这个次序—— 而把次序弄对，正是一份报表能出来的原因。
Step one gives the joined table from last lesson: three rows, with Ada twice.
第一步给出上一课那张连接后的表：三行，其中 Ada 出现两次。
Step two is to group those three rows by customer, which gives a pile of two for Ada and a pile of one for Cara.
第二步是把这三行按客户分组， 于是 Ada 得到一堆两行，Cara 得到一堆一行。
Notice the order matters: you must join before you group, because before the join there is no customer name to group by.
注意次序是有意义的：你必须先连接再分组， 因为在连接之前，根本没有客户名字可以拿来分组。
Here is the whole query.
这是完整的查询。
It looks long, but read it in that order — join, group, then summarise — and it is four familiar pieces stacked up.
它看起来很长，但按那个次序去读——先连接、再分组、然后汇总—— 它就是四个熟悉的部件叠在一起。
And out comes the report: Ada, two orders, sixty pounds fifty.
报表就出来了：Ada，两单，60.50。
Cara, one order, ninety-five.
Cara，一单，95。
One row per customer, which is what was asked for.
每个客户一行，正是题目要的。
One detail in that query is worth pausing on: the GROUP BY names customer's id, not customer's name.
那条查询里有一个细节值得停一下： GROUP BY 用的是客户的 id，不是客户的 name。
Names are not unique.
名字不是唯一的。
Two different people can both be called Ada, and grouping by name would add their spending together into one row.
两个不同的人可以都叫 Ada， 而按名字分组会把他们的消费加到同一行里。
Grouping by the key never does that, because the key is unique by definition.
按主键分组永远不会这样，因为主键按定义就是唯一的。
Four things to take with you.
带走四点。
One: join first, so the aggregate has both tables to work on.
第一：先连接，好让聚合函数两张表的数据都能用上。
Two: then GROUP BY, to make one pile per customer.
第二：然后 GROUP BY，让每个客户成为一堆。
Three: group by the key, because two people can share a name.
第三：按主键分组，因为两个人可能同名。
Four: name every summary column with AS.
第四：每一个汇总列都用 AS 命名。
Now run the tasks below.
现在去做下面的题。