場所分雷-復習17

2420 ワード
テキストリンク
継続率:ユーザーが登録日以降の指定日数内にどれだけのサービスを利用したかを示す指標.
固定パーセント:ユーザーが登録日の7日間以内にサービスを使用するかどうかを示す指標です.
WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , DATEADD(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
SELECT *
  FROM action_log_with_mst_users
  ORDER BY register_date
CAST:データ型を他のデータ型に変換する役割
DATEADD:指定した値で日付、時刻、タイムスタンプを追加するロール
OUTER JOIN:A、B表を接続する場合は、条件に合わないデータを表示したい場合にも使用できます.
WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , dateadd(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
, user_action_flag AS (
  SELECT user_id
       , register_date
       , SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
                   CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END))
         AS next_1_day_action
    FROM action_log_with_mst_users
    GROUP BY user_id, register_date
  )
SELECT *
  FROM user_action_flag
  ORDER BY register_date, user_id

指定した日付の翌日に、0と1のフラグでアクションがあるかどうかを示します.
WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , dateadd(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
, user_action_flag AS (
  SELECT user_id
       , register_date
       , SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
                   CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END))
         AS next_1_day_action
    FROM action_log_with_mst_users
    GROUP BY user_id, register_date
  )
SELECT register_date
     , AVG(100.0 * next_1_day_action) AS repeat_rate_1_day
  FROM user_action_flag
  GROUP BY register_date
  ORDER BY register_date
[java swing]コンピテンシー値再抽出機能の作成
サブプロジェクト実施の詳細