题目：围绕以下链接中的数据，用SQL辅助完成分析，并将结果在Excel中呈现。

https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1

一、数据集概览

1、数据规模

2、时间范围

2017年11月25日——2017年12月3日（共9天）

3、数据指标

有5个（均为随机抽取）：用户ID、商品ID、商品类目ID、行为类型、时间戳

其中的行为类型指标下分4个子指标。它们分别是：点击（商品详情页pv）、购买（buy）、加入购物车（cart）、喜欢 （fav）。

二、分析思路

1、总体目标

通过抽样、归因、验证（由于数据集仅含9天的数据，而可靠验证要求大量历史数据，所以此次分析跳过验证环节），达到预测消费者（用户）行为、提出建议、指导实践，并调整策略，追求利润最大化。

2、研究范围

鉴于数据规模庞大，所以需要抽取一部分，而非使用全量数据。出于研究分析目的（避免泛泛而谈，导致什么都没说清），还需缩小研究范围，定义研究内容。

2.1 研究内容

因为数据集中所含所有（5个）指标的核心是行为类型指标，所以研究方向为用户行为。由于用户行为分析的目的是：通过用户画像，预测其购买行为，追求利润最大化。因此，用户行为分析的重点与核心是购买行为。基于购买行为的用户画像的维度有4：交互（发生路径）、时间、用户（人）、商品（物）。为了进一步细化购买行为的具体内容，用下图（购买行为框架图）辅助说明：

2.2 假设

1）此次研究的对象为：电子商务，简称电商，且指狭义上的电商（E-Commerce）。其重点围绕基于互联网的电子交易，即实现整个贸易过程中各阶段贸易活动的电子化。本文以后提到的电商均指狭义电商。2）电商应用模式为：企业与消费者之间的模式（B2C）。

2.3 排除在此次分析范围之外的因素举例

由于数据局限，此类因素主要围绕7个方面，分别是：1）电商。具体包括：政策变化、供需情况、季节因素（淡旺季）、消费潮流、行业布局（是否存在垄断）、竞争对手情况、资本运作方式（如：投资人、资本构成）、产业链情况（如：商品供应商的生产及议/定价能力）。2）商铺特征。如：商铺规模、商铺品牌、商铺信誉、商铺售后服务质量和内容、商铺业务类型（如：代发货、批发、贴电商品牌但找他人制造）【注：因商铺业务类型的差异，会导致利润大小/构成不同】、是否参与仓储、发货等活动。3） 用户（消费者）特征。例如：用户年龄、性别、兴趣、所在城市、所处区域、是否为注册用户、注册时间、历史消费行为、社交数据（如：留言、点赞、对商品的评价）。4）商品特质。如：商品价格、售卖数量、款式、商品品牌影响力、商品售后服务质量和内容、商品（或其品类）是耐用品（导致低频购买），还是快消品（导致高频购买）。5）点击分析信息。如：（对特定商品网页的）浏览路径（先后顺序）、浏览时长（各页面停留时长）、购买体验（如：网页是否迅速跳转、商品信息是否醒目清晰）。6）广告活动。如：推送内容、限时秒杀、拼团购等。7）虚假数据。如：刷单、空包。

2.4 无法选取（而必须随机抽取）的因素列举

为了进一步缩小研究范围，需要具体到考虑可用/将用指标。发现排除了以上7种因素之后，使用现有的数据集，不得不模糊处理，而无法做到细化/聚焦（细节）的因素有3类。分别是：1）（商铺/商品）所处行业、2）（商铺）规模大小、3）（所售卖的）商品类别/具体商品。

3、（研究结论中）建议的方向

此次研究将要提出的建议（即要解决的研究问题），集中在4个层面。如：1）渠道效果评估。包括：（4种子行为类型之间的）转化渠道、转化率、购买行为发生的时间、购买人数、（对特定商品品种或特定商品的）购买次数、（特定商品类目下的）购买商品偏好。2）营销活动、产品策略指导。包括促销活动、广告推广、所售卖商品数量或品种的增减。3）电商经营效率提升。即多购进受欢迎的商品，扩大收入；并削减对转化率低、转化时间长、用户购进次数低商品的投入，降低成本。

三、指标选取

1、时间限定

