日拱一卒，伯克利教你写SQL，CS61A作业12

TechFlow-承志

发布于 2022-09-21 12:08:18

7710

发布于 2022-09-21 12:08:18

文章被收录于专栏：TechFlow

作者 | 梁唐

出品 | 公众号：Coder梁（ID：Coder_LT）

大家好，日拱一卒，我是梁唐。

你有学过数据库或者SQL语法吗？你有那种SQL语法全都知道，但真要写的时候依然抓瞎的体验吗？

SQL涉及到数据处理的方方面面，不仅仅是后端数据库会用到，也是算法工程师、数据分析师的必备技能。因此，如果你也曾有过类似体验，或者想要巩固一下自己对于SQL的理解。那么你算是来对了，这一次的伯克利CS61A的作业就关于SQL的应用。

再次安利一下伯克利CS61A这门课程，它是我见过最好的计算机入门课程。囊括十分丰富的内容：Python、算法、数据结构、机器学习、数据可视化，甚至还用上了编译原理的知识，写了一个Lisp的解释器。

这次的作业关于SQL，需要我们用SQL根据题目要求筛选出指定的数据。这让我想到了当年我刚去阿里实习的时候，那时候实习生每天做数据都需要用到SQL。但我当时SQL才刚刚初学语法，没有实战过几次，实习的时候战战兢兢，经常写得不够优雅，要是当时我实习之前能够做过这几题，肯定就不会那么狼狈了。

其实SQL本身的语法并不难，只需要有合适的场景，有合适的题目进行练习，几道题的思考和训练就可以大大提升熟练度和理解。因此对于SQL语法初学乍练的同学来说，这四道题是一个很好的思考和练习的机会，非常建议大家尝试。

课程链接：https://www.bilibili.com/video/BV16W411W76H

原始文档：https://inst.eecs.berkeley.edu//~cs61a/sp18/hw/hw12/

Dog Data

这次作业用到的数据是手动创建的，数据量很少，只有几条。

这份数据是关于宠物狗的，有各种类型的狗的信息，比如毛发、身高等等。这老师非常喜欢狗，讲课的视频当中还晒了他养的狗狗家族：

数据如下：

CREATE TABLE parents AS
  SELECT "abraham" AS parent, "barack" AS child UNION
  SELECT "abraham"          , "clinton"         UNION
  SELECT "delano"           , "herbert"         UNION
  SELECT "fillmore"         , "abraham"         UNION
  SELECT "fillmore"         , "delano"          UNION
  SELECT "fillmore"         , "grover"          UNION
  SELECT "eisenhower"       , "fillmore";

CREATE TABLE dogs AS
  SELECT "abraham" AS name, "long" AS fur, 26 AS height UNION
  SELECT "barack"         , "short"      , 52           UNION
  SELECT "clinton"        , "long"       , 47           UNION
  SELECT "delano"         , "long"       , 46           UNION
  SELECT "eisenhower"     , "short"      , 35           UNION
  SELECT "fillmore"       , "curly"      , 32           UNION
  SELECT "grover"         , "short"      , 28           UNION
  SELECT "herbert"        , "curly"      , 31;

CREATE TABLE sizes AS
  SELECT "toy" AS size, 24 AS min, 28 AS max UNION
  SELECT "mini"       , 28       , 35        UNION
  SELECT "medium"     , 35       , 45        UNION
  SELECT "standard"   , 45       , 60;

即使你使用的原始数据发生变化，你表的逻辑也必须是正确的。比如，你被要求筛选出所有h字母开头的狗的信息。你应该这么实现：

SELECT name FROM dogs WHERE "h" <= name AND name < "i";

而不是假设只有给定的数据，直接选出结果：

SELECT "herbert";

测试数据当中会针对这种情况做出改动。

Q1: Size of Dogs

某种宠物规范规定标准贵宾犬的身高在45到60厘米之间。

sizes表描述了每一个品种的身高的上限和下限，创建size_of_dogs表，它当中有两列，一列是狗的名称name，一列是它的size。

-- The size of each dog
CREATE TABLE size_of_dogs AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

-- Example:
SELECT name FROM size_of_dogs WHERE size="toy" OR size="mini";
-- Expected output:
--   abraham
--   eisenhower
--   fillmore
--   grover
--   herbert

开发完成之后，进行测试：

python3 ok -q small

答案

我们要做的就是根据每只狗的身高，找到它对应的size。

做法很简单，将dogs和sizes进行join，之后根据height和min, max筛选出合适的组合。

CREATE TABLE size_of_dogs AS
  SELECT name, size from dogs, sizes where height > min and height <= max;

Q2: By Height

创建表by_height，它有一列是所有拥有parent狗的名称，根据parent的身高进行从高到低排序。

-- All dogs with parents ordered by decreasing height of their parent
CREATE TABLE by_height AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

比如fillmore的parent eisenhower，身高是35，应该排在grover之前，它的parent是fillmore，身高只有32。

parent身高相同的狗的排序可以随意，比如barack和clinton都应该排在最后，而它们之间的顺序并不重要。

-- Example:
SELECT * FROM by_height;
-- Expected output:
--   herbert
--   fillmore
--   abraham
--   delano
--   grover
--   barack
--   clinton

开发完成之后测试：

python3 ok -q parent-height

答案

我们需要用每只狗parent的身高进行排序，所以我们需要知道parent的身高，所以需要将parents表和dogs表进行join，由于我们要拿的是parent的身高，所以join的条件是parent = name，也就是parent和身高表中的name相等。

最后根据身高进行排序即可，使用order by语法。

CREATE TABLE by_height AS
  SELECT child as name from parents, dogs 
  where parent = name 
  order by height desc;

