D.Horiyama's output

OSS-DB試験対策 緑本 ch9 組み込み関数と演算子

OSS-DBPostgreSQL勉強メモ

出典: 

集約関数

しってるから略

count()

sum()とavg()

min()とmax()

比較演算子

しってるから略

算術関数と演算子

算術関数

いろいろある

div()とmod()

postgres=# SELECT div(13, 3), mod(13, 3);
 div | mod 
-----+-----
   4 |   1
(1 row)
  • 商と剰余
  • 【補】マイナス周りの挙動
postgres=# SELECT div(-13, 3), mod(-13, 3);
 div | mod 
-----+-----
  -4 |  -1
(1 row)

postgres=# SELECT div(13, -3), mod(13, -3);
 div | mod 
-----+-----
  -4 |   1
(1 row)

postgres=# SELECT div(-13, -3), mod(-13, -3);
 div | mod 
-----+-----
   4 |  -1
(1 row)

  • せいしつ

    • 剰余はマイナスの値をとる
    • (商 x 除数 + 剰余)は被除数に戻る
    • 商はプラス割るプラスの符号違い
  • /演算子との違い
postgres=# SELECT 13/3, -13/3, 13/-3, -13/-3;
 ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------
        4 |       -4 |       -4 |        4
(1 row)

postgres=# SELECT 13/3, 13.0/3, 13/3.0;
postgres-# ;
;
 ?column? |      ?column?      |      ?column?      
----------+--------------------+--------------------
        4 | 4.3333333333333333 | 4.3333333333333333
(1 row)

floor()とceil()

postgres=# SELECT floor(4.2), ceil(4.2);
 floor | ceil 
-------+------
     4 |    5
(1 row)

postgres=# SELECT floor(-4.2), ceil(-4.2);
 floor | ceil 
-------+------
    -5 |   -4
(1 row)

round()とtrunc()

  • round, truncともに0が基準
  • round: 四捨五入 (0側を捨てる)
  • trunc: 0側に切り捨て
postgres=# SELECT digit, round(14.05, digit), trunc(14.05, digit) FROM (SELECT unnest as digit from unnest(ARRAY[-2,-1,0,1,2])) as digits;
 digit | round | trunc 
-------+-------+-------
    -2 |     0 |     0
    -1 |    10 |    10
     0 |    14 |    14
     1 |  14.1 |  14.0
     2 | 14.05 | 14.05
(5 rows)

random()

  • [0;1)

算術演算子

  • おもしろいやつだけ
postgres=# SELECT |/2 as sqrt, ||/2 as cbrt;
        sqrt        |        cbrt        
--------------------+--------------------
 1.4142135623730951 | 1.2599210498948734
(1 row)

  • 累乗

    • 0の0乗は1みたい
postgres=#  SELECT 2^8, 0^0;
 SELECT 2^8, 0^0;
 ?column? | ?column? 
----------+----------
      256 |        1
(1 row)
  • 階乗
postgres=# SELECT 4!, !!4;
 ?column? | ?column? 
----------+----------
       24 |       24
(1 row)

  • 絶対値

    • スペース必要
postgres=# SELECT @ -1;
 ?column? 
----------
        1
(1 row)

文字列演算子と述語

「||」演算子

postgres=# SELECT 'Kirima' || ' ' || 'Syaro';
   ?column?   
--------------
 Kirima Syaro
(1 row)

LIKE

しってる

SIMILAR TO

  • LIKE + 拡張正規表現も使える
  • 暗黙的に/^と$/で囲まれる感じ

「~」演算子

  • 部分一致検索用
一致で真 不一致で真
case sensitive ~ !~
ignore case ~* ~* 
postgres=# SELECT 'banana' ~* 'BAN';
 ?column? 
----------
 t
(1 row)

文字列関数

lower()とupper()

postgres=# SELECT lower('String'), upper('String');
 lower  | upper  
--------+--------
 string | STRING
(1 row)

substring()

postgres=# SELECT substring('relationship' from 1 for 8);
 substring 
-----------
 relation
(1 row)

  • 1文字目がfrom 1であることに留意する

    • 0にするとlengthから1つ減った感じになる
  • negative lengthは不可

replace()

postgres=# SELECT replace('JavaScript', 'java', 'ecma');
  replace   
------------
 JavaScript
(1 row)

postgres=# SELECT replace('JavaScript', 'Java', 'ECMA');
  replace   
------------
 ECMAScript
(1 row)
  • 【補】ignore caseとかしたい場合はregexp_replace関数をつかう
postgres=# SELECT regexp_replace('JavaSciprt JavaScript', 'java', 'ECMA');
    regexp_replace     
-----------------------
 JavaSciprt JavaScript
(1 row)

postgres=# SELECT regexp_replace('JavaSciprt JavaScript', 'java', 'ECMA', 'i');
    regexp_replace     
-----------------------
 ECMASciprt JavaScript
(1 row)

postgres=# SELECT regexp_replace('JavaSciprt JavaScript', 'java', 'ECMA', 'ig');
    regexp_replace     
-----------------------
 ECMASciprt ECMAScript
(1 row)

octetlength() / charlength()

postgres=# SELECT octet_length('あいうえお'),char_length('あいうえお'),char_length('あいうえお');
 octet_length | char_length | char_length 
