OSS-DB Silver 暗記 C-2

開発/SQL(32%):::組み込み関数【重要度:2】dokoQL可

PostgreSQL 11.5文書:関数と演算子

 

  • dokoQL で実習が可能です

 

集約関数

PostgreSQL 11.5文書:集約関数

  • 集約関数は入力値の集合から単一の結果を計算する
SELECT {count | sum | avg | min | max | ...}(列名)
    FROM テーブル名 [WHERE 条件]
    [GROUP BY 条件] [HAVING 条件];

 

  暗記  

  • 集約関数
    • count, sum, avg, min, max, etc.

 

:ブランド別売上

=> CREATE TABLE items_sold (
       id int,
       brand text,
       size text,
       sales int
   );

=> INSERT INTO items_sold VALUES
      (1, 'Foo', 'L', 10),
      (2, 'Foo', 'M', 20), 
      (3, 'Bar', 'L',  5),
      (4, 'Bar', 'M', 15),
      (5, 'Bar', 'S', 30);

:ブランドBarの行数をカウント

=> SELECT count(*) FROM items_sold WHERE brand='Bar';
-------
 count
-------
     3
-------

:ブランド毎の販売数合計

=> SELECT brand, sum(sales) FROM items_sold GROUP BY brand;
-----------+-------
   brand   |  sum
-----------+-------
   Foo     |   30
   Bar     |   50
-----------+-------

:サイズMで一番売れたブランド

=> SELECT * FROM items_sold
=> WHERE size='M' AND
=>       sales=(SELECT max(sales) FROM items_sold WHERE size='M');
------+---------+--------+---------
  id  |  brand  |  size  |  sales
------+---------+--------+---------
   2  |  Foo    |  M     |     20
------+---------+--------+---------

 

aggregate functions postgreSQL
Fig.1 aggregate functions

 

  暗記  

  • 集計関数が使える場所
    • SELECTHAVINGORDER BY

 

 

算術関数と演算子

PostgreSQL 11.5文書:算術関数と演算子

  • PostgreSQLの数多くの型に対する算術演算子が用意されている

 

算術演算子

  • +  –  *  /  %  ^  |/  !  @ ・・・

 

=> SELECT |/3;            -- 1.7320508…:ルート
=> SELECT 3!;             --         6 :階乗
=> SELECT @ -3.3;         --         3 :絶対値

 

算術関数

  • abs  sqrt  div  mod  ceil  floor  round  trunc  random ・・・

 

=> SELECT div(9, 4);       --   2:整数商
=> SELECT mod(9, 4);       --   1:剰余
=> SELECT ceil(-42.8);     -- -42:天井
=> SELECT floor(-42.8);    -- -43:床 
=> SELECT round(42.4);     --  42:小数点を四捨五入 
=> SELECT trunc(42.8);     --  42:小数点を切り捨て  

 

 

文字列関数と演算子

PostgreSQL 11.5文書:文字列関数と演算子

  • 文字列の値の調査や操作のための関数と演算子

 

  暗記  

  • 文字列関数
    • char_length, lower, upper, replace, substring, trim

 

「 || 」演算子

=> SELECT 'Post' || 'greSQL';        -- PostgreSQL:結合
=> SELECT 'Value: ' || 42;           -- Value: 42 :

 

文字列関数

=> SELECT char_length('jose');                   -- 4           :文字数
=> SELECT lower('TOM');                          -- tom         :小文字変換
=> SELECT upper('tom');                          -- TOM         :大文字変換
=> SELECT substring('Thomas' from 2 for 3);      -- hom         :部分文字列の取り出し
=> SELECT replace('abcdefabcdef', 'cd', 'XX');   -- abXXefabXXef:文字列の置換
=> SELECT trim(both 'xyz' from 'yxTomxx');       -- Tom         :特定文字の削除

 

 

パターンマッチ

PostgreSQL 11.5文書:パターンマッチ

 

  暗記  

  • PostgreSQLのパターンマッチには3つの異なった手法がある
    • LIKE, SIMILAR TO, 「~」演算子

 

LIKE

  • ワイルドカード文字(%  _ )をサポートしている
    • %:0 文字以上の並び一致
    • _ :任意の1 文字との一致

 

