SQLディープダイビング：マスタリングウィンドウ機能、一般的なテーブル式（CTE）、およびストアドプロシージャ

SQLは、ウィンドウ関数、一般的なテーブル式（CTE）、およびストアドプロシージャの3つの強力な関数を提供します。 1。ウィンドウ関数により、データセットを変更せずにグループ化と並べ替え操作が可能になります。 2.CTESは、複雑なクエリを簡素化するために一時的な結果セットを提供します。 3.ストアドプロシージャは、効率と一貫性を改善するために繰り返し実行できるSQLコードブロックを事前拡張します。

導入

データ駆動型の世界では、SQLは単なるクエリ言語ではなく、アートでもあります。今日、SQLの3つの強力な機能、ウィンドウ関数、一般的なテーブル式（CTE）、およびストアドプロシージャに飛び込みます。この記事を通じて、これらのツールを使用して複雑なデータの問題に対処し、SQLスキルを向上させ、データ分析と管理を安心させる方法を学びます。

基本的な知識のレビュー

SQLの魅力は、そのシンプルさと強力な機能にあります。ウィンドウ関数を使用すると、データセットを変更せずにデータをグループ化およびソートできます。 CTEは、複雑なクエリをより読みやすく管理するための一時的な結果セットの方法を提供します。ストアドプロシージャは、効率と一貫性を向上させるために繰り返し実行できるSQLコードブロックを事前に拡張します。

コアコンセプトまたは関数分析

ウィンドウ関数

ウィンドウ関数は、SQLの魔法のツールであり、データセットを変更せずにデータをグループ化およびソートできるようにします。彼らは、移動平均、ランキング、累積金額などを計算するのに役立つため、データ分析で非常に役立ちます。

選択します 
    従業員_id、
    サラリー、
    AVG_DEPARTMENT_SALARYとしてのAVG（給与）オーバー（部門ごとのパーティション）
    rank（）over（salary descによる注文）salary_rank
から 
    従業員;

この例では、各従業員の部門の平均給与を計算し、給与に基づいて従業員をランク付けします。ウィンドウ関数の力は、サブクエリやセルフボインを使用せずに同じクエリで複数の計算を実行できることです。

一般的なテーブル式（CTE）

CTEは、複雑なクエリの構造を簡素化し、コードをより読みやすく維持するためのSQLの一時的な結果セットです。 CTEは、自分自身を参照できるため、再帰的なクエリに特に役立ちます。

再帰的なemployee_hierarchyがas（
    employee_id、manager_id、0 as levelを選択します
    従業員から
    Manager_idはnullです
    ユニオンすべて
    E.EmployeE_ID、E.Manager_Id、eh.Level 1を選択します
    従業員からe
    emmanager_id = eh.employee_idにemployee_hierarchy ehに参加してください
））
select * from Employee_hierarchy;

この例では、CTEを使用して従業員の階層を構築します。 CTEの再帰的な性質により、複雑な自己接続クエリを書くことなく、従業員のツリー全体を簡単に通過できます。

ストアドプロシージャ

ストアドプロシージャは、繰り返し実行できるSQLコードのプリコンパイルブロックです。ネットワークトラフィックを短縮して時間を編集するため、複雑なロジックを実行したり、パフォーマンスを改善する必要がある場合に役立ちます。

手順を作成get_employee_details（in emp_id int）
始める
    選択します 
        E.Employee_id、
        e.first_name、
        e.last_name、
        D.DEPARTMENT_NAME
    から 
        従業員e
    参加する 
        department_id = d.department_idの部門
    どこ 
        e.Employee_id = emp_id;
終わり;

この例では、従業員の詳細を取得するためのストアドプロシージャを作成します。ストアドプロシージャの利点は、複雑なロジックをカプセル化し、複数回呼び出すことができ、コードの再利用性と一貫性を改善できることです。

使用の例

基本的な使用法

ウィンドウ関数の基本的な使用法は非常に簡単です。 OVER句を使用してウィンドウを定義し、さまざまな集計関数を使用して結果を計算できます。

