データ分析備忘録 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

2016年3月4日金曜日

【R】月ごとで表示（月次・月別の集計）

【目的】　日付データから月だけ抽出する
【方法】　epitoolsパッケージのmonth関数を使う
【補足】　 library(epitools)が必要

"2014-02-01"
から月だけを取り出すには、

> library(epitools)
> time <- as.Date("2014-02-01")
> month <- as.month(time)
> month$month
[1] "2"

とすればできる

他にも年数だけを取り出すことも可能
以下、month関数の活用例

> month
$dates
[1] "2014-02-01"

$mon
[1] 2

$month
[1] "2"

$stratum
[1] 16116
attr(,"origin")
[1] "1970-01-01"

$stratum2
[1] 16116
Levels: 16085 16116 16144

$stratum3
[1] "2014-02-15"

$cmon
[1] 1 2 3

$cmonth
[1] "1" "2" "3"

$cstratum
[1] 16085 16116 16144
attr(,"origin")
[1] "1970-01-01"

$cstratum2
[1] "2014-01-15" "2014-02-15" "2014-03-15"

$cmday
[1] 15 15 15

$cyear
[1] "2014" "2014" "2014"

参照サイト

epitoolsのガイド：　https://cran.r-project.org/web/packages/epitools/epitools.pdf
inside-R：　http://www.inside-r.org/packages/cran/epitools/docs/as.month

2015年8月28日金曜日

R 指定した列を選択（select）

【目的】　指定した列を抽出する
【方法】　select(df, v1, v2)
【補足】　library(dplyr)が必要

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

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

df.x

> df.x
  id item
1  1    a
2  2    a
3  3    b
4  4    b

select(df.x, id)

> select(df.x, id)
  id
1  1
2  2
3  3
4  4

R 条件に合うデータを抽出する（filter）

【目的】　条件に合うデータを抽出する
【方法】　filter(df, v == 0 )
【補足】　library(dplyr)が必要

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

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

df.x

> df.x
  id item
1  1    a
2  2    a
3  3    b
4  4    b

filter(df.x, item == "a")

> filter(df.x, item == "a")
  id item
1  1    a
2  2    a

2015年7月7日火曜日

R 条件を指定して任意の行を抽出（filter）

【目的】　任意の行を抽出する
【方法】　filter(df, x == 1)
【補足】　library(dplyr)が必要

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

filter(df.x, name == "a")

> filter(df.x, name == "a")
  id name num
1  2    a  11
2  3    a  11
3  4    a  11

filterを使って簡単に移動平均を求める方法
http://tips-r.blogspot.jp/2015/01/r_1.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

登録: 投稿 ( Atom )

データ分析備忘録 in Tokyo

注目の投稿

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