分析関数を置き換えるには、自己接続クエリ、複雑なサブクエリなどがよく使用されます。 これらのソリューションのほとんどは、パフォーマンスの点で効果がありません。
また、MySQLには再帰はありません。 ただし、通常は分析関数または再帰によって解決されるタスクの一部は、MySQLツールで処理できます。
これらのツールの1つは、SQLクエリ内の変数を操作する他のDBMSメカニズムにはない、ユニークで特徴的なツールです。 クエリ内で変数を宣言し、その値を変更して、出力のSELECTで置換できます。 さらに、リクエスト内の行を処理する順序、およびその結果、変数に値を割り当てる順序をカスタムソートで設定できます！
警告 この記事では、SELECT句の式の処理が左から右に実行されることを前提としていますが、MySQLのドキュメントにはこの処理順序の公式の確認はありません。 サーバーのバージョンを変更するときは、これに留意する必要があります。 一貫性を確保するために、ダミーのCASEまたはIFステートメントを使用できます。
再帰アナログ
フィボナッチ数列を生成する簡単な例を考えてみましょう（フィボナッチ数列では、各項は前の2つの合計に等しく、最初の2は1に等しい）。
SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, /* , 1 */ (SELECT 1 X UNION ALL SELECT 2)X;
このクエリは、最初の2つをカウントせずに18個のフィボナッチ数を生成します。
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
それでは、その仕組みを見てみましょう。
行5）6）では、9つのレコードが生成されます。 ここでは珍しいことはありません。
7行目では、2つの変数@ I、@ Jを宣言し、1を割り当てます。
行3）では、次のことが行われます。最初に、変数@Iに2つの変数の合計が割り当てられます。 次に、変数@Jに同じ値を割り当て、@ Iの値が既に変更されているという事実を考慮します。
言い換えると、SELECTの計算は左から右に実行されます-記事の冒頭の備考も参照してください。
さらに、変数の変更は、9つのレコードのそれぞれで実行されます。 新しい各行を処理するとき、変数@Iおよび@Jには、前の行を処理して計算された値が含まれます。
他のDBMSを使用して同じ問題を解決するには、 再帰クエリを作成する必要があります。
注：
変数は別のサブクエリで宣言する必要があります（行7）。SELECT句で変数を宣言した場合、1回しか評価されない可能性があります（特定の動作はサーバーのバージョンによって異なります）。 変数のタイプは、初期化される値によって決まります。 このタイプは動的に変更できます。 変数をNULLに設定すると、その型はBLOBになります。
前述のように、SELECTで行が処理される順序は、カスタムソートに依存します。 特定の順序での行番号付けの簡単な例：
SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;
Val Num 10 1 20 2 30 3 50 4
分析関数の類似物
変数を使用して分析関数を置き換えることもできます。 以下はいくつかの例です。 簡単にするために、すべてのフィールドがNULLではなく、1つのフィールドで並べ替えとパーティション分割（PARTITION BY）が発生すると仮定します。 NULL値とより複雑な並べ替えを使用すると、例はより面倒になりますが、本質は変わりません。
たとえば、TestTableテーブルを作成します。
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
どこで
group_id-グループ識別子（分析関数ウィンドウのアナログ）;
order_id-ソート用の一意のフィールド。
値は数値です。
テーブルにテストデータを入力します。
INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;
いくつかの分析関数の置換の例。
1）ROW_NUMBER（）OVER（ORDER BY order_id）
SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10
2）ROW_NUMBER（）OVER（PARTITION BY group_id ORDER BY order_id）
SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1
3）SUM（値）OVER（PARTITION BY group_id ORDER BY order_id）
SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1
4）LAG（値）OVER（PARTITION BY group_id ORDER BY order_id）
SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL
LEADの場合、すべて同じです。ソートをORDER BY group_id、order_id DESCに変更する必要があるだけです。
関数COUNT、MIN、MAXの場合、グループ（ウィンドウ）内のすべての行を分析するまで関数の値を見つけることができないため、すべてがやや複雑になります。 たとえば、MS SQLは、これらの目的のためにウィンドウを「スプール」します（ウィンドウ行を非表示のバッファテーブルに一時的に入れて、再びアクセスする）。 ただし、各ウィンドウについて、特定の並べ替えの最後の行の関数の値を計算できます（つまり、ウィンドウ全体を分析した後）。次に、ウィンドウ内の行を逆の順序で並べ替え、計算された値をウィンドウ全体に配置します。
したがって、2つのソートが必要です。 最終的なソートが上記の例と同じままになるように、最初にフィールドgroup_id ASC、order_id DESCでソートし、次にフィールドgroup_id ASC、order_id ASCでソートします。
5）カウント（*）オーバー（group_idによるパーティション）
最初の並べ替えでは、単にエントリに番号を付けます。 2番目では、ウィンドウのすべての行に最大数を割り当てます。これは、ウィンドウ内の行数に対応します。
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC /* */ )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id /* */ )T;
group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1
関数MAXおよびMINは、類推によって計算されます。 MAXの例のみを示します。
6）MAX（値）OVER（PARTITION BY group_id）
SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
7）COUNT（DISTINCT値）OVER（PARTITION BY group_id）
MS SQL Serverでは使用できない興味深いことですが、RANKからMAXを取得することにより、サブクエリで計算できます。 ここでも同じことを行います。 最初のソートでは、RANK（）OVER（PARTITION BY group_id ORDER BY value DESC）を計算し、2番目のソートでは、各ウィンドウのすべての行に最大値を入れます。
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;
group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
性能
まず、自己結合と変数を使用して、クエリの行番号付けのパフォーマンスを比較します。
1）自己接続の古典的な方法
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
テーブルTestTableの10000レコードに対して生成されるもの：
期間/フェッチ
16.084秒/ 0.016秒
2）変数の使用：
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
以下を生成します。
期間/フェッチ
0.016秒/ 0.015秒
結果はそれを物語っています。 ただし、変数を使用して計算された値は、フィルタリング条件では最適に使用されないことを理解する必要があります。 並べ替えと計算はすべての行に対して行われますが、最終的には一部の行のみが必要です。
このようなタスクの例についてさらに詳しく検討してください。
各group_id値のTestTableテーブルの最初の2行を、order_idでソートして印刷します。
以下に、分析機能をサポートするDBMSでこの問題を解決する方法を示します。
SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;
ただし、MySQLオプティマイザーはRowNumフィールドを計算するルールについて何も知りません。 彼はすべての行に番号を付け、その後必要な行を選択する必要があります。
100万件のレコードと20の一意のgroup_id値があるとします。 つまり 40行を選択するために、MySQLは100万行のRowNum値を計算します！ MySQLの単一クエリでは、この問題に対する美しい解決策はありません。 ただし、次のように、最初に一意のgroup_id値のリストを取得できます。
SELECT DISTINCT group_id FROM TestTable;
次に、他のプログラミング言語を使用して、次の形式のクエリを生成します。
SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;
20の簡単なクエリは、100万行のRowNumを計算するよりもはるかに速く動作します。