【実務・中級編】 組み込み関数概要 – PostgreSQL

PostgreSQLの組み込み関数、知っておくとマジで便利だから！〜文字列、計算、日付…現場で役立つヤツらを紹介〜

おいっす！データベースエンジニアの〇〇（←あなたの名前を入れてね）だ。普段はPostgreSQLと日々格闘してるんだけど、今日はちょっと息抜きがてら、みんなが現場で「あー、これ知ってたら楽だったのに！」って絶対思うであろう、PostgreSQLの組み込み関数について話そうと思う。

ぶっちゃけ、DBの操作って、SELECT文をひたすら書くだけじゃなくて、データの中身を加工したり、分析したりする場面もめちゃくちゃ多いんだよな。そんな時に、PostgreSQLが標準で提供してくれる「組み込み関数」が、マジで頼りになるんだ。これらを使いこなせると、コードがスッキリするし、処理も速くなるし、何より「できるエンジニア」って感じになれる（笑）。

今回は、現場でよく使うであろう主要な関数カテゴリをいくつかピックアップして、どんなことができるのか、具体的な例を交えながら紹介していくから、ぜひ最後までついてきてくれよな！

まずは基本！文字列操作関数でデータをキレイにしようぜ

データって、現場に入ってくる時って結構バラバラなんだよな。全角・半角混じりだったり、余計なスペースがあったり…。そんな時に大活躍するのが、文字列操作関数だ。

1. 文字列の整形・加工系

  • `TRIM()`: 文字列の先頭や末尾、あるいは両方から指定した文字（デフォルトはスペース）を取り除いてくれる。これ、地味だけどめちゃくちゃ使う。

— 例: ‘ Hello World ‘ から前後のスペースを削除
SELECT TRIM(‘ Hello World ‘);
— 結果: ‘Hello World’

— 例: ‘Hello‘ から を削除
SELECT TRIM(LEADING ” FROM ‘Hello‘);
— 結果: ‘Hello’

SELECT TRIM(TRAILING ” FROM ‘Hello‘);
— 結果: ‘Hello’

SELECT TRIM(BOTH ” FROM ‘Hello‘);
— 結果: ‘Hello’

`LEADING`、`TRAILING`、`BOTH` を使い分けるのがポイントだ。

  • `LOWER()` / `UPPER()`: 文字列をすべて小文字、あるいは大文字に変換してくれる。大文字・小文字を区別せずに検索したい時とかに便利だ。

SELECT LOWER(‘HeLLo’);
— 結果: ‘hello’

SELECT UPPER(‘HeLLo’);
— 結果: ‘HELLO’

  • `SUBSTRING()`: 文字列の一部を取り出す関数。開始位置と長さを指定するんだけど、PostgreSQLだとちょっと独特だから注意が必要だ。

— 例: ‘PostgreSQL’ の3文字目から4文字取り出す
SELECT SUBSTRING(‘PostgreSQL’ FROM 3 FOR 4);
— 結果: ‘stgr’

`FROM` と `FOR` を使うのがPostgreSQL流。他のDBだと `SUBSTRING(string, start, length)` って書くことが多いから、意識しておくといいぞ。

  • `REPLACE()`: 文字列中の特定の文字列を別の文字列に置き換える。これもよく使う。

— 例: ‘Hello World’ の ‘World’ を ‘Universe’ に置換
SELECT REPLACE(‘Hello World’, ‘World’, ‘Universe’);
— 結果: ‘Hello Universe’

「え、そんな単純なこと？」って思うかもしれないけど、これが複雑なデータ整形には欠かせないんだ。

2. 文字列の結合・分割系

  • `||` (連結演算子): 複数の文字列をくっつける。SQL標準だけど、PostgreSQLではこれが一番シンプルで分かりやすい。

SELECT ‘Hello’ || ‘ ‘ || ‘World’;
— 結果: ‘Hello World’

`CONCAT()` 関数もあるけど、個人的には `||` で十分かな。

  • `SPLIT_PART()`: 指定した区切り文字で文字列を分割し、指定した番目の部分を返す。これはマジで便利。

— 例: ‘apple,banana,orange’ を ‘,’ で分割して2番目の要素を取得
SELECT SPLIT_PART(‘apple,banana,orange’, ‘,’, 2);
— 結果: ‘banana’

CSVデータとかを扱うときに、この関数を知ってるかどうかで作業効率が全然変わるぞ。

数字を操る！数学・集計関数で分析の幅を広げよう

データ分析といえば、やっぱり数字だ。PostgreSQLには、基本的な算術演算はもちろん、集計に役立つ関数もたくさん用意されている。

1. 基本的な計算関数

  • `ABS()`: 絶対値を返す。

SELECT ABS(-10);
— 結果: 10

  • `ROUND()`: 数値を指定した桁数で丸める。

SELECT ROUND(123.456, 2);
— 結果: 123.46

小数点以下第2位で四捨五入してるのがわかるな。

  • `CEIL()` / `FLOOR()`: 切り上げ、切り捨て。

SELECT CEIL(123.456); — 結果: 124
SELECT FLOOR(123.456); — 結果: 123

  • `POWER()`: べき乗。

SELECT POWER(2, 3); — 2の3乗
— 結果: 8

2. 集計関数

