こんにちは、キノコードです。
今回は「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のレッスンの他に、様々な動画を出しております。
気に入っていただけましたら、新着通知も届きますので、ぜひチャンネル登録をお願いします。
プログラミング学習サービス「キノクエスト」のご紹介
キノコードでは、プログラミングを習得するためのプログラミング学習サービス「キノクエスト」を運営しています。
Pythonの問題の他に、SQLの問題も120問以上あります。
キノクエストには、学習カリキュラムがあり、学習順番に悩むことなく学習を進められます。
月額1,990円と本1冊分の値段です。
キノクエストの特徴は下記の通りです。
- SQLの問題も120問以上
- プログラミング学習をしている仲間が集まるコミュニティがある
- 1000問以上の問題を解いてプログラミングを習得
- 環境構築不要ですぐに始められる
- 動画と連動しているので、インプットもできる。
- 月額1,990円で、コミュニティもセット
キノクエストを詳しく知りたい方は、紹介ページをご覧ください。
▼キノクエストの紹介ページはこちら▼
https://kino-code.com/kq_service_a/
それでは、レッスンスタートです。
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のコードを紹介します。
気に入っていただけましたら、ぜひチャンネル登録をお願いします!
それではまた次のレッスンでお会いしましょう!