PostgreSQL 12.4文書のまとめノート　3章

#### [PostgreSQL 12.4文書](https://www.postgresql.jp/document/12/html/)ノート
※まとめノートです。自分の視点で書いたコードがたまに混ざっております。ライセンスは下部に記載しております。


#### 第3章 高度な諸機能
##### 3.1. はじめに
##### 3.2. ビュー
> ビューを自由に利用することは、SQLデータベースの良い設計における重要な項目です。 ビューはテーブル構造の詳細をカプセル化しますので、アプリケーションが発展するに従いテーブル構造が変わったとしても、一貫したインタフェースを保てます。
> ビューは実テーブルが使用できるほとんどの場所で使えます。 他のビューに対するビューの作成も珍しくはありません。

- 一貫したインターフェースを保てるのはわかるが、カプセル化って文の保存のことだろうか？

##### 3.3. 外部キー

- citiesテーブルに一致する項目がない行は絶対にweatherテーブルに挿入できなくしたいとします。以下のように外部キーをもつテーブルを作成し、実際にエラーを確認できる。

```
drop TABLE weather cascade;
drop TABLE cities cascade;

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);
CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
```

##### 3.4. トランザクション
- トランザクションは複数の独立した操作を単一のall or nothingにまとめ上げる。


> いったんトランザクションが完結しデータベースシステムに承認された場合は、確実に恒久的に保存され、たとえ直後にクラッシュが起こったとしても記録は失われないという確証も必要です。 例えばボブが自分の口座から現金を引き落として店舗から立ち去った直後にボブの口座からの引き落とし記録がシステムのクラッシュで消えてしまうことは受け入れられません。 トランザクションが実装されているデータベースでは、あるトランザクションによる全ての更新がそのトランザクションを完結したと通知を行う前に永続的記録装置（すなわちディスク上）にログを書き込むことで保証しています。
> PostgreSQLは実際全てのSQL文をトランザクション内で実行するようになっています。 BEGINを発行しない場合、それぞれの文は暗黙的にBEGINが付いているとみなし、（成功すれば）COMMITで囲まれているものとします。 BEGINとCOMMITで囲まれた文のグループはトランザクションブロックという

- セーブポイントは使わないので使う時が来たら再復習

##### 3.5. ウィンドウ関数
- window関数でパーティションを切って集約すれば平の行でとってくることができて情報が多いので便利そう

```
SELECT
passengerid,
age,
sex,
fare,
avg(fare) over (PARTITION by sex) as avg_fare
from train;
```

- ウィンドウ関数で考慮される行は、そのWHERE、GROUP BY、およびHAVING句でフィルターをかけられた問い合わせのFROM句によって生成された「仮想テーブル」の行
> ORDER BYは、行の順序付けが重要でない場合、省略可能であることを見てきました。 PARTITION BYも同様に割愛することができます。 この場合、全ての行を含む単一のパーティションが存在します。 

- ウィンドウ関数に関連した別の重要な概念があります。 それぞれの行に対して、そのウィンドウフレームと呼ばれる、そのパーティション内の行の集合が存在します。 ウィンドウ関数の中には、パーティション全体ではなく、ウィンドウフレームの行のみに対して作用するものもあります。
    - between等の演算子で指定するやつのことを指しているんだろう。

> ウィンドウ関数の中には、パーティション全体ではなく、ウィンドウフレームの行のみに対して作用するものもあります。 デフォルトでは、ORDER BYが指定されると、フレームは、パーティションの始めから現在の行までのすべての行、およびそれより後にあるがORDER BY句に従うと現在の行とおなじ順序になるすべての行から構成されます。 

- この仕様だとCumulative sumが正しく求められない。
- titanicデータセットでfare>0.0のorder by集合
に対して累積和をとったものがした画像だが、
6行目(便宜的に順序の概念を適用します)のcumsumはorder byした時に同じ順序になる7行目の6.495800000も含んでしまっている。厳密にいうとこれは累積和ではないし代替手段が必要。

```
SELECT passengerid,
fare,
sum(fare) OVER (ORDER BY fare)
FROM train
WHERE fare > 0
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/39d04e3d-3762-f500-8b80-bee2a997af89.png)

- 何やら重大なことのように言ってしまったが、ウィンドウフレームを「無制限に前の行から現在の行」と指定してやれば問題なく累積和を求めることができる。

```
SELECT passengerid,
fare,
sum(fare) OVER (ORDER BY fare
ROWS BETWEEN UNBOUNDED PRECEDING 
AND CURRENT ROW)
FROM train
WHERE fare > 0
;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1347377/60cdee69-ba10-d63e-b63b-5ff11cb565c4.png)

-  ウィンドウ演算が行われた後、行にフィルタ処理を行ったりグループ化を行う必要が生じた場合、副問い合わせを使用すればフィルタリングできる。

```
select
    passengerid,
    fare,
    rank,
    DENSE_RANK,
    ROW_NUMBER
from
(SELECT
    passengerid,
    fare,
    rank() OVER (ORDER BY fare desc) rank,
    DENSE_RANK() OVER (ORDER BY fare desc) DENSE_RANK,
    ROW_NUMBER() OVER (ORDER BY fare desc) ROW_NUMBER
FROM train
WHERE fare > 0
) tmp
where row_number < 10
;
```

- 当然だが、服問い合わせを使用せず直観に従って書いた以下のクエリは構文エラーである。

```
SELECT
    passengerid,
    fare,
    rank() OVER (ORDER BY fare desc) rank,
    DENSE_RANK() OVER (ORDER BY fare desc) DENSE_RANK,
    ROW_NUMBER() OVER (ORDER BY fare desc) ROW_NUMBER
FROM train
WHERE fare > 0
having ROW_NUMBER < 10
```

ウィンドウの使いまわしは省略

##### 3.6. 継承
> オブジェクト指向データベースとは
オブジェクト指向プログラミングを取り入れたデータベースを指す。 ... テキスト、音声、動画のデータの異なる種類や形式のデータが混在するデータ群や、多数のデータが一体的に結び付いたデータ構造の格納に適しており、特にCADシステムやマルチメディアデータベースなどの管理に適している。

> 継承とはオブジェクト指向データベースの概念です。 データベース設計においてこれまでになかった興味深い可能性を広げてくれます。

- 具体例がうまく動作しなかったので略、多分他ソースで補完できる