Data Lake Formation:DLF データ探索 - Taobao ユーザー行動分析

データのプレビューと確認

左側のナビゲーションペインで、[データ探索] をクリックします。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`;

次の図は、結果を示しています。

データの前処理

生のデータを前処理して、データの可読性と分析パフォーマンスを向上させることができます。

• Parquet 形式で user_log テーブルを作成し、日付でテーブルをパーティション化します。

• behavior_type の値を理解しやすい文字列に変換します。有効な値: 1、2、3、4。1 はクリック、2 はお気に入りに追加、3 はカートに追加、4 は支払いを示します。

• ログの time 列を date 列と hour 列に分割し、days of week 列を追加します。これは、日付と時間のレベルでデータを分析するのに役立ちます。

• 不要なフィールドを除外します。次に、必要なデータを 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. 商品テーブルに基づいて、データセット内で最も人気のある上位 10 個の商品カテゴリを分析します。

   -- 分析には 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. [データ探索] ページの右上隅にある [パスの設定] をクリックします。[OSS パスの選択] ダイアログボックスで、[結果の保存パス] パラメータを指定します。既存のフォルダを選択するか、フォルダを作成できます。

      

   2. 設定が完了したら、SQL ステートメントを実行します。[実行履歴] タブで、レコードの [アクション] 列の [ダウンロード] をクリックしてファイルをダウンロードできます。また、OSS コンソールでファイルにアクセスしてダウンロードすることもできます。

5. (オプション) SQL ステートメントを保存します。

   [保存] をクリックして、前の手順で実行した SQL ステートメントを保存できます。その後、必要に応じて、[保存済みクエリ] タブで保存済みの SQL ステートメントを実行できます。