SQLで関数を使用する方法

8か月 ago

芽依, 雨夜

6 minutes

著者は、「Write for Donations（寄付を募るために書く）」プログラムの一環として、Diversity in Tech Fundに寄付金を贈ることを選びました。

こんにちは、最初に紹介させていただきます。

リレーショナルデータベースと構造化クエリ言語（SQL）を使用する際には、リレーショナルデータベース管理システムからデータを格納、管理、取得することができます。SQLはデータベース内に格納されたデータをそのまま取得することができます。

SQLでは、関数を使用して計算やデータの操作を行うこともできます。たとえば、関数を使って商品の価格を最も近いドルに丸めたり、製品の平均購入数を計算したり、特定の購入品の保証期間が終了するまでの日数を求めることができます。

このチュートリアルでは、さまざまなSQL関数を使用して、数学的な計算、文字列と日付の操作、および集計関数を使用した要約の計算を行います。

前提条件

このガイドに従うには、SQLベースのリレーショナルデータベース管理システム（RDBMS）を実行するコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されました。

A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
Basic familiarity with executing SELECT queries to select data from the database, as described in our How To SELECT Rows FROM Tables in SQL guide.

Note

注意：多くのRDBMSは独自のSQL実装を使用しています。このチュートリアルで説明するコマンドはほとんどのRDBMSで動作しますが、標準のSQL構文では制限された数の関数が指定されています。さらに、標準の構文のサポートは異なるデータベースエンジンごとに異なります。MySQL以外のシステムでテストすると、正確な構文や出力が異なる場合があります。

以下のような日本語の表現に言い換えます（１つのオプションのみ）：
関数の使用方法を練習するために、いくつかのサンプルデータがロードされたテーブルを持つデータベースも必要です。本ガイド全体で使用する例として、MySQLサーバーに接続し、テスト用データベースを作成する詳細については、「MySQLへの接続とサンプルデータベースの設定」セクションをご参照ください。

MySQLに接続し、サンプルデータベースの設定を行います。

このセクションでは、MySQLサーバーに接続し、サンプルデータベースを作成することで、このガイドの例に従うことができます。

もしSQLデータベースシステムがリモートサーバー上で動作している場合、ローカルマシンからサーバーにSSHで接続してください。

ssh sammy@your_server_ip

それから、MySQLサーバープロンプトを開き、MySQLユーザーアカウントの名前である「sammy」を使ってください。

mysql -u sammy -p

本屋という名前のデータベースを作成してください。

CREATE DATABASE bookstore;

データベースが正常に作成された場合、以下のような出力が表示されます。

Output

Query OK, 1 row affected (0.01 sec)

書店データベースを選択するためには、次のUSEステートメントを実行してください。

USE bookstore;

次の出力を受け取ります。

Output

Database changed

データベースを選択した後、その中でサンプルテーブルを作成することができます。このガイドでは、異なる著者による本を販売する架空の書店を使用します。

書店のテーブル在庫には、書籍に関するデータが含まれます。以下の列が保持されます：

book_id: This column holds the identifier for each book, represented by the int data type. This column will become the table’s primary key, with each value becoming a unique identifier for its respective row.
author: This column holds the book author’s name, expressed using the varchar data type with a maximum of 50 characters.
title: This column holds the purchased book’s title, expressed using the varchar data type with a maximum of 200 characters.
introduction_date: Using the date data type, this column holds the date each book was introduced by the bookstore.
stock: This column holds the number of books the bookstore has in its inventory using the int integer data type.
price: This column stores the book’s retail price using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

以下のコマンドでサンプルテーブルを作成してください。

CREATE TABLE inventory (
book_id int,
author varchar(50),
title varchar(200),
introduction_date date,
stock int,
price decimal(5, 2),
PRIMARY KEY (book_id)
);

もし以下の出力が表示されれば、テーブルが作成されています。

Output

Query OK, 0 rows affected (0.00 sec)

その後、次のINSERT INTO操作を実行して、購入テーブルにいくつかのサンプルデータをロードしてください。

