##トレジャーデータのJSSDKで収集したログでユーザーセグメントを作成する方法 ref: http://qiita.com/itochu0523/items/4113e35c587ab40c384f

file0.sql

-- Presto
SELECT
  a.td_client_id as td_client_id
 ,a.td_os as td_os
 ,'Weekly_Over5_Accesses' as segment_name
FROM
(
 SELECT
  td_client_id
 ,td_os
 ,count(1) as cnt
 FROM 
  access
 WHERE
     TD_TIME_RANGE(time,'2015-05-24 00:00:00','2015-05-31 00:00:00','JST')
 GROUP BY td_client_id,td_os
 HAVING count(1) >= 5
) as a

file1.sql

-- Presto
SELECT
  a.hour as hour
 ,a.td_client_id as td_client_id
 ,a.td_os as td_os
 ,'Hour_Over5_Access' as segment_name
FROM
(
 SELECT
  td_time_format(time,'HH') as hour
 ,td_client_id
 ,td_os
 ,count(1) as cnt
 FROM 
  access
 WHERE
     TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
 GROUP BY 
  td_time_format(time,'HH')
 ,td_client_id
 ,td_os
 HAVING count(1) >= 5 
) as a
WHERE
 a.hour BETWEEN '20' AND '23'

file2.sql

-- Hive
SELECT
  a.weekday as weekday
 ,a.td_client_id as td_client_id
 ,a.td_os as td_os
 ,'Weekday_Over5_Access' as segment_name
FROM
(
 SELECT
  weekofyear(td_time_format(time,'yyyy-MM-dd')) as weekday
 ,td_client_id
 ,td_os
 ,count(1) as cnt
 FROM 
  access
 WHERE
     TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
 GROUP BY 
 weekofyear(td_time_format(time,'yyyy-MM-dd'))
,td_client_id,td_os
 HAVING count(1) >= 5 
) as a

file3.sql

SELECT 
  td_client_id,
  td_os,
  COUNT(1) AS cnt
FROM
  access
WHERE
  TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
  AND td_path = '/201506FreeUserSeminar_marketo-summit-2015-thankyou.html'
GROUP BY
  td_client_id,
  td_os
ORDER BY
  cnt DESC

file4.sql

 WHERE td_os in ('iOS','Android')

file5.sql

td_os in ('iOS')

file6.sql

 td_os like '%Windows%'

file7.sql

SELECT
  td_ip
 ,count(distinct(td_client_id)) as cnt
FROM
  access
WHERE
  TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
GROUP BY 
  td_ip
HAVING
  count(distinct(td_client_id)) > 1