Data Lake Formation：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進行儲存，後續可直接在已存查詢中開啟，做進一步的調用及修改。