概要概要

SQLとは何か、SQLを何に使用できるかを学び、次にデータベースからデータを選択してフィルタリングするための基本的なクエリの作成を開始します。

注：今日は実際にはSQLデータベースを使用しません。Pythonを使用してデータフレームを作成し、次にpandasqlというライブラリを使用してクエリを実行します。Pythonに慣れていない場合でも、チュートリアルに従い、SQLコマンドとその出力に注意を払うことをお勧めします。

SQLとは何ですか？

データはいたるところにあり、その多くは毎分作成されています。以前はこのデータを巨大なファイリングキャビネットの紙に保存していましたが、現在はデータベースと呼ばれるものにデジタルで保存しています。では、このデジタルデータベースから必要なデータを簡単に取得するにはどうすればよいでしょうか。それがSQLの目的です！SQL（Structured Query Language）は、データベースとの通信に使用する言語です。データベースにデータをプル、編集、または追加する場合は、SQLを使用してそれを行うことができます。データベースは、さまざまなアーキテクチャ（巨大なデータレイク、単純な1テーブルスキーマ）で作成でき、さまざまな言語（C ++、Java）で記述できますが、SQLは、誰でもユニバーサル構文を使用してこのデータにアクセスできる共通の基盤です。

SQLを使用してデータを編集し、データベースに追加できることはわかっていますが、SQLは、大量のレコードをフィルタリングして、探している正確なエントリのみを返す機能があるため、既存のデータをプルする場合に最も役立ちます。SQLメソッドはほぼ無限にあり、それぞれに固有の構文とコマンド内の必要な場所があります。これについては次の詳細。

構文とベストプラクティス

SQLコマンドを使用してデータベースからデータをプルすることをクエリと呼びます。SQLには、従わないとコードが破損する可能性のある一般的な構文規則がいくつかあります。クエリは非常に長く、非常に複雑になることがあるため、クエリがきちんと読みやすくなるように、何年にもわたっていくつかのベストプラクティスが開発されてきました。SQL構文の基本と、SQLクエリを作成するときに従う必要のあるいくつかの一般的なベストプラクティスルールについて見ていきましょう。

store_salesこれは、というデータベースからすべての行と列を返すだけの非常に基本的なSQLコマンドです（これはSQLで記述できる最も単純なクエリの1つであり、クエリがこれほど短くなることはめったにありません）。

''' 
SELECT * FROM store_sales;
'''

• ’’’…’’’-SQLクエリは常に文字列であり、トリプルクォートを使用することで、ほとんどのクエリが複数行を必要とするという事実を回避できます。
• SELECT —これは、データベースからデータをプルするために使用されるコマンドであり、常にデータプルのためにアクセスする列が示され、常にクエリの最初のコマンドが続きます。
• * —これは「すべて」を表すSQLであり、すべての行とすべての列を選択します（注：これは、アクセスする列によっては別のものになる可能性があります）
• FROM —これは、使用するデータソースを指定する方法です。これは常にSELECTコマンドの前にあり、後に続くものはすべてコマンドのターゲットになります。
• store_sales —これは架空のデータベースの名前です。使用しているデータベースによって異なります。
• ; —これは、コマンドが完了したことをデータベースに通知します。常にクエリを次のように終了します。 ;

前述のように、SQLの各コマンドには、クエリ内での独自の構文と配置があります。構文とユースケースをすばやく確認するために私が本当に気に入っているリファレンスシートは次のとおりです：https：//www.w3schools.com/sql/sql_ref_keywords.asp。メソッド名をクリックするだけで、いくつかのサンプルコマンドなど、メソッド名の使用方法に関する詳細情報を取得できます。

データ選択の基本

この記事の残りの部分では、データを選択してフィルタリングする基本的な方法に焦点を当てます。この架空のシナリオを回避します。私たちは20人の子供たちのクラスを持つ学校の先生です。四半期の終わりです。10週間の学期の毎週の終わりに、学生は前の週の教材のテストを受けます。これらのテストのスコアは、学校のシステムのSQLデータベースに保存されているため、生徒とそのテストのスコアに関する情報を取得するためのクエリをいくつか作成します。

前述のように、Pythonを使用して使用するデータ（pandas DataFrames）を作成し、pandasqlそれをクエリするために呼び出されるライブラリを使用します。pandasqlSQLiteを使用します。これは、アプリケーションのローカル/クライアントストレージ用のSQLのかなり一般的なフレーバーです。

インポートとデータ作成

クエリに飛び込みたいだけの場合は、WHEREセクションに進んでください。Pythonに慣れていない場合は、そうすることをお勧めします。

ランダムテストスコアデータを生成するために使用したコードは次のとおりです。これについては詳しく説明しません。何が起こっているのかを理解するのに役立つコメントが全体にいくつかあります。最終的なパンダのDataFrameはscores、SQLクエリを作成する変数に格納されます。

コードの一番下までスクロールすると、データの最初の5行が印刷されているのがわかります。

どこ

