【BigQuery】複雑な緯度経度の範囲を指定する方法

方法は？

WHERE文に下記の命令を記載するだけ。緯度経度は10進数。最後に、最初に指定した「経度1 緯度1」を入力することで範囲が閉じられる。

ST_COVERS(ST_GEOGFROMTEXT('POLYGON((経度1 緯度1,経度2 緯度2,～,経度1 緯度1))'),
ST_GEOGPOINT(経度のカラム名,緯度のカラム名))

例

SELECT
  time,latitude,longitude
FROM
  data
WHERE
  --error回避のため日本の範囲を指定
  latitude BETWEEN 23
  AND 46
  AND longitude BETWEEN 123
  AND 148
  --複雑な範囲を指定 
  AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.598915 35.687744,
    139.598909 35.687584,139.598915 35.687744))'),
    ST_GEOGPOINT(longitude,
      latitude))

エラー回避（ST_GeogPoint failed: Latitude must be between -90 and 90 degrees.）のために日本の範囲を先に指定している。

参考サイト

• 標準 SQL の地理関数

【トレジャーデータ：presto】任意の値や文字列を入れた列をつくる方法

入れたい値 AS 新しい列名
とすることで任意の値を入れた列を作ることができる。

• 数字の場合

SELECT
1 AS x
FROM data_table

• 文字列の場合

SELECT
'なにか' AS x
FROM data_table

【2020年】明けましておめでとうございます。

明けましておめでとうございます。

昨年は、ドコモに残るかメルカリでサービスのグロースやっていくかunerryで新たなチャレンジするかを決める決断の年でした。決断にあたり多くの方にアドバイスを頂きました。この場をかりて御礼申し上げます。

決断の結果、今日からunerryの社員になります。unerryでは、リアルとネットのデータ連携の推進やリアルとネットのデータを統合した新たなデータサービスやプロダクトの研究開発を進めていきます。

今までは個別のサービスのグロースを考えてきましたが、これからは社会全体やユーザ一人ひとりの生活の向上を考えた仕事をしたいと思っています。

本年もどうぞよろしくお願い申し上げます。

（データ連携のご相談いつでも歓迎！）

おまけ
ビーコンユーザだいたい友だち

【トレジャーデータ】トレジャーデータの結果をタブローオンラインに出力する

トレジャーデータの結果をタブローに出力

トレジャーデータ（treasure data）の結果をタブローオンライン（tableau online）に出力する方法をご紹介します。できてみればすごい簡単なんですが、私自身は何も知らずにやった結果、いろいろ調べたりエラーに振り回されて結構時間かかってしまいました。。

１．Catalogからtableauを選択してAuthenticationsを作成

catalogからtableauを選択

トレジャーデータにログインして、catalogからtableauを選択します。

２．ホスト名とタブローのログイン情報を入力

Server versionで「Tableau Online」を選択

タブローオンラインにログインした際のURLとして下記のものがあるとします。

https://*****.online.tableau.com/#/site/*この部分はサイトID*/home

ここでホスト名は、「*****.online.tableau.com」です。UsernameとPasswordは、タブローにログインするものと同じです。Use SSL?とVerify server certificate?は任意でチェックしてください。今回は、タブローオンラインに出力するので、Server versionをTableau Onlineにします。

３．Export Results To に上記で作ったAuthenticationsを選択

上記作成のAuthentications「test_tableau_online」を選択

SQLの出力先として上記で作成したtest_tableau_online（名前は任意）を選択します。

４．Export Resultsの内容を設定する

Site IDに注意

ここでエラーが沢山でて苦労しました（泣）。Site IDのところですが、TDのサイトに「Site ID The URL of the site to sign in to, it’s required for Tableau Online」と記載されていたので、URL全部書くのかなと勘違いしてエラー（[ERROR] (main): HTTP 301 Moved Permanently）が出まくりました。実際は、下記のタブローオンラインのURLを例にすると、site直下の「*この部分はサイトID*」がSite IDになります。

