MySQL8.0で中央値を求める方法

質問

Windows10 64bit環境でMySQL8.0を使い中央値を求めるため模索している素人です。
5.6ではユーザー定義変数を使った下記の方法で求めていました。
SELECT avg(t1.`金額`) as `金額`
FROM ( SELECT @rownum:=@rownum+1 as `row_number`, d.`金額`
FROM uriage d, (SELECT @rownum:=0) r
ORDER BY d.`金額` ) as t1,
( SELECT count(*) as total_rows FROM uriage d
) as t2
WHERE 1 AND t1.`row_number` in ( floor((total_rows+1)/2), floor((total_rows+2)/2));
しかし、これを8.0で実行すると結果は求められるのですが、次のような注意が出ます
「式内でのユーザー変数の設定は非推奨であり、将来のリリースで削除される予定です」
その上ODBCで通らなくなりました。そこで何か他にないかと検索したところ下記のサイトにPreparedStatementを使っている別の方法がありました、
*ttps://qiita.com/nkojima/items/2c483d4ddbdb29439c87
この場合。注意はでませんがODBC（デフォルト設定）では通らなかったのでさらに検索したところ
*ttps://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql&usg=ALkJrhjuCCaICL0uLguGt2oD4qbVM9cG7g
にウインドウ関数を使った単一ステートメントの下記の方法が紹介されていました、
SELECT AVG(t1.`金額`) AS median_val
FROM (SELECT `金額`,
ROW_NUMBER() OVER(ORDER BY `金額`) AS row_num
FROM uriage) t1,
(SELECT COUNT(*) AS num_records FROM uriage) t2
WHERE t1.row_num IN
(FLOOR((t2.num_records + 1) / 2),
FLOOR((t2.num_records + 2) / 2))
MySQLで100万件のレコードを対象に試したところ上記の変数を使った２件の方法より同等か少し遅い結果となりました。それでも標準SQLでの方法よりはるかに速いのですが・・・
また、以前使っていた32bit5.6に比べて上記2件の方法が遅いことがわかりました（平均3.0と1.9secが3.188と2.806secに）。・・・
とあるサイトで8.0ではCOUNT(*)が遅い原因の一つと考えられているとあったのででCOUNT(カラム名)に変更
(1)1.670　(2)1.640　(3)1.919
となりウインドウ関数を使った単一ステートメントの方法が一番遅い結果となりました。・・えっと！思いそこで前々回に質問しました（削除済み）・・平均値は前回のバッテリーモードから変更して測定し直しました。
さら検索していたら単一ステートメントの方法は見つかりませんでしたが、あるサイトにwith句をを使った方法が載っていました（試したところクエリが成立せず？）。1部を改変し下記クエリを試してみました。
SELECT COUNT(*)+1 INTO @rowindex FROM uriage;
WITH i AS (SELECT `金額`,ROW_NUMBER() OVER (ORDER BY `金額`) AS rowindex FROM uriage)
SELECT AVG(`金額`) AS median_duration FROM i WHERE i.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));
この方法では、COUNT(カラム名)よりCOUNT(*)の方が速い結果となりました。
(4)COUNT(カラム名)・・1.906　　　(5)COUNT(*)・・1.547・・・・これでまた混乱を増加
他のDBからのODBC経由でSQLを実行するスクリプトを組んでいるので(3)の単一ステートメント方法以外にも、(2)や(5)の方法を参考に最初にCOUNTするクエリで値を取得し変数使い次に計算式でクエリを整形し直してから発行する2度手間のスクリプトを組んで中央値を取得していましたが、投稿の後調べてみるとODBCのシステムDSNの設定でconnectionのAllow multiple statementsにチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる！
もし他に別の方法があればと思い再々投稿しました。

回答No.5

mpro-gram

index について、
over( order by `金額`)　があるので、金額カラムにもindex貼ると、行番号振るのが早くなると思う。
ただ集計回数より、insertやupdateなど編集回数が多いと、indexがいくつもあるのは、編集時のパフォーマンスが悪くなるので、どっちをとるかは、状況しだい。

お礼

お礼が遅れ申し訳ありません。次に質問した連番の振り直し問題に悪あがきをしているもので・・・
補足について勘違いしていました
AVG(t1.`金額`)になっていたのに気付かず実数で読み込めないとおもいこんでいました。パフォーマンス的にはwith句使用の(4)(5)より少々遅い結果となっていますが私のｐｃでは平均で2.00sec以内収まっています。
再度お礼申し上げます。

