プログラマのためのSQL 読書会(26)に参加

今回もやたら難解なクエリが多く、輪読がはかどらず、もくもく会になってしまう場面が多かったです*1

プログラマのためのSQL 第4版

検証環境:

• MySQL:Ver 14.14 Distrib 5.7.22, for osx10.13 (x86_64) using EditLine wrapper
• PostgreSQL:PostgreSQL 10.1 on x86_64-apple-darwin13.4.0

FETCH FIRST ROWS

fetch first 3 rowsを使って結果から「N行までのデータ」という取り方をすることができます。 それぞれのDBMSの実装依存でTOPやLIMITなども使えますが、一応SQL標準(ANSI 2008)の行数制限指定はこちらですので、 標準SQLにこだわる方はこちらの存在を知っておいた方が良いです。

list_seq  | cat_string
----------+------------
        1 | abc
        2 | bcd
        3 | cde
        4 | def
        5 | efg
        6 | fgh
        7 | ghi

SELECT * FROM make_string FETCH FIRST 3 ROWS ONLY;

 list_seq | cat_string
----------+------------
        1 | abc
        2 | bcd
        3 | cde
(3 rows)

limit句とfetch first句の対応状況です。

SQL-対応DB	Oracle	MySQL	PostgreSQL	DB2	SQLServer
limit	◯	◯	◯	×	×
fetch first	◯	◯	◯	◯	◯

SQLServerでは標準化される前はTOPという構文を使っていたようです。

p.547のクエリが動くのはPostgresだけ

らしいです

--▼P.547


CREATE TABLE Make_String
(list_seq INTEGER NOT NULL PRIMARY KEY,
 cat_string VARCHAR NOT NULL);--MySQLで試す場合はVARCHARのサイズ指定が必要
---

INSERT INTO Make_String
VALUES (1, 'abc'), (2, 'bcd'), (3, 'cde'),
       (4, 'def'), (5, 'efg'), (6, 'fgh'), (7, 'ghi');

---

WITH RECURSIVE
String_Tail(list_seq, cat_string)
AS
(SELECT list_seq, cat_string
   FROM Make_String
  WHERE list_seq > 1),
String_Head(list, max_list_seq, list_seq)
AS
(SELECT cat_string, 1, list_seq
   FROM Make_String
UNION ALL
SELECT String_Head.list || ', ' || String_Tail.cat_string,
       String_Head.max_list_seq + 1,
       String_Head.list_seq
  FROM String_Tail, String_Head
 WHERE String_Tail.list_seq = String_Head.max_list_seq + 1)
SELECT list
  FROM String_Head
 WHERE list_seq = 1
   AND max_list_seq = (SELECT MAX(list_seq)
                         FROM Make_String);

Postgresで試した結果

list                
-----------------------------------
abc, bcd, cde, def, efg, fgh, ghi
(1 row)

