
/* 主キーとUUID */

●Spanner
CREATE TABLE UUID_TEST
(uu VARCHAR(36) PRIMARY KEY);

INSERT INTO UUID_TEST (uu) VALUES(SPANNER.GENERATE_UUID());

●CockroachDB
CREATE TABLE UUID_TEST
(uu UUID PRIMARY KEY);

INSERT INTO UUID_TEST (uu) VALUES(GEN_RANDOM_UUID());

●TiDB
CREATE TABLE UUID_TEST
(uu VARCHAR(36) PRIMARY KEY);

INSERT INTO UUID_TEST (uu) VALUES(UUID());

●YugabyteDB
CREATE EXTENSION pgcrypto;

CREATE TABLE UUID_TEST
(uu UUID PRIMARY KEY);

INSERT INTO UUID_TEST (uu) VALUES(GEN_RANDOM_UUID());


●DSQL
CREATE TABLE UUID_TEST
(uu UUID PRIMARY KEY);

INSERT INTO UUID_TEST (uu) VALUES(gen_random_uuid());

●CockroachDB におけるハッシュ分散の構文サンプル
CREATE TABLE products
(ts DECIMAL PRIMARY KEY USING HASH,
 product_id INTEGER);

●TiDB におけるハッシュ分散の構文サンプル
CREATE TABLE Shard_Hash 
(id INT PRIMARY KEY NONCLUSTERED ) 
 SHARD_ROW_ID_BITS = 4;

●YugabyteDB におけるハッシュ分散の構文サンプル
CREATE TABLE customers
(customer_id CHAR(40) NOT NULL,
 company_name CHAR(40) NOT NULL,
   PRIMARY KEY (customer_id HASH));


/* 参照整合性制約 - 外部キー */

●参照整合性制約
-- 社員テーブル
CREATE TABLE Personnel
 (emp  VARCHAR(32),
  age  INTEGER,
  department VARCHAR(32),
    PRIMARY KEY (emp),
    FOREIGN KEY (department) REFERENCES departments (department)); 

-- 部署テーブル
CREATE TABLE Departments
 (department  VARCHAR(32),
    PRIMARY KEY (department)); 


INSERT INTO Departments VALUES('営業');
INSERT INTO Departments VALUES('研究開発');
INSERT INTO Departments VALUES('総務');
INSERT INTO Departments VALUES('人事');

INSERT INTO Personnel (emp, age, department) VALUES('山田信二',	28,	'営業');
INSERT INTO Personnel (emp, age, department) VALUES('高見恵三',	43,	'研究開発');
INSERT INTO Personnel (emp, age, department) VALUES('吉田洋子',	33,	'総務');
INSERT INTO Personnel (emp, age, department) VALUES('古沢香織',	42,	'営業');
INSERT INTO Personnel (emp, age, department) VALUES('武田宏',	30,	'人事');

●存在しない部署の社員を登録する
INSERT INTO Personnel (emp, age, department) VALUES ('後藤啓二', 40, '広報');

●所属する社員が存在する部署のレコードは削除できない
DELETE FROM Departments 
 WHERE TRIM(department) = '営業';

●参照整合性制約（カスケード）
-- 社員テーブル
CREATE TABLE Personnel
 (emp  VARCHAR(32),
  age  INTEGER,
  department VARCHAR(32),
    PRIMARY KEY (emp),
    FOREIGN KEY (department) REFERENCES departments 
    (department) ON DELETE CASCADE); 


/* 結合 - JOIN */

●二つのよく似たテーブル
CREATE TABLE Class_A
(id   VARCHAR(1), 
 name VARCHAR(16), 
   PRIMARY KEY(id));

CREATE TABLE Class_B
(id   VARCHAR(1), 
 name VARCHAR(16), 
   PRIMARY KEY(id));

INSERT INTO Class_A (id, name) VALUES('1', '田中');
INSERT INTO Class_A (id, name) VALUES('2', '鈴木');
INSERT INTO Class_A (id, name) VALUES('3', '伊集院');

INSERT INTO Class_B (id, name) VALUES('1', '田中');
INSERT INTO Class_B (id, name) VALUES('2', '鈴木');
INSERT INTO Class_B (id, name) VALUES('4', '西園寺');

●内部結合
SELECT A.id   AS id,
       A.name AS name
  FROM Class_A A INNER JOIN Class_B B
    ON A.id = B.id;

●左外部結合
SELECT A.id   AS id,
       A.name AS name
  FROM Class_A A LEFT OUTER JOIN Class_B B
    ON A.id = B.id;

●完全外部結合
SELECT COALESCE(A.id, B.id) AS id,
       COALESCE(A.name, B.name) AS name
  FROM Class_A A FULL OUTER JOIN Class_B B
    ON A.id = B.id;

●Spannerのインターリーブ機能を用いたテーブル定義
CREATE TABLE Singers (
 SingerId   INTEGER NOT NULL,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024)
 ) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INTEGER NOT NULL,
 AlbumId      INTEGER NOT NULL,
 AlbumTitle   VARCHAR(1024),
   PRIMARY KEY (SingerId, AlbumId))
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;


/* Anti Join */

●数値テーブルの定義
CREATE TABLE Num_A
(num     INTEGER);

CREATE TABLE Num_B
(num     INTEGER);

INSERT INTO Num_A (num) VALUES (1);
INSERT INTO Num_A (num) VALUES (2);
INSERT INTO Num_A (num) VALUES (3);
INSERT INTO Num_A (num) VALUES (4);

INSERT INTO Num_B (num) VALUES (1);
INSERT INTO Num_B (num) VALUES (2);
INSERT INTO Num_B (num) VALUES (NULL);


●数値テーブルの定義(Spanner)
CREATE TABLE Num_A
( p_key   INTEGER PRIMARY KEY,
  num     INTEGER);

CREATE TABLE Num_B
(p_key   INTEGER PRIMARY KEY,
 num     INTEGER);


INSERT INTO Num_A (p_key, num) VALUES (1, 1);
INSERT INTO Num_A (p_key, num) VALUES (2, 2);
INSERT INTO Num_A (p_key, num) VALUES (3, 3);
INSERT INTO Num_A (p_key, num) VALUES (4, 4);

INSERT INTO Num_B (p_key, num) VALUES (1, 1);
INSERT INTO Num_B (p_key, num) VALUES (2, 2);
INSERT INTO Num_B (p_key, num) VALUES (3, NULL);

●間違ったクエリ
SELECT num
  FROM Num_A
 WHERE num NOT IN (SELECT num FROM Num_B);

●正しいクエリ
SELECT num
  FROM Num_A
 WHERE NOT EXISTS 
       (SELECT * FROM Num_B
         WHERE Num_A.num = Num_B.num);


/* 条件分岐 - CASE式 */

●日付ごとの来客数テーブル
CREATE TABLE CustomerCount
(record_date DATE,
 dow VARCHAR(3),
 customers INTEGER,
   PRIMARY KEY (record_date));

INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-12', 'Mon', 212);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-13', 'Tue', 540);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-14', 'Wed', 145);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-15', 'Thr', 321);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-16', 'Fri', 670);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-17', 'Sat', 518);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-18', 'Sun', 420);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-19', 'Mon', 376);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-20', 'Tue', 222);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-21', 'Wed', 518);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-22', 'Thr', 842);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-23', 'Fri', 632);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-24', 'Sat', 190);
INSERT INTO CustomerCount (record_date, dow, customers) VALUES ('2024-11-25', 'Sun', 341);


●CASE式によるピボット
SELECT SUM(CASE WHEN dow = 'Mon' THEN customers ELSE 0 END) AS Mon,
       SUM(CASE WHEN dow = 'Tue' THEN customers ELSE 0 END) AS Tue,
       SUM(CASE WHEN dow = 'Wed' THEN customers ELSE 0 END) AS Wed,
       SUM(CASE WHEN dow = 'Thr' THEN customers ELSE 0 END) AS Thr,
       SUM(CASE WHEN dow = 'Fri' THEN customers ELSE 0 END) AS Fri,
       SUM(CASE WHEN dow = 'Sat' THEN customers ELSE 0 END) AS Sat,
       SUM(CASE WHEN dow = 'Sun' THEN customers ELSE 0 END) AS Sun
  FROM CustomerCount;


●スカラサブクエリによる解
SELECT DISTINCT
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Mon') AS Mon,
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Tue') AS Tue,
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Wed') AS Wed,
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Thr') AS Thr,
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Fri') AS Fri,
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Sat') AS Sat,
       (SELECT SUM(customers) FROM CustomerCount WHERE dow = 'Sun') AS Sun
  FROM CustomerCount;


