数据湖构建：DLF数据探索快速入门-淘宝用户行为分析

预览并检查数据

在左侧菜单栏，单击数据探索，在SQL查询框中输入以下语句，查看文件中的数据信息。

SET spark.sql.legacy.timeParserPolicy=LEGACY;
-- 预览数据
SELECT * FROM `demo_db`.`user` limit 10;
SELECT * FROM `demo_db`.`item` limit 10;

-- 用户数
SELECT COUNT(DISTINCT user_id) FROM `demo_db`.`user`;

-- 商品数
SELECT COUNT(DISTINCT item_id) FROM `demo_db`.`item`;

-- 行为记录数
SELECT COUNT(*) FROM `demo_db`.`user`;

结果如下：

数据预处理

我们对原始数据进行一定的处理，以提高数据的可读性，并提升分析的性能。

• 创建新表user_log，表格式为Parquet，按日期分区。

• 将behavior_type转换成更易理解的字符串信息：1-click; 2-collect; 3-cart; 4-pay。

• 将日志+时间的格式拆分为日期和小时两列，再加上周信息，便于分别做日期和小时级别的分析。

• 过滤掉不必要的字段，并将数据存入新表user_log。

后续我们会基于新表做用户行为分析。

CREATE TABLE `demo_db`.`user_log` 
USING PARQUET
PARTITIONED BY (date)
AS SELECT 
    user_id, 
    item_id,
    CASE 
      WHEN behavior_type = 1 THEN 'click'
      WHEN behavior_type = 2 THEN 'collect'
      WHEN behavior_type = 3 THEN 'cart'
      WHEN behavior_type = 4 THEN 'pay'
    END AS behavior, 
    item_category, 
    time,
    date_format(time, 'yyyy-MM-dd') AS date,
    date_format(time, 'H') AS hour,
    date_format(time, 'u') AS day_of_week
  FROM `dlf_demo`.`user`;
  
 -- 查看运行后的数据  
SELECT * FROM `demo_db`.`user_log` limit 10; 

结果如下：

用户行为分析

1. 首先，我们基于漏斗模型，对所有用户从点击到加购、收藏、购买的转化情况进行分析。

   -- 漏斗分析耗时13秒
   SELECT
     behavior, COUNT(*) AS total
   FROM `demo_db`.`user_log`
   GROUP BY behavior
   ORDER BY total DESC

   结果如下：

   

2. 然后对一周内每天的用户行为做统计分析。

   -- 用户行为分析耗时14秒
   SELECT 
     date, day_of_week,
     COUNT(DISTINCT(user_id)) as uv,
     SUM(CASE WHEN behavior = 'click' THEN 1 ELSE 0 END) AS click,
     SUM(CASE WHEN behavior = 'cart' THEN 1 ELSE 0 END) AS cart,
     SUM(CASE WHEN behavior = 'collect' THEN 1 ELSE 0 END) AS collect,
     SUM(CASE WHEN behavior = 'pay' THEN 1 ELSE 0 END) AS pay
   FROM `demo_db`.`user_log`
   GROUP BY date, day_of_week
   ORDER BY date

   结果如下（由于数据集经过裁剪，对于工作日和非工作日的结果有失真）。

   

3. 最后，我们结合商品表，分析出数据集中最受欢迎的是个商品品类

   -- 销售最多的品类耗时1分10秒
   SELECT item.item_category, COUNT(*) AS times
   FROM `demo_db`.`item` item
     JOIN `demo_db`.`user_log` log
     ON item.item_id = log.item_id
   WHERE log.behavior='pay'
   GROUP BY item.item_category
   ORDER BY times DESC
   LIMIT 10;

   结果如下：

   

4. （可选）下载分析结果。

   DLF提供将分析结果以CSV文件的形式下载的功能，启用该功能需要提前设置分析结果的保存路径（OSS路径）。设置后，查询结果会被保存到该路径下。

   1. 单击数据探索页面右上方的路径设置，设置结果存储路径，可以选择已有文件夹或者新建文件夹。

      

   2. 设置完成后，运行SQL查询，在运行历史页签的下载结果直接下载，也可以直接通过OSS路径访问和下载该文件。

      

5. （可选）保存SQL。

   通过单击保存，可以将本次分析用到的SQL进行保存，后续可直接在已存查询中打开，做进一步的调用及修改。