注目の投稿

【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道）

kepler.glのサイト画面 kepler.glを使ってコロナ対策の効果を分析したところ、テレビ、新聞、ネットのメディアから問い合わせや報道依頼が殺到。今も、土日返上で都内や全国の人流変化を分析しています。この記事では人流変化の可視化に便利なkepler.glにつ...

ラベル BigQuery の投稿を表示しています。 すべての投稿を表示
ラベル BigQuery の投稿を表示しています。 すべての投稿を表示

2023年2月20日月曜日

【BigQuery 】緯度経度から50mメッシュコードを算出する方法

ぐぐっても出なかったので自分でSQL書きました。

ただ、未検証なのでご了承のほどよろしくお願いいたします。

BigQuery

WITH
  t0 AS (
  SELECT
    139.6917337 AS longitude,
    35.6895014 AS latitude ),
  t1 AS (
  SELECT
    latitude,
    longitude,
    /* FLOOR X 以下で最大の整数値を返します。*/ 
    /*round X のみが存在する場合、X を最も近い整数に丸めます。N が存在する場合、X を小数点以下の N 桁に丸めます。*/ 
    /*MOD(X, Y) Y による X の除算の剰余を返します。*/ 
    /* 1次メッシュ 辺の長さ：約80km 経度差：1度 緯度差：40分*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(latitude*60/40) AS INT64) AS STRING), SAFE_CAST(SAFE_CAST(FLOOR(longitude)-100 AS INT64) AS STRING) ) AS mesh1d,
    /* 2次メッシュ 辺の長さ：約10km 経度差：7分30秒 緯度差：5分*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC), 40.0)/5) AS INT64) AS STRING), SAFE_CAST(SAFE_CAST(FLOOR(MOD(SAFE_CAST(longitude-100 AS NUMERIC), 1.0)*60/7.5) AS INT64) AS STRING) ) AS mesh2d,
    /*3次メッシュ 辺の長さ：約1km 経度差：45秒 緯度差：30秒 */ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0)* 60/30) AS INT64) AS STRING), SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(SAFE_CAST(longitude AS NUMERIC),1.0)*60,7.5) * 60/45) AS INT64) AS STRING) ) AS mesh3d,
    /* 2分の1地域メッシュ 辺の長さ：約500m 経度差：22.5秒 緯度差：15秒 三次メッシュを緯線方向、経線方向に2等分してできる区域*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.5)*4)*2+ FLOOR(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75)* 60/22.5)+1 AS INT64) AS STRING) ) AS mesh4d,
    /* 4分の1地域メッシュ 辺の長さ：約250m 経度差：11.25秒 緯度差：7.5秒 */ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5),0.5),0.25)*8)*2+ FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1)*60,7.5),0.75),0.375)*60/11.25)+1 AS INT64) AS STRING) ) AS mesh5d,
    /* 8分の1地域メッシュ 辺の長さ：約125m 経度差：5.625秒 緯度差：3.75秒 */
     CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.5),0.25),0.125)*16)*2+ FLOOR(MOD(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1)*60,7.5),0.75),0.375),0.1875)* 60/5.625)+1 AS INT64) AS STRING) ) AS mesh6d,
    /* 10分の1地域メッシュ 辺の長さ：約100m 経度差：4.5秒 緯度差：3秒 三次メッシュを緯線方向、経線方向に10等分してできる区域*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.1)*20)*2+ FLOOR(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75)* 60/4.5)+1 AS INT64) AS STRING) ) AS mesh7d,
    /* 20分の1地域メッシュ 辺の長さ：約50m 経度差：2.25秒 緯度差：1.5秒 1/10メッシュを緯線方向、経線方向に2等分してできる区域*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.1),0.05)*40)*2+ FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75),0.075)* 60/2.25)+1 AS INT64) AS STRING) ) AS mesh8d
  FROM
    t0 )
SELECT
  latitude,
  longitude,
  CONCAT(mesh1d,mesh2d,mesh3d,mesh4d,mesh5d,mesh6d,mesh7d,mesh8d) AS mesh50m
FROM
  t1


参考


By 時刻: 0 件のコメント :
ラベル: , , ,

2020年6月12日金曜日

【BigQuery】int型の数字を日付に変換する

グーグルのサイトを参考
（標準 SQL のタイムスタンプ関数のページを参考）

ーー以下引用部分ーー

TIMESTAMP_SECONDS
1970-01-01 00:00:00 UTC からの秒数

TIMESTAMP_MILLIS
1970-01-01 00:00:00 UTC からのミリ秒数

TIMESTAMP_MICROS
1970-01-01 00:00:00 UTC からのマイクロ秒数


By 時刻: 0 件のコメント :
ラベル:

2020年5月21日木曜日

【BigQuery】時間と分を抜き出す

タイムスタンプから時間として扱るかたちで時間と分のみを抜き出す方法

--%R＝%H:%M 形式の時刻
    FORMAT_TIMESTAMP("%R",TIMESTAMP)

出力結果は、「02:30」というような形で出てくる。
もちろん、時間として扱えるので、

 FORMAT_TIMESTAMP("%R",TIMESTAMP) > ''12:30"

とすれば、12時30分以降のデータを抽出できる。

◇参考サイト





By 時刻: 0 件のコメント :
ラベル:

2020年5月1日金曜日

【BigQuery】特定の文字を含むものだけを集計する

フィールド「x」の先頭の文字が「Z」である場合に「y」の合計を集計するSQL
「LIKE」はあいまい検索、「%」はワイルドカードで「Z%」はZの後ろに何があってもよいことを示す。


SELECT
  SUM(CASE
      WHEN x LIKE "Z%" THEN y
  END
    ) AS total
FROM
  data


By 時刻: 0 件のコメント :
ラベル: , , ,

2020年4月21日火曜日

【BigQuery】都内の各地点のUUを前後の期間で比較するデータを集計してケプラーで描画する

ケプラーを利用すると緯度経度のデータからグラフィカルなマップグラフを作成できるとを以前記事に書きました（【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道））。今回、ケプラーのH3というグラフ形式を使って、東京都内各地点内の異なる期間のUUの比率をマップグラフで描画するため必要なデータを集計すためのクエリを紹介します。また、集計データのケプラーでの描画については、下記のサイトが分かりやすいのでご参考にして下さい。

◇クエリ例

WITH
  t1 AS (
  SELECT
    UDF.geoToH3(latitude,
      longitude,
      8) AS geoH3_1,
    COUNT(DISTINCT uid) AS CntUU_1,
  FROM
    data
  WHERE
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) >= TIMESTAMP('2020-02-02 00:00:00')
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) < TIMESTAMP('2020-02-03 00:00:00')
    AND latitude BETWEEN 23
    AND 46
    AND longitude BETWEEN 123
    AND 148
    --
    --東京都内
    AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.868043 35.628718,139.887956 35.653272,139.885209 35.680048,139.911302 35.685068,139.918855 35.70347,139.896195 35.728001,139.899629 35.744164,139.879716 35.761996,139.878343 35.77481,139.86255 35.79542,139.771226 35.810456,139.732774 35.778152,139.650376 35.799875,139.616731 35.763667,139.54326 35.752523,139.421723 35.765896,139.371598 35.764782,139.277528 35.834953,139.170411 35.843153,139.016603 35.891563,138.955491 35.853728,139.044755 35.717259,139.219163 35.645868,139.411424 35.582232,139.454015 35.541781,139.471867 35.519988,139.482167 35.534517,139.459508 35.571946,139.476674 35.595958,139.443028 35.606008,139.453328 35.6328,139.492467 35.619963,139.528859 35.652889,139.658635 35.598192,139.715627 35.553513,139.750645 35.548765,139.796651 35.524738,139.809354 35.51384,139.868043 35.628718))'),
      ST_GEOGPOINT(longitude,
        latitude))
  GROUP BY
    geoH3_1 ),
  t2 AS (
  SELECT
    UDF.geoToH3(latitude,
      longitude,
      8) AS geoH3_2,
    COUNT(DISTINCT uid) AS CntUU_2,
  FROM
    data
  WHERE
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) >= TIMESTAMP('2020-04-19 00:00:00')
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) < TIMESTAMP('2020-04-20 00:00:00')
    AND latitude BETWEEN 23
    AND 46
    AND longitude BETWEEN 123
    AND 148
    --
    --東京都内
    AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.868043 35.628718,139.887956 35.653272,139.885209 35.680048,139.911302 35.685068,139.918855 35.70347,139.896195 35.728001,139.899629 35.744164,139.879716 35.761996,139.878343 35.77481,139.86255 35.79542,139.771226 35.810456,139.732774 35.778152,139.650376 35.799875,139.616731 35.763667,139.54326 35.752523,139.421723 35.765896,139.371598 35.764782,139.277528 35.834953,139.170411 35.843153,139.016603 35.891563,138.955491 35.853728,139.044755 35.717259,139.219163 35.645868,139.411424 35.582232,139.454015 35.541781,139.471867 35.519988,139.482167 35.534517,139.459508 35.571946,139.476674 35.595958,139.443028 35.606008,139.453328 35.6328,139.492467 35.619963,139.528859 35.652889,139.658635 35.598192,139.715627 35.553513,139.750645 35.548765,139.796651 35.524738,139.809354 35.51384,139.868043 35.628718))'),
      ST_GEOGPOINT(longitude,
        latitude))
  GROUP BY
    geoH3_2 ),
  join_log AS (
  SELECT
    geoH3_1,
    MAX(CntUU_1) AS CntUU_1,
    MAX(CntUU_2) AS CntUU_2,
  FROM
    t1
  LEFT JOIN
    t2
  ON
    t1.geoH3_1 =t2.geoH3_2
  WHERE
    geoH3_1 IS NOT NULL
  GROUP BY
    geoH3_1 )
SELECT
  *,
  CntUU_2 / CntUU_1 AS rate
FROM
  join_log
ORDER BY
  CntUU_1 DESC


◇参考サイト




By 時刻: 0 件のコメント :
ラベル: , , ,

2020年4月19日日曜日

【BigQuery】1分毎に1レコード生成するクエリ

1分毎に1レコードを生成するクエリ。今回は、1分毎に北太平洋の任意の緯度経度を入れている。ケプラーで位置情報などを可視化する際に、複数のデータのグラフの高さを調整するために使うクエリ。1分毎でそのデータの最大値とならない場合は、1秒毎にしたり、同様のクエリを複数UNIONするなどで対応できる。

  SELECT
    DISTINCT FORMAT_DATETIME('%Y-%m-%d %H:%M',
      DATETIME(TIMESTAMP(time),
        'Asia/Tokyo') ) AS time_jpn,
    --北太平洋 31.036119, 165.169956
    31.036119 AS latitude,
    165.169956 AS longitude
  FROM
    table
  ORDER BY
    time_jpn


By 時刻: 0 件のコメント :
ラベル: , , ,

2020年4月6日月曜日

【BiqQuery】時間だけを取り出す

ビッククエリで時間だけを取り出すクエリはこちら
例えば、11:20だと11、06:30だと06を抽出する。

  SELECT
    userid,
    jpn_time,
    FORMAT_TIMESTAMP("%H", TIMESTAMP(jpn_time)) AS hour
  FROM
    table


By 時刻: 0 件のコメント :
ラベル: , , ,

2020年4月5日日曜日

【BigQuery】各ユーザが1週間にログインした日数を集計する方法

各ユーザが1週間に何日訪問したかを集計するクエリ。まず、各ユーザの日ごとの訪問日を集計し、1週間を週初めの日にち（月曜）に統一して週で何日訪問しているかを単純にカウントする。

  • クエリ例
WITH
  dau AS (
  SELECT
    date,
    userid
  FROM
    table
  GROUP BY
    date,
    userid)
SELECT
  DATE_ADD(date, INTERVAL - EXTRACT(DAYOFWEEK
    FROM
      DATE_ADD(date, INTERVAL -1 day)) +1 day) AS w_week,
  adid,
  COUNT(userid) AS vdays
FROM
  dau
GROUP BY
  w_week,
  userid
ORDER BY
  vdays DESC


By 時刻: 0 件のコメント :
ラベル: , ,

2020年3月29日日曜日

【BigQuery】連続ログイン日数とその期間の最初と最後のログイン日を集計するクエリ

日次で連続してログインや訪問しているユーザの日数とその連続する期間の最初の日と最後の日を集計するクエリ。連続ログインが途切れて再度ログインした際は、別の期間として新たに集計。急いで書いたから読みにくい。。

◇クエリ例

WITH
  stays_table AS (
  SELECT
    *,
    CASE
      WHEN visit_date != DATE_ADD(lag_visit_date, INTERVAL 1 DAY) THEN 1
    ELSE
    0
  END
    AS not_continuous_flg
  FROM (
      --ユーザ毎の日付昇順での前回のデータを入れる項目の作成
    SELECT
      userid,
      visit_date,
      LAG(userid,1) OVER (PARTITION BY userid ORDER BY visit_date) AS lag_user_id,
      LAG(visit_date,1) OVER (PARTITION BY userid ORDER BY visit_date) AS lag_visit_date
    FROM (
      SELECT
        userid,
        DATE(TIMESTAMP(jpn_day)) AS visit_date
      FROM
        `data`
      GROUP BY
        userid,
        visit_date ) ) ),
  flg_cum AS(
  SELECT
    *,
    SUM(not_continuous_flg) OVER (PARTITION BY userid ORDER BY visit_date) AS cum_not_cont_date_flg
  FROM
    stays_table ),
  t1 AS (
  SELECT
    *,
    COUNT(cum_not_cont_date_flg) OVER (PARTITION BY userid, cum_not_cont_date_flg ORDER BY cum_not_cont_date_flg) AS cnt
  FROM
    flg_cum ),
  t00 AS (
    --00グループ：初回
  SELECT
    userid,
    not_continuous_flg,
    cum_not_cont_date_flg,
    MAX(cnt) AS stay_days,
    MIN(visit_date) AS first_loginday
  FROM
    t1
  WHERE
    t1.not_continuous_flg = 0
    AND t1.cum_not_cont_date_flg = 0
  GROUP BY
    userid,
    not_continuous_flg,
    cum_not_cont_date_flg,
    cnt
  ORDER BY
    userid ),
  t11 AS (
    --1と1以上グループ：2回目以降
  SELECT
    userid,
    not_continuous_flg,
    cum_not_cont_date_flg,
    MAX(cnt) AS stay_days,
    MIN(visit_date) AS first_loginday
  FROM
    t1
  WHERE
    t1.not_continuous_flg = 1
    AND t1.cum_not_cont_date_flg > 0
  GROUP BY
    userid,
    not_continuous_flg,
    cum_not_cont_date_flg,
    cnt
  ORDER BY
    userid,
    not_continuous_flg,
    cum_not_cont_date_flg ),
  x1 AS (
  SELECT
    userid,
    cum_not_cont_date_flg,
    stay_days,
    first_loginday
  FROM
    t00
  UNION ALL
  SELECT
    userid,
    cum_not_cont_date_flg,
    stay_days,
    first_loginday
  FROM
    t11 ),
  x2 AS (
  SELECT
    userid,
    cum_not_cont_date_flg,
    stay_days,
    first_loginday
  FROM
    x1
  GROUP BY
    userid,
    cum_not_cont_date_flg,
    stay_days,
    first_loginday
  ORDER BY
    userid,
    cum_not_cont_date_flg,
    stay_days,
    first_loginday )
SELECT
  userid,
  cum_not_cont_date_flg,
  stay_days,
  first_loginday,
  DATE_ADD(first_loginday, INTERVAL stay_days - 1 DAY) AS last_loginday
FROM
  x2

◇参考サイト





By 時刻: 0 件のコメント :
ラベル: , , ,

【BigQuery】よく使うクエリ構文

よく使う構文


  • DATE()：タイムスタンプの時間を日次（例：2020/1/1）に変換
  • TIMESTAMP()：文字列の日付をタイムスタンプに変換
  • EXTRACT(DAYOFWEEK FROM ***)：タイムスタンプから曜日を抽出（日曜:１～土曜7:）
  • CAST(FORMAT_TIMESTAMP("%H", ***) AS int64) ：タイムスタンプ（***）から時間を抽出
  • _TABLE_SUFFIX BETWEEN '***' AND '***'：複数のテーブルを指定の範囲から参照する
  • ST_DWITHIN(ST_GeogPoint(139.***,  35.***), ST_GeogPoint(longitude,latitude),500)：経度（139.***, longitude）、緯度（35.***, latitude）が指定した範囲内（500m）であれば抽出


◇クエリ例

SELECT
--文字列の日付をタイムスタンプに変換して日次のみ抽出
  DATE(TIMESTAMP(time),'Asia/Tokyo') AS date,
--曜日を抽出：日曜 1 ~ 7 土曜
  EXTRACT(DAYOFWEEK
    FROM
      jpn_day ) AS youbi,
--時間を抽出
  CAST(FORMAT_TIMESTAMP("%H", TIMESTAMP(time)) AS int64) AS hour
FROM
  `data*`
WHERE
  --data[*]部分を指定したテーブルを参照
  _TABLE_SUFFIX BETWEEN '20200201'
  AND '2020203'
  --標準時間を日本時間に変換して期間を指定
  AND TIMESTAMP(DATETIME(TIMESTAMP(time),
      'Asia/Tokyo')) >= TIMESTAMP('2020-02-01 00:00:00')
  AND TIMESTAMP(DATETIME(TIMESTAMP(time),
      'Asia/Tokyo')) < TIMESTAMP('2020-02-03 00:00:00')
  --指定の緯度経度より500m以内,緯度: 35.*** 経度: 139.***
  AND ST_DWITHIN(ST_GeogPoint(139.***,
      35.***),
    ST_GeogPoint(longitude,
      latitude),
    500)


By 時刻: 0 件のコメント :
ラベル: ,

2020年1月19日日曜日

【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.）のために日本の範囲を先に指定している。

参考サイト


By 時刻: 0 件のコメント :
ラベル: , , , , , ,
登録: 投稿 ( Atom )