補足

再度の回答ありがとうございます。
>金額カラムにもindex貼ると、行番号振るのが早くなると思う。ただ集計回数より、insertやupdateなど編集回数が多いと、indexがいくつもあるのは、編集時のパフォーマンスが悪くなるので、どっちをとるかは、状況しだい。
indexと編集時のパフォーマンスの問題なんとなく理解で出来るが・・・
別途回答につき「)」の多さでなく「 min_record]」「max_record 」がwhere t.rowindex between で実数として読み込めないこと方だと思いますが？

回答No.4

mpro-gram

済みません select文を括る　(　)　の対応が微妙に間違っているようです。
netにつないでるのと別のPCでテストしていたので、写し間違った。

with name as (select 文)
, name2 as (select文)
select　本文

の構文です。先の回答には２つ目のselect文最後によけいな閉じ括弧がある。
count(*) の微妙な時間差は、私もよくわからないです。
indexを使えているかどうかは、explainしてみると良いです。

回答No.3

mpro-gram

済みません二重回答になってしまいました。
回答削除機能はないのか。

回答No.2

mpro-gram

mysqlでCOUNT(*)が遅いのは、innodb の場合です。transaction関連の影響で全件数え上げるしかないので。mysql8は defaultでinnodbテーブルになるので遅いと言われてるのでしょう。index があるカラムだとindex 集計で済むので速くなるのでしょう。
MyIsamだとwhere句もgroup by句もないなら、テーブル定義見るだけなのでほんとに一瞬、というか定数扱いになる。
where句なしの row_number() over(order by `金額`) のみで、`金額`カラムが先頭のindexがあれば、indexが利用されるようなので、以下のようにwith句で2テーブル定義すればちょっとは速いでしょうか？
with t as (SELECT `金額`,ROW_NUMBER() OVER (ORDER BY `金額`) AS rowindex FROM uriage)
, m as (SELECT floor( (COUNT(*)+1)/2) AS min_record, floor((count(*)+2)/2) as max_record FROM uriage))
SELECT AVG(t1.`金額`) AS median_val from t,m where t.rowindex between m.min_record and m.max_record ;
in と between でどっちが速いか、微妙に数値違うのかは確かめていません。temporaryテーブルでの範囲指定はindex無効なのでbetweenの利点がないし。

補足

回答有難うございます。
>COUNT(*)が遅いのは、innodb の場合です。transaction関連で全件数え上げるしかないので。mysql8は defaultでinnodbテーブルになるので遅いと言われてるのでしょう。index があるカラムだとindex 集計で済むので速くなるのでしょう。
mysqlの構造は素人なのでよく分かりませんがMyIsamエンジンのことはさておき、5.6のuriageテーブルのエンジンはinnoDBです。また8.0も5.6でもインデックスは日付カラムに付けてあります。8.0で高速になったとありますがクエリの書き方で十分な効果を得られない・・・
ウィンドウ関数を使った後の2つのクエリでcount(*)かcount(カラム名)の使い方によって反対のパフォーマンス・・どんな書き方すれば？
>with句で2テーブル定義・・
のクエリについてですがそのままコピペで走らせたせいかクエリが成立しません。
with句の使い方がよくわからないのでご教授おねがいいたします。

回答No.1

mpro-gram

COUNT(*)が遅いのは、innodb の場合です。transaction関連で全件数え上げるしかないので。mysql8は defaultでinnodbテーブルになるので遅いと言われてるのでしょう。index があるカラムだとindex 集計で済むので速くなるのでしょう。
MyIsamだとwhere句もgroup by句もないなら、総件数はテーブル定義見るだけなのでほんとに一瞬、というか定数扱いになる。
where句なしの row_number() over(order by `金額`) のみで、`金額`カラムが先頭のindexがあれば、indexが利用されるようなので、以下のようにwith句で2テーブル定義すればちょっとは速いでしょうか？

with t as (SELECT `金額`,ROW_NUMBER() OVER (ORDER BY `金額`) AS rowindex FROM uriage)
, m as (SELECT floor( (COUNT(*)+1)/2) AS min_record, floor((count(*)+2)/2) as max_record FROM uriage))
SELECT AVG(t.`金額`) AS median_val from t,m where t.rowindex between m.min_record and m.max_record ;

in と between でどっちが速いか、微妙に数値違うのかは確かめていません。temporaryテーブルでの範囲指定はindex無効なのでbetweenの利点がないし。