SQL分析阿里云淘宝电商数据

本文使用SQL语句对50w条淘宝用户数据进行分析。
数据库：SQL Server。

一、提出问题

1、分析目的

本文期待通过挖掘淘宝用户数据价值，研究用户活跃度，购买行为，商品销量等情况，对提升电商关键业务指标提出建议。
具体讨论问题如下：

1. 从点击到购买，用户流失情况如何？
2. 日常用户活跃度如何？具体的时间段用户活跃度呢？
3. 特殊日期（周末）用户活跃度情况如何？
4. 用户分时间段下单情况？哪个时间段下单最多？
5. 核心用户有哪些，特点如何？
6. 用户点击与购买之间是否存在关联？
7. 哪些商品，商品类目最受欢迎，订单量最高?

2、分析框架

二、数据准备

1、数据来源

数据源自阿里云天池数据库——User Behavior Data from Taobao for Recommendation

2、数据描述

数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。
详见下图：

3、数据预处理

3.1截取数据

由于100w条数据量太大，使用查询语句运转太慢，所以改为选取其中50w，运行速度需6s。

--使用Rand()进行随机抽取
select top 500000 * into data from UserBehavior
order by RAND() 

3.2设置列名及属性

进入数据管理器，选择设计，修改列名及属性如下图：

如果出现报错，可以参考此文 SQL SERVER不能修改表的解决方法（增加字段、修改字段名）

3.3时间戳的转化

--转化时间戳为时间
select DATEADD(S, 时间戳+ 8 * 3600,'1970-01-01 00:00:00' ) from data
ALTER TABLE data ADD 时间  datetime NULL;
update data
set 时间 = DATEADD(S, 时间戳+ 8 * 3600,'1970-01-01 00:00:00' )

--新增列，日期
ALTER TABLE data ADD 日期  date NULL;
update data
set 日期 = Datename(year, 时间)+'-'+Datename (month,时间)+'-'+Datename(day,时间) 
from data

--新增列，时间
ALTER TABLE data ADD 时分秒 time(0) NULL;
update data
set 时分秒 = Datename(HOUR, 时间)+':'+Datename (MINUTE,时间)+':'+Datename(SS,时间) 
from data

3.4数据检查

去除重复项：

select distinct