https://*****.online.tableau.com/#/site/*この部分はサイトID*/home

５．トレジャーデータでSQLを実行するとタブローにデータが出力されます

もし、401エラー（[ERROR] (main): HTTP 401 Unauthorized）が出たら下記の原因が考えられます。

• authentication設定画面で、host, user, passwordなどが間違っていた場合
• result output設定画面で、datasource, site, project nameが間違っていた場合
• tableau側でprojectとdatasourceの権限設定により発生する場合

私の場合は、hostとsiteの設定が正しくなくてエラーが出まくりました。。

補足

tableauはタイムスタンプ型でないと時間データとして扱うことができません。そこで、CASTでタイムスタンプ型に変換します。

例：時間文字列（例：2019-10-17 08:21:36）をCASTでタイムスタンプ型に変換するクエリ

SELECT 

  CAST(

    time AS TIMESTAMP

  ) AS "time"

FROM

  log LIMIT 1

参考サイト

• Tableau Online - Export*
• サイトのエクスポートまたはインポート（tableau）
• Tableau Server/OnlineへTreasureDataのクエリ実行結果を出力する

【トレジャーデータ：Presto】自動でMAUを集計する（TD_TIME_TRUNC）

トレジャーデータ（Presto）で日数を計算する方法としてTD_TIME_ADDがありますが、月や年単位の計算をサポートしていません。そこで、TD_DATE_TRUNCを使って月単位の期間を指定してMAUを自動で集計する方法を紹介します。

◇TD_DATE_TRUNCの使い方（トレジャーデータサイトより）

1. SELECT
2.   TD_TIME_FORMAT(time,'yyyy-MM-dd HH:mm:ss','JST') AS t,
3.   TD_DATE_TRUNC('minute',  time, 'JST' ) AS mnt,
4.   TD_DATE_TRUNC('hour',    time, 'JST' ) AS h,
5.   TD_DATE_TRUNC('day',     time, 'JST' ) AS d,
6.   TD_DATE_TRUNC('week',    time, 'JST' ) AS w,
7.   TD_DATE_TRUNC('month',   time, 'JST' ) AS m,
8.   TD_DATE_TRUNC('quarter', time, 'JST' ) AS q,
9.   TD_DATE_TRUNC('year',    time, 'JST' ) AS y
10. FROM access_log
11. WHERE TD_TIME_RANGE(time,'2017-01-15','2017-01-16','JST')
12. ORDER BY t DESC
13. LIMIT 1

実行結果（トレジャーデータサイトより）

◇TD_DATE_TRUNCを使ったMAU自動集計例

timeは”2019-10-17 08:21:36.000”のような時間文字列を想定。そのため、TD_TIME_PARSEでタイムスタンプに変換しています（TD_TIME_PARSEとは）。また、トレジャーデータのスケジュール機能（TD_SCHEDULED_TIME）で月初に自動で実行するようにしているため、月初の日付に-1dとすることで前月の月初から月末まで集計できるようにしています。

SELECT
  TD_TIME_FORMAT(TD_TIME_PARSE(time,
      'UTC'),
    'yyyy-MM',
    'UTC') AS time,
  COUNT(DISTINCT user_id) AS UU
FROM
  all_beaconlog
WHERE
  TD_TIME_RANGE(TD_TIME_PARSE(time,
      'UTC'),
    TD_DATE_TRUNC('month',
      TD_TIME_ADD(TD_DATE_TRUNC('month',
          TD_SCHEDULED_TIME(),
          'UTC'),
        '-1d'),
      'UTC'),
    TD_DATE_TRUNC('month',
      TD_SCHEDULED_TIME(),
      'UTC'),
    'UTC')
GROUP BY
  TD_TIME_FORMAT(TD_TIME_PARSE(time,
      'UTC'),
    'yyyy-MM',
    'UTC')
ORDER BY
  time

