bigquery クエリ

コード

SELECT
  record_id,
  amount,
  if(amount > 100000, 1, 0) as flag1,
  if(REGEXP_EXTRACT(string(amount), '(.{3}$)') = '000', 1, 0) as flag2
FROM
  [{プロジェクト名}:{データセット名}.{テーブル名}]
SELECT
  code,
  user_id,
  count(*) as count
FROM
  [{プロジェクト名}:{データセット名}.{テーブル名}]
WHERE
  // 直近7日間(日本時間に変更するために9時間足している)
  date(transaction_date) >= DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(), 9, 'HOUR'), -7, 'DAY'))
GROUP BY
  code,
  user_id
having
  count >= 2

JSTへの変換。前日に変換。

SELECT
  timestamp,
  STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(timestamp) + 32400000000, '%Y/%m/%d') as jst1,
  STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(DATE_ADD(timestamp,-1,'DAY')) + 32400000000, '%Y/%m/%d') as jst2
FROM
  [bit-flyer:twitter.followers_counts]

前日比集計

SELECT 
  a.date,
  a.xrp,
  b.xrp,
  (a.xrp - b.xrp) as ratio
FROM
 (SELECT
    STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(timestamp) + 32400000000, '%Y/%m/%d') as date,
    min(xrp) as xrp
  FROM
    [bit-flyer:twitter.followers_counts]
  GROUP BY
    date
  ) as a
LEFT JOIN
  (SELECT
    STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(DATE_ADD(timestamp,+1,'DAY')) + 32400000000, '%Y/%m/%d') as date,
    min(xrp) as xrp
  FROM
    [bit-flyer:twitter.followers_counts]
  GROUP BY
    date
   ) as b
ON
  a.date = b.date
ORDER BY
  a.date

参考

https://qiita.com/wapa5pow/items/afb32ee83d3a47355008
https://cloud.google.com/bigquery/sql-reference/query-syntax?hl=ja