Posted at

Treasure Dataで特定のセグメントのユーザーのうち、特定の行動をとったUU数をdailyで取得するクエリ

More than 3 years have passed since last update.


この記事の目的

特定のセグメントのユーザーのうち、ある特定の行動を行うUU数をdailyで取得するためのクエリのサンプルです。

JOIN ONを使い、2つのtableをつなぐのがポイントになります


Table 構造


user( 特定のセグメント )

time
id
path

Oct 07, 2015 @ 02:39:21 AM
1
email

Oct 07, 2015 @ 02:19:17 AM
2
email

Oct 07, 2015 @ 01:18:47 AM
3
facebook


follow( 特定の行動 )

time
uid
friend_id

Oct 02, 2015 @ 02:35:28 AM
1
2

Oct 02, 2015 @ 02:08:27 AM
2
3

Oct 02, 2015 @ 01:56:15 AM
4
1


実際のクエリ


HiveQl

SELECT 

d,
COUNT(DISTINCT you) AS NUMBER
FROM (
SELECT
d,
you,
COUNT(you)
FROM
(
SELECT
td_time_format(users.time,
'yyyy-MM-dd',
'Asia/Tokyo') AS d,
users.id AS you,
follow.friend_id AS they
FROM
users
JOIN
follow
ON (
users.id = follow.uid
)
WHERE
td_time_range(users.time,
td_time_add(td_scheduled_time(),
'-1d'),
td_scheduled_time())
) t1
GROUP BY
d,
you
HAVING
COUNT(you)> 1
) t2
GROUP BY
d
ORDER BY
d ASC