Q3: Sentences

为每个狗兄弟组合创建一个句子，兄弟狗组合的定义是拥有一样size的狗。

并且每对狗兄弟组合，用它们的size来描述，可以参考下方的注释：

-- Filling out this helper table is optional
CREATE TABLE siblings AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

-- Sentences about siblings that are the same size
CREATE TABLE sentences AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

每个狗兄弟组合只能出现一次，并且确保组合中的狗名字以字母序排序，比如barack and clinton而不是clinton and barack。

样例：

-- Example:
SELECT * FROM sentences;
-- Expected output:
--   barack and clinton are standard siblings
--   abraham and grover are toy siblings

提示：

首先，创建siblings表（我们已经提供了基础的框架），比较狗兄弟的size可以简化问题

如果你让一张表join它自己，你可以在from语句后面加上as给它起一个别名

你可以使用 ||来拼接字符串

开发完成之后进行测试：

python3 ok -q size-siblings

答案

思路是先把所有可能构成答案的狗兄弟组合找出来，然后再join上size进行过滤。

这里有一个潜在限制条件，因为要找的是狗兄弟，所以要保证找出来的两只狗拥有相同的parent，其次要保证这两只狗的字典序。

理解了这一点之后，其实就是一个很简单的表自我join的例子：

-- Filling out this helper table is optional
CREATE TABLE siblings AS
  SELECT a.child as first, b.child as second 
  from parents as a, parents as b
  where a.parent = b.parent and a.child < b.child;

找出所有潜在的狗兄弟之后，我们再join上狗的size，保证两只狗兄弟的size相同。

-- Sentences about siblings that are the same size
CREATE TABLE sentences AS
  SELECT a.first || ' and ' || a.second || ' are ' || sa.size || ' siblings'
  from siblings a, size_of_dogs sa, size_of_dogs sb
  where a.first = sa.name and a.second = sb.name and sa.size = sb.size;

Q4: Stacks

狗狗们也可以玩叠罗汉（stack），身高较小的狗狗可以站在较高的狗狗身上。假设不考虑狗狗叠罗汉之后的身高损失，创建一张有两列数据的表，它存储所有4只狗叠在一起并且高度达到170厘米的组合。

第一列是以逗号分割的狗狗的名称，第二列是狗狗叠在一起之后的总高度，将这些组合按照总高度进行生序排序。

-- Ways to stack 4 dogs to a height of at least 170, ordered by total height
CREATE TABLE stacks_helper(dogs, stack_height, last_height);

-- Add your INSERT INTOs here

CREATE TABLE stacks AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

在每个狗罗汉当中，每只狗只能出现一次，并且从上到下狗狗按照身高排序，即身高最高的狗狗在最下方。我们可以假设没有身高相同的狗狗。

-- Example:
SELECT * FROM stacks;
-- Expected output:
--   abraham, delano, clinton, barack|171
--   grover, delano, clinton, barack|173
--   herbert, delano, clinton, barack|176
--   fillmore, delano, clinton, barack|177
--   eisenhower, delano, clinton, barack|180

推荐可以按照下列思路来完成：

从给定的stack_helper空表开始，它用来存储所有狗罗汉的信息，包括狗罗汉的名称（狗狗的名称以逗号分割），总高度以及最下方狗狗的身高。可以使用最下方狗狗的身高来保证狗罗汉是按照狗狗的身高排列的。
使用insert into语句来初始化stack_helper表，将一只狗当成罗汉来进行初始化。你可以使用下列语法将一张表中的数据插入另外一张表：

sqlite> CREATE TABLE ints AS
   ...>   SELECT 1 AS n UNION
   ...>   SELECT 2      UNION
   ...>   SELECT 3;
sqlite> INSERT INTO ints(n) SELECT n+3 FROM ints;
sqlite> SELECT * FROM ints;
1
2
3
4
5
6

使用一只狗的罗汉创建两只狗组成的罗汉，重复这个过程，直到我们拥有四只狗的罗汉
记得使用stack_helper表中的数据来填充stacks表

完成之后进行测试：

python3 ok -q stack

答案

其实题目已经把整个思路和过程都已经阐述得很明白了，我们使用stacks_helper表作为中间表，存储叠罗汉过程中的中间结果。

先创建只有一只狗的罗汉，再join狗狗表，创建所有两只狗可能组成的罗汉组合。再依次类推，创建三只和四只狗的罗汉组合。

创建新的狗罗汉时需要保证最下方狗狗的身高大于last_height即当前罗汉中最下方狗的身高，之后我们将stacks_helper表中所有身高总和大于等于170的结果筛选出来，再按照身高进行排序即可。

-- Ways to stack 4 dogs to a height of at least 170, ordered by total height
CREATE TABLE stacks_helper(dogs, stack_height, last_height);

-- Add your INSERT INTOs here

insert into stacks_helper
select name, height, height
from dogs;

insert into stacks_helper
select dogs || ', ' || name, stack_height + height, height
from stacks_helper, dogs
where height > last_height;

insert into stacks_helper
select dogs || ', ' || name, stack_height + height, height
from stacks_helper, dogs
where height > last_height;

insert into stacks_helper
select dogs || ', ' || name, stack_height + height, height
from stacks_helper, dogs
where height > last_height;


CREATE TABLE stacks AS
  SELECT dogs, stack_height
  from stacks_helper
  where stack_height >= 170
  order by stack_height;

到这里，这门课所有的project和作业都算是肝完了，前两天有同学提醒我漏掉了lab。我会在之后补上，感谢大家的阅读和支持。

喜欢本文的话不要忘记三连~

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-05-01，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法