Artem Yevtushenko｜Builder Education｜7 min read｜April 24, 2019
SQLは、最も強力なデータ処理ツールの1つです。 SQL Superstarでは、この汎用性の高い言語を最大限に活用し、美しく効果的なクエリを作成するための実用的なアドバイスをお届けします。
データウェアハウスやレポート用の別個の分析データベースを持たずに運用している場合、本番データベースが最新の最新データの唯一のソースとなる可能性が高いです。 本番データベースへのクエリは、最適化が重要です。 非効率的なクエリは、本番データベースのリソースを消費し、クエリにエラーが含まれている場合は、パフォーマンスの低下や他のユーザーへのサービスの低下を引き起こします。
最初にビジネス要件を定義する
BI のビジネス要件を定義するためのベスト プラクティスを別の場所で取り上げました。
- 関連するステークホルダーを特定する。 必要なすべての関係者が、クエリを開発するための議論に参加していることを確認します。 本番のデータベースにクエリを実行するときは、DBA チームが含まれていることを確認してください。 クエリに明確でユニークな目的を持たせる。 探索的なレポートや重複したレポートのために本番データベースに負担をかけることは、不必要なリスクです。 対象者を特定することで、レポートの機能と範囲を定義します。 これにより、適切なレベルの詳細を持つテーブルにクエリを集中させることができます。
- 優れた質問をする。 5つのWに従います。 誰が？ 何を？ どこで？ いつ？
- 非常に具体的な要件を書き、利害関係者に確認します。 本番データベースのパフォーマンスは、不明確または曖昧な要件を持つにはあまりにも重要です。
SELECT fields instead of using SELECT *
探索的なクエリを実行するとき、多くの SQL 開発者はテーブルから利用可能なすべてのデータをクエリする略語として SELECT * (select all と読みます) を使用します。
SELECT ステートメントを使用すると、ビジネス要件を満たすために必要なデータのみを照会するようにデータベースを誘導することができます。
非効率:
SELECT *
FROM Customers
このクエリは、電話番号、アクティビティの日付、営業や顧客サービスからのメモなど、customer テーブルに保存されている他のデータを取り込む可能性があります。
Efficient:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
このクエリははるかにすっきりしており、郵送先の必要な情報のみを引き出します。
すべてのテーブルとフィールド名のインデックスを保持するには、INFORMATION_SCHEMA や ALL_TAB_COLUMNS などのシステム テーブルからクエリを実行します (MS SQL Server の場合は、こちらをご覧ください)。
今すぐSQLを始めましょう！
Avoid SELECT DISTINCT
SELECT DISTINCTは、クエリから重複したデータを削除する便利な方法です。 SELECT DISTINCT は、クエリ内のすべてのフィールドをグループ化して、異なる結果を作成することで機能します。 しかし、この目的を達成するためには、大量の処理能力が必要となります。 また、データが不正確になるほどグループ化されてしまうこともあります。
非効率で不正確：
SELECT DISTINCT FirstName, LastName, State
FROM Customers
このクエリでは、同じ州に住む複数の人が同じ名字と名前を持つことを考慮していません。 David Smith や Diane Johnson などの人気のある名前がまとめられてしまい、不正確なレコード数になってしまいます。
Efficient and accurate:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
フィールドを追加することで、SELECT DISTINCTを使用せずに重複しないレコードが返されました。 データベースはフィールドをグループ化する必要がなく、レコード数も正確になりました。
Sisenseの動作をご覧ください。
Explore Dashboard
INNER JOIN (not WHERE)で結合を作成する
SQL開発者の中には、以下のようにWHERE句で結合を作ることを好む人もいます。
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
このタイプの結合は、Cartesian ProductやCROSS JOINとも呼ばれるCartesian Joinを作成します。
Cartesian Joinでは、変数のすべての可能な組み合わせが作成されます。 この例では、1,000人の顧客と1,000人の総売上高があった場合、クエリはまず1,000,000件の結果を生成し、次にCustomerIDが正しく結合されている1,000件のレコードをフィルタリングします。 これはデータベースのリソースを非効率的に使用していることになり、データベースは必要な作業の100倍を行っていることになります。 Cartesian Joinは大規模なデータベースでは特に問題となります。なぜなら、2つの大きなテーブルをCartesian Joinすると、何十億、何兆もの結果が作成される可能性があるからです。
Cartesian Joinの作成を防ぐには、代わりにINNER JOINを使用します。
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
データベースは、CustomerIDが等しい1,000件の希望するレコードのみを生成します。
一部のDBMSシステムは、WHERE結合を認識し、代わりにINNER JOINとして自動的に実行することができます。 そのようなDBMSシステムでは、WHERE結合とINNER JOINの間にパフォーマンスの違いはありません。 しかし、INNER JOINはすべてのDBMSシステムで認識されます。
フィルタの定義にはHAVINGではなくWHEREを使用する
効率的なクエリの目的は、データベースから必要なレコードだけを取り出すことです。 SQL Order of Operationsによれば、HAVING文はWHERE文の後に計算されます。 条件に基づいてクエリをフィルタリングする意図がある場合は、WHERE文の方が効率的です。
例えば、2016年の1年間に200件の販売が行われたと仮定し、2016年の顧客ごとの販売数をクエリする場合を考えてみましょう。
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
このクエリは、Salesテーブルから1,000件の売上レコードを引き出し、次に2016年に発生した200件のレコードをフィルタリングし、最後にデータセット内のレコードをカウントします。
それに比べて、WHERE 句は取得するレコードの数を制限します：
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
このクエリは、2016 年に作成された 200 個のレコードを取得し、その後、データセット内のレコードを数えます。 HAVING句の最初のステップが完全になくなっています。
HAVINGは、集約されたフィールドでフィルタリングするときにのみ使用する必要があります。
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5
>> 無料のスターターキットです。 無料のSQLスターターキットで今すぐ始めましょう
ワイルドカードはフレーズの最後にのみ使用する
都市名や名前などのプレーンテキストデータを検索する場合、ワイルドカードを使用すると最も広い範囲を検索することができます。
先頭にワイルドカードを使用した場合、特に末尾のワイルドカードと組み合わせた場合、データベースは選択されたフィールド内のどこかに一致するものがないか、すべてのレコードを検索することになります。
「Char」で始まる都市を検索するクエリを考えてみましょう：
SELECT City FROM Customers
WHERE City LIKE ‘%Char%’
このクエリでは、Charleston、Charlotte、Charltonという期待通りの結果が得られます。
SELECT City FROM Customers
WHERE City LIKE ‘Char%’
このクエリは、Charleston、Charlotte、Charltonという予想される結果のみを引き出します。
Use LIMIT to sample query results
クエリを初めて実行する前に、LIMIT ステートメントを使用して、結果が望ましい意味のあるものになることを確認します。 LIMITステートメントは、指定されたレコード数のみを返します。
上述の2016年の販売クエリでは、10レコードの制限を検討します：
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10
使用可能なデータセットがあるかどうかは、サンプルで確認できます。
ピーク時以外にクエリを実行する
分析クエリが本番データベースに与える影響を最小限にするために、ピーク時以外にクエリを実行するようにスケジュールすることについて、DBAに相談してください。 クエリを実行するのは、同時接続ユーザー数が最も少ない時間帯、つまり一般的には真夜中（午前3～5時）です。
以下の条件を多く持つクエリほど、夜間に実行される可能性が高いと考えられます。
- 大きなテーブルからの選択（>1,000,000レコード）
- カルテシアン結合またはCROSS JOIN
- ループステートメント
- SELECT DISTINCTステートメント
- ネストされたサブクエリ
- 長いテキストまたはメモのフィールドでのワイルドカード検索li フィールドのワイルドカード検索
- 複数スキーマのクエリ
自信を持ってクエリを実行する
これらのヒント（さらに他のSQLのヒントやコツ）を念頭に置くことで、効率的で美しいクエリを構築することができるはずです。 効率的で美しいクエリを構築できるはずです。これらのクエリはスムーズに実行され、チームが必要とする画期的な洞察をもたらします。
無料のSQLスターターキットはこちら：