結構ちゃんと調べて知っておくと無駄なクエリ書かなくて済むのがある。
Case 1. X日前からの日付指定したい時: TD_TIME_ADD ,TD_TIME_RANGE, TD_SCHEDULED_TIME
集計する時 X日前までほしい、とかいう要件多いけど、「今日が何日で何日前までで・・・」とかカレンダー見るのめんどい。
下記を覚えとけばいつでも実行タイミングからX日前とかで取得できるので何も考えずにコピペで使いまわせる。
TD_SCHEDULED_TIME でクエリの実行タイミングを起点とし、TD_TIME_ADDで1日前にして、それをTD_TIME_RANGEで区間としている
SELECT
:
WHERE
-- 1日間のデータなら -1d。一ヶ月なら-30dとか。
TD_TIME_RANGE(time,
TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d', 'JST'),
null,
'JST'
)
覚えづらいのでだいたいどっかからコピペしてくる。
Case 2. URLのパースしたい(presto): URL function
prestoの標準関数
regexp_likeとかで頑張りたくなるけどurl_extract_***という関数が用意されている。
超便利。
SELECT *
FROM access
WHERE
url_extract_host(referer) = "google.com"
-- 正規表現でやるとこんな感じ
-- regexp_like(referer, '.*google.com/.*')
Case 3. UAからのブラウザ判定したい：TD_PARSE_AGENT
ブラウザをUAから簡単に判定してくれる関数。超便利。
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
|os_family|os_major|os_minor|ua_family|ua_major|ua_minor|device|
|---|---|---|---|---|---|---|---|
|Windows 7 | null | null|IE|11 | 0 |Other|
|Android|5|0|Chrome Mobile|47|0|SC-04F|
この関数使うときだいたいIEだけバージョン取りたいとかなるのでWITHと組み合わせてこんなふうにする
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 a
)
SELECT * FROM b
Case 4. ボット判定したい TD_PARSE_AGENT(agent)['category']
TD_PARSE_AGENTは、ボット判定などにも便利
アクセスログ解析だとBotも厄介な問題。だがcategoryを利用すると、これが結構いい感じに解決出来る。
値としては "pc", "smartphone", "mobilephone", "appliance", "crawler", "misc", "unknown"のどれかが返ってくるようなので、これを判定すると良い
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']
Case 5. X分間のアクセスを同一としてセッション数で取りたい: TD_SESSIONIZE
第三引数をキーとして、第二引数で指定した時間以内のアクセスであれば同一のセッションとしてIDを生成してくれる。
滞在時間とか知りたい時良さそう。
TD_SESSIONIZEする値は、ソートされている必要があるので注意（これちゃんと読んでなくて
SELECT TD_SESSIONIZE(time, 3600, ip_address) as session_id
FROM ...
ORDER BY ip_address, time
-- ↑ソートしないと正しく出ない！
Case 6. arrayのunique化(presto)したい : map_keys, histogram
ハック的。array_aggで特定のカラムの値をarray<T>型にしてくれのだが、同値が大量に並んだ状態になる
SELECT
some_name,
map_keys(
histogram(some_type)
)
FROM foo
GROUP BY some_name
Case 7. 大きいテーブルに対して試しで実行したい(presto) TABLESAMPLE
でかいテーブルに対して、そのままクエリ投げて遅くて試しづらい時はTABLESAMPLEが使える。
SELECT *
FROM access TABLESAMPLE BERNOULLI(1)
入れる値はパーセンテージらしいので、上記なら1%。