Edited at

2つのtableに存在するuidのユニーク数を昨日1日分取得したい場合のクエリ

More than 3 years have passed since last update.


この記事の目的

今回は、「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を縦に並べたかった(この表現が正しいかわかりませんが)ので、探し方がイマイチわからず苦労しました。

そのため、共有しておこうと思いました。