/* ループ - ウィンドウ関数 */

●株価テーブル
CREATE TABLE StockPrice
(deal_date     DATE,
 ticker_symbol VARCHAR(4),
 stock_price   INTEGER,
   PRIMARY KEY (deal_date, ticker_symbol));


INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-01', 'AAAA', 120);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-02', 'AAAA', 125);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-03', 'AAAA', 150);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-04', 'AAAA', 90);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-05', 'AAAA', 104);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-06', 'AAAA', 190);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-01', 'BBBB', 300);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-05', 'BBBB', 200);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-07', 'BBBB', 150);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-09', 'BBBB', 212);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-12', 'BBBB', 350);
INSERT INTO StockPrice (deal_date, ticker_symbol, stock_price) VALUES ('2025-04-13', 'BBBB', 800);

●ウィンドウ関数による移動平均(※Spannerでは動作せず)
SELECT deal_date,
       ticker_symbol,
       AVG(stock_price) OVER (PARTITION BY ticker_symbol 
                                  ORDER BY deal_date
                                   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS mvg_avg 
  FROM StockPrice;


●ウィンドウ関数による順位の求め方(※Spannerはウィンドウ関数をサポートしていないため動作せず)
SELECT deal_date,
       ticker_symbol,
       stock_price,
       RANK() OVER (PARTITION BY ticker_symbol 
                        ORDER BY stock_price DESC) AS stock_rank 
  FROM StockPrice;


●SpannerのGoogleSQLにおけるDDL（ただし移動平均を求めるクエリは動作しない）
CREATE TABLE StockPrice
 (deal_date DATE,
  ticker_symbol STRING(4),
  stock_price INT64)
    PRIMARY KEY (deal_date, ticker_symbol);

●相関サブクエリによるランキングの求め方(Spannerでも動作する)
SELECT deal_date,
       ticker_symbol,
       stock_price,
       (SELECT COUNT(*)
          FROM StockPrice SP1
         WHERE SP0.ticker_symbol = SP1.ticker_symbol
           AND SP1.stock_price >= SP0.stock_price) AS stock_rank 
  FROM StockPrice SP0;


/* HAVING句 */


●学生テーブル
CREATE TABLE Students
(student_id   INTEGER,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE,
   PRIMARY KEY (student_id));

INSERT INTO Students VALUES(100,  '理学部',   '2025-10-10');
INSERT INTO Students VALUES(101,  '理学部',   '2025-09-22');
INSERT INTO Students VALUES(102,  '文学部',   NULL);
INSERT INTO Students VALUES(103,  '文学部',   '2025-09-10');
INSERT INTO Students VALUES(200,  '文学部',   '2025-09-22');
INSERT INTO Students VALUES(201,  '工学部',   NULL);
INSERT INTO Students VALUES(202,  '経済学部', '2025-09-25');

●提出日にNULLを含まない学部を選択する
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
                           THEN 1
                           ELSE 0 END);



/* サブクエリ */

●顧客明細テーブル
CREATE TABLE Receipts
(customer_id   VARCHAR(4) NOT NULL, 
 seq           INTEGER NOT NULL, 
 price         INTEGER NOT NULL, 
   PRIMARY KEY (customer_id, seq));

INSERT INTO Receipts VALUES ('A',   1   ,500    );
INSERT INTO Receipts VALUES ('A',   2   ,1000   );
INSERT INTO Receipts VALUES ('A',   3   ,700    );
INSERT INTO Receipts VALUES ('B',   5   ,100    );
INSERT INTO Receipts VALUES ('B',   6   ,5000   );
INSERT INTO Receipts VALUES ('B',   7   ,300    );
INSERT INTO Receipts VALUES ('B',   9   ,200    );
INSERT INTO Receipts VALUES ('B',   12  ,1000   );
INSERT INTO Receipts VALUES ('C',   10  ,600    );
INSERT INTO Receipts VALUES ('C',   20  ,100    );
INSERT INTO Receipts VALUES ('C',   45  ,200    );
INSERT INTO Receipts VALUES ('C',   70  ,50     );
INSERT INTO Receipts VALUES ('D',   3   ,2000   );


