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

この記事の目的

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