Google アナリティクス 4 ( GA4 )

Google アナリティクス 4 ( GA4 )の設定などの情報をお届けします。

BigQuery SQL

「SQLチートシート for BigQuery」 β版

投稿日:2021年6月11日 更新日:

SUMなどの比較的簡単な関数は省略させていただていおりますので、ご了承ください。

条件式に利用できる記号

= 等しい aaa = 2
<> 等しくない aaa <> “千葉”
!= 等しくない aaa != 100
> より大きい aaa > 100
< より小さい aaa < 100
>= 以上 aaa >= 100
<= 以下 aaa <= 100

AVG (平均値)

SELECT
  AVG(DATE_DIFF("2019-12-31", 〇, YEAR)) AS 〇
FROM
  〇
WHERE
  〇 = "千葉"
AND 〇 = 2

BETWEEN (値1から値2の間に当てはまる)

SELECT
 *
FROM
 〇
WHERE
 〇 BETWEEN "2017-02-01" AND "2017-02-10"
ORDER BY
 〇 DESC
LIMIT 1

CAST (データ型を返還する)

SELECT
  〇
  , CAST(AVG(〇) AS INT64) AS 〇
FROM
  〇
GROUP BY
  〇
ORDER BY
  2 DESC

CEIL, CEILING (該当フィールド内の値以上で最小の整数値を返す)

関連する関数:

FLOOR (該当フィールド内の値よりも小さい最大の整数値を返す)

SELECT
  CEIL(SUM(revenue) / COUNT(DISTINCT FORMAT_DATETIME("%F", 〇)) / 100) * 100 AS 〇
FROM
  〇

上記の場合、10の位で切り上げして、100の単位で位で丸めるために、100で割った後で、100を掛けている。

SELECT CEIL(1359/100) * 100

の結果は、1400.0

CONCAT(concatinate コンカティネート) (文字列やフィールドの値を連結する)

SELECT
 CONCAT(〇, "-", 〇) AS 〇
 , cost
FROM
 〇
ORDER BY
 cost DESC
LIMIT 3
SELECT
 IF(
   〇 = 1
   , CONCAT(〇, "〇")
   , CONCAT(〇, "〇")
 ) AS 〇
FROM
 〇
ORDER BY
 〇 DESC
LIMIT 5

CORR (2変数の相関関係を求める)

SELECT
 CORR(〇, 〇) AS 〇
FROM
〇

COUNT (データの個数を数える)

SELECT COUNT(*) AS pageviews
FROM 〇

レコード数(データ数)を調べたいだけならば*を指定することも可能。

DATE_ADD (日付に一定の値を加える)

SELECT
〇, 〇, 〇
, DATE_ADD(〇, INTERVAL 10 YEAR) AS 〇
FROM 〇
WHERE 〇 = "〇"
AND 〇 = 1
AND 〇 IS NOT NULL
ORDER BY 〇 ASC

デイトパート:YEAR / QUATER / MONTH / WEEK / DAY

DATE_DIFF (日付・時刻の差分を取得する)

SELECT
 DATE_DIFF(current_date(), 〇, YEAR) AS 〇
FROM
 〇
ORDER BY
 〇 DESC
LIMIT 1

DATE_DIFF(current_date(), 〇, YEAR) AS 〇 の YEAR は「デイトパート」。

デイトパート:YEAR / QUATER / MONTH / WEEK / DAY

SELECT 〇, 〇, 〇, 〇, 
DATE_DIFF(〇, 〇, YEAR) AS 〇
FROM 〇
WHERE 〇y IS NOT NULL
AND 〇  IS NOT NULL
ORDER BY 〇
LIMIT 1
SELECT 
DATE_DIFF(MAX(〇), MIN(〇), DAY) AS 〇
FROM 〇

FLOOR (該当フィールド内の値よりも小さい最大の整数値を返す)

SELECT
 FLOOR(SUM(〇) / COUNT(DISTINCT 〇) / 100) * 100 AS 〇
FROM
 〇

上記の場合、10の位で切り捨て、100の位で丸めるために、100で割った後で、100を掛けている。

FORMAT_DATETIME (日付・時刻の表示形式を変更する)

SELECT
 FORMAT_DATETIME("%F", 〇) AS 〇
FROM
 〇
ORDER BY
 〇 DESC
LIMIT 1
SELECT
  SUM(〇) AS 〇
FROM
  〇
WHERE
  〇 = 5
AND FORMAT_DATETIME("%Y", 〇) = "2019"

FORMAT_DATETIME(表示形式を指定するキーワード, DATE型の値)

DATETIME でサポートされる形式設定要素

