OSS-DB　Silver

OSS-DBは色々なものがありますが、商用データベースとの連携に優れ、エンタープライズシステムでも多くの導入実績があるのが「PostgreSQL」です。
OSS-DB Silverは「PostgreSQL　11」に関する能力を問う資格です。

試験で問われる範囲は以下の通りです。

これらから50題出題され、65%程度の正答率で合格となります。

今回は開発/SQLの中から組み込み関数と演算子について説明します。

Sec 9. 組み込み関数と演算子

集約関数

• 集約関数を使用した結果は単純には検索条件に使うことはできない。
  →副問合せを使うか、HAVINGを利用する

• count() : 条件に合致する結果のレコード数を求める場合に使用する

  SELECT count(列名) FROM テーブル名 WHERE 検索条件;

  • NULLレコードは無視される

  • 例)

  SELECT 部署, count(*) AS 人数 FROM member GROUP BY 部署;
   部署 | 人数
  ------+------
   総務 |    1
   開発 |    3
   営業 |    2
  (3 rows)
  
  # 集約関数を条件にする場合はHAVINGを使用する
  SELECT 部署, count(*) AS 人数 FROM member GROUP BY 部署 HAVING (count(*) >= 3);
   部署 | 人数
  ------+------
   開発 |    3
  (1 row)

• sum(), avg() : 合計、平均

  SELECT sum(列名) FROM テーブル名 WHERE 検索条件;
  SELECT avg(列名) FROM テーブル名 WHERE 検索条件;

• min(), max() : 最小、saidai

  SELECT min(列名) FROM テーブル名 WHERE 検索条件;
  SELECT max(列名) FROM テーブル名 WHERE 検索条件;

比較演算子

• 等号、不等号は通常通り使用可能
• 等しくない場合は、<>または!=を使用する。
• NULLの判定には、X IS NULLまたはX IS NOT NULLを使用する

算術関数と演算子

• abs(X) : 絶対値

• div(y, x) : y/xの整数商

• mod(y, x) : y/x の剰余

• sqrt(x) : xの平方根

• power(x, y) : xのy乗

• ceil(x) : xより小さくない最小の整数

• floor(x) : xより大きくない最大の整数

• round(x) : xの小数点を四捨五入

  • 第二引数に、小数点を四捨五入する桁を設定可能

• trunc(x) : xの小数点を切り捨て

  • 第二引数に、小数点を切り捨てる桁を設定可能

• log(x) : xの常用対数

• pi() : 円周率

• random() : 0.0<= x < 1.0 の乱数値

  例えば1から100までのランダムな整数値を使いたい場合は、

  SELECT (random() * 100)::int % 100;

  とする。

• |/ : 平方根

• ||/ : 立方根

• @ : 絶対値

• 計算の優先度

  1. 累乗(^)
  2. 乗算(*)、除算(/)、剰余(%)
  3. 加算(+)、減算(-)
  4. その他の演算子
  5. 不等号系

文字列演算子と述語

• || 演算子 : 文字列の結合

  文字列 || 文字列;

• LIKE : 文字列に対する前方/後方/中間一致検索を行う

  SELECT 列名 FROM テーブル名 WHERE 列名 LIKE '条件';

  • 条件では、%(任意の文字列)、_(任意の1文字)が使用可能。

• SIMILAR TO : 正規表現を使ったパターンマッチング

  文字列 SIMILAR TO ’条件';

  • 条件に正規表現のメタ文字が使用可能

• ~演算子 : 文字列のパターンマッチングを行う（文字列の一部であっても真となる）

  # 正規表現に一致する場合に真となる（大文字小文字は区別される）
  文字列 ~ 正規表現
  # 正規表現に一致する場合に真となる（大文字小文字は区別されない）
  文字列 ~* 正規表現
  # 正規表現に一致しない場合に真となる（大文字小文字は区別される）
  文字列 !~ 正規表現
  # 正規表現に一致しない場合に真となる（大文字小文字は区別されない）
  文字列 !~* 正規表現

文字列関数

• leength()/char_length() : 文字数の取得

• octet_length() : 文字列のバイト数

• substring() : 文字列の抽出

  substring(文字列 [from 開始位置] [for 文字数])

• upper() : 大文字に変換

• lower() : 小文字に変換

• repeat() : 文字列の繰り返し

• replace() : 文字列の置換

  replace(文字列, 置換前文字列, 置換後文字列)

• lpad() : 指定文字で文字列の先頭を埋める

  lpad(文字列, 文字数, [文字])

  • 指定の文字数になるまで、文字列の先頭に文字を埋め込む
  • 文字を省略した場合は、半角の空白が埋め込まれる

• rpad() : 指定文字で文字列の末尾を埋める

• trim() : 文字列の先頭/末尾/前後の指定文字を削除

  trim([leading | trailing | both] [文字] from 文字列)

  • 削除する文字を省略した場合は空白が削除される

• ltrim() : 文字列の先頭の指定文字を削除

• rtrim() : 文字列の末尾の指定文字を削除

日付/時刻の関数と演算子

• 現在の日付/時刻を取得する

  • now() : タイムスタンプの取得
  • current_timestamp : タイムスタンプの取得
  • localtimestamp : タイムスタンプの取得
  • localtime : 時刻の取得
  • current_time : 時刻の取得
  • current_date : 日付の取得

  ↑まではトランザクション開始時の日付/時刻を取得する関数

  • statement_timestamp() : SQLの開始時の日付/時刻を取得する
    →1つのSQL内で複数回実行しても、そのSQL内では常に同じ値が返る
  • clock_timestamp() : 関数が実行された時点の日付/時刻を取得する

• age() : 2つのtimestamp型の引数をとり、第一引数から第二引数の減算を行う

  age(timestamp値, timestamp値)

• extract(), date_part() : 部分フィールドの取得

  extract(field from [timestamp値 | interval値])
  date_part('field', [timestamp値 | interval値])

  • fieldにはhour, monthなどが指定可能

• date_trunc() : 指定した精度で日付/時刻情報を取得する

  date_trunc(field, timestamp値)

• 数値に加筆を行う場合は、日や時間の間隔を表すINTERVAL型を使用する。
  INTERVALは「'数値 単位'::interval」で表現する。指定可能な単位はyear, month, dayなど。

データ型書式設定関数

• to_char()

  to_char(データ, パターン)

  • パターンは文字列として記述するため、シングルクォートで囲む

  例）

  SELECT to_char('20211219 113000'::timestamp, 'YYYY-MM-DD HH24:MI:SS');
  #       to_char
  #---------------------
  # 2021-12-19 11:30:00
  
  SELECT to_char('2021-10-31'::timestamp, 'Mon DD YYYY');
  #   to_char
  #-------------
  # Oct 31 2021

• to_date()/to_timestamp() : 文字列型を日付型やタイムスタンプ型に変更する

  to_date(文字列, パターン)
  to_timestamp(文字列, パターン)
  to_timestamp(数値) -- 数値はUNIXタイム

• to_number() : 文字列を数値型に変換することが可能

  to_number(文字列, パターン);ß