LuckyTemplates ユーザー向けの一般的な SQL テーブル式

このブログでは、 CTEとも呼ばれるいくつかの一般的なテーブル式について説明します。複雑な問題が発生した場合は、これらのSQL テーブル式 を使用すると解決できます。

CTEを使用すると、あらゆるタスクを小さく管理しやすいクエリに分割して、複雑な問題を解決できます。

次に、 CTE を使用してコードのトラブルシューティング作業をどのように効率化できるかを見てみましょう。

目次

• 共通の SQL テーブル式を作成するための構文 
• 複数の共通 SQL テーブル式 
  • 複数の CTE を使用して複雑なコードを解決する
  • Case ステートメントでの CTE の使用
• 結論

共通の SQL テーブル式を作成するための構文 

まず、「 WITH」と入力し、その後にCTE Expression 名を入力し、その後に「AS」を入力する必要があります。

その後、括弧内にクエリを入力できます。これは、仮想テーブルとして機能する指定された式名に保存されます。

次に、設定した式名で作成した仮想テーブルを使用してSELECT ステートメントを追加します。

この例では、式名に「 USACusts」を使用します。

クエリには、このコードを使用します。 

このクエリは仮想テーブルを作成します。顧客の所属国が「米国」である顧客 ID ごとにグループ化された合計金額が表示されます。データはSales.Customersテーブルから抽出されます。

custidとAgg_Amountという 2 つの列が表示されます。USACustsテーブルは物理テーブルではなく 、単なる仮想テーブルであることに注意してください。つまり、データベースには表示されなくなります。

クエリの後に、SELECT ステートメントを追加できます。

このステートメントは、 USACustsテーブルから1000を超えるすべてのAgg_Amountを選択します。

複数の共通 SQL テーブル式 

複数のCTE は、コード内の複雑なエラーをより小さなチャンクに分割することで解決するのに役立ちます。さらに、必要な数のCTEを定義できます。以下は複数のCTEの例です。

この例では、最初のCTEはC1です。そこから、YEAR(orderdate)を選択し、 Sales.Orders テーブルからorderyearとcustidという 2 つの列を作成しました。

C2という名前の2 番目のCTEでは、 orderyearを選択し、 COUNTコマンドを使用してcustidの数を収集しました。次に、 C1からnumcustsという名前を付けました。2015より大きいorderyearのみを選択するためのWHERE条件もC2に追加されました。次に、それらをorderyearごとにグループ化しました。

その後、C2からorderyearとnumcustsを選択しました。次に、500を超える顧客の数のみを選択するWHERE条件を追加しました。

要約すると、このコードは、注文年が2015 年より大きい顧客を選択し、注文年の条件に基づいて500を超える顧客の数のみを選択するために使用されます。

複数の CTE を使用して複雑なコードを解決する

次に、大規模なデータセットのトラブルシューティングにおけるCTEの使用法をさらに強調するために、さらに例を示します。複数のCTEの別の例を次に示します。

CTEには任意の名前を付けることができますが、このブログでは、例で CTE をどのように使用するかを簡単に識別して理解できるように、C1、C2、C3 などを使用しています。

例に戻ると、Sales.SalesOrderDetailsテーブルから注文数量が5を超える販売のみを選択するC1を作成しました。

次にC2では、C1 に保存したデータを利用してProduct.IDに基づいて製品を結合し、製品名(p.Name)と製品の色(p.Color)でグループ化しました。

C1で条件を宣言したため、注文数量(OrderQty)が5より大きい注文についてのみ、製品名と製品の色をC2にグループ化します。

その後、コマンドを使用して、注文数量が1000を超えるすべてのデータを C2 から選択します。

トラブルシューティングの目的で、作成した各CTE を選択するだけで、正しいデータが表示されているかどうかを確認できます。以下の例を参照してください。

C1の場合、 Sales.SalesOrderDetailテーブルの注文数量が5を超えるすべてのデータが表示されます。

次に、C2には、 C1からの製品名と色でグループ化された注文数量の出力が表示されます。C2では、 orderqty列が表示されますが、これはC1から生成した単なる計算列であるため、実際にはC2にはありません。ここから、orderqty列で任意の条件の使用を開始し、トラブルシューティングを実行できます。

これで、複数のCTE が複雑なコードのエラーをCTEに保存することで解決し、実際のコードを損傷することなくトラブルシューティングを開始できることがわかりました。

Case ステートメントでの CTE の使用

同じコンテキストの別の例を使用して、複雑なコードのトラブルシューティングで複数のCTEを利用する方法を示してみましょう。 

この場合、 CASEステートメントを使用して、C2からのorderqtyの集計量に基づく結果を含むProduct Category列を作成します。

前述したように、最初のCTEおよび製品カテゴリにはorderqty がありませんでしたが、中間テーブルを利用してさまざまな計算を実行しました。

次の例では、前の例で作成した製品カテゴリ に基づいて集計金額を作成します。ただし、これを行うには、 Product カテゴリのCASEステートメントを3 番目のCTE ( C3 )に配置します。

先に進む前に、 C3の下に「 SELECT * FROM C3」を追加して、C3の出力を確認してみましょう。

C3では、ProductName、Color、orderqty、およびProduct Category列が表示されます。ここから、Product categoryに基づいて集計を開始できます。C3の下で次のコマンドを使用してこれを実行してみましょう。

以下の例は、上記のコマンドを使用して集計金額を作成した後の結果です。

当初、 Product category 列とorderqty列はありませんでしたが、複数のCTEを利用することで、これらの列に基づいてサンプル出力を生成できるようになりました。

この時点で、 CTEの助けを借りてコードをより効率的に操作できることがわかります。

LuckyTemplates ユーザーの SQL 一時テーブルとビュー
IN、NOT IN、LIKE、NOT LIKE を使用した
SQL のフィルター OFFSET と FETCH を使用した SQL データの抽出

結論

このチュートリアルでは、共通 SQL テーブル式を作成する際の適切な構文と、データの小さな部分を分離して特定のCTE内に格納することで複雑なコードのトラブルシューティングに複数のCTEを利用する方法を学びました。

これを学ぶことで、コードをより効率的にデバッグできるようになります。CTEには任意の名前を使用できますが、作成しない限りCTE を使用したり呼び出したりすることはできないことに注意してください。CTEに慣れると、自分はSQLの中級ユーザーであると考えることができます。

このトピックやその他の関連コンテンツについて詳しく知りたい場合は、以下の関連リンクのリストをご覧ください。

ではごきげんよう、

ハーフィズ