(例)%F%Y%m%d 形式の日付。

SELECT
  SUM(〇) AS 〇
FROM
  〇
WHERE
  FORMAT_DATETIME("%Y", DATETIME型の値) = "2019"
SELECT
  COUNT(DISTINCT 〇) AS 〇
FROM
  〇
WHERE
  FORMAT_DATETIME("%m", 〇) IN("01", "12")

DATETIME型とは:

DATETIME0001-01-01 00:00:00~9999-12-31 23:59:59.999999

DATETIME オブジェクトは、タイムゾーンに依存せずにカレンダーや時計に表示される日時を表します。これには年、月、日、時、分、秒、サブ秒が含まれます。絶対的な時刻を表すには、タイムスタンプを使用します。

正規形式
YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.F]]
  • YYYY: 4 桁の年
  • [M]M: 1 桁または 2 桁の月
  • [D]D: 1 桁または 2 桁の日
  • ( |T): スペースまたは区切り文字「T」
  • [H]H: 1 桁または 2 桁の時(有効な値は 00~23)
  • [M]M: 1 桁または 2 桁の分(有効な値は 00~59)
  • [S]S: 1 桁または 2 桁の秒(有効な値は 00~59)
  • [.F]: 最大で小数第 6 位まで(マイクロ秒の精度)
SELECT
  COUNT(DISTINCT 〇) AS 〇
FROM
  〇
WHERE
  FORMAT_DATETIME("%Y-%Q", 〇) = "2019-1"
%Q10 進数として表示される四半期(1~4)。
SELECT
  FORMAT_DATETIME("%Y-%p", 〇) AS 〇
  , SUM(revenue) AS 〇
FROM
  sample.sales
GROUP BY
  1
ORDER BY
  1
%Y10 進数として表示される、世紀を含む年。2021
%y10 進数(00-99)として表示される年。世紀は含みません。先頭に 0 を追加することもできます。%C と混在できます。%C が指定されていない場合、年 00~68 は 2000 年代、年 69~99 は 1900 年代です。2
%pAM または PM のいずれか。

HAVING (集計結果を絞り込む)

SELECT
  cost
  , COUNT(*) AS △
FROM
  〇
GROUP BY
  〇
HAVING cost < 500
ORDER BY
  1

WHERE句はGROUP BY句よりも後には記述できない。

しかし、WHERE句をGROUP句の前に記述すると、△フィールドを認識できない。

そこで利用するのがHAVNG句。

IF (2グループでの分類)

SELECT
  SUM(IF(
      〇 < "2019-10-01"
      , 〇 * 1.08
      , 〇 * 1.1
    )) AS 〇
FROM
  〇
SELECT
  SUM(IF(
      〇 = 1
    AND 〇 IS FALSE
    AND 〇 = 1
      , 2000
      , 〇
    )) AS 〇
FROM
  〇

IN (いずれかの値に当てはまる)

SELECT
 *
FROM
 〇
WHERE
 〇 IN("〇", "〇", "〇")
AND 〇 IS TRUE
ORDER BY
 user_id

IN の後は、 (     ) で囲む。

LENGTH (文字列の長さを取得する)

SELECT
 〇
 , 〇
FROM
 〇
WHERE
 LENGTH(SUBSTR(〇, 1, STRPOS(〇, " ") - 1)) = 3
AND LENGTH(SUBSTR(〇, STRPOS(〇, " ") + 1)) = 3

LIKE (パターンに当てはまる)

SELECT
 *
FROM
 〇
WHERE
 name LIKE "〇"

% 任意の数の任意の文字
_ 1つの任意の文字
\ エスケープを処理する

MOD (割り算の余りを求める)

SELECT
 〇
FROM
 〇
WHERE
 MOD(〇, 3) = 0
AND MOD(〇, 7) = 0
ORDER BY
 1
LIMIT 1

NOT IN (いずれの値にも当てはまらない)

SELECT
 VAR_POP(〇) AS var_cost
FROM
 〇
WHERE
 〇 NOT IN("〇")

RAND (乱数を発生させる)

SELECT
 *
FROM
 〇
ORDER BY
 RAND()
LIMIT 3

REGEXP_CONTAINS (文字列の有無を判定する)

文字列が含まれていれば、「true」が返される。

SELECT
 *
FROM
 〇
WHERE
 〇 = "〇"
AND REGEXP_CONTAINS(〇, r"〇$") = false
SELECT
  COUNT(*) AS 〇
FROM
  〇
WHERE
  REGEXP_CONTAINS(〇, r"^/xxx/xxx_id_\d\d?/$") = true