INSERT INTO inventory
VALUES
(1, ‘Oscar Wilde’, ‘The Picture of Dorian Gray’, ‘2022-10-01’, 4, 20.83),
(2, ‘Jane Austen’, ‘Pride and Prejudice’, ‘2022-10-04’, 12, 42.13),
(3, ‘Herbert George Wells’, ‘The Time Machine’, ‘2022-09-23’, 7, 21.99),
(4, ‘Mary Shelley’, ‘Frankenstein’, ‘2022-07-23’, 9, 17.43),
(5, ‘Mark Twain’, ‘The Adventures of Huckleberry Finn’, ‘2022-10-01’, 14, 23.15);

指定された値を持つ5冊の本がインベントリーテーブルに追加されるINSERT INTO操作が行われます。以下の出力は、すべての5行が追加されたことを示しています。

Output

Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0

それでは、残りのガイドをフォローし、SQLで関数を使用する準備が整いました。

SQL関数の理解

関数は、1つまたは複数の値を受け取り、データに対して計算や変換を行い、新しい値を結果として返す名前付きの式です。SQLの関数は、数学の関数に似たようなものです。関数log(x)は、あるxを取り、xの対数の値を返します。

通常、リレーショナルデータベースから情報を取得する場合（変換せずに）、SELECTクエリを使用し、ステートメントで列名を指定してデータベースに値を返すよう要求します。

たとえば、もっとも高価なものからもっとも安価なものまで順に並べ替えられた、すべての本のタイトルと価格を取得したい場合は、次のステートメントを実行することができます。

SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;

以下の出力を受け取ることになります。

Output

+————————————+——-+——————-+ | title | price | introduction_date | +————————————+——-+——————-+ | Pride and Prejudice | 42.13 | 2022-10-04 | | The Adventures of Huckleberry Finn | 23.15 | 2022-10-01 | | The Time Machine | 21.99 | 2022-09-23 | | The Picture of Dorian Gray | 20.83 | 2022-10-01 | | Frankenstein | 17.43 | 2022-07-23 | +————————————+——-+——————-+ 5 rows in set (0.000 sec)

この文では、タイトル、価格、および導入日が列名として示され、出力結果では、各書籍ごとにそれらの列から取得した完全な書名、価格、および書籍が書店に入荷した日付がデータベースで提示されます。

ただし、何らかの処理や操作の後でデータベースから値を取得することも考えられます。最も近いドルに切り上げた書籍の価格、大文字で表示された書籍のタイトル、または月や日を含まない紹介年。こういった場合には、関数を使用することがあります。

SQL関数は、操作するデータのタイプに応じていくつかのグループに大別されます。以下は最も一般的に使用される関数です。

Mathematical Functions: Functions operating on numerical values and performing computations, such as rounding, logarithms, square roots, or powers.
String Manipulation Functions: Functions operating on strings and text fields that perform text transformations, such as converting the text to uppercase, trimming, or replacing words within the values.
Date and Time Functions: Functions operating on fields holding dates. These functions perform computations and transformations, such as adding a number of days to the given date or taking just a year from the full date.
Aggregate Functions: A special case of mathematical functions that operate on values coming from multiple rows, such as calculating an average price for all rows.

Note

注意：MySQLを含むほとんどの関係データベースは、SQLの標準セットに追加の操作を追加しています。これらの操作は、それぞれのデータベースエンジンに固有のものです。標準セット外の多くの関数は、多くのデータベースで同様に機能しますが、一部は特定のRDBMSに制限されており、その固有の機能に対応しています。データベースが提供する関数については、選択したデータベースのドキュメントを参照することができます。MySQLの場合は、「Built-In Function and Operator Reference」で詳細を学ぶことができます。

以下の例は、SELECTクエリを使用して書店の在庫データベースの価格値を変更するために使用される架空の存在しない関数であるEXAMPLEの一般的な構文を示しています。

SELECT EXAMPLE(price) AS new_price FROM inventory;

