Nullの時に置換する方法

文字列がNullの時、別の文字列に置き換えるにはCOALESCEを使用します。

※Nullでない時は、『Nullチェックする文字列』がそのまま返ります。

(OracleでのNVL、SQL ServerでのISNULL、MySQLでのCOALESCEに相当します)

--Nullの時、別の文字に置換する
COALESCE( Nullチェックする文字列 , Nullの場合に置換する文字 )

 

 

使用例

COALESCEを使った例を紹介します。 ※次の例はそのままコピー&貼り付けで実行できます

--次の例は、チェックする文字列がnullのため'abc'が返ります 
select * From COALESCE(null , 'abc');   --【結果】:'abc' 

--次の例は、チェックする文字列がnullでないため'xyz'が返ります 
select * From COALESCE('xyz' , 'abc');  --【結果】:'xyz'

 

 

合計値、平均値を求める例

nullが含まれる(かもしれない)列を使って合計、平均値を求める例を紹介します。

--数値列がnullなら0に変換して合計値を求める 
select sum(coalesce(数値列,0)) from テーブル名;

--nullのデータは無視して平均値を求める
--※nullを「-1」に変換し、「-1」を無効値として除外する
select avg(coalesce(数値列,0)) from テーブル名
where coalesce(数値列,-1) != -1;

 

 

その他の文字列操作

文字列の結合、nullの結合

改行コードで結合、置換する

文字数のカウント(length)、バイト数を取得する

文字の切り取り(Left、Right、Substring)

検索文字位置の取得(strpos)

全角半角スペースを除去する(trim)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

文字を置換する、複数置換する(replace、translate)

前0埋め、後ろ0埋め(lpad、rpad)

 

GROUP BYとは

GROUP BYとはグループ化を行うときに使う構文です。

 

グループ化とは、例えば学校を例すると、

・クラスごとに合計点を計算する

・学年ごとに平均点を計算する

・教科ごとに最高点、最低点を計算する

等々、○○ごとに集計をするために使います。

 

 

集約関数の種類

求めたいものによって、SUMやMAXという関数(集約関数といいます)を使います。

SUM  … 合計を求める

MAX … 最大値を求める

MIN … 最小値を求める

AVG … 平均を求める

COUNT … 行数をカウントする

 

 

GROUP BYの構文と注意

GROUP BYの構文としてはこのような形になります。

--GROUP BYの構文
SELECT 列名1 , SUM(列名2) , AVG(列名3) 
FROM テーブル名 
GROUP BY 列名1; --列名1でグループ化する

 

GROUP BYの注意点は、SELECTからFROMまでの間に書く列名は、GROUP BYの後に書いてある列名だけそのまま書くことができるという点です。

 

上の構文では、『GROUP BY 列名1』となっているため、『SELECT 列名1 FROM テーブル名』と書くことができます。逆にいうと、列名2と列名3はGROUP BYの後ろに書かれていないため、集約関数とともに書かないといけません。そのため列名2 , 3は『SUM(列名2) , AVG(列名3)』となっています。

 

 

GROUP BYの注意(並び替え)

GROUP BYでは、集計をかけつつ並び替えも可能です。

(例えば、クラス別の合計点を計算し、合計点の高い順に並べるなど)

--ORDER BY(並び順)のSQL例
SELECT 列名1 , SUM(列名2) , AVG(列名3) 
FROM テーブル名 
GROUP BY 列名1 
ORDER BY 列名1 , SUM(列名2);

 

ORDER BYの後も基本的には『SELECT~FROM』の決まりと変わりありません。つまり列名2と列名3はGROUP BYの後ろに書かれていないため、ORDER BYでも集約関数とともに書かないといけません。以上が注意点になります。

 

 

SUM…合計を求める

SUMは合計値を求める関数です。

--合計を求める構文(列名2でグループ化、列名1を合計する)
select SUM(列名1) 
from テーブル名 
GROUP BY 列名2;

--例1:生徒でグループ化、合計点を取得する
select student_code , sum(score) 
from t_student_score 
group by school_code,student_code 
order by sum(score) desc;

--例2:教科でグループ化、合計点を取得する
select subject_code , sum(score) 
from t_student_score 
group by school_code,subject_code 
order by sum(score) desc;

 

 

Min…最小値を求める、Max 最大値を求める

Minは最小値を求める関数です。

--Minを使ったSQLの書き方
select Min(列名1) from テーブル名 GROUP BY 列名2;   --最小値を求める
select Max(列名1) from テーブル名 GROUP BY 列名2;   --最大値を求める

--生徒ごとに最低点、最高点を取得する
select student_code , min(score) , max(score) 
From t_student_score 
group by school_code,student_code 
order by student_code;

 

 

avg…平均点を求める

avgは最小値を求める関数です。

--avgを使ったSQLの書き方
select avg(列名1) from テーブル名 GROUP BY 列名2;

--生徒ごとに平均点を取得する
select student_code , avg(score) 
From t_student_score 
group by school_code,student_code 
order by student_code;

 

 

COUNT…行数(データの数)をカウントする

COUNTは最小値を求める関数です。

--countを使ったSQL文の例
select count(*) from テーブル名 GROUP BY 列名2;

--生徒ごとの点数データ数をカウントする
select student_code,count(*)
From t_student_score 
group by school_code,student_code 
order by student_code;

文字を左から○文字切り取る(Left関数)

文字列の左から○文字取得するには、Left関数を使用します。

切り取る文字数で指定するのは、全半角を考慮しない単純な文字数です。

--Left関数
Left( 切り取る文字列 , 左から切り取る文字数 )

--文字列'ABCDEFGHIJK'について、左から3文字切り取る
select * from left('ABCDEFGHIJK',3);  -- "ABC"

--文字列'あいうえおかきくけこ'について、左から5文字切り取る
select * from left('あいうえおかきくけこ',5);  -- "あいうえお"

 

 

文字を右から○文字切り取る(Right関数)

文字列の右から○文字取得するには、Right関数を使用します。

切り取る文字数で指定するのは、全半角を考慮しない単純な文字数です。

--Right関数
Right( 切り取る文字列 , 右から切り取る文字数 )

--文字列'ABCDEFGHIJK'について、右から3文字切り取る
select * from right('ABCDEFGHIJK',3);  -- "IJK"

--文字列'あいうえおかきくけこ'について、右から5文字切り取る
select * from right('あいうえおかきくけこ',5);  -- "かきくけこ"

 

 

文字を左のX文字目から○文字切り取る(Substring関数)

文字列から指定した部分を取得するには、substring関数を使用します。

切り取る位置、文字数で指定するのは、全半角を考慮しない単純な文字数です。

--Substring関数
--「切り取り始める位置」は、0からではなく1からカウント
Substring( 切り取る文字列 , 切り取り始める位置 , 切り取る文字数)

--文字列'ABCDEFGHIJK'について、3文字目から4文字切り取る
select * from substring('ABCDEFGHIJK',3,4);  --"CDEF"

--文字列'あいうえおかきくけこ'について、5文字目から3文字切り取る
select * from substring('あいうえおかきくけこ',5,3);  --"おかき"

--3つ目の引数『切り取る文字数』は、その文字列の文字数を超えてもエラーにはなりません
--次の例は3文字目から100文字取得としていますが、エラーにならず最後まで取得可能です
select * from substring('ABCDEFGHIJK',3,100);  --"CDEFGHIJK"

 

 

先頭から○文字削る、後ろから○文字削る

substringとlengthを使い、文字を削る例を紹介します。

-- 1.先頭からX文字を削る例
--'abcdefgh'の先頭3文字を削る(=4文字目以降すべて取得する)
select * from substring('abcdefgh',4,length('abcdefgh'));    --"defgh"

-- 2.後ろからX文字を削る例
--'abcdefgh'の後ろ3文字を削る
select * from substring('abcdefgh',1,length('abcdefgh')-3);  --"abcde"

 

あわせて読みたい

https://postgresweb.com/post-5440

 

 

Where文

Where文をSelect文の後につけることで、抽出条件を指定できます。

select 列名 from テーブル名 where 条件;

抽出条件が複数あった場合は、「and」でつなぎます。

select 列名 from テーブル名 where 条件1 and 条件2;

 

Select文に抽出条件を加えた例です。

--学生コード = 'stu01'となっているデータを抽出する。
select * from m_student_score where student_code = 'stu01';

--得点(score)が80以上のデータを取得する
select * from m_student_score where score >= 80;

--得点(score)が50以下のデータを取得する
select * from m_student_score where score <= 50;

--得点(score)が50以下のデータ 
--かつ 学生コードが'sub06'となっているデータを抽出する。
select * from m_student_score where score <= 50 and student_code = 'stu06'; 

