日拱一卒,伯克利教你写SQL

发布网友发布时间：2024-10-04 01:00

共1个回答

热心网友时间：2024-10-08 01:19

大家好，日拱一卒，我是梁唐。本文始发于公众号Coder梁

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

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

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

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

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

课程链接

原始文档

Github

Dog Data

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

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

数据如下：

CREATE TABLE parents ASSELECT "abraham" AS parent, "barack" AS child UNIONSELECT "abraham", "clinton" UNIONSELECT "delano" , "herbert" UNIONSELECT "fillmore" , "abraham" UNIONSELECT "fillmore" , "delano"UNIONSELECT "fillmore" , "grover"UNIONSELECT "eisenhower" , "fillmore";CREATE TABLE dogs ASSELECT "abraham" AS name, "long" AS fur, 26 AS height UNIONSELECT "barack" , "short", 52 UNIONSELECT "clinton", "long" , 47 UNIONSELECT "delano" , "long" , 46 UNIONSELECT "eisenhower" , "short", 35 UNIONSELECT "fillmore" , "curly", 32 UNIONSELECT "grover" , "short", 28 UNIONSELECT "herbert", "curly", 31;CREATE TABLE sizes ASSELECT "toy" AS size, 24 AS min, 28 AS max UNIONSELECT "mini" , 28 , 35UNIONSELECT "medium" , 35 , 45UNIONSELECT "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 dogCREATE TABLE size_of_dogs ASSELECT "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 ASSELECT 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 parentCREATE TABLE by_height ASSELECT "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 ASSELECT child as name from parents, dogs where parent = name order by height desc;Q3: Sentences

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

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

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

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

样例：

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

提示：

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

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

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

开发完成之后进行测试：

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

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

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

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

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

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

SELECT name FROM dogs WHERE "h" <= name AND name < "i";4Q4: Stacks

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

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

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

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

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

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

从给定的stack_helper空表开始，它用来存储所有狗罗汉的信息，包括狗罗汉的名称（狗狗的名称以逗号分割），总高度以及最下方狗狗的身高。可以使用最下方狗狗的身高来保证狗罗汉是按照狗狗的身高排列的。

使用insert into语句来初始化stack_helper表，将一只狗当成罗汉来进行初始化。你可以使用下列语法将一张表中的数据插入另外一张表：

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

使用一只狗的罗汉创建两只狗组成的罗汉，重复这个过程，直到我们拥有四只狗的罗汉

记得使用stack_helper表中的数据来填充stacks表

完成之后进行测试：

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

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

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

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

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

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

原文：https://juejin.cn/post/7097848399881306120