関数（例えばEXAMPLE）は、カラム名（price）を括弧で囲んだ引数として受け取ります。このクエリの部分は、データベースに対して関数EXAMPLEを実行し、カラムpriceの値に対しての操作結果を返すことを指示します。AS new_priceは、クエリの期間中に計算された値に一時的な名前（new_price）を割り当てるため、出力で関数の結果を区別し、WHEREやORDER BY節を使用して計算された値を参照することができます。

以下のセクションでは、一般的に使用される計算を行うために数学関数を使用します。

数学的な関数を使用する

数学関数は、サンプルデータベース内の書籍の価格や在庫数などの数値に作用します。これらは、データベース内で計算を行い、結果を要件に合わせるために使用することができます。

ラウンディングはSQLで最もよく使用される数学関数の一つです。全ての本の価格を取得する必要がありますが、最寄りの整数ドルに丸められた値に興味があります。そのためには、ラウンド関数を使用することができます。この関数は丸め操作を行います。

以下のステートメントを実行してみてください。

SELECT title, price, ROUND(price) AS rounded_price FROM inventory;

次の出力は画面に表示されます。

Output

+————————————+——-+—————+ | title | price | rounded_price | +————————————+——-+—————+ | The Picture of Dorian Gray | 20.83 | 21 | | Pride and Prejudice | 42.13 | 42 | | The Time Machine | 21.99 | 22 | | Frankenstein | 17.43 | 17 | | The Adventures of Huckleberry Finn | 23.15 | 23 | +————————————+——-+—————+ 5 rows in set (0.000 sec)

クエリは、タイトルと価格の列から値を抽出し、ROUND(price)関数の結果を持つ一時的なrounded_price列と一緒に返します。この関数は1つの引数、列名（この場合は価格）を取り、テーブル内のその列の値を最も近い整数値に丸めた結果を返します。

以下のクエリを実行してみましょう。丸める小数点以下の桁数を定義するための追加の引数や、単一の列名ではなく算術演算も受け入れることができる丸め関数があります。

SELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;

以下の出力を受け取ります。 (Anata wa shita no shutsuryoku o uketorimasu.)

Output

+————————————+——-+——-+————-+ | title | stock | price | stock_price | +————————————+——-+——-+————-+ | The Picture of Dorian Gray | 4 | 20.83 | 83.3 | | Pride and Prejudice | 12 | 42.13 | 505.6 | | The Time Machine | 7 | 21.99 | 153.9 | | Frankenstein | 9 | 17.43 | 156.9 | | The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 | +————————————+——-+——-+————-+ 5 rows in set (0.000 sec)

`ROUND(価格 * 在庫数, 1)`を実行すると、まず単一の本の価格を在庫の冊数で乗算し、その結果の価格を小数点以下1桁で四捨五入します。その結果は、stock_price という一時的な列で表示されます。

MySQLに組み込まれている他の数学関数には、三角関数、平方根、累乗、対数、指数関数などがあります。数学関数をSQLで使用する方法については、SQLチュートリアル「数学的な式と集約関数の使用方法」で詳しく学ぶことができます。

次のセクションでは、SQL関数を使用してデータベースからテキストを操作します。

文字列操作関数を使用して

SQLの文字列操作関数は、SQLクエリの処理時にテキストを保持する列の値を変更することができます。これらの関数は、例えば、大文字小文字の変換、複数の列からデータを連結する、または検索と置換の操作を実行するために使用することができます。

本のタイトルをすべて小文字に変換して取得し、文字列の関数を使用し始めます。次の文を実行してください。

SELECT LOWER(title) AS title_lowercase FROM inventory;

以下の出力は画面に表示されます。

Output

SQL関数であるLOWERは、単一の引数を取り、その内容を小文字に変換します。結果のデータは、一時的な列としてtitle_lowercaseという名前で表示されます。

今回は、すべての著者を大文字に変換して取得してください。以下のSQLクエリを実行してみてください。

SELECT UPPER(author) AS author_uppercase FROM inventory;

以下の出力が送られます。

Output

LOWER関数の代わりに、大文字に変換するUPPER関数を使用しました。両方の関数は、データを取得する際に文字の大文字・小文字の一貫性が必要な場合に使用することができます。