確かにMySQLではエラーになった。

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECURSIVE
String_Tail(list_seq, cat_string)
AS
(SELECT list_seq, cat_string
   F' at line 1

p551のクエリに関して

CREATE TABLE Performance
(portfolio_id CHAR(7) NOT NULL,
 execute_date DATE NOT NULL,
 rate_of_return DECIMAL(13,7) NOT NULL);

---

INSERT INTO Performance
VALUES ('001', '2006-01-06', 0.5),
       ('001', '2006-01-07', 0.3),
       ('001', '2006-01-08', 0.6),
       ('001', '2006-01-09', 0.8),
       ('001', '2006-01-10', 0.1);


---


CREATE TABLE BigPi
(execute_date DATE NOT NULL,
 day_1 INTEGER NOT NULL,
 day_2 INTEGER NOT NULL,
 day_3 INTEGER NOT NULL,
 day_4 INTEGER NOT NULL,
 day_5 INTEGER NOT NULL);

---

INSERT INTO BigPi
VALUES ('2006-01-06', 1, 0, 0, 0, 0),
       ('2006-01-07', 0, 1, 0, 0, 0),
       ('2006-01-08', 0, 0, 1, 0, 0),
       ('2006-01-09', 0, 0, 0, 1, 0),
       ('2006-01-10', 0, 0, 0, 0, 1);

SELECT portfolio_id,
       (SUM((1.00 + P1.rate_of_return) * M1.day_1) *
        SUM((1.00 + P1.rate_of_return) * M1.day_2) *
        SUM((1.00 + P1.rate_of_return) * M1.day_3) *
        SUM((1.00 + P1.rate_of_return) * M1.day_4) *
        SUM((1.00 + P1.rate_of_return) * M1.day_5)) AS product
  FROM Performance AS P1, BigPi AS M1
 WHERE M1.execute_date = P1.execute_date
  AND P1.execute_date BETWEEN '2006-01-06' AND '2006-01-10'
 GROUP BY portfolio_id;

途中結果がsumを経由しているので値として0になっているのか、nullが含まれているから0担っているのかわからない、という問題があり対策のCASE式として以下のものが書籍中に紹介されていた

CASE WHEN SUM((1.00 + P1.rate_of_return) * M1.day_N)=0.00
  THEN CAST(NULL AS DECIMAL(13,7))
  ELSE SUM((1.00 + P1.rate_of_return) * M1.day_N)
END

この対策は結局P1.rate_of_returnに-1のものが入ればNULLが伝搬して結果がNULLになってしまうのでは？ということを主張しました(が検証しきれなかった)

上記を適用したクエリと全体は以下のようになる

SELECT portfolio_id,
       (       
       CASE WHEN SUM((1.00 + P1.rate_of_return) * M1.day_1)=0.00
         THEN CAST(NULL AS DECIMAL(13,7))
         ELSE SUM((1.00 + P1.rate_of_return) * M1.day_1)
       END *

       CASE WHEN SUM((1.00 + P1.rate_of_return) * M1.day_2)=0.00
         THEN CAST(NULL AS DECIMAL(13,7))
         ELSE SUM((1.00 + P1.rate_of_return) * M1.day_2)
       END *

       CASE WHEN SUM((1.00 + P1.rate_of_return) * M1.day_3)=0.00
         THEN CAST(NULL AS DECIMAL(13,7))
         ELSE SUM((1.00 + P1.rate_of_return) * M1.day_3)
       END *

       CASE WHEN SUM((1.00 + P1.rate_of_return) * M1.day_4)=0.00
         THEN CAST(NULL AS DECIMAL(13,7))
         ELSE SUM((1.00 + P1.rate_of_return) * M1.day_4)
       END *

       CASE WHEN SUM((1.00 + P1.rate_of_return) * M1.day_5)=0.00
         THEN CAST(NULL AS DECIMAL(13,7))
         ELSE SUM((1.00 + P1.rate_of_return) * M1.day_5)
       END
        ) AS product
  FROM Performance AS P1, BigPi AS M1
 WHERE M1.execute_date = P1.execute_date
  AND P1.execute_date BETWEEN '2006-01-06' AND '2006-01-10'
 GROUP BY portfolio_id;

とりあえず実行してみたところ結果は変わらない

portfolio_id |                product                
-------------+---------------------------------------
001          | 6.17760000000000000000000000000000000
(1 row)

適当にPerformanceテーブルからレコードを抜くとnullになった

portfolio_id | product
-------------+---------
001          |        
(1 row)

もちろん対策前のクエリでは結果は0になります

portfolio_id |                product                
-------------+---------------------------------------
001          | 0.00000000000000000000000000000000000
(1 row)

結局P1.rate_of_returnに-1のものが入ればNULLが伝搬して結果がNULLになってしまうのでは？

上記の自分の疑問が検証できそうなデータを作成しました

DELETE FROM Performance;
INSERT INTO Performance
VALUES ('001', '2006-01-06', 0.5),
       ('001', '2006-01-07', 0.3),
       ('001', '2006-01-08', 0.6),
       ('001', '2006-01-09', -1),--これ!
       ('001', '2006-01-10', 0.1);

するとやはり結果はNULLになりました

 portfolio_id | product
--------------+---------
 001          |        
(1 row)

ただし利率が-1になるような金融というか口座は存在し得ないと思うのでこちらの不具合は多分起こらないかも。 というか普通はこの辺の処理はホスト側の言語でやると思います。

感想

特に本書中で触れられていませんが、BigPiの中身のデータのような感じで 単位行列のようなデータを利用した変態なクエリが多かった印象です。

セルコの他の本でグラフ理論？を扱っているものがありましたが、 線形代数(行列)を使ったクエリ、テーブル設計、RDBMSの実装などもあればさらに変な使い方ができて面白いかも知れません。

*1:おそらく自分が出たことのある会で一番進捗が悪かったと思います