これは `GROUP BY` と組み合わせて使うことが多いやつだな。

  • `COUNT()`: 条件に合う行数や、指定した列の非NULL値の数を数える。

— 例: usersテーブルのレコード数を数える
SELECT COUNT() FROM users;

— 例: activeなユーザーの数を数える (activeカラムがtrueのレコード)
SELECT COUNT() FROM users WHERE active = TRUE;

`COUNT()` と `COUNT(column_name)` の違い、ちゃんと理解してるか？ `COUNT()` は全行数、`COUNT(column_name)` はその列に値が入ってる行数だ。

  • `SUM()`: 指定した列の合計値を計算する。

— 例: ordersテーブルのtotal_amountの合計を計算
SELECT SUM(total_amount) FROM orders;

  • `AVG()`: 指定した列の平均値を計算する。

— 例: gradesテーブルのscoreの平均値を計算
SELECT AVG(score) FROM grades;

  • `MAX()` / `MIN()`: 指定した列の最大値、最小値を返す。

— 例: productsテーブルのpriceの最大値と最小値を取得
SELECT MAX(price), MIN(price) FROM products;

これらの集計関数は、レポート作成やKPI算出の基本中の基本だから、絶対マスターしておけよ！

時系列データを自在に操る！日付・時刻関数

システム開発してると、いつデータが作成されたとか、いつ更新されたとか、そういう日付や時刻情報を扱うことは避けられない。PostgreSQLには、そんな時系列データを扱うための強力な関数がたくさんあるんだ。

1. 日付・時刻の取得・フォーマット

  • `NOW()` / `CURRENT_TIMESTAMP`: 現在の日時を取得する。

SELECT NOW();
— 例: 2023-10-27 10:30:00.123456+09

`CURRENT_TIMESTAMP` とほぼ同じだけど、`NOW()` の方がPostgreSQLらしい感じがするな。

  • `DATE_TRUNC()`: 日付や時刻を指定した単位（年、月、日、時間など）で切り捨てる。

— 例: 現在の日時を「日」単位で切り捨てる（その日の0時0分0秒になる）
SELECT DATE_TRUNC(‘day’, NOW());
— 例: 2023-10-27 00:00:00+09

— 例: 現在の日時を「月」単位で切り捨てる
SELECT DATE_TRUNC(‘month’, NOW());
— 例: 2023-10-01 00:00:00+09

集計で「月別」とか「日別」にデータをまとめたい時に、めちゃくちゃ役立つぞ。

  • `TO_CHAR()`: 日付や時刻を指定したフォーマットの文字列に変換する。これはもう、必須スキルと言ってもいい。

— 例: 現在日時を ‘YYYY/MM/DD HH:MI:SS’ 形式で表示
SELECT TO_CHAR(NOW(), ‘YYYY/MM/DD HH:MI:SS’);
— 結果: ‘2023/10/27 10:30:00’

— 例: 月末日を取得
SELECT TO_CHAR(DATE_TRUNC(‘month’, NOW()) + INTERVAL ‘1 month’ – INTERVAL ‘1 day’, ‘YYYY-MM-DD’);
— 結果: ‘2023-10-31’

フォーマットコード（`YYYY`、`MM`、`DD`など）を覚えると、表示形式の自由度が爆上がりする。公式ドキュメントで一覧を確認しておくといいぞ。

2. 日付・時刻の計算

  • `INTERVAL`: 日付や時刻の加算・減算に使う。

— 例: 今から1週間後の日時
SELECT NOW() + INTERVAL ‘1 week’;

— 例: 2日前から3時間前の日時
SELECT NOW() – INTERVAL ‘2 days 3 hours’;

この `INTERVAL` の書き方、覚えておくと日時計算が驚くほど楽になる。

  • `EXTRACT()`: 日付や時刻から特定の要素（年、月、日、曜日など）を取り出す。

— 例: 今日の曜日を数値で取得 (日曜日=1, 月曜日=2, …)
SELECT EXTRACT(DOW FROM NOW());
— 例: 5 (金曜日)

— 例: 今月の何日目か
SELECT EXTRACT(DAY FROM NOW());
— 例: 27

まとめ：まずは触ってみることが大事！

今日は、PostgreSQLの組み込み関数のほんの一部を紹介したけど、どうだったかな？
文字列操作、数学計算、日付・時刻処理…これらは日常的に使う場面が多いはずだ。

もちろん、今回紹介した以外にも、JSONを扱う関数、配列を扱う関数、ウィンドウ関数なんていうもっと高度なものまで、PostgreSQLには数え切れないほどの関数が用意されている。

一番大事なのは、「こういう関数があるんだな」って知っておくこと、そして実際に自分で触ってみることだ。
ちょっとしたデータ整形や集計で困ったら、「もしかして、あの関数が使えるんじゃないか？」って思い出して、公式ドキュメントを引いてみる。この習慣が、君を「できるエンジニア」に近づけてくれるはずだ。

さあ、今日から君もPostgreSQL関数マスターへの第一歩を踏み出そうぜ！
もし、「この関数についてもっと詳しく知りたい！」とか、「こういう処理をしたいんだけど、どんな関数がある？」なんて質問があれば、気軽にコメントで聞いてくれよな！

スポンサーリンク

コメント

タイトルとURLをコピーしました