【トレジャーデータ:Presto】cronの設定（任意のスケジュールでSQLを自動実行）

スケジュールをカスタムしたい場合はCronで設定（上記は毎日11時に実行される）

トレジャーデータでは簡単にスケジュールを設定してSQLを実行できる。細かくスケジュールを指定する場合は、Cron（クーロン）で設定する。Cronの設定方法については、下記の通り。

◇トレジャーデータサイトより「Cron（クーロン）」

 *    *    *    *    *
 -    -    -    -    -
 |    |    |    |    |
 |    |    |    |    +----- day of week (0 - 6) (Sunday=0)
 |    |    |    +---------- month (1 - 12)
 |    |    +--------------- day of month (1 - 31)
 |    +-------------------- hour (0 - 23)
 +------------------------- min (0 - 59)

本記事トップの画像を例にするとは、「0 11 * * *」とあるのでこの場合は毎日11時に実行される。

◇その他参考サイト

• クーロン(cron)をさわってみるお

【レトルトカレー】カロリーランキングと美味しさ評価！

左上からカロリー高い順に並べてみた

スーパーでレトルトカレーをたくさん購入

せっかくなので食べた感想を書いてみます

【カロリー1位：美味しさ72点】神田カレーグランプリ第1回優勝のチーズカレー

神田カレーグランプリ第1回優勝のチーズカレー

チーズが想像以上にいっぱい

一口目でチーズのまろやかさとかおりが口全体に広がる感じで衝撃！前半は、その衝撃とともに大変美味しく頂けました。ただ、最初の衝撃が大きいせいか後半はちょっと飽きがくる味でした。某山盛りラーメンのようにたまに食べたくなる味です。

【カロリー2位：美味しさ75点】富良野市場のポークカレー

富良野市場のポークカレー

ジャガイモがまるまる入ってる

まずジャガイモがまるまる入っているのが衝撃的！メインのポークはルーの中に隠れちゃってますがそこそこ大きいのが入っています。味はポークカレーそのもので素朴で落ち着いた味わいです。ガツンとくる味ではないですが、レトルトカレーによくある後味の悪さみたいのはないです。なんとかく健康に良さそうな美味しさでした。

【カロリー3位：美味しさ69点】富良野市場のスープカレー

富良野市場のスープカレー

ジャガイモが思ったより小ぶり

スパイシーだけど落ち着くおいしさ。じゃがいもが思ったより小さくカットされていて残念でしたが、チキンは柔らかくてうまい。スープカレーだからごはんよりもコンキリエとかと一緒に食べたほうがよかったかも。

【カロリー4位：美味しさ63点】信州りんごバター味チキンカレー

信州リンゴバター味チキンカレー

パスタで食べてみた

バターの風味でまろやかな味わい。りんごの味があまり感じなかったのが残念。今回は、写真のようにパスタで食べてみましたが、このカレーはごはんと食べるのがおすすめです！

【カロリー5位：美味しさ70点】函館カレー中辛

函館カレー州から

意外とスパイシー

一口目でほどよいスパイシー感が口の中に広がりおいしい。特に尖ったところはないけど、カレーとして普通においしい。特徴的なカレーに飽きたときに良いかも。レトルトで「普通においしいカレーが食べたい」と思ったときに最適なカレー。

【カロリー6位：美味しさ65点】函館港町カレー中辛

函館港町カレー

具が思ったほど入ってない

パッケージほど具材がなくて少しがっかりでしたが、トマトのほどよい酸味とシーフードのうまみがマイルドに味わえておいしいです。中辛でしたが辛くはなかったです。

【カロリー７位：美味しさ72点】函館カレー中辛

究極の函館カレー

大きな具材がごろごろ

大きな具材がごろごろ入っていて満足感高いです。また、ほかの函館カレーシリーズに比べてスパイシー感がやや高い感じです。後味も変な感じはなく、味も普通においしいです。

残りのカレーはまた今度！