REGEXP_EXTRACT (合致する文字列を返す)

SELECT
 page
 , REGEXP_EXTRACT(〇, r"^/〇/prod_id_(\d\d?)/")  AS 〇
FROM
 〇
WHERE
 STRPOS(〇, "/〇/") > 0
ORDER BY
 〇
LIMIT 5

REGEXP_REPLACE (合致する文字列を置換する)

SELECT BL,
REGEXP_REPLACE(BL, r"\([0-9]*\)", "") AS BL2
FROM booklive.booklive_bl

 〇
 , REGEXP_REPLACE(〇, r"〇$", "〇") 
 AS 〇
FROM
 〇
WHERE
 STRPOS(〇, "〇") > 0

ROUND (該当フィールド内の値を指定した桁数で丸める)

SELECT
  FORMAT_DATETIME("%Y", 〇) AS 〇
  , ROUND(AVG(〇), 2) AS 〇
FROM
  〇
GROUP BY
  〇
ORDER BY
  〇

STDDEV_POP (標準偏差を求める)

SELECT
 STDDEV_POP(〇) AS 〇
FROM
 〇

STRPOS (特定文字列の出現位置を取得する)

STRPOS(対象文字列, 検索文字列)

(例)STRPOS(“日本”, “本”) 戻り値:2

SELECT
 〇
 , 〇
FROM
 〇
WHERE
 SUBSTR(〇, 1, STRPOS(〇, " ") - 1) LIKE "%〇"
AND gender = 1
SELECT
 〇
 , REGEXP_REPLACE(〇, r"〇$", "〇") 
 AS 〇
FROM
 〇
WHERE
 STRPOS(〇, "〇") > 0
SELECT
 page
 , REPLACE(page, "?〇", "")  AS 〇
FROM
 〇
WHERE
 STRPOS(〇, "〇") > 0
ORDER BY
 date_time
LIMIT 3

上記では、「STRPOS(〇, “□”) > 0」で、戻り値が0よりも大きければ、文字列”□”が存在するという使い方をしている。

SELECT
 page
 , REGEXP_EXTRACT(〇, r"^/〇/prod_id_(\d\d?)/")  AS 〇
FROM
 〇
WHERE
 STRPOS(〇, "/□/") > 0
ORDER BY
 〇
LIMIT 5

上記でも、「STRPOS(〇, “/□/”) > 0」で、戻り値が0よりも大きければ、文字列”□”が存在するという使い方をしている。

SUBSTR (文字列の一部を取り出す)

SUBSTR(対象文字列, 位置, 文字数)

SELECT
 〇
 , 〇
FROM
 〇
WHERE
 SUBSTR(〇, 1, STRPOS(〇, " ") - 1) LIKE "%〇"
AND gender = 1
SELECT
 name
 , 〇
FROM
 〇
WHERE
 〇 IS NOT NULL
AND SUBSTR(〇, 1, 1) = "川"
ORDER BY
 birthday
LIMIT 3
SELECT
 COUNT(*) AS 〇
FROM
 〇
WHERE
 SUBSTR(page, 1, 16) = "/xxxx/xxxxxxxxx/"

[文字数]は省略可能で、省略した場合は[位置]から末尾までの文字列を取得する。

SELECT
 〇
 , 〇
FROM
 〇
WHERE
 LENGTH(SUBSTR(〇, 1, STRPOS(〇, " ") - 1)) = 3
AND LENGTH(SUBSTR(〇, STRPOS(〇, " ") + 1)) = 3

上記の例は、姓も名も3文字の顧客リストを取得している。

VER_POP (分散を求める)

SELECT
 VAR_POP(〇) AS var_cost
FROM
 〇
WHERE
 〇 NOT IN("〇")

以上

-BigQuery, SQL

執筆者:

関連記事

GA4からBigQueryにエクスポートした eコマースデータの「items.item_」で始まるデータをSQLで取り出すには?

GA4からBigQueryにエクスポートした 拡張 eコマースデータの「items.item_」で始まるデータをSQLで取り出すには? たとえば、[items.item_id]などのデータをSQLで取 …

BigQuery に入れた GA4 のデータを データポータル で活用

今回は、BigQuery に入れた GA4 のデータを データポータル で表示する、というテーマでお送りします。

BigQuery に入れた GA4 のデータを データポータル で活用(改)

下記で作ったデータポータルの画面は、実際にご覧いただくことができます。 https://datastudio.google.com/reporting/a400c9f2-05ae-45d2-8602- …

ウェブ最適化ではじめる機械学習 ―A/Bテスト、メタヒューリスティクス、バンディットアルゴリズムからベイズ最適化まで