●サブクエリによる解
SELECT *
  FROM (SELECT customer_id, seq,
               ROW_NUMBER() OVER (PARTITION BY customer_id 
                                      ORDER BY seq) AS min_seq
          FROM Receipts) TMP
 WHERE min_seq = 1; 


/* ビュー */

●ビューの定義（Spanner以外）
CREATE VIEW V_Class (id, name)
AS
SELECT A.id   AS id,
       A.name AS name
  FROM Class_A A LEFT OUTER JOIN Class_B B
    ON A.id = B.id;

このようにビューの定義で結合等の操作を行っていても問題なく作成可能です。なお、Spannerだけは定義に列名を含めるとエラーになるという仕様があるため、以下のように列名を削除してください。

●ビューの定義（Spanner）
CREATE VIEW V_Class 
AS
SELECT A.id   AS id,
       A.name AS name
  FROM Class_A A LEFT OUTER JOIN Class_B B
    ON A.id = B.id;

●マテリアライズド・ビューの定義（YugabyteDBとCockroachDB）
CREATE MATERIALIZED VIEW MV_Class (id, name)
AS
SELECT A.id   AS id,
       A.name AS name
  FROM Class_A A LEFT OUTER JOIN Class_B B
    ON A.id = B.id;


/* 再帰共通表式 */

●再帰共通表式による自然数列の生成(※Spannerでは動作せず)
WITH RECURSIVE NumberGenerate (num) AS
(SELECT 1 AS num /* 開始点となるクエリ */
 UNION ALL
 SELECT num + 1 AS num /* 再帰的に繰り返されるクエリ */
   FROM NumberGenerate
  WHERE num < 99)
SELECT num
  FROM NumberGenerate;


●経路テーブルの定義
CREATE TABLE Routes
(source_city	  VARCHAR(32),
 destination_city VARCHAR(32) ,	
 distance         INTEGER NOT NULL,
   CONSTRAINT pk_Cities PRIMARY KEY (source_city, destination_city));

INSERT INTO Routes (source_city, destination_city, distance) VALUES ('New York', 'Boston',	215);
INSERT INTO Routes (source_city, destination_city, distance) VALUES ('New York', 'Philadelphia',	95);
INSERT INTO Routes (source_city, destination_city, distance) VALUES ('Philadelphia', 'Washington',	140);
INSERT INTO Routes (source_city, destination_city, distance) VALUES ('Boston', 'Chicago', 985);
INSERT INTO Routes (source_city, destination_city, distance) VALUES ('Washington',	'Atlanta',	640);
INSERT INTO Routes (source_city, destination_city, distance) VALUES ('Atlanta',	'Miami',	660);

●再帰共通表式による解
WITH RECURSIVE CityDistances (source_city, destination_city, distance) AS (
    -- 開始点：New Yorkからの直接の経路
    SELECT source_city, destination_city, distance
      FROM Routes
     WHERE source_city = 'New York'
    UNION ALL
    -- 再帰部分：既に見つけた経路から延長して新しい経路を発見
    SELECT cd.source_city, r.destination_city, cd.distance + r.distance AS total_distance
    FROM CityDistances cd INNER JOIN Routes r
         ON cd.destination_city = r.source_city)
-- 結果の出力：New Yorkから各都市への最短距離
SELECT destination_city AS city,
       MIN(distance) AS distance_from_new_york
  FROM CityDistances c1
 GROUP BY destination_city
 ORDER BY distance_from_new_york;

/* LIMIT句 */

●LIMIT句で結果の表示を3行に制限
SELECT source_city, destination_city, distance
  FROM Routes
 ORDER BY distance
 LIMIT 3;


/* 集合演算 */

●微妙に異なる二つのテーブル
CREATE TABLE Tbl_A
(key_col   VARCHAR(1) NOT NULL,
 col_1     INTEGER,
 col_2     INTEGER,
 col_3     INTEGER,
   PRIMARY KEY (key_col));

CREATE TABLE Tbl_B
(key_col   VARCHAR(1) NOT NULL,
 col_1     INTEGER,
 col_2     INTEGER,
 col_3     INTEGER,
   PRIMARY KEY (key_col));

