OSS-DB Silver 暗記 C-2

管理者 2021-02-202022-02-10 0 Comments OSS-DB Silver

Contents

1 算術演算子
2 算術関数
3 「 || 」演算子
4 文字列関数
5 LIKE
6 SIMILAR TO
7 「~」演算子
8 日付/時刻関数
9 to_char()
- 9.1 共有:

開発/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

暗記

集計関数が使える場所
- SELECT, HAVING, ORDER BY

算術関数と演算子

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

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

算術演算子

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

=> SELECT |/3;            -- 1.7320508…：ルート
=> SELECT 3!;             --         6 ：階乗
=> SELECT @ -3.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-  数値 → 文字列

OSS-DB Silver 暗記 C-2

集約関数

算術関数と演算子

算術演算子

算術関数

文字列関数と演算子

「 || 」演算子

文字列関数

パターンマッチ

LIKE

SIMILAR TO

「~」演算子

日付/時刻関数と演算子

日付/時刻関数

データ型書式設定関数

to_char()

参考

コメントを残すコメントをキャンセル

集約関数

算術関数と演算子

算術演算子

算術関数

文字列関数と演算子

「 || 」演算子

文字列関数

パターンマッチ

LIKE

SIMILAR TO

「~」演算子

日付/時刻関数と演算子

日付/時刻関数

データ型書式設定関数

to_char()

参考

共有:

こちらもおすすめ

Canvasに長方形を描く２

JavaScript：多数のファイル名を変更する

Web Storage を用いた学習システムの雛型

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル