PostgreSQL ビルトイン関数

PostgreSQL のビルトイン関数は、データに対して様々なオペレーションを実行するためにあらかじめ定義された関数（ファンクション）です。本章では、データのクレンジング、トランスフォーム、および分析に不可欠な文字列処理、数値計算、日付時刻関数に焦点を当てて解説します。

これらのビルトイン関数を理解し使いこなすことで、PostgreSQL におけるデータプロセッシングの能力が大幅に向上します。

1. 文字列処理関数

文字列処理関数を使用すると、テキストデータの変更や情報の抽出が可能になります。PostgreSQL は、このための非常に豊富な関数ライブラリを提供しています。

1.1 character_length() または char_length()

この関数は、文字列内のキャラクター数を返します。

SELECT character_length('PostgreSQL'); -- 出力: 10
SELECT char_length('Hello'); -- 出力: 5

例: first_name カラムを含む employees（従業員）テーブルがあるとします。各従業員の名前の長さを取得できます。

SELECT first_name, character_length(first_name) AS name_length
FROM employees;

1.2 octet_length()

この関数は、文字列が占有するバイト数を返します。マルチバイトキャラクター（UTF-8 エンコーディングの日本語など）を扱う場合、character_length と octet_length の違いは非常に重要です。

SELECT octet_length('PostgreSQL'); -- 出力: 10 (ASCII または UTF-8 エンコーディングと仮定)
SELECT octet_length('こんにちは'); -- 出力: 15 (UTF-8 の場合、通常日本語1文字は 3 バイト)

例: 特定のエンコーディングにおける文字列のストレージサイズを確認する場合：

SELECT product_name, octet_length(product_name) AS name_size
FROM products;

1.3 upper() と lower()

これら2つの関数は、それぞれ文字列をアッパーケース（大文字）またはローワーケース（小文字）に変換します。

SELECT upper('postgresql'); -- 出力: POSTGRESQL
SELECT lower('POSTGRESQL'); -- 出力: postgresql

例: データの比較時にケース（大文字・小文字）を統一する場合：

SELECT email FROM users WHERE lower(email) = '[email protected]';

1.4 substring()

この関数は、文字列からサブストリングを抽出します。引数として、元の文字列、開始位置、およびサブストリングの長さを取ります。また、よりセマンティックな SUBSTRING(string FROM start FOR length) 構文も使用できます。

SELECT substring('PostgreSQL', 1, 4); -- 出力: Post
SELECT substring('PostgreSQL' FROM 6 FOR 4); -- 出力: gres

例: 日付の文字列から年を抽出する場合：

SELECT order_date, substring(order_date::text FROM 1 FOR 4) AS order_year
FROM orders;

1.5 trim()

この関数は、主に文字列の先頭と末尾にあるホワイトスペースを削除するために使用されます。特定のキャラクターを指定して削除することも可能です。

SELECT trim('   PostgreSQL   '); -- 出力: PostgreSQL
SELECT trim(both 'x' from 'xxxPostgreSQLxxx'); -- 出力: PostgreSQL
SELECT trim(leading 'x' from 'xxxPostgreSQLxxx'); -- 出力: PostgreSQLxxx (先頭のみ削除)
SELECT trim(trailing 'x' from 'xxxPostgreSQLxxx'); -- 出力: xxxPostgreSQL (末尾のみ削除)

例: ユーザー入力に含まれる余分なスペースをクレンジングする場合：

UPDATE products SET description = trim(description);

1.6 replace()

この関数は、文字列内で指定したサブストリングが出現するすべての箇所を、別の新しいサブストリングに置換します。

SELECT replace('PostgreSQL is great', 'great', 'awesome'); -- 出力: PostgreSQL is awesome

例: スペルミスの単語を修正する場合：

UPDATE products SET product_name = replace(product_name, 'cofee', 'coffee');

1.7 concat()

