<!--
小さなテーブルの取得方法
-->
小さなテーブルの取得方法と、CHAPTER 4～8で掲載しているSQL文を確認できます。以下の目次より、小さなテーブルの取得方法、各章（CHAPTER）ごとのSQL文を参照してください。
小さなテーブルの取得方法
小さなテーブルは、SQL文の実行結果（結果テーブル）をBigQueryのテーブルとして保存することで利用できます。SQL文の実行結果をテーブルとして保存するには、以下のように操作してください。
なお、本書のSECTION 2-6（P.070）でも、SQL文の実行結果をテーブルとして保存する方法を解説しています。
1SQL文を実行して実行結果を保存する
本ページから小さなテーブルのSQL文をコピーし、クエリエディタにペーストして［実行］をクリックします。結果テーブルが表示されるので、［結果を保存］をクリックします。
2保存先を選択する
実行結果の保存先が表示されるので、［BigQueryテーブル］をクリックします。
3データセットとテーブル名を指定して保存する
送信先を設定する画面が表示されました。データセット名（impress_sweets）とテーブル名を入力して、［保存］をクリックします。
4小さなテーブルとしてSQL文の実行結果を保存できた
SQL文の実行結果をテーブルとして保存できました。保存したテーブルをクリックすると、スキーマやテーブルのプレビューなどを確認できます。
CHAPTER 4
s_4_1_a
WITH s_4_1_a AS (
SELECT "A" AS product_id, "T" AS is_proper, 1 AS qty UNION ALL
SELECT "B", "T", 3 UNION ALL
SELECT "C", "F", 2 UNION ALL
SELECT "B", "T", 4 UNION ALL
SELECT "C", "F", 1
)
SELECT * FROM s_4_1_a
s_4_2_a
WITH s_4_2_a AS (
SELECT "A" AS user_id, "クッキー" AS product, 1 AS qty UNION ALL
SELECT "A", "クッキー", 2 UNION ALL
SELECT "A", "ショートケーキ", 6 UNION ALL
SELECT "B", "ショートケーキ", NULL UNION ALL
SELECT "B", NULL, 2 UNION ALL
SELECT "B", "ショートケーキ" , 6 UNION ALL
SELECT "C", NULL, 0 UNION ALL
SELECT "C", "ショートケーキ" , 2 UNION ALL
SELECT "C", "ゼリー", 4
)
SELECT * FROM s_4_2_a
CHAPTER 5
s_5_2_a
WITH s_5_2_a AS(
SELECT "B" AS product_id, 4 AS qty UNION ALL
SELECT "C", 2 UNION ALL
SELECT "D", 3
)
SELECT * FROM s_5_2_a
s_5_2_b
WITH s_5_2_b AS(
SELECT "A" AS product_id, "アジ" AS product_name UNION ALL
SELECT "B", "サバ" UNION ALL
SELECT "C", "タコ"
)
SELECT * FROM s_5_2_b
s_5_3_a
WITH s_5_3_a AS (
SELECT DATE("2023-01-01") AS year_month, "Men's" AS product_category, 134 AS sales UNION ALL
SELECT DATE("2023-01-01"), "Lady's", 122 UNION ALL
SELECT DATE("2023-02-01"), "Men's", 155 UNION ALL
SELECT DATE("2023-02-01"), "Lady's", 116 UNION ALL
SELECT DATE("2023-03-01"), "Men's", 152 UNION ALL
SELECT DATE("2023-03-01"), "Lady's", 139
)
SELECT * FROM s_5_3_a
s_5_3_b
WITH s_5_3_b AS (
SELECT DATE("2023-01-01") AS month, "Men's" AS category, 130 AS target UNION ALL
SELECT DATE("2023-01-01"), "Lady's", 120 UNION ALL
SELECT DATE("2023-02-01"), "Men's", 160 UNION ALL
SELECT DATE("2023-02-01"), "Lady's", 120 UNION ALL
SELECT DATE("2023-03-01"), "Men's", 160 UNION ALL
SELECT DATE("2023-03-01"), "Lady's", 130
)
SELECT * FROM s_5_3_b
s_5_3_c
WITH s_5_3_c AS (
SELECT "A" AS product_id, "S" AS shop_id, 134 AS sales UNION ALL
SELECT "A", "T" , 122 UNION ALL
SELECT "A", "U" , 155 UNION ALL
SELECT "B", "S" , 116 UNION ALL
SELECT "B", "T" , 152 UNION ALL
SELECT "B", "U" , 139
)
SELECT * FROM s_5_3_c
s_5_3_d
WITH s_5_3_d AS (
SELECT "S" AS shop_id, 1 AS area_id, "築地" AS shop_name UNION ALL
SELECT "T", 1, "銀座" UNION ALL
SELECT "U", 2, "豊洲"
)
SELECT * FROM s_5_3_d
s_5_3_e
WITH s_5_3_e AS (
SELECT "A" AS product_id, "アジ" AS product_name UNION ALL
SELECT "B", "タコ"
)
SELECT * FROM s_5_3_e
s_5_3_f
WITH s_5_3_f AS (
SELECT 1 AS area_id, "中央区" AS area_name UNION ALL
SELECT 2, "江東区"
)
SELECT * FROM s_5_3_f
s_5_4_a
WITH s_5_4_a AS (
SELECT 2021 AS year, 272 AS qty UNION ALL
SELECT 2022,309 UNION ALL
SELECT 2023,310
)
SELECT * FROM s_5_4_a
s_5_4_b
WITH s_5_4_b AS (
SELECT "A" AS user_id, 3400 AS ltv UNION ALL
SELECT "B",8200 UNION ALL
SELECT "C",1500 UNION ALL
SELECT "D",1600 UNION ALL
SELECT "E",5100 UNION ALL
SELECT "F",3900
)
SELECT * FROM s_5_4_b
s_5_4_c
WITH s_5_4_c AS (
SELECT "A" AS user_id, null AS registration_year, 2021 AS first_purchase_year UNION ALL
SELECT "B",2021,2021 UNION ALL
SELECT "C",null, 2022 UNION ALL
SELECT "D",2021,2022 UNION ALL
SELECT "E",2022,2023 UNION ALL
SELECT "F",2023,2023
)
SELECT * FROM s_5_4_c
s_5_4_d
WITH s_5_4_d AS (
SELECT 1 AS order_id, "アジ" AS product_name UNION ALL
SELECT 1, "サバ" UNION ALL
SELECT 1, "タコ" UNION ALL
SELECT 2, "キス" UNION ALL
SELECT 2, "タコ" UNION ALL
SELECT 3, "アジ" UNION ALL
SELECT 3, "タコ"
)
SELECT * FROM s_5_4_d
s_5_5_a
WITH s_5_5_a AS (
SELECT DATE("2024-01-10") AS date, "アジ" AS product_name, 3 AS qty UNION ALL
SELECT DATE("2024-01-11"), "タコ", 1 UNION ALL
SELECT DATE("2024-01-15"), "サバ", 3 UNION ALL
SELECT DATE("2024-01-18"), "キス", 2 UNION ALL
SELECT DATE("2024-01-20"), "タイ", 1
)
SELECT * FROM s_5_5_a
s_5_5_b
WITH s_5_5_b AS (
SELECT DATE("2024-02-09") AS date, "アジ" AS product_name, 3 AS qty UNION ALL
SELECT DATE("2024-02-12"), "ブリ", 3 UNION ALL
SELECT DATE("2024-02-15"), "キス", 3 UNION ALL
SELECT DATE("2024-02-19"), "タイ", 2 UNION ALL
SELECT DATE("2024-02-28"), "イカ", 1
)
SELECT * FROM s_5_5_b
s_5_5_c
WITH s_5_5_c AS (
SELECT DATE("2024-03-01") AS date, "アジ" AS product_name, 1 AS qty UNION ALL
SELECT DATE("2024-03-03"), "エビ", 3 UNION ALL
SELECT DATE("2024-03-16"), "サバ", 2 UNION ALL
SELECT DATE("2024-03-19"), "タイ", 2 UNION ALL
SELECT DATE("2024-03-22"), "タコ", 1
)
SELECT * FROM s_5_5_c
CHAPTER 6
s_6_1_a
WITH s_6_1_a AS (
SELECT "東京" AS pref, "R3" AS year, 1041 as min_wage UNION ALL
SELECT "東京", "R4", 1072 UNION ALL
SELECT "東京", "R5", 1113 UNION ALL
SELECT "北海道", "R3", 889 UNION ALL
SELECT "北海道", "R4", 920 UNION ALL
SELECT "北海道", "R5", 960 UNION ALL
SELECT "千葉", "R3", 953 UNION ALL
SELECT "千葉", "R4", 984 UNION ALL
SELECT "千葉", "R5", 1026
)
SELECT * FROM s_6_1_a
s_6_1_b
WITH s_6_1_b AS (
SELECT 1 AS pref_id, '北海道' AS pref, 5092 AS population, 960 AS r5_min_wage UNION ALL
SELECT 2, '青森', 1184, 898 UNION ALL
SELECT 3, '岩手', 1163, 893 UNION ALL
SELECT 4, '宮城', 2264, 923 UNION ALL
SELECT 5, '秋田', 914, 897 UNION ALL
SELECT 6, '山形', 1030, 900 UNION ALL
SELECT 7, '福島', 1750, 900 UNION ALL
SELECT 8, '茨城', 2776, 953 UNION ALL
SELECT 9, '栃木', 1876, 954 UNION ALL
SELECT 10, '群馬', 1872, 935 UNION ALL
SELECT 11, '埼玉', 7083, 1028 UNION ALL
SELECT 12, '千葉', 6055, 1026 UNION ALL
SELECT 13, '東京', 13447, 1113 UNION ALL
SELECT 14, '神奈川', 8859, 1112 UNION ALL
SELECT 15, '新潟', 2132, 931 UNION ALL
SELECT 16, '富山', 1009, 948 UNION ALL
SELECT 17, '石川', 1105, 933 UNION ALL
SELECT 18, '福井', 743, 931 UNION ALL
SELECT 19, '山梨', 777, 938 UNION ALL
SELECT 20, '長野', 1977, 948 UNION ALL
SELECT 21, '岐阜', 1911, 950 UNION ALL
SELECT 22, '静岡', 3518, 984 UNION ALL
SELECT 23, '愛知', 7297, 1027 UNION ALL
SELECT 24, '三重', 1733, 973 UNION ALL
SELECT 25, '滋賀', 1362, 967 UNION ALL
SELECT 26, '京都', 2491, 1008 UNION ALL
SELECT 27, '大阪', 8537, 1064 UNION ALL
SELECT 28, '兵庫', 5282, 1001 UNION ALL
SELECT 29, '奈良', 1285, 936 UNION ALL
SELECT 30, '和歌山', 886, 929 UNION ALL
SELECT 31, '鳥取', 536, 900 UNION ALL
SELECT 32, '島根', 647, 904 UNION ALL
SELECT 33, '岡山', 1817, 932 UNION ALL
SELECT 34, '広島', 2711, 970 UNION ALL
SELECT 35, '山口', 1302, 928 UNION ALL
SELECT 36, '徳島', 710, 896 UNION ALL
SELECT 37, '香川', 919, 918 UNION ALL
SELECT 38, '愛媛', 1292, 897 UNION ALL
SELECT 39, '高知', 676, 897 UNION ALL
SELECT 40, '福岡', 4940, 941 UNION ALL
SELECT 41, '佐賀', 785, 900 UNION ALL
SELECT 42, '長崎', 1286, 898 UNION ALL
SELECT 43, '熊本', 1686, 898 UNION ALL
SELECT 44, '大分', 1095, 899 UNION ALL
SELECT 45, '宮崎', 1041, 897 UNION ALL
SELECT 46, '鹿児島', 1547, 897 UNION ALL
SELECT 47, '沖縄', 1416, 896
)
SELECT * FROM s_6_1_b
s_6_2_a
WITH s_6_2_a AS (
SELECT 123 AS order_id, 18600 AS fashion, NULL AS zakka, 5800 AS food UNION ALL
SELECT 124, NULL, 2400, 8800 UNION ALL
SELECT 125, 6900, 2900, 11200 UNION ALL
SELECT 126, 4200, 3800, 4500 UNION ALL
SELECT 127, NULL, 9800, NULL
)
SELECT * FROM s_6_2_a
s_6_2_b
WITH s_6_2_b AS (
SELECT "A" AS ad_id, "before" AS phase, DATE("2024-01-01") AS month, 0.69 AS bounce_rate UNION ALL
SELECT "A", "before", DATE("2024-02-01"), 0.67 UNION ALL
SELECT "A", "after", DATE("2024-03-01"), 0.63 UNION ALL
SELECT "A", "after", DATE("2024-04-01"), 0.61 UNION ALL
SELECT "B", "before", DATE("2024-01-01"), 0.50 UNION ALL
SELECT "B", "before", DATE("2024-02-01"), 0.53 UNION ALL
SELECT "B", "after", DATE("2024-03-01"), 0.56 UNION ALL
SELECT "B", "after", DATE("2024-04-01"), 0.54
)
SELECT * FROM s_6_2_b
s_6_3_a
WITH s_6_3_a AS (
SELECT "ABC" AS user_id, 123 AS order_id, DATE("2024-03-05") AS order_date, "ファッション" AS item_cat, 10000 AS revenue UNION ALL
SELECT "ABC", 124, DATE("2024-04-10"), "ファッション", 12000 UNION ALL
SELECT "ABC", 124, DATE("2024-04-10"), "雑貨", 3900 UNION ALL
SELECT "ABC", 125, DATE("2024-05-12"), "グルメ", 5800 UNION ALL
SELECT "STU", 126, DATE("2024-05-13"), "グルメ", 8600 UNION ALL
SELECT "STU", 127, DATE("2024-05-27"), "ファッション", 3900 UNION ALL
SELECT "STU", 127, DATE("2024-05-27"), "雑貨", 6600 UNION ALL
SELECT "XYZ", 128, DATE("2024-06-01"), "ファッション", 2900 UNION ALL
SELECT "XYZ", 128, DATE("2024-06-01"), "ファッション", 6900 UNION ALL
SELECT "XYZ", 129, DATE("2024-06-19"), "雑貨", 38000
)
SELECT * FROM s_6_3_a
s_6_3_b
WITH s_6_3_b AS (
SELECT 123 AS order_id, "A" AS item, 1200 AS revenue UNION ALL
SELECT 123, "B", 2200 UNION ALL
SELECT 124, "A", 1200 UNION ALL
SELECT 124, "D", 1200 UNION ALL
SELECT 124, "E", 2000 UNION ALL
SELECT 125, "C", 1500 UNION ALL
SELECT 125, "E", 2000 UNION ALL
SELECT 126, "B", 2200 UNION ALL
SELECT 126, "C", 1500
)
SELECT * FROM s_6_3_b
s_6_3_c
WITH s_6_3_c AS (
SELECT 101 AS room, DATE("2024-06-03") AS start_date, DATE("2024-06-07") AS end_date UNION ALL
SELECT 102, DATE("2024-06-10"), DATE("2024-06-14") UNION ALL
SELECT 103, DATE("2024-06-17"), DATE("2024-06-20") UNION ALL
SELECT 104, DATE("2024-06-23"), DATE("2024-06-26") UNION ALL
SELECT 105, DATE("2024-06-27"), DATE("2024-06-29")
)
SELECT * FROM s_6_3_c
s_6_3_d
WITH s_6_3_d AS (SELECT *
FROM UNNEST(GENERATE_DATE_ARRAY("2024-06-01", "2024-06-30", INTERVAL 1 DAY)) AS date
)
SELECT date,
FORMAT_DATE("%A", date) AS day_of_week
FROM s_6_3_d
CHAPTER 7
s_7_2_a
WITH s_7_2_a AS (
SELECT "大変満足の行くセミナーでした。ありがとうございます。" AS text UNION ALL
SELECT "満足感が半端ない。この満足を味わえるのだったら有料でも良いです。"
)
SELECT * FROM s_7_2_a
s_7_3_a
WITH s_7_3_a AS (
SELECT "この映画、最高だった。888888！" AS post UNION ALL
SELECT "この映画のファンは、09012345678までショートMください。03-1234-1234でもOK" UNION ALL
SELECT "パンフレットをプレゼントするので33-9999-1111まで連ください"
)
SELECT * FROM s_7_3_a
s_7_3_b
WITH s_7_3_b AS (
SELECT "/" AS uri UNION ALL
SELECT "/?utm_source=google" UNION ALL
SELECT "/index.html" UNION ALL
SELECT "/index.html?utm_source=yahoo"
)
SELECT * FROM s_7_3_b
s_7_4_a
WITH s_7_4_a AS (
SELECT DATETIME("2024-01-11 20:18:12") AS order_time, 6 AS qty UNION ALL
SELECT DATETIME("2024-01-18 05:11:10"), 2 UNION ALL
SELECT DATETIME("2024-01-25 17:07:12"), 4 UNION ALL
SELECT DATETIME("2024-02-02 09:00:05"), 9 UNION ALL
SELECT DATETIME("2024-02-25 10:47:59"), 5 UNION ALL
SELECT DATETIME("2024-02-28 22:48:06"), 6
)
SELECT * FROM s_7_4_a
s_7_4_b
WITH s_7_4_b AS (
SELECT DATE("2024-01-01") AS order_date, 6 AS qty UNION ALL
SELECT DATE("2024-01-02"), 2 UNION ALL
SELECT DATE("2024-01-03"), 4 UNION ALL
SELECT DATE("2024-01-04"), 6 UNION ALL
SELECT DATE("2024-01-05"), 2 UNION ALL
SELECT DATE("2024-01-06"), 8 UNION ALL
SELECT DATE("2024-01-07"), 6 UNION ALL
SELECT DATE("2024-02-01"), 5 UNION ALL
SELECT DATE("2024-02-02"), 9 UNION ALL
SELECT DATE("2024-02-03"), 5 UNION ALL
SELECT DATE("2024-02-04"), 6 UNION ALL
SELECT DATE("2024-02-05"), 2 UNION ALL
SELECT DATE("2024-02-06"), 4 UNION ALL
SELECT DATE("2024-02-07"), 6
)
SELECT * FROM s_7_4_b
s_7_5_c
WITH s_7_5_c AS (
SELECT "Aさん" AS name, 1 AS score_a, 3 AS score_b UNION ALL
SELECT "Bさん",1,3 UNION ALL
SELECT "Cさん",3,3 UNION ALL
SELECT "Dさん",3,3 UNION ALL
SELECT "Eさん",5,5 UNION ALL
SELECT "Fさん",5,5 UNION ALL
SELECT "Gさん",7,5 UNION ALL
SELECT "Hさん",7,5
)
SELECT * FROM s_7_5_c
CHAPTER 8
s_8_1_a
WITH s_8_1_a AS (
SELECT 1 AS order_id, "A" AS user_id, "real_shop" AS store, 3000 AS revenue UNION ALL
SELECT 2, "B", "e_commerce", 2000 UNION ALL
SELECT 3, "A", "e_commerce", 1000 UNION ALL
SELECT 4, "B", "e_commerce", 3500 UNION ALL
SELECT 5, "B", "real_shop", 1800 UNION ALL
SELECT 6, "A", "real_shop", 2750
)
SELECT * FROM s_8_1_a
サポートページに戻る
<a href="">
<img src="static/img/dekiru-net.png" alt="" width="120" />
Source: できるネット
小さなテーブルの取得方法 -『BigQueryではじめるSQLデータ分析 GA4 & Search Console & Googleフォーム対応』