Day08–利用JavaWeb+大数据实现纽约市共享单车地图服务

原创声明：近期发现在关键字搜索我的文章时，发现很多文章被其他网站直接一字不落的拿去。在这里我先谢谢大家对我写的内容的肯定，我也会继续努力。但是，很多网站连标明都未标明来源，这就让人很可气，毕竟每一篇文章都是我一个字一个字敲出来的。我也不是在抱怨什么，只是希望那些某些站长在“转载”他人文章时标明来源，注明作者。不要只是简单的一个来源互联网。万分感谢。

此外，本站文章如未标注文章来源，则均为作者原创，请转载附上原文链接，并声明作者。保护原创，从我做起，谢谢合作。

如果是我的项目的话，源码会全部上传到我的Github，大家有需要的可以拿去研究，也可以我们一起交流探讨。最后，祝愿每一个默默努力的人都可以取得理想的结果。

-----------吃土少年8

需求跟进：

1. 创建结果表，分析出每日的骑行总数

   • 我们之前导入到数据库的tb_trip_1904中保存了19年4月的全部骑行数据，本用例需要从这些数据中，统计出4月每一天的总骑行量，然后进行可视化

   • 因为是历史数据，不会发生变化，只需进行一次查询。设计一张结果表，用于保存信息，后续用户需要查看统计结果时，可以直接查询该表。

   • 设计tb_day_count表

     • trip_count 字段 日骑行数
     • trip_day 字段 日期
     • trip_year 字段 年份
     • trip_month 字段 月份

   • 如图：

   • 

   • SQL语句如下：

   • 原始数据 -> 按照年分组 -> 按照月分组 -> 按照日分组 -> 统计数据条目。

   • 可以省略年月分组，因为都是19.04的数据。

   • 该表中，骑行时间用starttime字段表示，该字段同时包含了年月日时分秒的信息，如果想要按照日期进行分组，需要从starttime中截取出日期的值。

   • “2019-04-01 12:30：59.8600” -> 01

   • 采用MySQL内置函数day（str） 截取出天。

   • 有内置函数需求就去查MySQL函数即可。

   • select year(starttime),month(starttime),day(starttime),count(*) from tb_trip_1904 group by year(starttime),month(starttime),day(starttime);
     

   • 也可以起别名

   • select year(starttime) as trip_year,
            month(starttime) as trip_month,
            day(starttime) as trip_day,
            count(*) as trip_count 
     from 
            tb_trip_1904 
     group by 
            year(starttime),month(starttime),day(starttime);
     

   • 将查询结果保存到表中，在上述语句之前加一句insert intotb_day_count即可

   • insert into tb_day_count
     select year(starttime) as trip_year,
            month(starttime) as trip_month,
            day(starttime) as trip_day,
            count(*) as trip_count 
     from 
            tb_trip_1904 
     group by 
            year(starttime),month(starttime),day(starttime);
     

   • 作业：对tb_trip_1904进行分析，写出下列需求的SQL语句，并对结果进行截图，如果结果数据条数很多，截图中仅包含前5条记录即可：

     1. 计算每一天不同性别的用户的骑行数量

        • insert into day_gender_count
          select day(starttime) as trip_day,
                 count(*) as trip_count
          from
                 tb_trip_1904
          group by
                 day(starttime),gender;
          

     2. 统计一个月30天中，每小时的总骑行数量(即4月1-30日0点-1点骑行数量的和，4月1-30日1-2点骑行数量的和，以此类推)

        • insert into hour_count
          select hour(starttime) as trip_hour,
                 count(*) as trip_count
          from   
                 tb_trip_1904
          group by
                 hour(starttime);
          

     3. 排名前5的热门起始站点的名称，当月的骑行次数

        • insert into order_station
          select start_station_name,
                 count(*) as trip_count
          from
                 tb_trip_1904
          group by
                 start_station_name order by count(*) desc limit 5;
          

     4. 年龄在18-30之间的各性别用户的平均骑行时长

基础知识：

1. SQL语句
   • SQL：结构化查询语言，是关系系型数据库的通用操作语言。程序员必备基础技能。
   • SQL不是编程语言，是数据库的操作语言，是非过程性的语言。
   • database ----- 一般与一个项目相对应
   • table ----------- 一般与一个模块相对应
   • SQL提供了对上述3个级别的增删改查(也称CRUD，Create，Read，Update，Delete)操作的语句。
   • SQL操作database
     • 创建
     • create database 库名
     • 删除
     • drop database 库名
     • 修改
     • alter 库名 …
     • 查看库的列表
     • show databases
     • 查询建库语句
     • show create database 库名
     • 使用库
     • use 库名
   • SQL操作table
     • 创建
     • create table 表名(
     • 字段名 字段类型 [约束] [默认值] [comment ’ ']，
     • …
     • );
     • 删除
     • drop table表名
     • 修改
     • alter 表名 …
     • 查看当前库下表的列表
     • show tables
     • 查询建表语句
     • show create tablse 表名
     • 查看表的结构
     • desc 表名
   • SQL操作表中数据
     • 向表中插入数据
       • insert into 表名 [(字段列表)] values(值的列表)[,(值的列表)…] --[]表示可以没有
       • insert into 表名 values(xxx,xxxx,xxx…) -----省略字段列表，但是values列表中的顺序不能混和字段不能少
       • insert into 表名 (字段1，字段3，字段2) values (xxx,xxx,xxx) — 字段顺序和值顺序对应好就行
       • 企业会硬性要求有字段名
       • insert into 表名 (字段列表) values(值的列表),(值的列表)… -----一次性插入多条记录
     • 删除数据
       • delete from 表名 --删除表中所有记录
       • delete from 表名 where 条件 —删除符合条件的语句
     • 改数据
       • update 表名 set 字段名=值 [where 条件] — 没条件就全改
     • 查询
       • select * from 表名 [where 条件] -----数据少可以这么干，多了就是个灾难
       • select 字段列表 from 表名 -----企业中要求必须使用字段列表
     • where子句
       • select username，age from student where id>4 ----查询id大于4的数据的username和id
     • order by 子句
       • select * from student order by age [asc] —不指定的话默认从小到大（升序）
       • select * from student order by age desc —降序
   • 聚合函数（对表中的一列数据进行统计）
     • select count(*) from 表名 ------求数据的行（条）数
     • count(字段名) -------该字段的值不为null的数据的条数
     • sum(字段名) ---- 求该字段数据的和
     • avg(字段名) ------- 求该字段下的数据的平均值
     • select avg(字段名) from 表名 where 条件 —指定条件
     • max(字段名) --最大值
     • min(字段名) --最小值
     • 需求：查询1班的平均分 select avg(score) from student2 where class=‘class1’;
   • 分组函数
     • group by
     • select class,avg(score) from student2 group by class;
     • 上述语句的执行逻辑是先获取student2表中所有的数据，然后按照class字段的值将表中数据分成多张子表，每张子表中，所有的数据的class字段的值是相同的，然后在分别对每个子表中的数据进行求平均值操作。
     • 练习：统计tb_trip_1904表中不同性别的人的骑行总次数。
     • select gender, count(*) from tb_trip_1904 group by gender;
     • 特别注意！！！！在分组查询中，select后面仅能够出现聚合函数或分组字段，未参加分组的字段，不能出现在select后面。
     • select class,avg(score) from student2 group by class;
     • 以上述语句为例，select后面可以出现avg(score)，也可以出现class字段，但是不能出现id和username，因为没有参加分组。
     • 注意！！！在group by之后，可以包含多个字段，以逗号分割。实际执行逻辑是先按第一个字段的值将表格拆分成多个子表，然后再对第二个字段的值，对每个子表进行进一步拆分，以此类推。
     • 例子：
     • 
   • limit子句（MySQL方言，限定查询到的数据的条数）
     • select * from 表名 limit 1 --仅显示一条
     • select * from 表名 limit 2,1 --跳过前两条，显示一条