Data Lake Formation:DLF データ探索クイックスタート：タオバオユーザー行動分析

データのプレビューおよび確認

以下のステートメントを実行して、生データを確認し、レコード数をカウントします。SET spark.sql.legacy.timeParserPolicy=LEGACY により、Spark SQL のレガシ日付フォーマットパーサーを使用して time カラムを正しく解析します。

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 テーブルでは、行動タイプが数値コード（1～4）で格納されており、日付と時刻が 1 つのカラムに結合されています。以下のステートメントにより、この生データを分析に適した形式に変換します。

• USING PARQUET により、出力をカラム指向 Parquet 形式で保存します。これは CSV と比較してデータ圧縮率が高く、分析クエリの実行速度も向上します。

• PARTITIONED BY (date) により、クエリで特定の日付をフィルターする際に Spark が日付パーティション全体をスキップできるようになります。

• CASE WHEN ブロックにより、数値の行動コードを読みやすいラベル（click、collect、cart、pay）に変換します。

• date_format により、time カラムを分離し、date、hour、day_of_week の各カラムを作成します。これにより、後続のクエリで日付単位および時間単位の分析が可能になります。

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 -- 1=月曜日 ... 7=日曜日
  FROM `dlf_demo`.`user`;

-- 結果の確認
SELECT * FROM `demo_db`.`user_log` LIMIT 10;

以下の図に結果を示します。

ユーザー行動の分析

以下に示す 3 つの分析は、すべて前ステップで作成した user_log テーブルを対象にクエリを実行します。

1. ファネル分析：データセット全体において、ユーザーが各行動タイプを実行した回数をカウントします。これにより、クリック → お気に入り登録 → カート追加 → 支払いというファネルにおけるユーザー離脱箇所が明らかになります。

   -- 実行時間：約 13 秒
   SELECT
     behavior, COUNT(*) AS total
   FROM `demo_db`.`user_log`
   GROUP BY behavior
   ORDER BY total DESC;

   以下の図に結果を示します。

   

2. 日次関与度分析：ユニークビジター（UV）および行動回数を日付および曜日ごとに集計します。これにより、アクティビティが平日または週末にピークを迎えるかを把握できます。

   説明

   本チュートリアル用にデータセットが抽出されているため、曜日のパターンは実際のタオバオトラフィックを反映していない可能性があります。クエリ構文自体は正しいため、有意義なトレンドを得るには完全なデータセットに対して適用してください。

   -- 実行時間：約 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. 人気上位商品カテゴリ：item テーブルと user_log を結合し、購入回数が最も多い上位 10 商品カテゴリを特定します。behavior = 'pay' でフィルターすることで、閲覧活動ではなく実際の購入のみを対象とします。

   -- 実行時間：約 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. （任意）結果のダウンロード：クエリ結果を CSV ファイルとして保存します。

   1. データ探索 ページの右上隅にある パス設定 をクリックします。[OSS パスの選択] ダイアログボックスで、結果保存パス を既存のフォルダに設定するか、新しいフォルダを作成します。

   2. クエリを実行した後、実行履歴 タブに移動し、[操作] 列の ダウンロード をクリックして結果ファイルをダウンロードします。ファイルは OSS コンソールからもアクセス可能です。

5. （任意）SQL ステートメントの保存：保存 をクリックして SQL ステートメントを保存します。保存済みのクエリは、今後の実行のために 保存済みクエリ タブで利用可能です。