SQL

Standard SQL CookBook - 4/N

Bigquery standard SQLにおけるset operaters syntaxの確認

公開日: 2022-07-14
更新日: 2022-07-20

  概要
目的 Bigquery standard SQLにおけるset operaters syntaxの確認
分類 SQL Cookbook
SQL Standard SQL
環境 BigQuery

Table of Contents

syntax

1

UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

UNION { ALL | DISTINCT }

  • 二つのクエリの結果を結合する
  • ALLを指定すると、recordが重複していてもそのまま残る
  • DISTINCTを指定すると、recordの重複は排除され一意になる

union all

1
2
3
4
5
6
7
8
9
10
11
12
13

WITH dat AS(
    SELECT
        1 AS col_1,
        2 AS col_2
    )
    ,dat_2 AS(
    SELECT
        1 AS col_1,
        2 AS col_2
    )
SELECT * FROM dat
UNION ALL
SELECT * FROM dat_2

THEN,

行 col_1 col_2
1 1 2
1 1 2

union distinct

1
2
3
4
5
6
7
8
9
10
11
12
13

WITH dat AS(
    SELECT
        1 AS col_1,
        2 AS col_2
    )
    ,dat_2 AS(
    SELECT
        1 AS col_1,
        2 AS col_2
    )
SELECT * FROM dat
UNION DISTINCT
SELECT * FROM dat_2

Then,

行 col_1 col_2
1 1 2

INTERSECT DISTINCT

  • 両方に含まれるrecordを返す
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

WITH dat AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<col_1 INT64, col_2 INT64>
                (1,2),
                (2,3),
                (1,2),
                (3,4)
                ])     
    )
    ,dat_2 AS(
    SELECT
        1 AS col_1,
        2 AS col_2
    )
SELECT * FROM dat
INTERSECT DISTINCT
SELECT * FROM dat_2

THEN,

行 col_1 col_2
1 1 2

EXCEPT DISTINCT

  • left table(except distinctの前に呼び出したテーブル)に存在してright table(except distinctの後に呼び出したテーブル)に存在しないrecordを抽出
  • EXCEPT ALLはサポートされていない
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

WITH dat AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<col_1 INT64, col_2 INT64>
                (1,2),
                (2,3),
                (1,2),
                (3,4)
                ])     
    )
    ,dat_2 AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<col_1 INT64, col_2 INT64>
                (1,2),
                (1,2),
                (5,4)
                ])     
    )
SELECT * FROM dat
EXCEPT DISTINCT
SELECT * FROM dat_2

THEN,

行 col_1 col_2
1 2 3
2 2 3

XORオペレーション

BigQueryではxorオペレーション固有のコマンドは存在しないですが,UNION ALL,INTERSECT,EXPECTを組み合わせることで計算することは可能です. 以下例を紹介します

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

WITH dat AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<col_1 INT64, col_2 INT64>
                (1,2),
                (2,3),
                (1,2),
                (3,4),
                (3,4)
                ])     
    )
    ,dat_2 AS(
    SELECT
        * 
    FROM UNNEST([STRUCT<col_1 INT64, col_2 INT64>
                (1,2),
                (1,2),
                (5,4)
                ])     
    ),
    dat_all AS(
        SELECT * FROM dat
        UNION ALL
        SELECT * FROM dat_2
    ),
    dat_intersect AS(
        SELECT * FROM dat
        INTERSECT DISTINCT
        SELECT * FROM dat_2
    )
SELECT * FROM dat_all
EXCEPT DISTINCT
SELECT * FROM dat_intersect
Share Buttons
 Share on:
Feature Tags
Leave a Comment
(注意：GitHub Accountが必要となります）
CONTENTS