【Google】意外と知らないグーグルスプレッドシートなどのファイルが保存されている場所を開く方法

矢印のところをクリックすることでファイルを保存している場所を開ける

グーグルスプレッドシートなどのファイルがどこに保存されているか分からない場合、上記の画像内の矢印の箇所をクリックすることで保存されているフォルダを開くことができます。

自分も最近知りました。。。

【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

◇参考サイト

• 【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道）
• BigQuery・Kepler.gl・Uber H3による空間情報分析の始め方
• BigQuery GISを用いた位置情報データ分析の入門
• Table of Cell Areas for H3 Resolutions

【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

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

ビッククエリで時間だけを取り出すクエリはこちら

例えば、11:20だと11、06:30だと06を抽出する。

  SELECT

    userid,

    jpn_time,

    FORMAT_TIMESTAMP("%H", TIMESTAMP(jpn_time)) AS hour

  FROM

    table

【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