MySQLしかろくに使ってこなかった人間が知って便利だった Presto & Treasure Data 小ネタ ref: http://qiita.com/inuscript/items/061a6ab5fd6564e8686c

file0.sql

SELECT *
FROM access
WHERE 
  url_extract_host(referer) = "google.com"
  -- 正規表現でやるとこんな感じ
  -- regexp_like(referer, '.*google.com/.*')

file1.sql

SELECT * FROM (
    SELECT * FROM (
        SELECT * AS day
           :
    )
)

file10.sql

EXPLAIN (FORMAT GRAPHVIZ)
SELECT * FROM ...

file11.sql

SELECT *
FROM access TABLESAMPLE BERNOULLI(1)

file12.sql

SELECT 
  some_name,
  map_keys(
    histogram(some_type)
  )
FROM foo
GROUP BY some_name

file13.sql

SELECT
  :
WHERE 
  -- 1日間のデータなら -1d。一ヶ月なら-30dとか。
  TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d', 'JST'), 
    null
    'JST'
  )

file14.sql

SELECT
  TD_PARSE_AGENT(user_agent)['os_family']
  TD_PARSE_AGENT(user_agent)['os_major']
  TD_PARSE_AGENT(user_agent)['os_minor']
  TD_PARSE_AGENT(user_agent)['ua_family']
  TD_PARSE_AGENT(user_agent)['ua_major']
  TD_PARSE_AGENT(user_agent)['ua_minor']
  TD_PARSE_AGENT(user_agent)['device']
FROM access

file15.sql

WITH a AS (
  SELECT 
    TD_PARSE_AGENT(user_agent)['ua_family'] AS family,
    TD_PARSE_AGENT(user_agent)['ua_major'] AS version
  FROM 
    access
),
b AS (
  SELECT
    if(family = 'IE', family || version, family) AS browser
  FROM b
),

file16.sql

SELECT 
  *
  TD_PARSE_AGENT(user_agent)['ua_family'] AS family,
  TD_PARSE_AGENT(user_agent)['ua_major'] AS version
FROM
  access
WHERE TD_PARSE_AGENT(user_agent) IN ['pc', 'smartphone']

file17.sql

SELECT TD_SESSIONIZE(time, 3600, ip_address) as session_id
FROM ...
ORDER BY ip_address, time -- ソートする

file2.sql

WITH a AS (
    SELECT * FROM some_table
),
b AS (
    SELECT * FROM a
),
c AS (
    SELECT * FROM b
)
SELECT * FROM c

file3.sql

WITH a AS (
    SELECT * FROM some_table
),
b AS (
    SELECT * FROM a
),
c AS (
    SELECT * FROM a
)
SELECT * FROM c, b

file4.sql

WITH 
config AS (
  SELECT 2 AS val_a
),
tbl AS (
  SELECT
    val_a,
    some_value * val_a -- some_value * 2
  FROM some_table, config
)
SELECT * FROM tbl

file5.sql

WITH config AS (
  SELECT
    TD_TIME_ADD(TD_SCHEDULED_TIME(),'-10d', 'JST') AS start_time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(),'-1d', 'JST') AS end_time
)
SELECT *
FROM access, config
WHERE TD_TIME_RANGE(time, config.start_time, config.end_time, 'JST')

file6.sql

SELECT a || '_' || b 
FROM *

file7.sql

-- IEだけversion付けたいみたいなとき
SELECT
  if(family='IE', family || version, family) 

file8.sql

SELECT coalesce(a, b)

file9.sql

SELECT 
  CASE ua
    WHEN 'IE' THEN family || '_'|| version
    WHEN 'Chrome' THEN family
    WHEN 'Firefox' THEN family
    WHEN 'Safari' THEN family
    WHEN 'Opera' THEN family
    ELSE 'Other'
  END