26.7から27.1.3まで読みました。

p478の複雑なクエリの解説

基底テーブル

--▼P.476

CREATE TABLE AnnualSales1
(salesman CHAR(15) NOT NULL PRIMARY KEY,
 jan DECIMAL(5,2),
 feb DECIMAL(5,2),
 mar DECIMAL(5,2),
 apr DECIMAL(5,2),
 may DECIMAL(5,2),
 jun DECIMAL(5,2),
 jul DECIMAL(5,2),
 aug DECIMAL(5,2),
 sep DECIMAL(5,2),
 oct DECIMAL(5,2),
 nov DECIMAL(5,2),
 "dec" DECIMAL(5,2)); -- decは予約語のため、ダブルクォートが必要

イメージとしては1～12月の売上とセールスマンの情報をペアに持つテーブルになります。とりあえずこのテーブルに入るデータを用意します。

投入データ(オリジナル)

insert into AnnualSales1
    (salesman,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,"dec")
values
    ('yabu',200.2,190.3,220.21,240.12,220.65,510.78,190.89,220.11,230.43,210.32,190.43,480.23)

1個目のビュー

--▼P.477
CREATE VIEW NumberedSales
AS SELECT salesman,
    1 AS M01, jan,
    2 AS M02, feb,
    3 AS M03, mar,
    4 AS M04, apr,
    5 AS M05, may,
    6 AS M06, jun,
    7 AS M07, jul,
    8 AS M08, aug,
    9 AS M09, sep,
    10 AS M10, oct,
    11 AS M11, nov,
    12 AS M12, "dec"
  FROM AnnualSales1;

2個目のビュー

--▼P.478
CREATE VIEW AnnualSales2 (salesman, month, sales_amt)
AS SELECT S1.salesman,
        (CASE WHEN A.nbr = M01 THEN 'Jan'
              WHEN A.nbr = M02 THEN 'Feb'
              WHEN A.nbr = M03 THEN 'Mar'
              WHEN A.nbr = M04 THEN 'Apr'
              WHEN A.nbr = M05 THEN 'May'
              WHEN A.nbr = M06 THEN 'Jun'
              WHEN A.nbr = M07 THEN 'Jul'
              WHEN A.nbr = M08 THEN 'Aug'
              WHEN A.nbr = M09 THEN 'Sep'
              WHEN A.nbr = M10 THEN 'Oct'
              WHEN A.nbr = M11 THEN 'Nov'
              WHEN A.nbr = M12 THEN 'Dec'
              ELSE NULL END),
        (CASE WHEN A.nbr = M01 THEN jan
              WHEN A.nbr = M02 THEN feb
              WHEN A.nbr = M03 THEN mar
              WHEN A.nbr = M04 THEN apr
              WHEN A.nbr = M05 THEN may
              WHEN A.nbr = M06 THEN jun
              WHEN A.nbr = M07 THEN jul
              WHEN A.nbr = M08 THEN aug
              WHEN A.nbr = M09 THEN sep
              WHEN A.nbr = M10 THEN oct
              WHEN A.nbr = M11 THEN nov
              WHEN A.nbr = M12 THEN "dec"
              ELSE NULL END)
     FROM NumberedSales AS S1
          CROSS JOIN
            (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12))
                AS A(nbr);

select * from AnnualSales1

salesman	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
yabu	200.20	190.30	220.21	240.12	220.65	510.78	190.89	220.11	230.43	210.32	190.43	480.23

select * from NumberedSales;

salesman	M01	jan	M02	feb	M03	mar	M04	apr	M05	may	M06	jun	M07	jul	M08	aug	M09	sep	M10	oct	M11	nov	M12	dec
yabu	1	200.20	2	190.30	3	220.21	4	240.12	5	220.65	6	510.78	7	190.89	8	220.11	9	230.43	10	210.32	11	190.43	12	480.23

select * from AnnualSales2;

salesman	month	sales_amt
yabu	Jan	200.20
yabu	Feb	190.30
yabu	Mar	220.21
yabu	Apr	240.12
yabu	May	220.65
yabu	Jun	510.78
yabu	Jul	190.89
yabu	Aug	220.11
yabu	Sep	230.43
yabu	Oct	210.32
yabu	Nov	190.43
yabu	Dec	480.23

イメージ的には

水平展開されている規定テーブルを縦横変換する感じでしょうか。

p479のクエリについて

自宅で帰って動かしたところ、PostgreSQL 10では動作しませんでした。

SELECT *
  FROM ((SELECT x FROM Foo) --このサブクエリの結果に名前を付ける必要あり
       LEFT OUTER JOIN
       (SELECT x FROM Bar) --このサブクエリの結果に名前を付ける必要あり
          ON Foo.x = Bar.x) AS Foobar(x1, x2)
       INNER JOIN Floob
         ON Floob.y = x1;

以下のように変えると動きました。