別の便利な文字列操作関数はCONCATです。この関数はテキスト値を複数の引数として受け取り、それらを結合します。書籍の著者とタイトルを1つの列に組み合わせて取得してみてください。そのためには、以下のステートメントを実行してください。

SELECT CONCAT(author, ‘: ‘, title) AS full_title FROM inventory;

この文は、次の出力を返します。

Output

CONCAT関数は複数の文字列を連結し、3つの引数で実行されました。最初の引数の”author”は、著者の名前が格納されている列を指します。2番目の引数である”:”は、著者と書籍のタイトルをコロンで区切るための任意の文字列値です。最後の引数である”title”は、書籍のタイトルが格納されている列を指します。

このクエリの結果では、データベースエンジンによって直接連結された単一の一時的な「full_title」という列に著者とタイトルが返されます。

MySQLに組み込まれている他の文字列関数には、文字列の検索と置換、部分文字列の取得、文字列値のパディングとトリミング、正規表現の適用などの関数があります。複数の値を連結するためのSQL関数の使用方法については、「How To Manipulate Data with CAST Functions and Concatenation Expressions in SQL」チュートリアルで詳しく説明されています。MySQLのドキュメントの「String Functions and Operators」も参照できます。

次のセクションでは、データベースから日付を操作するためにSQL関数を使用します。

日付と時刻の関数を使用する。

SQLにおける日付と時間の機能は、SQLクエリの処理時に日付やタイムスタンプを保持する列の値を操作することができます。これらの機能は、日付情報の一部を抽出したり、日付演算を行ったり、日付やタイムスタンプを必要な出力形式に書式設定したりするために使用することができます。

出力に単一の日付列を持つ代わりに、本の紹介日を年、月、日ごとに別々に分割する必要があると仮定しましょう。

以下のステートメントを実行してみてください。

SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;

あなたはこの出力を受け取ります。

Output

+——————-+——+——-+——+ | introduction_date | year | month | day | +——————-+——+——-+——+ | 2022-10-01 | 2022 | 10 | 1 | | 2022-10-04 | 2022 | 10 | 4 | | 2022-09-23 | 2022 | 9 | 23 | | 2022-07-23 | 2022 | 7 | 23 | | 2022-10-01 | 2022 | 10 | 1 | +——————-+——+——-+——+ 5 rows in set (0.000 sec)

このSQL文は、YEAR、MONTH、DAYという3つの個別の関数を使用しています。それぞれの関数は、日付が格納されている列名を引数として受け取り、完全な日付から年、月、または日の一部だけを抽出します。これらの関数を使用することで、SQLクエリ内で個別の日付の断片にアクセスすることができます。

もうひとつの便利な日付操作関数はDATEDIFFです。この関数を使用すると、2つの日付間の日数を取得することができます。今度は、各本の導入日から現在日までの経過日数を確認してみてください。

以下のクエリを実行してください。

SELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;

以下の出力は画面に表示されます。

Output

+——————-+————+ | introduction_date | days_since | +——————-+————+ | 2022-10-01 | -30 | | 2022-10-04 | -27 | | 2022-09-23 | -38 | | 2022-07-23 | -100 | | 2022-10-01 | -30 | +——————-+————+ 5 rows in set (0.000 sec)

DATEDIFF関数は、2つの引数を受け取ります：開始日と終了日です。DATEDIFF関数は、これら2つの時点を分離する日数を計算します。結果は、終了日が前の場合には負の数になることがあります。この例では、最初の引数は在庫テーブル内の日付を保持しているintroduction_date列名です。2つ目の引数は、現在のシステム日付を表す別の関数であるCURRENT_DATEです。このクエリを実行すると、この2つの時点の間の日数が取得され、結果がdays_sinceという一時列に入れられます。

Note

注意：DATEDIFFは公式のSQL標準関数の一部ではありません。多くのデータベースはこの関数をサポートしていますが、構文は異なることがあります。この例ではMySQLのネイティブな構文に従っています。

