データ分析備忘録 in Tokyo: ユニーク

ラベル ユニーク の投稿を表示しています。すべての投稿を表示

2016年9月15日木曜日

【トレジャーデータ】JOINを使って複数のテーブルからデータ抽出する複雑なSQLを書く

目的：自社サイトの記事別UUを抽出し、かつ、他のサイトも閲覧している重複UU及び自社サイトを閲覧して実際に商品を購入しているUUの抽出

結果のイメージ

article_name	自社サイトUU	自社サイトと他社サイトの重複UU	自社サイトと購買者の重複UU
山とは？	1000	40	5
川とは？	100	20	2
海とは？	10	1	1

＊後に下記のコードはWITH句を使って改善した
【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善

SELECT
mysite.article_name,
MAX(mysite.my_uu) AS my_uu,
MAX(othersite.other_uu) AS other_uu,
MAX(order.order_uu) AS oder_uu
FROM (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS my_uu
    FROM
      データベース名.mysite_log
    WHERE
-- Sfariを除外したいときに記述
      browser NOT LIKE '%Safari%'
-- 期間を指定
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
    GROUP BY
      article_name
) mysite
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS othersite_uu
    FROM
      データベース名.mysite_log
    WHERE
      browser NOT LIKE '%Safari%'
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
      AND user_id IN(
        SELECT
          user_id
        FROM
          othersite_log
        WHERE
          TD_TIME_RANGE(time,
            '2016-06-01',
            '2016-07-01',
            'jst')
      )
    GROUP BY
      article_name
) othersite
ON (
    mysite.article_name = othersite.td_article
)
JOIN
(
-- 下記はサイト閲覧ユーザID（user_id）と商品購入者ID（order_id）が異なる場合を想定
-- そのためuser_idとorder_idが紐付けられたテーブル（userid_orderid_matching）を利用
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS order_uu
    FROM
      データベース名.mysite_log
    WHERE
      browser NOT LIKE '%Safari%'
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
      AND user_id IN(
        SELECT
          user_id
        FROM (
            SELECT
              user_id,
              order_id     -- 商品購入者のID
            FROM
--商品購入者IDをサイト閲覧ユーザIDの照合テーブル
              データベース名.userid_orderid_matching
          ) matching
        JOIN (
            SELECT
              order_id
            FROM
              データベース名.order_log
            WHERE
              TD_TIME_RANGE(time,
                '2016-06-01',
                '2016-07-01',
                'jst')
          ) order_log
          ON (
            matching.order_id = order_log.order_id
          )
      )
    GROUP BY
      article_name
) order
ON (
    mysite.article_name = order.article_name
)
GROUP BY
mysite.article_name
ORDER BY
mysite_uu DESC

◇補足
期間設定を下記に変更することで、
過去30日間のデータを毎日自動集計することも可能。
＊Scheduleの設定は別途必要

WHERE
TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(),
      '-30d'),
    TD_SCHEDULED_TIME(),
    'JST')

◇参照サイト
「組合せ計算」から出発する，データ分析のための「JOIN」
*トレジャーデータのアカウントがないと見れない
https://support.treasuredata.com/hc/ja/articles/215646908

SELECT構文：JOINを使ってテーブルを結合する
http://rfs.jp/sb/sql/s03/03_3.html

2015年7月3日金曜日

R：複数列のユニークデータを抽出する（重複除去）

【目的】　複数列のユニークデータを抽出する（重複データを除去）
【方法】　distinct(df, x)
【補足】　library(dplyr)が必要

#テスト用データフレームを作成

v.x <- c(1,2,3,4)
v.x1 <- c("x","a","a","a")
v.x2 <- c("11","11","11","11")
df.x <- data.frame(id = v.x, name = v.x1, num = v.x2)

df.x

> df.x
  id name num
1  1    x  11
2  2    a  11
3  3    a  11
4  4    a  11

distinct(df.x, name)

> distinct(df.x, name)
  id name num
1  1    x  11
2  2    a  11

R ユニーク数をカウントする
http://mototeds.blogspot.jp/2015/06/r_29.html

2015年6月29日月曜日

R：複数列のユニーク数をカウントする（重複除去してカウント）

【目的】　ユニーク数を数える（重複を除去して数える）
【方法】　n_distinct(v.x)
【補足】　library(dplyr)が必要

#テスト用データフレームを作成

v.x <- c(1,2,3,4)
v.x1 <- c("x","a","a","a")
v.x2 <- c("11","11","11","11")
df.x <- data.frame(id = v.x, name = v.x1, num = v.x2)

df.x

> df.x
  id name num
1  1    x  11
2  2    a  11
3  3    a  11
4  4    a  11
>

df.x %>%
summarize(id_u = n_distinct(id),name_u = n_distinct(name),num_u = n_distinct(num))

> df.x %>%
+   summarize(id_u = n_distinct(id),name_u = n_distinct(name),num_u = n_distinct(num))
  id_u name_u num_u
1    4      2     1

R ユニークデータを抽出する
http://mototeds.blogspot.jp/2015/07/r.html

登録: 投稿 ( Atom )

データ分析備忘録 in Tokyo

注目の投稿

【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道）