--得点(score)が30以上50以下のデータを取得する(以下の二つは結果が同じです)。 
select * from m_student_score where score >= 30 and score <= 50;
select * from m_student_score where score between 30 and 50;

 

like

文字を抽出条件にする時、等しい場合は「=」、部分一致の場合は「like」を使います。

--等しいものを抽出
select 列名 from テーブル名 where 条件 = 文字列;

--前方一致するものを抽出
select 列名 from テーブル名 where 条件 like 文字列%;

--後方一致するものを抽出
select 列名 from テーブル名 where 条件 like %文字列;

--部分一致するものを抽出
select 列名 from テーブル名 where 条件 like %文字列%;

 

like例

--名前が「織田信長」と一致するデータを取得
select * From m_student where student_name = '織田信長';

--名前が「豊臣」から始まるデータを取得
select * From m_student where student_name like '豊臣%';

--名前が「家康」で終わるデータを取得
select * From m_student where student_name like '%家康';

--名前が「田」を含むデータを取得
select * From m_student where student_name like '%田%';

 

In

「○か○か○のもの」のように条件を指定する場合は、IN句を使用します。

select 列名 from テーブル名 where 列名 in(値1 , 値2 , 値3);

--例:商品分類が食品、電化製品、車の商品を取得する。
select * from 商品 where 分類 in('食品' , '電化製品' , '車');

 

Not In

「○か○か○でないもの」のように条件を指定する場合は、NOT IN句を使用します。

select 列名 from テーブル名 where 列名 not in(値1 , 値2 , 値3);

--例:商品分類が食品、電化製品、車でない商品を取得する。
select * from 商品 where 分類 not in('食品' , '電化製品' , '車');

 

サブクエリ

SELECT文を実行すると、現在接続中のセッションを取得できます。

select 列名 from テーブル名1 
where 列名1 in(select 列名2 from テーブル名2); --列名1と列名2は一致してなくてもOKです。

--例:Aさんが購入した商品を取得する
select * from 商品 where 商品コード in(select 商品コード from 商品購入履歴 where ユーザーID = AさんのID);

上の例では、()内の『select 商品コード from 商品購入履歴 where ユーザーID = AさんのID』の

結果で、商品コードで「商品」テーブルを取得しています。

構文

数値を文字に変換し書式を設定するには、to_char(数値 ,  書式)を使用します。

使用例はこの下を参照して下さい。

--数値を文字に変換する
to_char( 数値 , 書式 )

 

あわせて読みたい

※ 書式の指定が必要ない数値→文字の変換は、castを使う方法がおすすめです

https://postgresweb.com/post-342

 

 

使用例

to_charを使うと先頭に謎の空白が入るので、「FM」をつけ、空白がつかないようにしています。

0と9の違いは、0は必ずその桁数まで表示され、9は数値がある桁数まで表示します。

書式は公式に例がありますが実務では使用しないものが多く、次の例で事足ります。

-- カンマ区切り+少数点(3桁まで表示)
select * From to_char(12345.67, 'FM999,999.999');     -- 12,345.67

-- カンマ区切り+少数点(3桁で表示)
select * From to_char(12345.67, 'FM999,999.000');     -- 12,345.670

-- マイナス値+カンマ区切り+少数点(3桁まで表示)
select * From to_char(-12345.67, 'FM999,999.999');     -- -12,345.67

-- マイナス値+カンマ区切り+少数点(3桁で表示)
select * From to_char(-12345.67, 'FM999,999.000');     -- -12,345.670

 

 

 

返り値の最後が「.」になってしまう時の対処方法

引数の値が整数なのか小数なのかはっきりしない場合、

例1のような書式設定をすると、返り値の最後が「.」で終わってしまうケースがあります。

その場合、例2のようにcase文を使って場合分けをすると解決できます。

 -- 例1.返り値の最後が「.」になってしまうケース
select to_char(12345, 'FM999,999.999');   -- "12,345." 

-- 例2.帰り値の最後が「.」になってしまうのを対処したパターン(CASE文を使用) 
select case 
when right(to_char(12345, 'FM999,999.9'),1) = '.' then 
to_char(12345, 'FM999,999')              -- 最後が「.」なら整数として書式設定する
else to_char(12345, 'FM999,999.9') end;  -- そうでなければ小数用の書式設定をする
-- "12,345"

 

 

