データベース操作・集計 - kuboshoの脳内dump

データベース操作・集計

カテゴリー: #リスキリング

kuboshoのアンラーニング・リスキリングの3日目は「データベース操作」を取り扱う。

ここではデータベースの操作のみ扱う。

概要

とりあえず既存のデータを抽出したり、テーブル同士を結合したい場合のやり方を書いていく。

データ操作一覧

CRUDでいう「CUD」の部分がいま使っている教本にはないため、現状は詳細を割愛。

（CUDに当たる部分は基本情報技術者試験に出てこないの？）

選択（selection）：CRUDの「R」

テーブル内のレコード（行、row）を抽出する

SELECT で実現できる

SELECT * FROM {TableA} の書き方だとすべてのレコードが読み込まれてしまうため、WHERE {条件式} で読み込むデータを絞る

射影（projection）：CRUDの「R」

テーブル内の列（attribute, column）を抽出する

列に光をあてて照らすイメージ

SELECT で実現できる

SELECT {Col1, Col2, ColN} という構文が射影

結合（join, union）

1つのSELECTで各テーブルを結合してデータを取り出したい場合に使う

CROSS JOIN, INNER JOIN, (LEFT / RIGHT / FULL) OUTER JOINといった結合方法がある

よく使われるのはINNER JOINとLEFT OUTER JOINとのこと

INNER JOINは指定した条件に当てはまらないレコードは除外される

LEFT OUTER JOINは {TableA} LEFT OUTER JOIN {TableB} ON {条件式} といった構文になり、{TableA} のデータを基準に、指定したテーブル同士を結合する

なおいちばんやさしい基本情報技術者絶対合格の教科書＋出る順問題集には特に注意書きなくANSI X3.135:1989で定められた FROM {TableA}, {TableB} というCROSS JOIN（暗黙的結合または自然結合）の書き方しか書いていない

さすがに今はもう実務で使われないだろう……むしろこれで使う人が出たらどうするんだ？

CROSS JOINも組み合わせ爆発が起こりやすいのであまり使われないようだけど、CROSS JOINに関しても SELECT * FROM {TableA} CROSS JOIN {TableB} のような書き方を最初から学んだほうが良さそう

JOINは横方向にテーブル内のデータを結合し、UNIONは縦方向にテーブル内のデータを結合する

UNIONはinfinite scrollのUIをイメージすると分かりやすいかも

挿入（insert）：CRUDの「C」

更新（update）：CRUDの「U」

削除（delete）：CRUDの「D」

データ集計

データを基に合計や平均などを求めたい場合は関数を使う

SUM(columnName), AVG(columnName), MIN(columnName), MAX(columnName), COUNT((*|columnName)) といったものがある

COUNT() だけは列ではなく行を数えるもの

COUNT(*) はデータベースのtuple（この場合は行）の件数を数えるためデータに NULL が入っていてもそれを含むが、COUNT(columnName) は NULL を含まない

データのグループ化

PostgreSQL: Documentation: 18: 7.2. Table Expressionsからコードを引用。GROUP BY は下記のようにグループ化したい列を指定し、その後 HAVING でさらにデータを絞る構文になっている。

code:sql

SELECT select_list

FROM ...

WHERE ...

GROUP BY grouping_column_reference grouping_column_reference...

HAVING boolean_expression

SUM(columnName) と任意の列を SELECT したときのように、非集約の列と集約済みの結果を同時に扱う場合は、GROUP BY を使って、どの列を基準として集約済みの結果をまとめるか定義する必要がある。

データビュー

CREATE VIEW で作れるやつ。スプレッドシートでいう「グループ化ビュー」や「フィルター表示」といったもの

あとはピボットテーブルとか？

元データと連動して表示を変えられるとともに、表示したくないデータを除外（隠ぺい）できる

index（索引）

データベース内のある列を基に索引を作ること

特定のレコードを早く見つけられる

レコードを更新するたびにindexを貼りなおす必要があるため、indexを増やしすぎた場合はindexの更新処理によってデータベースの処理実行効率が下がる

なのでindexはほどほどに、必要なだけ貼る

ストアドプロシージャ

個別のクエリをまとめて処理できるようにできる仕組み

PostgreSQL: Documentation: 18: CREATE PROCEDURE

RDBMSへの依存が生まれるため、使われない場合もある

Are there any good reasons to still use DB Stored Procedures in 2022? : r/softwarearchitecture

昔は使ってたけど今はあまり使ってないDBの機能 #MySQL - Qiita

新人の頃、SQLのストアドプロシージャやファンクションは使わない方が良いと教わったのですが何故ですか？ - Quora

適材適所で使う場合もあるんだろうなという感想を持った

Gitなどのバージョン管理システムで管理しにくいのかも

ORMを使う場合はストアドプロシージャを使わないことが多そう。ORM側でそれぞれのデータベース管理システムのストアドプロシージャに対応しないといけなくなるし、それは面倒では？