このWHERE引数は、基本フィルタリング引数として使用されます。それは常にあなたのFROM …宣言の後に来て、あなたがフィルタリングしたい条件が常に続きます。条件は数値データまたは文字列データのいずれかであり、列に両方のタイプのデータを含めることはできません。WHERE演算子は他のプログラミング言語とほとんど同じように機能しますが、簡単な概要は次のとおりです。

さて、私たち教師が、最初の週のテストでクラスの誰がうまくいかなかったかを知りたいとしましょう。そのためのクエリがどのように見えるかを見てみましょう（WHERE句を使用）。「それほど熱くなかった」を77％未満のスコアとして定量化します。

さて、ここで何が起こったのですか？最初に気付くのは、のSELECT name, test_1_score代わりにを使用したことですSELECT *。これらは両方ともscoresテーブルの列であり、SELECTステートメントでそれらを示すことにより、これら2つの列の情報のみを返すと言っています。

次に、を選択したいと言っていますFROM scores。ではpandasql、作業環境内の現在のpandasDataFrameオブジェクトはアクセス可能なSQLテーブルとして扱われます。それが事実でscoresあり、私たちのデータソースであり、FROM条項でそのように示しています。

また、レコードだけが必要だとも言いましたWHERE test_1_score <77。データは数値であるため、数学演算子を使用して、すべての値を_blank_未満にする必要があると言うことができます。

別のものを試してみましょう。今回は、毎週の最終テスト（テスト10）でどの生徒が特にうまくいったか（90％以上）を確認したいと思います。

驚くばかり！私たちは、単に私たちはの値にフィルタリングを適用したかったことが示されたtest_10_scoreとは反対にtest_1_score、その後に当社オペレータサインをひっくり返し「よりも大きいです。」

2つの値の間

資料を明確に理解しているが、本来あるべきほど勉強していなかった生徒を見つけたいとしたらどうでしょうか。スコアが80％より高く90％未満の学生に起こったことを想定します。

クエリは次のようになります。ここでは、演算子を単に「スタック」します。

しかし、ご覧のとおり、80〜90の範囲外のあらゆる種類の値を取得しています。

これは、代わりに使用する必要のある組み込みのSQL演算子があるためです。そのメソッドの名前を推測できますか？あなたはそれを推測しました、それはBETWEENです。BETWEEN技術的には論理演算子であるため、常にWHEREコマンドの後に続きます。AND数値の範囲を示す2つの値（目的の範囲の開始と終了を区切ります）を要求します。ここでの注意： SQLBETWEENは包括的です（と同等80<=test_10_score<=90）。

これで、80〜90％のスコアを含む学生の名前とスコアのみを含む、はるかに小さなリストがあることがわかります。

および/または

これまで、WHEREステートメントで指定する条件は1つだけでした。実際には、必要な数の条件を設定できるため、SQLは複雑なフィルタリングに非常に役立ちます。WHEREコマンドに続いて複数の状態を示すには、次の2つの方法があります。

• AND —両方/すべての条件が真であるエントリを返したい場合に使用されます（たとえば、テスト9および10で90％を超えるスコアの学生が必要です）
• OR —いずれかの条件が真であるエントリを返したい場合に使用され、ORチェーン内の任意の数の条件に一致するすべてのレコードを返します

すごい！さて、学期の第1週と第2週が同じ資料を扱っていたとしましょう。そして、その資料の少なくともかなりの部分を理解した学生のリストを取得したいと思います。このために、テスト1またはテスト2のいずれかで85％を超えるスコアを獲得した学生の名前を返します。これは、ORオペレーターに最適です。

さて、かっこいい。ほとんどの場合、私たちのクラスは最初の2週間の資料のかなりの部分を理解したようです

素晴らしいところANDとOR、あなたが希望として一緒にそれらの多くとしてチェーンをできることです。'nマッチをミックスすることもできます！

たとえば、最終テスト（テスト10）が1〜9週目のすべての資料の最終テストである場合、学期の初めに成績が悪く、学期の終わり。1週目または2週目にテストでうまくいかず、学期中に勉強する時間がなく、最終テスト（テスト10）でもうまくいかなかった学生を見ていきます。 ）。次のSQLクエリを見て、それが何をしているのかがわかるかどうかを確認してください。

今回は、テスト1またはテスト2のいずれかで75％未満であり、テスト10でも75％未満である学生の名前のみを返したいと述べました。どちらのテストをOR気にしないため、最初に使用しました。彼らがどちらかで悪いことをしたかどうかだけを気にしました。次にAND、最後の条件が真であることが絶対に必要であることを示していました。エントリが最初の2つの条件のいずれかと最後の条件に一致しなかった場合、それらは返品に含まれません。

ORDER BY

長いクエリの戻り値をふるいにかけることが依然として難しい場合があり、出力内の行をさらに整理してフィルタリングすると役立つ場合があります。ORDER BYこれを行うための非常に一般的で便利な方法であり、ほとんどすべてのデータ型（文字列、時系列、数値など）で機能します。ORDER BYまた、さまざまな修飾子は常に末尾近くに配置し、常に条件節の後に配置する必要があります（例WHERE）。

