【SQL超入門講座】18.SUBSTRING|文字列の一部を切り出す方法

挨拶

こんにちは、キノコードです。
今回は「substring」について説明します。
substr関数は文字列から一部分を切り出すことができる関数です。

例えばこのような数年分の商品の売り上げデータから、どの月に一番商品が売れるか調べたいとしましょう。
このときsubstring関数を使えば、日付から月の部分だけを切り出すことができるので、集計が楽に行えます。

売上日売上金額
2021-09-01¥12,000
2021-08-23¥9,000
2020-09-05¥5,000
2020-05-27¥10,000

このようにカラムのデータの一部分を切り出して、集計や分類を行うことはよくあります。
そのため、substring関数の使い方はしっかりマスターしておくようにしましょう。

キノコードでは、SQLのレッスンの他に、様々な動画を出しております。
気に入っていただけましたら、新着通知も届きますので、ぜひチャンネル登録をお願いします。

それではレッスンスタートです。

【SQL超入門講座】18.SUBSTRING|文字列の一部を切り出す方法

substringについて

substring関数は対象の文字列から、開始位置と文字数を指定して、一部文を切り出すことができます。
substring関数の基本書式はこちらです。

substring(文字列(カラム名), 開始位置, 文字数)

第一引数に対象の文字列またはカラムを指定します。
第二引数に文字列を切り出す開始位置を整数で指定します。
この開始位置の整数は、プラス、マイナスどちらも取ることができます。
開始位置の整数をNとすると、次のように考えることができます。
もしNがプラスの場合、最初の文字を1番目と考え、最初の文字からN番目が開始位置となります。
例えばNが3であれば、最初の文字から3文字目が開始位置となります。

もしNがマイナスの場合、最後の文字を-1番目と考え、最後の文字からN番目が開始位置となります。
例えばNが-3であれば、最後の文字から3文字前が開始位置となります。

substring関数はDBによって関数名や文法が若干異なります。
substrと記載するものもありますので、使い分けに注意しましょう。

それでは実際にテーブルを用いて、使い方を確認しましょう。
今回使用するテーブルはこちらのtest_tableです。
test_tableはどの社員がいつ、何を売り上げたか記録されているテーブルです。

文字列から切り出して条件抽出

まずtest_tableから、「社員ID」の下二桁が「0~20」のデータを全て抽出してみましょう。
SQLはこのように書くことができます。

SELECT *
FROM test_table
WHERE substring("社員ID",3,2) BETWEEN '00' and '20'

WHERE句にsubstring関数を使って条件を書きます。
第一引数に対象のカラム「社員ID」を指定します。
この社員IDから下二桁を切り出し、条件に合致するか調べます。
社員IDは4桁なので、下二桁の開始位置は最初の文字から数えて3番目です。
そのため第二引数に3と書きます。

もちろん下二桁の開始位置を、最後の文字から数えて2番目と考えてもOKです。
その場合、第二引数には-2と書きましょう。
最後に、第三引数に切り取る文字数の2を書きます。
これで社員IDから下二桁が切り出せたことになります。
このsubstring関数で切り出した社員IDの下二桁が、0から20の間としたいので、between関数を使います。

それではSQLを実行します。

「社員ID」の下二桁が「0~20」のデータが98件抽出されました。

年月ごとの売り上げ集計をする

では次に「売上日の年月ごと」の「売上金額の合計」のデータを抽出してみましょう。
SQLはこのように書くことができます。

SELECT substring("売上日",1,7) 年月,
sum("売上金額") 売上合計
from test_table
group by 年月

売上金額を年月ごとにグループにして集計します。
この売上金額の集計基準となる年月は、売上日からsubstring関数を使って抽出します。

まず第一引数に対象となる売上日のカラムを指定します。
売上日の最初の文字から7番目の文字までが年月の部分です。
そのため第二引数に1と書き、第三引数に7と書きます。
この年月の部分を切り取ったカラムには、そのまま「年月」という別名をつけておきましょう。

年月ごとに売上金額を集計するので、GROUP BY句に年月のカラムを指定します。
そしてSELECT句で年月のカラムを抽出するとともに、SUM関数で売上金額を集計しましょう。
それではSQLを実行します。

「売上金額の合計」が「年月ごと」に12件抽出されました。

今度は「売上日の日ごと」の「売上金額の合計」のデータを抽出してみましょう。
SQLは先ほど作成したものを流用して、このように書くことができます。

SELECT substr(売上日,9) 日, SUM(売上金額) 売上合計
FROM test_table
GROUP BY 日

売上金額の集計が年月ごとではなく、日ごとに変わるだけなので、先ほど作成したSQLと構成は同じです。
先ほどはsubstring関数で売上日から年月を切り出しましたが、日を切り出すよう引数を変更しましょう。

売上日の日の部分の開始位置は、売上日の最初から数えるより、最後から数えたほうが簡単です。
しかし、posgresqlでは、最後から数える概念がありません。
日は売上日の先頭から数えて9番め以降なので、substringの第二引数に9と書きます。 文字数は最後までなので、第三引数は省略できます。
そして日の部分を切り取ったカラムには、そのまま「日」という別名をつけておきましょう。

それではSQLを実行します。

これで、「売上金額の合計」が「日ごと」に31件抽出されました!

ED

さて、substringの説明と使用例をご紹介しましたが、いかがでしたでしょうか?
キノコードでは、これからも色々なSQLのコードを紹介します。
気に入っていただけましたら、ぜひチャンネル登録をお願いします!
それではまた次のレッスンでお会いしましょう!