--------------+-------------+-------------
           15 |           5 |           5
(1 row)

trim() / lpad() / rpad()

  • 文字列の端を落とす

    • 文字を指定しない場合、デフォルトで空白文字
postgres=# SELECT trim(leading '*' from '****hoge****');
  ltrim   
----------
 hoge****
(1 row)

postgres=# SELECT trim(trailing '*' from '****hoge****');
  rtrim   
----------
 ****hoge
(1 row)

postgres=# SELECT trim(both '*' from '****hoge****');
 btrim 
-------
 hoge
(1 row)

postgres=# SELECT trim('*' from '****hoge****');
 btrim 
-------
 hoge
(1 row)

  • ltrim,rtrim,btrimでも同様のことができる

    • fromとかは渡さない
postgres=# SELECT ltrim('****hoge****', '*');
  ltrim   
----------
 hoge****
(1 row)

postgres=# SELECT rtrim('****hoge****', '*');
  rtrim   
----------
 ****hoge
(1 row)

postgres=# SELECT btrim('****hoge****', '*');
 btrim 
-------
 hoge
(1 row)

  • lpad/rpad: パディング追加

    • 文字を指定しない場合、デフォルトで空白文字
postgres=# SELECT lpad ('piyo', 10, '*');
    lpad    
------------
 ******piyo
(1 row)

postgres=# SELECT rpad ('piyo', 10, '*');
    rpad    
------------
 piyo******
(1 row)

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

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

postgres=# BEGIN;

postgres=# SELECT current_timestamp,statement_timestamp(),clock_timestamp();
       current_timestamp       |      statement_timestamp      |        clock_timestamp        
-------------------------------+-------------------------------+-------------------------------
 2020-01-25 10:40:15.202836+00 | 2020-01-25 10:40:18.613984+00 | 2020-01-25 10:40:18.614083+00
(1 row)

postgres=# SELECT current_timestamp,statement_timestamp(),clock_timestamp();
       current_timestamp       |      statement_timestamp      |        clock_timestamp        
-------------------------------+-------------------------------+-------------------------------
 2020-01-25 10:40:15.202836+00 | 2020-01-25 10:40:20.751923+00 | 2020-01-25 10:40:20.751981+00
(1 row)

postgres=# SELECT current_timestamp,statement_timestamp(),clock_timestamp();
       current_timestamp       |      statement_timestamp      |        clock_timestamp        
-------------------------------+-------------------------------+-------------------------------
 2020-01-25 10:40:15.202836+00 | 2020-01-25 10:40:36.631681+00 | 2020-01-25 10:40:36.631779+00
(1 row)
  • statement_timestamp(): SQL開始時点の日付/時刻
  • clock_timestamp(): この関数が実行された時点の日付/時刻
  • ほかの: トランザクション開始時点の日付/時刻

age()

postgres=# select age('2020-01-01'::timestamp, timestamp '2000-01-01');
   age    
----------
 20 years
(1 row)

  • 引数は引き算の順番

    • (to, from)

extract() / date_part()

postgres=# SELECT extract(month from now());
 date_part 
-----------
         1
(1 row)

postgres=# SELECT date_part('month', now());
 date_part 
-----------
         1
(1 row)
  • 日付からminuteを抽出したりしようとすると、単に0を得る

date_trunc()

  • 指定の精度に日付/時刻情報を切り捨てる
postgres=# SELECT date_trunc('minute', now());
       date_trunc       
------------------------
 2020-01-25 10:49:00+00
(1 row)

postgres=# SELECT date_trunc('day', now());
       date_trunc       
------------------------
 2020-01-25 00:00:00+00
(1 row)

postgres=# SELECT date_trunc('month', now());
       date_trunc       
------------------------
 2020-01-01 00:00:00+00
(1 row)

日付 / 時刻演算子

  • 時刻に時間(interval)を足したりできるよ、という話
  • 数値も足せる
postgres=# SELECT date('2020-01-01') + 1;
SELECT date('2020-01-01') + 1;
  ?column?  
------------
 2020-01-02
(1 row)

データ型書式設定関数

  • キャストと異なり、書式も設定できる

to_char()

postgres=# SELECT to_char(now(), 'YYYYMMDD');
 to_char  
----------
 20200125
(1 row)
  • 12時間/24時間
postgres=# SELECT to_char(timestamp '2020-01-23T23:45:12Z', 'HHMISS');
 to_char 
---------
 114512
(1 row)

postgres=# 
SELECT to_char(timestamp '2020-01-23T23:45:12Z', 'HHMISS');
postgres=# SELECT to_char(timestamp '2020-01-23T23:45:12Z', 'HH12MISS');
 to_char 
---------
 114512
(1 row)

postgres=# SELECT to_char(timestamp '2020-01-23T23:45:12Z', 'HH24MISS');
 to_char 
---------
 234512
(1 row)

  • hour

    • HH,HH12: 12時間
    • HH24: 24時間

  • minute

    • MMはmonthとかぶるのでMI

todate() / totimetamp()

postgres=# SELECT to_date('20000101', 'YYYYMMDD');
  to_date   
------------
 2000-01-01
(1 row)

to_number()

postgres=# SELECT to_number('42.195km', '99.999km');
 to_number 
-----------
    42.195
(1 row)
  1. abd? de