この関数は、2つ以上の文字列を連結（コンカチネート）します。PostgreSQL では、|| オペレーターを使用しても連結が可能です。

SELECT concat('Post', 'greSQL'); -- 出力: PostgreSQL
SELECT 'Post' || 'greSQL'; -- 出力: PostgreSQL
SELECT concat('First Name: ', first_name, ', Last Name: ', last_name) FROM employees;

例: 名と姓を連結してフルネームを作成する場合：

SELECT concat(first_name, ' ', last_name) AS full_name FROM employees;

1.8 split_part()

この関数は、指定したデリミタ（区切り文字）に基づいて文字列を分割し、n 番目のパーツを返します。

SELECT split_part('apple,banana,orange', ',', 2); -- 出力: banana

例: メールアドレスからドメイン名を抽出する場合：

SELECT email, split_part(email, '@', 2) AS domain FROM users;

2. 数值运算函数

数値関数は、数値データに対して数学的な演算や計算を実行することを可能にします。

2.1 abs()

この関数は、数値の絶対値を返します。

SELECT abs(-10); -- 出力: 10
SELECT abs(5); -- 出力: 5

例: 2つの値の大小に関わらず、その差異を計算する場合：

SELECT abs(actual_value - expected_value) AS difference FROM measurements;

2.2 ceil()

この関数は、数値を最も近い整数に切り上げ（シーリング）します。

SELECT ceil(4.2); -- 出力: 5
SELECT ceil(-4.2); -- 出力: -4

例: 一定量のアイテムを配送するために必要なボックスの数を計算する場合（各ボックスのキャパシティに制限があり、1つでも余れば追加のボックスが必要なケース）：

SELECT ceil(total_items / items_per_box) AS number_of_boxes FROM orders;

2.3 floor()

この関数は、数値を最も近い整数に切り捨て（フローリング）します。

SELECT floor(4.8); -- 出力: 4
SELECT floor(-4.8); -- 出力: -5

例: 従業員の勤続年数を計算する場合（端数を切り捨て）：

SELECT floor(months_of_service / 12) AS years_of_service FROM employees;

2.4 round()

この関数は、数値を指定した小数点以下の桁数で四捨五入します。桁数を指定しない場合は、最も近い整数に四捨五入されます。

SELECT round(4.56); -- 出力: 5
SELECT round(4.56, 1); -- 出力: 4.6
SELECT round(4.56, 0); -- 出力: 5.0

例: 表示用に価格を小数点第2位まで四捨五入する場合：

SELECT product_name, round(price, 2) AS rounded_price FROM products;

2.5 random()

この関数は、0.0 から 1.0 の間の疑似乱数を返します。

SELECT random(); -- 出力: 0.56789 のようなランダムな数値

例: テーブルからサンプリングとしてランダムに 10 行のデータを抽出する場合：

SELECT * FROM customers ORDER BY random() LIMIT 10;

2.6 sqrt()

この関数は、数値の平方根（ルート）を返します。

SELECT sqrt(25); -- 出力: 5
SELECT sqrt(2); -- 出力: 1.4142135623730951

例: ピタゴラスの定理を使用して2点間の直線距離を計算する場合：

SELECT sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) AS distance FROM coordinates;

2.7 power()

この関数は、数値の指定した累乗を計算します。

SELECT power(2, 3); -- 出力: 8 (2 の 3 乗)
SELECT power(5, 2); -- 出力: 25 (5 の 2 乗)

例: 複利を計算する場合：

SELECT principal * power(1 + interest_rate, years) AS future_value FROM investments;

2.8 trunc()

この関数は、数値を指定した小数点以下の桁数で切り捨て（トランク）します。round() の四捨五入とは異なり、単純に余分な小数位をカットします。

SELECT trunc(4.56); -- 出力: 4
SELECT trunc(4.56, 1); -- 出力: 4.5

例: 除算結果の整数部分のみを抽出する場合：

SELECT trunc(total_sales / number_of_customers) AS average_sales_per_customer FROM sales_data;

3. 日付与时间函数

日付関数を使用すると、日付および時刻データ内の特定の情報を操作したり抽出したりできます。

3.1 now() または current_timestamp

これら2つの関数は、現在の日付と時刻を返します。

SELECT now(); -- 出力: 現在の日付と時刻。例: 2023-10-27 10:00:00+00
SELECT current_timestamp; -- 出力: now() と同様

例: レコード作成時にタイムスタンプを保存する場合：

INSERT INTO orders (order_date) VALUES (now());

3.2 current_date

この関数は、現在の日付のみ（時刻を含まない）を返します。

SELECT current_date; -- 出力: 現在の日付。例: 2023-10-27

例: 今日行われたすべてのオーダーを照会する場合：

SELECT * FROM orders WHERE order_date::date = current_date;

3.3 current_time

この関数は、現在の時刻のみ（タイムゾーン情報を含む、日付を含まない）を返します。

SELECT current_time; -- 出力: 現在の時刻。例: 10:00:00+00

例: 現在の正確な時刻にスケジュールされているイベントを選択する場合（厳密な一致が必要なため、このユースケースは比較的稀です）：

SELECT * FROM events WHERE event_time = current_time;

3.4 date()

この関数は、完全なタイムスタンプから日付部分を抽出するために使用されます。

SELECT date(now()); -- 出力: 現在の日付。例: 2023-10-27

例: 日付ごとにオーダーをグループ化する場合：

SELECT date(order_date), COUNT(*) FROM orders GROUP BY date(order_date);

3.5 extract()

この関数は、日付またはタイムスタンプから特定のフィールド（年、月、日、時、分、秒など）を抽出するために使用されます。

SELECT extract(year FROM now()); -- 出力: 現在の年。例: 2023
SELECT extract(month FROM now()); -- 出力: 現在の月。例: 10
SELECT extract(day FROM now()); -- 出力: 現在の日。例: 27
SELECT extract(hour FROM now()); -- 出力: 現在の時。例: 10

例: 月ごとに売上データを分析する場合：

SELECT extract(month FROM order_date) AS order_month, SUM(total_amount) FROM orders GROUP BY order_month;

3.6 age()

この関数は、2つの日付またはタイムスタンプ間の時間差（期間）を計算します。

SELECT age(timestamp '2023-10-27 10:00:00', timestamp '2023-10-20 00:00:00'); -- 出力: 6 days 10:00:00
SELECT age(now(), timestamp '2000-01-01 00:00:00'); -- 出力: 2000年1月1日から現在までの期間

例: 従業員の入社から現在までの勤続期間を計算する場合：

SELECT first_name, last_name, age(now(), hire_date) AS employee_age FROM employees;

3.7 date_trunc()

この関数は、タイムスタンプを指定した精度（年、月、日、時など）で切り捨てます。その期間の開始時刻を返します。

SELECT date_trunc('month', now()); -- 出力: 今月の初日の午前0時。例: 2023-10-01 00:00:00+00
SELECT date_trunc('year', now()); -- 出力: 今年の初日の午前0時。例: 2023-01-01 00:00:00+00

例: 月ごとに売上データを集計し、date_trunc を使用して時間軸を統一する場合：

SELECT date_trunc('month', order_date), SUM(total_amount) FROM orders GROUP BY date_trunc('month', order_date);

3.8 to_char()

この関数は、日付またはタイムスタンプを指定したフォーマットの文字列に変換します。

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'); -- 出力: 指定フォーマットの現在日時。例: 2023-10-27 10:00:00
SELECT to_char(now(), 'Mon DD, YYYY'); -- 出力: 例: Oct 27, 2023

例: レポート表示用に、オーダーの日付をフォーマットする場合：

SELECT order_id, to_char(order_date, 'Month DD, YYYY') AS formatted_date FROM orders;