選択します 
    Product_id、
    sale_date、
    sale_amount、
    running_totalとしてsum（sale_amount）over（product_id orderによるパーティション）
から 
    販売;

この例では、各製品の蓄積された販売を計算します。句PARTITION BYデータをグループ化し、句ORDER BY Windowsの順序を定義します。

CTEの基本的な使用も非常に簡単です。 WITHキーワードを使用してCTEを定義し、その後のクエリで参照できます。

 top_sellersを（
    Total_SalesとしてProduct_id、sum（sale_amount）を選択します
    販売から
    Group by Product_id
    Total_Sales DESCによる注文
    制限10
））
select * from top_sellers;

この例では、CTEを使用して、販売が最も高い10個の製品を見つけます。 CTEは、クエリ構造をより明確かつ簡単に管理します。

ストアドプロシージャの基本的な使用法も非常に簡単です。 CREATE PROCEDUREステートメントを使用してストアドプロシージャを定義し、 CALLステートメントを使用して呼び出すことができます。

 get_employee_details（1）に電話してください。

この例では、以前に定義されたストアドプロシージャを呼び出して、従業員ID 1で従業員の詳細を取得します。

高度な使用

ウィンドウ関数の高度な使用法には、 ROWSまたはRANGEを使用してウィンドウの範囲を定義し、 LAGとLEAD関数を使用してフロントラウのデータにアクセスすることが含まれます。

選択します 
    Product_id、
    sale_date、
    sale_amount、
    lag（sale_amount）over over（sale_dateによるproduct_id Orderによるパーティション）
    next_saleとしてed（sale_amount）over（product_id orderによるパーティション）
から 
    販売;

この例では、 LAGおよびLEAD関数を使用して、各製品の以前と次の販売を取得します。このような高度な使用法は、より複雑なデータ分析を実行するのに役立ちます。

CTEの高度な使用には、再帰CTEを使用して階層データを処理すること、複数のCTEを使用して複雑なクエリを簡素化することが含まれます。

再帰category_hierarchyを使用して（
    category_id、parent_category_id、0 as levelを選択します
    カテゴリから
    ここで、parent_category_idはnullです
    ユニオンすべて
    c.category_id、c.parent_category_id、ch.level 1を選択します
    カテゴリからc
    c.parent_category_id = ch.category_idにcategory_hierarchy chに参加します
）、、
product_categories as（
    P.Product_id、Ch.Category_Id、Ch.Levelを選択します
    製品からp
    category_hierarchy ch on p.category_id = ch.category_idに参加します
））
select * from product_categories;

この例では、再帰CTEを使用して製品カテゴリの階層を構築し、別のCTEを使用して製品をカテゴリに関連付けます。このような高度な使用法は、複雑な階層データに対処するのに役立ちます。

ストアドプロシージャの高度な使用には、複雑なビジネスロジックを実装するためのカーソルの使用、例外処理、およびトランザクション管理が含まれます。

手順を作成update_EmployeE_SALARY（EMP_ID INT、NEW_SALARY DECIMAL（10、2））
始める
    sqlexceptionの出口ハンドラーを宣言します
    始める
        ロールバック;
        辞任;
    終わり;

    トランザクションを開始します。
    従業員を更新します
    SET SALARY = NEW_SALARY
    employee_id = emp_id;
    専念;
終わり;

この例では、従業員の給与を更新するためのストアドプロシージャを作成します。ストアドプロシージャは、データの一貫性と整合性を確保するために、トランザクション管理と例外処理を使用します。

一般的なエラーとデバッグのヒント

ウィンドウ関数を使用する場合のよくある間違いは、 OVER句の使用を忘れることです。これにより、SQLエンジンがウィンドウ機能を正しく解析できなくなります。

 - エラー例選択 
    従業員_id、
    サラリー、
    AVG（給与） - からの条項がありません 
    従業員;