過去10週間の各学生のテストスコアの全体的な平均である新しい列をテーブルに作成します。この列をと呼びますtest_avg。

これで、生徒の平均で保存された順序付きリストを取得できます。テストの点数。そのクエリは次のようになりますが、出力を見てください。

完全にソートされたリストを取得しますが、そのリストは昇順です。これはSQLのデフォルト設定ですORDER BY。私たちは、リストを取得したい場合にソート降順、SQLは非常に簡単にそれを作るために、DESC列インジケーターの後にタグを追加するだけです。

これで、リターンテーブルの先頭に最高値が表示されます。

複数の列で返品を注文することもできます。これを行うには、ORDER BY引数の後に列の値をコンマで区切ります。例えば、次のクエリに戻りname、test_avgおよびtest_10_score並び順test_10_scoreまず、その後でソートtest_avg。微妙ですが、ソートの2番目のレイヤーがどこで発生しているかがわかるかどうかを確認してください。

よく見ると（行1と2、3と4、および12と13を確認してください）、この2層の順序付けが実際に行われていることがわかります。

この二重順序付けは、この状況では必ずしも有用ではありませんが、次の例を取り上げます。自動車販売店で働いていて、修理作業のために特定のメーカーとモデルの特定の販売を見つけるために何万ものレコードを分類する必要があると想像してみてください。現在、2つの値（たとえばyear、その後car_model）による並べ替えが非常に必要になり、SQLを使用すると簡単に並べ替えることができます。

GROUP BY

ここで説明する最後の主要な方法は、そのGROUP BY方法です。これは、指定された列で行をグループ化するという、まさにそのように聞こえますが、aの戻り値GROUP BYは、過去に見たクエリとは少し異なります。GROUP BY一般的な値に基づいて行を集約し、通常、カテゴリ変数を含む列に使用されます。と同様にORDER BY、常にクエリの最後に配置されます。句GROUP BYと同じクエリで使用するのが一般的です。ORDER BYこの場合、常にGROUP BY最後に配置します。

教師/テストのスコアの例から離れて、この関数を使用して、生徒を文字の成績でグループ化することができます。そして、それが私たちがやろうとしていることです。まず、テーブルに新しい列を作成しましょう。この列gradeを呼び出して、次の平均テストスコア条件に基づいてレターグレードを入力します。

右端を見ると、新しい列が表示されます。

これで、かなりクールなクエリを作成できます。次のクエリを見て、何が起こっているのかがわかるかどうかを確認してください（まだ説明していない方法があります）。

クエリでおそらく最初に気付いたのはCOUNT(name)引数です。それは私たちのSELECT条項にあるので、私たちがテーブルから引き出しているのはある種の情報であることがわかります。COUNT()はSQLの非常に便利な組み込みメソッドであり、指定した方法でグループ化された、戻り値のエントリ数を表す単一の値を返します。各グループの名前、別名学生の数を数えることを選択します。グループはGROUP BY grade引数によって指定されます。引数は、多くの行を、列の現在のすべての値を表すいくつかの行に集約しgradeます。

もう1つの非常に便利な組み込みSQLメソッドはAVG()です。と同じ行に沿って機能し、COUNT()指定した方法でグループ化された、指定された列の平均を表す単一の値を返します。次のクエリでは、各文字の成績グループのすべての生徒の平均テストスコアを返します。

COUNT()とによって作成された新しい列に注目してくださいAVG()。それらは直感的に名前が付けられており、列の内容を正確に説明していますが、少し乱雑に見えます。SQLAS引数を使用してこれを修正できます。これは、ツールベルトに含めると非常に便利な引数です。特に、さまざまなカテゴリを多数集約し始める場合は、使い慣れたカスタマイズ可能な命名規則を使用すると非常に役立ちます。

追加のヘルパー：ASおよびAVG（）

次のクエリは、ASステートメントを使用して、名前が変更された単一の値を返しますtest_1_avg。ご想像のとおり、この値はテスト1のスコアのクラス全体の平均になります。

出力ですぐに結果を確認できtest_1_avgます。これは、単一の値を保持する新しくダビングされた列です。

AS 名前を変更するオブジェクトの直後に配置され、クエリ内で列または新しい値を導入する場所であればどこでも使用できます。

結論

SQLクラッシュコースの今回の記事ではこれで終わりです。このシリーズの次の記事では、SQLテーブルとデータベース（テーブルの格納に使用されるSQLアーキテクチャ）を作成する方法について説明します。

学んだことを要約すると、最初に、SQL（Structured Query Language）がデータベースとの通信に使用する言語であり、データベースからデータを追加、削除、編集、および取得できることを学びました。次に、データを取得するためのいくつかの方法を学び、SQLのベストプラクティスと構文について学びました。引数にはクエリ内で必要な特定の場所があること、クエリの最後にセミコロンが必要であること、データの行と列を集計するための組み込みメソッドがたくさんあることを学びました。

読んでくれてありがとう、次回はチューニングして、そして幸せなSQLing！