Sqlの楽しさ：Postgresの再帰Cte

共通テーブル式（Cte）は、SQL内の強力な構成要素です。 日々の会話では、CteがWITH句と呼ばれることがあります。 Cteは、そのクエリの実行中にのみ実体化され、そのクエリの外部には存在しないビューに似ていると考えることができます。 CTEは、大規模なSQLクエリをより読みやすくするための非常に便利な構成要素です。 しかし、それらは再帰的に使用することもでき、plpgsqlやplv8のような手続き型言語にドロップダウンすることなく、非常に複雑なクエリを作成できます。

再帰Cteは、何らかの条件が満たされるまで自分自身を呼び出すことができます。 次に、再帰CTEをもう少し詳しく分析して、それを使用できるものを見てみましょう:

WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; 

上記が実行されると、次の結果が得られます:

 n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) 

上記では、generate_seriesでこれを簡単に行うこともできます。 しかし、私たちに固執すれば、generate_seriesでは不可能な、私たちができるより複雑なことがわかります。 まず、それがどのように機能するかを詳しく見てみましょう。

あなたが気づく最初の部分はWITH RECURSIVEです。 これは、CTEが再帰的に自分自身を呼び出すことができることをPostgresに伝えます。 あなたが気づく次の部分は、それにいくつかのパラメータを取ることです。 この場合、(n)は、必要に応じて複数を取ることもできます。 CTEにさらに移動すると、実行される最初のクエリSELECT 10があり、最初の値が生成されます。 第二の部分は、すべての楽しみが始まる場所です。 UNION ALLは、ループから生成されたすべてのレコードを返すことを指定します。 次に、SELECT n+10 FROM tens WHERE n+10<= 100は、条件が満たされるまで作成されたtensCTEを呼び出し続けます。 だから、これらは基本ですが、興味深い質問です：再帰CTEを使用するのはいつですか？ データにツリー構造または階層構造がある場合、再帰Cteを使用すると、すべてのデータをロードしてコード内でループを実行するよりもはるかに簡単になります。 Eコマースやショッピングカテゴリを扱うアプリケーションでは、再帰的なCteが大きな助けになります。 例を挙げて、Postgresの再帰Cteの利点をもう少し具体的にしてみましょう。 まず、従業員のテーブルを作成し、次に従業員の例をいくつかロードします。 （はい、このリストのCitus Dataで私たちのチームからの名前のいくつかが表示されます。 彼らは頭に浮かんだ最初の名前でした。)

CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); 

今、私は私に会社内の特定の組織にロールアップするすべてのレポートを与えるクエリを書くつもりです。 この場合、私は自分自身と私のすべてのレポートを、各人のマネージャー idと一緒に取得します:

WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) 

次にデータ全体で再帰的な計算を行う必要がある場合は、すべてのデータをアプリケーションにロードするのではなく、SQLで直接行うことを検討してくださ さらに読むために、これらの有用なリソースのいくつかを見てみることを検討してください:

• PostgreSQL docs on Cte
• CTEでtraveling salesmanの問題を解決する
• すべての子を持つツリーを取得する

注として、現時点のCteはPostgreSQLの最適化フェンスですが、将来的には変化することが期待されています。 共通テーブル式は、レポート作成に非常に便利なツールです。 Cteの可読性がパフォーマンスへの影響を上回る場合がありますが、それらを使用する場合のトレードオフを考慮してください

• sqlでの楽しい
• Postgres