このエラーを回避するには、ウィンドウ関数を使用するときは、 OVER句が常に含まれていることを確認してください。

CTEを使用する場合、よくある間違いは、CTE内のすべての必要な列を定義するのを忘れることです。これにより、後続のクエリがCTEのデータを正しく参照できなくなります。

 -TOP_SELLERSを使用したエラー例（
    Product_id -Total_Sales列が販売から欠落しているかどうかを選択します
    Group by Product_id
    Total_Sales DESCによる注文
    制限10
））
select * from top_sellers;

このエラーを回避するには、CTEを定義するときに必要なすべての列を含めるようにしてください。

ストアドプロシージャを使用する際のよくある間違いは、例外を処理するのを忘れることです。これにより、エラーが発生したときにトランザクションを正しくロールバックできない場合があります。

 - エラー例CREATE PROSTURE update_EmployeE_SALARY（emp_id int、in new_salary Decimal（10、2））
始める
    従業員を更新します
    SET SALARY = NEW_SALARY
    employee_id = emp_id;
終わり;

このエラーを回避するには、ストアドプロシージャに例外処理とトランザクション管理を含めるようにしてください。

パフォーマンスの最適化とベストプラクティス

ウィンドウ関数を使用する場合、パフォーマンスの最適化の重要なポイントは、適切なウィンドウフレームを選択することです。 ROWSまたはRANGE条項を使用すると、ウィンドウ関数の計算量を減らすことができるため、クエリパフォーマンスを大幅に改善できます。

 - 最適化の例選択 
    Product_id、
    sale_date、
    sale_amount、
    running_totalとして、sum（sale_amount）over（sale_id oder
から 
    販売;

この例では、 ROWS句を使用して、クエリのパフォーマンスを改善できるウィンドウフレームワークを定義します。

CTEを使用する場合、パフォーマンスの最適化の重要なポイントは、CTEで複雑な計算を使用しないことです。 CTEは、複雑な計算が含まれている場合、クエリパフォーマンスに影響を与える可能性のある一時的な結果セットです。

 -Sales_Summaryを使用した最適化の例（
    Total_SalesとしてProduct_id、sum（sale_amount）を選択します
    販売から
    Group by Product_id
））
SELES * FROM SALE_SUMMARY;

この例では、クエリパフォーマンスを改善するために、CTEの外側に複雑な計算を行います。

ストアドプロシージャを使用する場合、パフォーマンスの最適化の重要なポイントは、ストアドプロシージャでカーソルを使用しないようにすることです。カーソルは、ラインごとにデータを処理する必要があるため、パフォーマンスの劣化を引き起こす可能性があります。

 - 最適化の例CREATE PROSTURE update_Employee_Salaries（）
始める
    従業員を更新します
    SET SALARY = SALARY * 1.1;
終わり;

この例では、カーソルの使用を避け、バッチアップデート操作を使用してパフォーマンスを向上させます。

SQLコードを作成する際には、ベストプラクティスには、意味のあるエイリアスの使用、コードの注釈、コードの読み取り可能かつ保守可能な状態を維持することが含まれます。

 - ベストプラクティスの例選択 
    employee_id as emp_id、 - 意味のあるエイリアスを使用e.first_name、 - コメントコードe.last_name、
    D.DEPARTMENT_NAME-コードの読みやすさと保守性を保持します 
    従業員e
参加する 
    e.department_id = d.department_idの部門d;

これらのベストプラクティスに従うことにより、より効率的で保守可能なSQLコードを作成できます。

SQLを探索する過程で、ウィンドウ関数、CTE、およびストアドプロシージャの基本的および高度な使用法を習得しただけでなく、一般的なエラーを回避し、パフォーマンスを最適化する方法を学びました。この記事が、これらの強力なSQL機能をよりよく理解し、適用し、データ分析と管理においてより大きな成功を収めるのに役立つことを願っています。

以上がSQLディープダイビング：マスタリングウィンドウ機能、一般的なテーブル式（CTE）、およびストアドプロシージャの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。