'abc' LIKE 'abc'        -- true
'abc' LIKE 'ab'         -- false
'abc' LIKE 'a%'         -- true(%:0文字以上の並び一致)
'abc' LIKE '_b_'        -- true(_:任意の1文字との一致)

 

=> CREATE TABLE tbl (s text);
=> INSERT INTO tbl VALUES
=>     ('ABCDEF'),
=>     ('BCDEFG'),
=>     ('CDEFGH');
=> SELECT * FROM tbl WHERE s LIKE '%B%';
------------
     s
------------
   ABCDEF
   BCDEFG
------------

SIMILAR TO

  • 正規表現を使用したパターンマッチ
    • パターンマッチメタ文字をサポートしている
      • |  *  +  ?  {m}  {m,}  {m,n}  ( )  [ ]・・・
  • ワイルドカード文字(%  _ )をサポートしている
'abc' SIMILAR TO 'abc'       -- true
'abc' SIMILAR TO 'a'         -- false
'abc' SIMILAR TO '%(b|d)%';  -- true( | :二者択一)
'abc' SIMILAR TO '[a-z]+';   -- true([ ]:文字クラスの指定)

 

------------
     s
------------
   ABCDEF
   BCDEFG
   CDEFGH
------------

=> SELECT * FROM tbl WHERE s SIMILAR TO '%(B|X)%';
------------
     s
------------ 
   ABCDEF
   BCDEFG
------------

 

「~」演算子

  • 「~」演算子は正規表現パターンマッチのバリエーションを増やす
1. 文字列 ~   正規表現:正規表現一致で真、大文字小文字を区別する
2. 文字列 ~*  正規表現:正規表現一致で真、大文字小文字を区別しない
3. 文字列 !~  正規表現:正規表現不一致で真、大文字小文字を区別する
4. 文字列 !~* 正規表現:正規表現不一致で真、大文字小文字を区別しない

 

------------
     s
------------
   ABCDEF
   BCDEFG
   CDEFGH
------------

=> SELECT * FROM tbl WHERE s ~ '^C';    -- ^:文字列の先頭にマッチ
------------
     s
------------ 
   CDEFGH
------------

=> SELECT * FROM tbl WHERE s !~* 'f$';  -- $:文字列の末尾にマッチ
 ------------ 
     s
------------ 
   BCDEFG
   CDEFGH
------------

 

 

日付/時刻関数と演算子

PostgreSQL 11.5文書:日付/時刻関数と演算子

  • 日付/時刻型の値の処理で使用可能な関数と演算子

 

日付/時刻関数

=> SELECT age('2021-02-19', '1986-02-09');     -- 年齢:引数間の減算
---------------------- 
         age
----------------------
   35 years 10 days
---------------------- 

※1986-02-09:前回のハレー彗星近日点通過日

=> SELECT now();                  -- 現在の日付と時刻(現在のトランザクション開始時)
=> SELECT current_date            -- 現在の日付
=> SELECT current_time            -- 現在の時刻
=> SELECT current_timestamp;      -- now()と同じ
=> SELECT statement_timestamp();  -- 現在の日付と時刻(現在のSQL開始時)
=> SELECT clock_timestamp();      -- 現在の日付と時刻(現在の関数開始時)
=> SELECT extract(hour from timestamp '2001-02-16 20:38:40');    -- 抽出:timestamp
---------------
   date_part
--------------- 
          20
---------------
=> SELECT extract(month from interval '2 years 3 manths');       -- 抽出:interval
---------------
   date_part
---------------
           3
--------------

 

 

データ型書式設定関数

PostgreSQL 11.5文書:データ型書式設定関数

  • 日付/時刻型の値の処理で使用可能な関数と演算子

 

to_char()

to_char(データ, パターン)
=> SELECT to_char(current_timestamp, 'HH12:MI:SS');      -- 03:43:28 タイムスタンプ → 文字列
=> SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');  -- 15:02:12 時間間隔 → 文字列
=> SELECT to_char(125, '999');                           -- 125      整数 → 文字列
=> SELECT to_char(125.8::real, '999D9');                 -- 125.8    実数 → 文字列
=> SELECT to_char(-125.8, '999D99S');                    -- 125.80-  数値 → 文字列

 

参考

 

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。