MySQLに組み込まれたその他の日付操作関数には、日付や時間の間隔の加算と減算、異なる言語形式に対する日付の書式設定、日と月の名前の取得、新しい日付値の作成などがあります。SQLで日付を操作する方法については、「How To Work with Dates and Times in SQL」のチュートリアルで詳しく学ぶことができます。また、MySQLのドキュメンテーションの「Date and Time Functions」も参考にできます。

次のセクションでは、集計関数の使い方を学びます。

集計関数の使用

これまでのすべての例では、SQLの関数を使用して、1つの行内の個々の列値、つまり書店における1冊の本を表すものに変換や計算を適用しました。SQLは、複数の行をまたがった数値計算を実行する方法を提供し、データセット全体に関する集計情報を見つけるのに役立ちます。

SQLでの主要な集約関数には、以下のものがあります。

AVG for the average of the values the calculations are performed on.
COUNT for the number of values the calculations are performed on.
MAX for the maximum value.
MIN for the minimum value.
SUM for the sum of all values.

SELECTクエリには複数の集計関数を組み込むことができます。書店に掲載されている書籍の数、利用可能な書籍の最高価格、全カタログの平均価格を確認したいと思った場合、以下の文を実行してください。

SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;

このステートメントは以下の出力を返します。 (Kono sutētomento wa ika no shutsuryoku o kaeshimasu)

Output

+——-+———–+———–+ | count | max_price | avg_price | +——-+———–+———–+ | 5 | 42.13 | 25.106000 | +——-+———–+———–+ 1 row in set (0.001 sec)

上記のクエリは同時に3つの集計関数を使用しています。COUNT関数はクエリが参照する行数をカウントします。この例ではタイトルが引数として渡されていますが、列ごとにチェックされる行数が同じであれば、他の任意の列名も引数として使用することができます。MAX関数は価格列から最大値を計算します。ここでは、計算はその列の値に対して行われるため、列名が重要です。最後の関数はAVG関数で、価格列のすべての価格の平均を計算します。

集計関数をこのように使用すると、データベースは集計計算の値を表す仮の列を持った単一の行を返します。ソースの行は内部的に計算に使用されますが、クエリを通じて返されることはありません。この例では、全ての行を一括して考慮し、在庫テーブルから統計値を計算するために集計関数を使用しました。

SQLを使えば、テーブル内の行をグループに分けて、そのグループごとに集計値を計算することもできます。例えば、異なる著者の書籍の平均価格を計算して、どの著者が最も高価なタイトルを出版しているか調べることができます。このような計算のために行をグループ化する方法については、「How To Use GROUP BY and ORDER BY in SQL」チュートリアルで詳しく学ぶことができます。また、「How To Use Mathematical Expressions and Aggregate Functions in SQL」チュートリアルを参考にして、集計関数の使用方法についてさらに詳しく学ぶこともできます。

結論

このガイドに従うことで、SQL関数とは何か、そして関数を使用して数値、文字列、日付を操作する方法を学びました。数値の値を丸めるためにROUNDを使用し、複数の列を結合するためにCONCATを使用し、2つの時間点の間の日数を計算するためにDATEDIFFを使用しました。さらに、COUNT、SUM、AVGなどの集計関数を使用して複数の行にわたる集計を生成しました。

データの操作と計算の一部をデータベースエンジンにオフロードするために、関数を使用することができます。このチュートリアルでは、その目的のために関数の基本のみをカバーしています。データを頑強に取得し、分析するために、WHERE句とGROUP BYとORDER BYの使用方法について記述された条件付きクエリと関数の組み合わせを使用することができます。

ここに示されているコマンドは、ほとんどの関係データベースで機能するはずですが、すべてのSQLデータベースは独自の言語実装を使用していることに注意してください。各コマンドとその全てのオプションの詳細な説明については、DBMSの公式ドキュメントを参照する必要があります。

もしSQL言語やそれに関連する様々な概念についてもっと学びたい場合は、How To Use SQLシリーズの他のガイドをご覧いただくことをおすすめします。