INSERT INTO Tbl_A VALUES('A', 1, 2, 3);
INSERT INTO Tbl_A VALUES('B', 4, 5, 6);
INSERT INTO Tbl_A VALUES('C', 7, 8, 9);

INSERT INTO Tbl_B VALUES('A', 1, 2, 3);
INSERT INTO Tbl_B VALUES('B', 4, 5, 6);
INSERT INTO Tbl_B VALUES('C', 7, 8, 0);


●UNIONによるレコードの集約
SELECT * FROM Tbl_A
UNION
SELECT * FROM Tbl_B;


●INTERSECTによるレコードの集約
SELECT * FROM Tbl_A
INTERSECT
SELECT * FROM Tbl_B;


●EXCEPTによるレコードの減算
SELECT * FROM Tbl_A
EXCEPT
SELECT * FROM Tbl_B;


/* JSON */

●JSON型を持つテーブル(TiDB)
CREATE TABLE Member
(id INTEGER PRIMARY KEY,
 memo JSON NOT NULL);

●登録するデータ
INSERT INTO Member VALUES(1, '{"name": "Alice", "age": 45, "height": 172}');
INSERT INTO Member VALUES(2, '{"name": "Bob",   "age": 30, "height": 185}');
INSERT INTO Member VALUES(3, '{"name": "Kent",  "age": 30, "height": 169}');
INSERT INTO Member VALUES(4, '{"name": "Mary",  "age": 45, "height": 169}');
INSERT INTO Member VALUES(5, '{"name": "Joe",   "age": 22, "height": 169}');

●年齢が同じ人のペアを見つける（TiDB）
SELECT M1.memo->>"$.name" AS name1, M2.memo->>"$.name" AS name2
  FROM Member M1
       INNER JOIN Member M2
    ON M1.id > M2.id
 WHERE M1.memo->>"$.age" = M2.memo->>"$.age";


●JSONB型を持つテーブル(YugabyteDBとCockroachDB)
CREATE TABLE Member
(id INTEGER PRIMARY KEY,
 memo JSONB NOT NULL);

●年齢が同じ人のペアを見つける（YugabyteDBとCockroachDB）
SELECT M1.memo->'name' AS name1, M2.memo->'name' AS name2
  FROM Member M1
       INNER JOIN Member M2
    ON M1.id > M2.id
 WHERE M1.memo->>'age' = M2.memo->>'age';

●GINインデックスの作成（YugabyteDBとCockroachDB）
CREATE INDEX gin_idx_memo ON Member USING GIN (memo);

●検索インデックスの作成（Spanner）
ALTER TABLE Member
ADD COLUMN memo_tokens spanner.tokenlist
  GENERATED ALWAYS AS (spanner.tokenize_jsonb(memo)) VIRTUAL HIDDEN;

CREATE SEARCH INDEX idx_memo ON Member (memo_tokens);


●インデックス作成構文のサンプル（全DBMS共通）
CREATE INDEX idx_col1 ON Tbl_A (col_1);


/* ベクトル型 */

●ベクトル型のテーブル定義
-- 3次元のベクトル型
CREATE TABLE vector_table 
( id INT PRIMARY KEY,
  embedding VECTOR(3));

-- データ登録
INSERT INTO vector_table (id, embedding) VALUES (1, '[0.3, 0.5, -0.1]');
INSERT INTO vector_table (id, embedding) VALUES (2, '[0.4, 0.8, 0.5]');
INSERT INTO vector_table (id, embedding) VALUES (3, '[0.5, 0.1, -0.3]');
INSERT INTO vector_table (id, embedding) VALUES (4, '[0.8, 3.2, -1.3]');
INSERT INTO vector_table (id, embedding) VALUES (5, '[3.0, 1.2, 1.8]');


●ユークリッド距離の計測
SELECT id, embedding,
       embedding <-> '[3,1,2]' AS distance
  FROM vector_table
 ORDER BY distance
 LIMIT 3;


●`pgvector`拡張機能を有効化（YugabyteDB）
CREATE EXTENSION vector;

●ベクトル型のテーブル定義（GoogleSQL）
-- 3次元のベクトル型
CREATE TABLE vector_table 
( id INT64 PRIMARY KEY,
  embedding ARRAY<FLOAT64>(vector_length => 3));

INSERT INTO vector_table (id, embedding) VALUES (1, [0.3, 0.5, -0.1]);