to_charを使った日付書式

to_charは日付の書式設定にも使用することができます。

 

あわせて読みたい

https://postgresweb.com/post-399

 

 

日、週、月の加算をする

-- 日付の加算、週の加算、月の加算
日付 + cast( '5 days' as INTERVAL )       -- 5日加える
日付 + cast( '5 weeks' as INTERVAL )      -- 5週加える
日付 + cast( '5 months' as INTERVAL )     -- 5か月加える

-- 時間の加算、分の加算、秒の加算
日付 + cast( '5 hours' as INTERVAL )      -- 5時間加える
日付 + cast( '5 minutes' as INTERVAL )    -- 5分加える
日付 + cast( '5 seconds' as INTERVAL )    -- 5秒加える

-- マイナスを付けると減算になります
日付 + cast( '-5 days' as INTERVAL )      -- 5日前を求める

※ 日付はDate型、Timestamp型どちらでもOKです。

※ 例ではdaysのように複数形になっていますが、1 daysや2 dayと記述しても普通に動きます。

 

あわせて読みたい

日付の差を求める方法をこちらで紹介しています

https://postgresweb.com/post-6433

 

 

 

日、週、月の加算例

日、週、月の加算例です。ここの例はそのままコピー&貼り付けで実行できます。

日付の加算

-- 1.現在時刻に5日加える
select now() + cast('5 days' as INTERVAL);
-- 2020-07-16 08:00:00.000000+09


-- 2.『2020/1/1』に5日加える
select cast('2020/1/1' as date) + cast('5 days' as INTERVAL); 
-- 2019-01-06 00:00:00


-- 3.『2020/1/1 9:00:00』に5日加える
select cast('20200101 09:00:00' as timestamp) + cast('5 days' as INTERVAL); 
-- 2020-01-06 09:00:00

 

週の加算例

-- 1.現在時刻に5週加える
select now() + cast('5 weeks' as INTERVAL);
-- 2020-08-15 08:00:00.000000+09


-- 2.『2020/1/1』に5週加える
select cast('2020/1/1' as date) + cast('5 weeks' as INTERVAL); 
-- 2019-02-05 00:00:00


-- 3.『2020/1/1 9:00:00』に5週加える
select cast('20200101 09:00:00' as timestamp) + cast('5 weeks' as INTERVAL); 
-- 2020-02-05 09:00:00

 

月の加算例

-- 1.現在時刻に5ヶ月加える
select now() + cast('5 months' as INTERVAL);
-- 2020-12-11 08:00:00.000000+09


-- 2.『2020/1/1』に5ヶ月加える
select cast('2020/1/1' as date) + cast('5 months' as INTERVAL); 
-- 2019-06-01 00:00:00


-- 3.『2020/1/1 9:00:00』に5ヶ月加える
select cast('20200101 09:00:00' as timestamp) + cast('5 months' as INTERVAL); 
-- 2020-06-01 09:00:00

 

 

時、分、秒の加算例

時間の加算例

-- 1.現在時刻に5時間加える
select now() + cast('5 hours' as INTERVAL);
-- 2020-07-11 13:00:00.000000+09


-- 2.『2020/1/1』に5時間加える
select cast('2020/1/1' as date) + cast('5 hours' as INTERVAL); 
-- 2020-01-01 05:00:00


-- 3.『2020/1/1 9:00:00』に5時間加える
select cast('20200101 09:00:00' as timestamp) + cast('5 hours' as INTERVAL); 
-- 2020-01-01 14:00:00

 

分の加算例

-- 1.現在時刻に5分加える
select now() + cast('5 minutes' as INTERVAL);
-- 2020-07-11 13:00:00.000000+09


-- 2.『2020/1/1』に5分加える
select cast('2020/1/1' as date) + cast('5 minutes' as INTERVAL); 
-- 2020-01-01 00:05:00


-- 3.『2020/1/1 9:00:00』に5分加える
select cast('20200101 09:00:00' as timestamp) + cast('5 minutes' as INTERVAL); 
-- 2020-01-01 09:05:00

 

秒の加算例

-- 1.現在時刻に5秒加える
select now() + cast('5 seconds' as INTERVAL);
-- 2020-07-11 08:00:05.000000+09


-- 2.『2020/1/1』に5秒加える
select cast('2020/1/1' as date) + cast('5 seconds' as INTERVAL); 
-- 2020-01-01 00:00:05


