MySQL汎用テーブル式(CTE)のメソッドサブクエリによる効率的な使用

テキストアドレス
MySQLのCTEには、非再帰的な方法と再帰的な方法の2つがあります.
なぜCTEを使う必要があるのですか?
同じクエリで派生テーブルを2回参照することはできません.したがって、派生テーブル・クエリは2回以上計算され、パフォーマンスに深刻な問題があることを示します.CTEを使用すると、サブクエリは1回のみ計算されます.
CTE 非再帰方式
派生テーブルの使用は、通常、次のようになります.
SELECT... FROM (subquery) AS derived, t1 ...
subqueryというサブクエリはFROM句に置かれる.
CTEの構文は次のとおりです.
SELECT... WITH derived AS (subquery) SELECT ... FROM derived, t1 ...  
このsubqueryサブクエリはWITH AS句に配置され、SELECT/UPDATE/DELELETEに配置され、WITHを含む derived AS の文の前に.
毎年の給与が前年比で上昇している割合を見つけるには、CTEを使用しない場合は、2つのサブクエリが必要です.この2つのサブクエリは同じです.MySQLは同じクエリであることを認識できません.これにより、2回のクエリが発生します.

mysql> SELECT
q1.year,
q2.year AS next_year,
q1.sum,
q2.sum AS next_sum,
100*(q2.sum-q1.sum)/q1.sum AS pct
FROM
(SELECT year(from_date) as year, sum(salary) as
sum FROM salaries GROUP BY year) AS q1,
(SELECT year(from_date) as year, sum(salary) as sum
FROM salaries GROUP BY year) AS q2
WHERE q1.year = q2.year-1;
+------+-----------+-------------+-------------+-----
-----+
| year | next_year | sum | next_sum | pct
|+
------+-----------+-------------+-------------+-----
-----+
| 1985 | 1986 | 972864875 | 2052895941 |
111.0155 |
| 1986 | 1987 | 2052895941 | 3156881054 |
53.7770 |
| 1987 | 1988 | 3156881054 | 4295598688 |
36.0710 |
| 1988 | 1989 | 4295598688 | 5454260439 |
26.9732 |
| 1989 | 1990 | 5454260439 | 6626146391 |
21.4857 |
| 1990 | 1991 | 6626146391 | 7798804412 |
17.6974 |
| 1991 | 1992 | 7798804412 | 9027872610 |
15.7597 |
| 1992 | 1993 | 9027872610 | 10215059054 |
13.1502 |
| 1993 | 1994 | 10215059054 | 11429450113 |
11.8882 |
| 1994 | 1995 | 11429450113 | 12638817464 |

再帰的でないCTEを使用して、前回のクエリの結果を再利用できる場合は、クエリを1回だけ行う必要があります.

mysql>
WITH CTE AS
(SELECT year(from_date) AS year, SUM(salary) AS
sum FROM salaries GROUP BY year)
SELECT
q1.year, q2.year as next_year, q1.sum, q2.sum as
next_sum, 100*(q2.sum-q1.sum)/q1.sum as pct FROM
CTE AS q1,
CTE AS q2
WHERE
q1.year = q2.year-1;
+------+-----------+-------------+-------------+-----
-----+
| year | next_year | sum | next_sum | pct
|+
------+-----------+-------------+-------------+-----
-----+
| 1985 | 1986 | 972864875 | 2052895941 |
111.0155 |
| 1986 | 1987 | 2052895941 | 3156881054 |
53.7770 |
| 1987 | 1988 | 3156881054 | 4295598688 |
36.0710 |
| 1988 | 1989 | 4295598688 | 5454260439 |
26.9732 |
| 1989 | 1990 | 5454260439 | 6626146391 |
21.4857 |
| 1990 | 1991 | 6626146391 | 7798804412 |
17.6974 |
| 1991 | 1992 | 7798804412 | 9027872610 |
15.7597 |
| 1992 | 1993 | 9027872610 | 10215059054 |
13.1502 |
| 1993 | 1994 | 10215059054 | 11429450113 |
11.8882 |
| 1994 | 1995 | 11429450113 | 12638817464 |
10.5812 |
| 1995 | 1996 | 12638817464 | 13888587737 |
9.8883 |
| 1996 | 1997 | 13888587737 | 15056011781 |
8.4056 |
| 1997 | 1998 | 15056011781 | 16220495471 |
7.7343 |
| 1998 | 1999 | 16220495471 | 17360258862 |
7.0267 |
| 1999 | 2000 | 17360258862 | 17535667603 |
1.0104 |
| 2000 | 2001 | 17535667603 | 17507737308 |
-0.1593 |
| 2001 | 2002 | 17507737308 | 10243358658 |
-41.4924 |
+------+-----------+-------------+-------------+-----
-----+
17 rows in set (1.63 sec)

クエリーの結果と同様に、パフォーマンスは50%近く向上しました.
また、派生クエリは相互参照できません.

SELECT ... FROM (SELECT ... FROM ...) AS d1, (SELECT ... FROM d1 ...) AS d2 ...
ERROR: 1146 (42S02): Table ‘db.d1’ doesn’t exist

上のクエリの1つをd 1としてマークし、後のクエリで再びd 1をクエリすることは許可されません.
CTEの方式は相互に引用することができる:

WITH d1 AS (SELECT ... FROM ...), d2 AS (SELECT ... FROM d1 ... )
SELECT FROM d1, d2 ...

d 1とd 2はそれぞれ2つのサブクエリであるが、d 2はクエリd 1の結果セットである.
要約すると、再帰的でないCTEでは、WITH ASを使用してサブクエリを定義し、複数のサブクエリ間をカンマで区切ってからSELETE文を使用し、名前で参照する前にサブクエリを定義します.
CTE 再帰方式
再帰的方式はCTEのサブクエリがそれ自体を参照することができ,再帰的方式を用いる場合,WITH句ではWITH RECURSIVEを用いる.再帰CTE句には、2つの部分が含まれている必要があります.1つはシードクエリ(自己参照不可)、もう1つは再帰クエリです.この2つのサブクエリは、UNION、UNIONALL、またはUNION DISTINCTを使用できます. つながっている.
シードSELECTは1回のみ実行され、初期のデータサブセットが得られますが、再帰SELECTは新しいローが生成されないまで繰り返し実行され、最終的にはすべての結果セットがクエリーされます.これは、親子関係のあるクエリーなどの深層クエリーに非常に役立ちます.
簡単な例を挙げると、1から5までの5つの数を印刷する場合は、再帰CTEを使用して次のようにします.

mysql> WITH RECURSIVE cte (n) AS
( SELECT 1 /* seed query */
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5 /* recursive query */
)SELECT * FROM cte;
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)

まずWITH RECURSIVE句を見てみましょう.
cteはサブクエリの名前であり、(n)は列であり、サブクエリ文は(SELECT 1ユニオンALL SELECT n+1 FROM cte WHERE n<5)であり、ここでSELECT 1はシードSELECTであり、一度だけ実行されるが、SELECT n+1 FROM cte WHERE n<5は再帰SELECTであり、つまりこの再帰クエリは、nの値が5以上になるまで実行され続け、再帰SELECTで自身のcteに引用されることに注意する.サブクエリが定義されたら、SELECTでこのcteをクエリすればいいです.
会社の組織アーキテクチャデータをクエリーする場合は、管理レベルをクエリーします.
テストテーブルを作成します.

mysql> CREATE TABLE employees_mgr (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees_mgr
(id)
);

サンプルデータを挿入するには、次の手順に従います.

mysql> INSERT INTO employees_mgr VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO
(manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to
333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

再帰CTEの実行:

mysql> WITH RECURSIVE employee_paths (id, name, path)
AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees_mgr
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees_mgr AS e
ON ep.id = e.manager_id
)SELECT * FROM employee_paths ORDER BY path;

 結果は次のとおりです.

+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | Yasmina | 333 |
| 198 | John | 333,198 |
| 29 | Pedro | 333,198,29 |
| 4610 | Sarah | 333,198,29,4610 |
| 72 | Pierre | 333,198,29,72 |
| 692 | Tarek | 333,692 |
| 123 | Adil | 333,692,123 |
+------+---------+-----------------+
7 rows in set (0.00 sec)

pathの列には管理レベルの関係が見られ、333は最高の指導者であり、4610、72、123は小兵である.
まとめ:
通常、ツリー構造をクエリーするときにWITH RECURSIVE CTEクエリーを使用し、サブクエリーとデータ列を定義してから、SELECTでこのCTE句をクエリーすればよい.
複数の同じサブクエリを単純に使用するだけで非再帰CTEを使用すると効率的ですよ~?
ITリソースのダウンロード