nPathクリックストリーム データの例 - Advanced SQL Engine - Teradata Database

この文は、例で使用するクリックストリーム データの入力テーブルを作成します。

CREATE MULTISET TABLE clicks1 (
  userid INTEGER,
  sessionid INTEGER,
  pageid INTEGER,
  category INTEGER,
  ts TIMESTAMP FORMAT 'YYYY-MM-DDbHH:MI:SS',
  referrer VARCHAR (256),
  val FLOAT
) PRIMARY INDEX ( userid );

この文は、各行のpageidとシーケンスで次の行のpageidを取得します。

SELECT dt.sessionid, dt.pageid, dt.next_pageid FROM nPath (
  ON clicks1 PARTITION BY sessionid ORDER BY ts
  USING
  Mode (OVERLAPPING)
  Pattern ('A.B')
  Symbols (TRUE AS A, TRUE AS B)
  Result (
    FIRST(sessionid OF A) AS sessionid,
    FIRST (pageid OF A) AS pageid,
    FIRST (pageid OF B) AS next_pageid
  )
) AS dt;

行ごとに、この呼び出しは、あるシーケンスで先行する行の数をカウントします(現在の行を含む)。開始行より前の行でパターンを一致させる必要があるためORDER BY句ではDESCを指定し、一方でセマンティクスでは開始行より後の行でパターンが一致するように指示します。

SELECT dt.sessionid, dt.pageid, dt.countrank FROM nPath (
  ON clicks1 PARTITION BY sessionid ORDER BY ts DESC
  USING
  Mode (OVERLAPPING)
  Pattern ('A*')
  Symbols (TRUE AS A)
  Result (
    FIRST (sessionid OF A) AS sessionid,
    FIRST (pageid OF A) AS pageid,
    COUNT (* OF A) AS countrank
  )
) AS dt;

このクエリーは、pageid 50で始まりpageid 80に進むかあるいはカテゴリ9またはカテゴリ10内のページに進むユーザー クリック パスを検索し、パス内の最後のページのpageidを検索し、ページ80への閲覧数をカウントし、最後のページごとに最大カウントを返して、出力をソートします。このクエリーでは、5ページより少ないパスと、カテゴリがゼロ未満のページは無視されます。

SELECT dt.last_pageid, MAX(dt.count_page80) FROM nPath (
  ON (SELECT * FROM clicks1 WHERE category >= 0)
    PARTITION BY sessionid ORDER BY ts
  USING
  Pattern ('A.(B|C)*')
  Mode (OVERLAPPING)
  Symbols (
    pageid = 50 AS A,
    pageid = 80 AS B,
    pageid <> 80 AND category IN (9,10) AS C
  )
  Result (
    LAST(pageid OF ANY (A,B,C)) AS last_pageid,
    COUNT (* OF B) AS count_page80,
    COUNT (* OF ANY (A,B,C)) AS count_any
  )
) AS dt WHERE dt.count_any >= 5
GROUP BY dt.last_pageid
ORDER BY MAX(dt.count_page80);