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にチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる！
もし他に別の方法があればと思い再々投稿しました。
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にチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる！
もし他に別の方法があればと思い再々投稿しました。
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にチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる！
もし他に別の方法があればと思い再々投稿しました。
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にチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる！
もし他に別の方法があればと思い再々投稿しました。
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にチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる！
もし他に別の方法があればと思い再々投稿しました。
MySQL5.7でランキングの集計をしたいのですが、遅すぎて困っています。
URIAGEテーブルのSITEN_CODE毎にKINGAKU_RANKとKENSU_RANKを集計したい。
SELECT
URIAGE.SITEN_CODE,
URIAGE.SHAIN_BANGO,
(SELECT
COUNT(URIAGE2.KINGAKU)
FROM URIAGE AS URIAGE2
WHERE URIAGE2.KINGAKU > URIAGE.KINGAKU AND URIAGE2.SITEN_CODE = URIAGE.SITEN_CODE)+1 AS KINGAKU_RANK,
URIAGE.KINGAKU,
(SELECT
COUNT(URIAGE2.KENSU)
FROM URIAGE AS URIAGE2
WHERE URIAGE2.KENSU > URIAGE.KENSU AND URIAGE2.SITEN_CODE = URIAGE.SITEN_CODE)+1 AS KENSU_RANK,
URIAGE.KENSU
FROM URIAGE
・件数が少ない内は動いたので、データ登録（約13万件）
・データ登録後、次のSQLで12時間動かしても終了しない。
という状態で困っています。
高速にランキング集計する方法があればご教授くださいm(_ _)m
・OS(CentOS7)のタイムゾーン：United States
である場合、my.cnfにて
・MySQLシステムのタイムゾーン(SYSTEM)
・セッションのタイムゾーン(SYSTEM)
という設定を行いました。
show variables like '%time_zone%';
を実行すると以下のようになっておりこの状態で
select now(); を行ったらUTCタイムが表示されました。
United Statesの時刻が表示されると思ったのですが
なぜUTC時刻が表示されたのでしょうか。
+--------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| system_time_zone | SYSTEM |
| time_zone | SYSTEM |
MySQLの初心者ですが、対象期間で集計を行いランキング付けし任意の上位何位までを抽出する方法について教えてください。
全データから集計後上位5位を選ぶ場合・・同順有り
select x.`品名`,x.kei,(select count(*)+1 from (select `品名`,sum(`金額`) as kei from uriage group by `品名`) y where x.kei<y.kei ) as rank
from (select `品名`,sum(`金額`) as kei from uriage group by `品名`) x
having rank <= 5
order by rank
といろいろ参考にしてで出来ましたが。任意の対象期間（日付）を設定して実行するSQL文が思いつきません。どこに期間を挿入するのか・・・悪あがきで思い付いた方法として
CREATE OR REPLACE VIEW uriage_01 AS
SELECT `品名`,SUM(`金額`) AS `金額` FROM uriage WHERE `日付` BETWEEN "2001/01/01" AND "2001/01/31" GROUP BY `品名`
で任意期間の集計結果のビューを作りそのビューから
select r1.`品名` as `品名`, r1.`金額` as p, count(r2.`金額`)+1 as rank
from uriage_01 as r1 left join uriage_01 as r2
on r1.`金額` < r2.`金額`
group by r1.`品名`
having rank <= 5
order by rank
と手間の掛かる方法です。単純に5行だけに限定ならorder by `金額`Limit 5 にすれば良いだけですけど・・・同順があるとどうすれば良いのかと思考停止状態です。尚、当方は、MySQLの勉強を始めたばかりにですので宜しくお願いいたします
