2つのtableに存在するuidのユニーク数を昨日1日分取得したい場合のクエリ
2715 ワード
この記事の目的
今回は、「2つのtableに存在するuidのユニーク数を昨日1日分取得したい場合のクエリ」を記載します。
ただいま運営中のPlayer!でTreasure Dataを利用しているため、データExportに使うHive QLを設定した際のちょっとした知識の共有。
数値の分析体制については、こちらの記事をご参考ください。iPhoneアプリの数値分析体制を一挙公開
できること
例えば、下記のようにtableが2つあるとします。
news_show
| time | uid | news_id |
|---|---|---|
| Oct 02, 2015 @ 02:39:21 AM | 1 | 10 |
| Oct 02, 2015 @ 02:19:17 AM | 2 | 20 |
| Oct 02, 2015 @ 01:18:47 AM | 3 | 30 |
picks_list
| time | uid | news_id |
|---|---|---|
| Oct 02, 2015 @ 02:35:28 AM | 1 | 11 |
| Oct 02, 2015 @ 02:08:27 AM | 4 | 22 |
| Oct 02, 2015 @ 01:56:15 AM | 5 | 33 |
その2つのtableに存在するユニークuid数を、昨日1日分取得することができます。
具体的なクエリ
HiveQL
SELECT
td_time_format(time,
'yy-MM-dd',
'Asia/Tokyo') AS d, /// 元データのtimeがUNIXなので、JSTに変換
COUNT(DISTINCT uid) AS active_users /// アクティブユーザーとして、ユニークなuid数を取得
FROM (
SELECT
uid,
time
FROM
news_show
UNION
ALL SELECT
uid,
time
FROM
picks_list
) tmp /// ()内で、picks_listとnews_showのuidとtimeを合体させている
WHERE
td_time_range(time,
td_time_add(td_scheduled_time(),
'-1d'),
td_scheduled_time()) /// クローンスケジュールを毎日夜中の12時に回すので、前日00:00 ~ 23:59まで取得できる
GROUP BY
td_time_format(time,
'yy-MM-dd',
'Asia/Tokyo')
Export結果(to Google Spreadsheet)
| d | active_users |
|---|---|
| 2015-10-01 | 5 |
まとめ
Treasure Dataの場合、Supported Hive UDFs (User Defined Functions)で検索すると、かなりの確率で悩みは解決します。
ただ、今回は、2つのtableに共通するuidを縦に並べたかった(この表現が正しいかわかりませんが)ので、探し方がイマイチわからず苦労しました。
そのため、共有しておこうと思いました。
Author And Source
この問題について(2つのtableに存在するuidのユニーク数を昨日1日分取得したい場合のクエリ), 我々は、より多くの情報をここで見つけました https://qiita.com/abe-perorist/items/b8a8fc396890f7061ba4著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .