前言

好好学一次数据库,这里准备的是CMU15445的课程,这里做的是2018秋季的,先是homework1,写十个sql。

链接

正文

Sql1

送的不用管。

Sql2

Sql3

佛了,第三题就那么绕,这里我们需要计算属于每个城市的旅行站总的旅行的比例,那么这里我们分块来算,首先肯定要知道总的旅行数量这个简单select count(1) as cnt from trip,然后是每个城市的旅行的数量,这里的定义是如果旅行的起发地或者结束地是这个城市,那么这次旅行就属于这个城市,也就是说一个旅行可能属于两个城市,注意这里如果起发地和结束地是一个地方的话只算一次,所以要用distinct,所以我们需要一个城市-旅行id的结果集select city, count(distinct(id)) as cnt from trip, station where trip.start_station_id = station_id or trip.end_station_id = station_id group by city,最后用每个城市旅行的数量city_trip_cnt除以总的旅行数量,保留四位小数。

Sql4

这个跟上面类似,我们要找出每个城市中最受欢迎的站点,那么我们首先需要得到每个城市每个站点的数量的集合,类似city-station_name-trip_cnt,然后在算每个城市中旅游次数最多的站点是哪个,计算每个城市每个站点的旅游数量select city, station_name, count(distinct(id)) as trip_cnt from trip, station where trip.start_station_id = station_id or trip.end_station_id = station_id group by station_name, city记得group by station_id要按站点分类,然后跟第三题一样计算每站的旅行数量,最后按城市分类group by city来计算每个城市最受欢迎的站点,按照城市升序排列。

Sql5

这题应该是最难的了,需要仔细分析下,首先我们看下两个函数的用法

首先我们需要得到start_time和end_time的日期的集合,这里可以用下with as语句(子查询)

with dates as (select date(start_time) as tdate from trip union select date(end_time) as tdate from trip bike_id <= 100)

因为我们只需要统计出发或者结束的日期,注意这里用union可以去重。

接着我们需要分析一下The average bike utilization的定义,对于除数很简单就是select conut(distinct(bike_id)) from trip where bike_id <= 100,然后分析下被除数应该为分为被减数(结束时间)和减数(开始时间),由题目的条件可知我们需要对这两个时间和那日的时间进行一下比较,对于某个tdata,当end_time < tdate+1的时间时取end_time,当end_time > tdate+1的时间时我们应该取tdate+1,所以我们应该用min(),同理对于开始时间我们就应该取较大的要用max,所以最后被除数应该就是

strftime('%s', min(datetime(end_time), datetime(tdate, '+1 day'))) - strftime('%s', max(datetime(start_time), datetime(tdate))))

最后我们可以得出这个utilization

注意我们这里来到了这题的关键所在,我们现在要将trip表和我们之前定义的dates联合起来(这是类似一个笛卡尔积),每一行的trip都会与每一个tdate进行组合,但是这里我们要注意,只有当start_time和end_time这个区域的一部分属于[tdate,tdate+1]之间时才是符合条件的(否则tdate就不再这个trip的时间中,上面计算的utilization就没有意义了),也就是

当然别忘了还有个限制条件bike_id <= 100,然后按照tdate分类,按照avg_duration降序,limit取前十

Sql6

这题比上一题简单很多,题目要求我们查找存在错误的数据,当一辆自行车被两个旅行同时使用时,就是错误。首先我们用with as选出bike_id在100到200之间的trip方便后面的使用。然后我们需要用两个相同的表来查,所以将use_trip定义为former_trip和latter_trip然后按照要求打印就行了,最后加上限制条件两个的bike_id相等,时间有重叠,只打印前面一个的结果,然后按照要求排序就行了。

Sql7

比较简单,别忘了having cat > 1的限定条件哦

Sql8

我们先用with as得到一个每种天气的天数,然后就是正常按照条件搜索,注意要限制好旅行的地区,从而对应的天气。

Sql9

要求输出在同一行,那么我么可以用子查询的方法来把它分成两个部分。

Sql10

总结

homework的sql太复杂了,一般也不会有人这么写吧。。。好的继续学习了