with
foo(x)   as (values(1),(2),(3)),
bar(x)   as (values(1),(4),(3)),
floob(x,y) as (values(1,1),(9,2))

--↑共通テーブル式でクエリ実行に必要なテーブルを宣言しています。

SELECT *
  FROM ((SELECT x FROM Foo) as foo --修正
       LEFT OUTER JOIN
       (SELECT x FROM Bar) as bar --修正
          ON Foo.x = Bar.x) AS Foobar(x1, x2)
       INNER JOIN Floob
         ON Floob.y = foobar.x1;

結果

x1	x2	x	y
1	1	1	1
2		9	2

そもそもの私の疑問はこれの一つ上の「まだ間違い」とコメントされているクエリの結合結果に別名を付け足せれば動くのでは？というものでした。

SELECT *
  FROM (Foo AS F1
        LEFT OUTER JOIN
          Bar AS B1
          ON F1.x = B1.x)
          as Foo(x,y) --この行を付け足しています！
        INNER JOIN Floob
           ON Floob.y = Foo.x; -- まだ間違い

私が付け足した行で正しくうごきました。

x1	x2	x	y
1	1	1	1
2		9	2

結果も本書で正しい、とされている最終のクエリと同じものになりました。*1

MySQLと艦これ

昔艦これでMySQL Clusterのエラーメッセージが出ていたらしい。

http://nice-boat.jp/archives/46

最近のMySQLでは全てのエラーにエラーコードがついた。
エラーコードといえば。。。

と言う文脈でこの話題が出てきました。

そもそもエンドユーザーにはミドルウェアのエラーメッセージは出さないのが基本です！！！

https://www.ipa.go.jp/security/awareness/vendor/programmingv1/b09_03.html

一時テーブル

create global temporary table などはこの本が出た後にSQL標準に入ったかも？という話だが、「create global temporary table iso」などでググってもそれらしいものが出なかった。

一時表はmysqlはクエリのチューニングでよく使う

アッカーマン関数

再帰の説明に出てきてなにこれ？となった

与える数が大きくなると爆発的に計算量が大きくなるという特徴があり、性能測定などに用いられることもある。

https://ja.wikipedia.org/wiki/%E3%82%A2%E3%83%83%E3%82%AB%E3%83%BC%E3%83%9E%E3%83%B3%E9%96%A2%E6%95%B0

だそうです。

郵便番号はユニークではない。

https://qiita.com/_takwat/items/3a121656425fac7bb820

事態は結構深刻だと思う。

郵便番号は必ず1つの町名に紐づいているわけではない
市区町村をまたいで同じ郵便番号を持つケースがある
市区町村はおろか県を飛び越えて同じ郵便番号を持ちうるケースがある
まぁ郵便番号をナチュラルキーにするのはやめましょうということですね。
- ナチュラルキー事態があまり好きではありません。

UNION互換

行式、テーブル式の結果の変数型と順番が同じこと。

UNION 互換とは、以下の二つの条件を共に満たすことです。 1.列の数が同一であること 2.同じ列位置の列のデータ型が同じ（または自動的に型変換可能）であること

http://www.geocities.jp/mickindex/database/celko/celko_so.html

ミックさんのサイトより引用。

和両立ともいう

和両立(union compatible)とは、2つの関係において、次数(属性数／列数)が等しく、対応する属性同士のドメイン(値の型・範囲)が等しく、2つの関係の型が適合している様子をいいます。次の例は和両立である2つの関係です。

https://www.db-siken.com/kakomon/21_haru/am2_8.html

デスペの過去問解説から引用。

一応本書にも34章に解説がある😅

これはイカの全ての条件を満たすことを言う。 1.２つのテーブルが同じ列数である 2.同じ位置の隠れつは同じデータ型(または自動的な変換が可能)である。

本書34賞より引用。ミックさんのサイトのものとほぼ同じですね。

この本後半で解説するものを断りもなく前半でバンバン出し過ぎな気がする。

誤植

p.488 ZipCodeにcity列がない

▼P.488

CREATE TABLE ZipCodes
(state_code CHAR(2) NOT NULL PRIMARY KEY,
 low_zip CHAR(5) NOT NULL UNIQUE,
 high_zip CHAR(5) NOT NULL UNIQUE,
     CONSTRAINT zip_order_okay CHECK(low_zip < high_zip));

---

SELECT A1.name, A1.street, SZ.city, SZ.state_code, A1.zip
  FROM ZipCodes AS SZ, AddressBook AS A1
 WHERE A1.zip BETWEEN SZ.low_zip AND SZ.high_zip;

p489 Address.zip => AdressBook.zipでは？との指摘がありました。

▼P.489

SELECT name, street, city, state, zip
  FROM StateZip2, AddressBook
 WHERE state =
         (SELECT state
            FROM StateZip2
           WHERE high_zip =
                   (SELECT MIN(high_zip)
                      FROM StateZip2
                     WHERE Address.zip <= StateZip2.high_zip));