-- 3.『2020/1/1 9:00:00』に5秒加える
select cast('20200101 09:00:00' as timestamp) + cast('5 seconds' as INTERVAL); 
-- 2020-01-01 09:00:05

 

 

【応用】月末日を求める

使う頻度はそう高くはないですが、日付を加える方法を使って月末日を求める方法があります。

例えば2月の月末日を求めたい時、3/1から1日前を求めればよいことになります。

-- 2021年2月(下の例では2021/2/14の日付から)の月末日を取得する
select date(DATE_TRUNC('month', cast('2021/2/14' as date)) + '1 month' +'-1 Day');
-- "2021-02-28" 
あわせて読みたい

上の例で使用している関数はこちらで紹介しています。

https://postgresweb.com/postgresql-date-trunc

 

 

配列の要素数を取得する

配列の要素数を取得するには「array_length」を使います。

-- 配列の要素数を取得する
-- ※ カンマのあとの「1」は1次元の配列であることを表しています
array_length( 配列や配列の変数名 , 1 )

 

 

使用例

-- 1次元配列の要素数を取得する
select * from array_length(array[1,2,3,4,5],1);    --【結果】:5
select * from array_length(array['A','B','C'],1);  --【結果】:3

 

 

 

2次元配列の要素数

2次元配列の要素数を取得するにもarray_length関数を使います。

1次元の要素数を取得するにはarray_length( 配列 , 1 )を、

2次元の要素数を取得するにはarray_length( 配列 , 2 )を指定します。

-- 2次元配列の1次元の要素数を取得する
select * from array_length(array[[1,2],[3,4],[5,6]],1);  --【結果】:3

-- 2次元配列の2次元の要素数を取得する
select * from array_length(array[[1,2],[3,4],[5,6]],2);  --【結果】:2

 

 

構文

型を変換するにはcast(キャスト)を使用します。

-- 型を変換する
cast( A as B )    -- Aを型Bに変換する

A: 型変換をする文字・数字・日付など

B: 変換する型を指定します。

※ castではなく「'123'::integer」、「123::text」のように「::型」で表現する方法もありますが、

こちらにあるとおり同じ意味です。

 

B(変換する型)に設定する代表的な型

 型  代表例
 文字型  character(桁数) , character varying , text
 数値型  integer , numeric
 日付型  date , timestamp
 論理型  boolean

 

 

文字から数値へ変換する例

文字から数値へ変換する例です。SELECT文はそのまま実行できます。

-- '0123'をintegerへ変換する
select cast('0123' as integer);     -- 123

-- '0123.45'をnumericへ変換する
select cast('0123.45' as numeric);  -- 123.45

-- 小数点第何位までで変換
-- numericの左の数値は全体の桁数、右は小数点の桁数
SELECT cast('1234567.345' as numeric(10,2) );  -- 小数点第2位まで:1234567.35
SELECT cast('1234567.345' as numeric(10,3) );  -- 小数点第3位まで:1234567.345
SELECT cast('1234567.345' as numeric(12,5) );  -- 小数点第5位まで:1234567.34500

-- マイナス値
SELECT cast('-123456.789' as numeric);        -- -123456.789
SELECT cast('-123456.789' as numeric(8,2) );  -- -123456.79

-- 'abc'をintegerへ変換する
select cast('abc' as integer);      -- エラーが発生

--上の例は、次のようにも書くこともできます(同じ意味です)
select '0123'::integer;             -- 123
select '0123.45'::numeric;          -- 123.45
select 'abc'::integer;              -- エラー

 

あわせて読みたい

数値でないものを変換する可能性がある場合は、事前チェックが必要です。

https://postgresweb.com/post-5440

 

 

 

数値から文字へ変換する

数値から文字へ変換する例です。SELECT文はそのまま実行できます。

-- 12345をcharacter varyingへ変換する
select cast(12345 as character varying);     -- '12345'

-- 12345をcharacter varying(3)へ変換する
select cast(12345 as character varying(3));  -- '123'

-- 45678をtextへ変換する
select cast(45678 as text);                  -- '45678'

 

 

動画で確認する

説明分だけだとイメージがわからない場合は動画をご覧ください。

(無音でもわかるように字幕をいれています)

 

 

関連記事

前0埋め、後ろ0埋め(lpad、rpad)

数値の書式設定をする

文字を日付に変換する