为了说明不同时间的购买行为特征，所以抽取一个工作日和一个周末作为代表。在此取周三作工作日的样本，周六作周末的样本。（因为在5个（周一至周五）不同工作日中选出任意一天的代表意义不明显；而选用周六而非周日，是因为考虑到周日后紧接着工作日（周一），所以周六作为周末的代表意义较大。）数据集含2017年11月25日——2017年12月3日（共9天）的数据。其数据起始时点2017年11月25日为周六，终止时点2017年12月3日为周日。因此，选取2017年11月29日（周三）和2017年12月2日（周六）（以保证选取的工作日和周末在同一周）。

2、意义限定

遵循二八法则，上图（2.1 研究内容处的购买行为框架图）中提到的活跃时段，暂定义为80%的购买行为发生的时段。非活跃时段，暂定义为20%的购买行为发生的时段。畅销商品，暂定义为购买行为中对应的排名前3的商品。滞销商品，暂定义为购买行为中对应的排名倒数前3的商品。

3、指标（设计）框架

因此，遵循从总体到分部（细节）的原则，指标就变成了：取2017年11月29日（周三）和2017年12月2日（周六）两个全天的数据。分别在这两天内，沿袭上面的购买行为框架图并整理，得到购买行为指标框架如下：

当然，上图（购买行为指标框架）仅为暂定框架。如果分析过程中，发现异常数据情况，需要酌情做进一步细分、挖掘、解释以理解数据意义。

四、数据清洗

1、（从客户端）导入数据

1.1 思路

目标是：导入2017年11月29日（周三）和2017年12月2日（周六）的数据。希望尽量导入满足此次分析要求的最小量数据，提高导入（数据）效率。

（限制）条件：数据集内数据均为随机抽取，无法在导入前明确限定日期。必须先导入/下载部分数据，再加以判断。

数据（范围）回顾：数据集含2017年11月25日——2017年12月3日（共9天）的数据。其数据起始时点2017年11月25日为周六，终止时点2017年12月3日为周日。即9天（数据）中，包含了一个周一到周日的整周，和一个周末（含周六、周日共2天）。这个多出来的周末，指的是2017年11月25日（周六）和2017年11月26日（周日）。

所以，先导入10万条（数据）记录，看是否包含了目标日期。

1.2 从源表导入数据

具体大约分为9个分步。

1）创建表格，新建字段

红框处为新建表的表名。绿框处为需要特别留意的部分。虽然，时间戳在类别上属于时间，但设置成时间类型，会导致显示不正常。因此，要先设置成varchar（字符型）字段，导入后再转换为时间类型。

2）选择数据源

两红框处均需保留图中所示的样子。因为数据量巨大，所以第1个红框内设置为csv文件（非Excel文件），以便导入。另：进行这一步（选择数据源）之前，必须先用Excel（程序）打开这个csv文件。

3）选择分隔符（保持默认选项）

4）选择导入数据记录的数量

蓝框处定义了数据的起始位置，这一行要用来放字段名称。绿框处设置了（除字段名称外的）数据（导入）起始行。红框处指明（导入的）数据（所在的）结束行。因为前面已经创建了新表（表头已存在），所以这里仅自第2行始，导入10万条数据记录。

5）选择目标表

蓝色处无法勾选，因为目标表已存在。

6）定义源表和目标表之间的栏位对应

7）选择导入模式

保留红框处默认选项。

8）导入数据

红框处表明数据完整无误，导入成功。

9）导入的10万条数据记录的部分（截图）示意

1.3 关于重复值和缺失值

因为数据导入时将用户ID、商品ID、时间戳设成主键，杜绝了重复值出现。由于设置了NOT NULL，缺失值也不复存在。

1.4 将时间戳转化为日期格式

因为数据量巨大，不同时间戳区别不明显。除非导出全量数据并一一判定，则无法找全2017年11月29日（周三）和2017年12月2日（周六）这两天的数据。所以，放弃这一在限定日期，研究购买行为的思路。改为观察已导入的这10万条记录中的购买行为。

1.5 在表中添加3列：日期时间、日期、时间

1.6 检查数据范围是否与数据集所说（2017年11月25日——2017年12月3日（共9天）的数据）相符。

红框处属于异常值

查看异常值

删除异常值

再次检查数据范围

发现和数据范围2017年11月25日——2017年12月3日（共9天）的数据相符。

在删掉在9天数据（数据范围）之外的数据之后，查看9个日期的分布情况

统计合格数据共有99955条

考虑到篇幅太长，第五部分构建模型和第六部分结论与建议（参见下篇）