chatGPT4に頼んだら直してくれた。こりゃあ便利じゃ～！
mysql8.0のSQL
WITH
range_data AS (
SELECT
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name) AS ID,
SUM(CASE WHEN ds.scoresub > 0 THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN ds.scoresub < 0 THEN 1 ELSE 0 END) AS losses,
(SUM(CASE WHEN ds.scoresub > 0 THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS range_win_rate_percentage,
SUM(ds.scoresub) AS range_total_val
FROM
dealer_scores AS ds
LEFT JOIN dealers AS d1 ON ds.dealer_id = d1.id
LEFT JOIN dealers AS d2 ON ds.dealer2_id = d2.id
LEFT JOIN dealers AS d3 ON ds.dealer3_id = d3.id
LEFT JOIN dealers AS d4 ON ds.dealer4_id = d4.id
WHERE
ds.created BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'
GROUP BY
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name)
),
overall_data AS (
SELECT
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name) AS ID,
(SUM(CASE WHEN ds.scoresub > 0 THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS overall_win_rate_percentage,
SUM(ds.scoresub) AS overall_total_val
FROM
dealer_scores AS ds
LEFT JOIN dealers AS d1 ON ds.dealer_id = d1.id
LEFT JOIN dealers AS d2 ON ds.dealer2_id = d2.id
LEFT JOIN dealers AS d3 ON ds.dealer3_id = d3.id
LEFT JOIN dealers AS d4 ON ds.dealer4_id = d4.id
GROUP BY
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name)
)
SELECT
r.ID,
r.range_win_rate_percentage,
r.range_total_val,
o.overall_win_rate_percentage,
o.overall_total_val
FROM
range_data r
JOIN
overall_data o ON r.ID = o.ID
ORDER BY
r.range_win_rate_percentage DESC,
r.range_total_val DESC;
上記をmysql5.6で動作するように。
SELECT
r.ID,
r.range_win_rate_percentage,
r.range_total_val,
o.overall_win_rate_percentage,
o.overall_total_val
FROM
(
SELECT
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name) AS ID,
SUM(CASE WHEN ds.scoresub > 0 THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN ds.scoresub < 0 THEN 1 ELSE 0 END) AS losses,
(SUM(CASE WHEN ds.scoresub > 0 THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS range_win_rate_percentage,
SUM(ds.scoresub) AS range_total_val
FROM
dealer_scores AS ds
LEFT JOIN dealers AS d1 ON ds.dealer_id = d1.id
LEFT JOIN dealers AS d2 ON ds.dealer2_id = d2.id
LEFT JOIN dealers AS d3 ON ds.dealer3_id = d3.id
LEFT JOIN dealers AS d4 ON ds.dealer4_id = d4.id
WHERE
ds.created BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'
GROUP BY
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name)
) r
JOIN
(
SELECT
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name) AS ID,
(SUM(CASE WHEN ds.scoresub > 0 THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS overall_win_rate_percentage,
SUM(ds.scoresub) AS overall_total_val
FROM
dealer_scores AS ds
LEFT JOIN dealers AS d1 ON ds.dealer_id = d1.id
LEFT JOIN dealers AS d2 ON ds.dealer2_id = d2.id
LEFT JOIN dealers AS d3 ON ds.dealer3_id = d3.id
LEFT JOIN dealers AS d4 ON ds.dealer4_id = d4.id
GROUP BY
CONCAT(d1.name, '-', d2.name, '-', d3.name, '-', d4.name)
) o ON r.ID = o.ID
ORDER BY
r.range_win_rate_percentage DESC,
r.range_total_val DESC;
他のAIも直せるのか調べてみた。
bingのは中身同じだろうからイケるとは思うけど。
＜結果発表＞
・bard
使い勝手：×
SQLの出来：×
聞き方を変えても、まともな答えが返ってこない。
withは使わないでと指示しないとダメ。
スッキリしたSQLを返してくれた。
SELECT
CONCAT_WS('-', d1.name, d2.name, d3.name, d4.name) AS ID,
AVG(ds.scoresub > 0) AS range_win_rate_percentage,
SUM(ds.scoresub) AS range_total_val,
CONCAT_WS('-', d1.name, d2.name, d3.name, d4.name) AS overall_ID,
AVG(ds.scoresub > 0) AS overall_win_rate_percentage,
SUM(ds.scoresub) AS overall_total_val
FROM
dealer_scores AS ds
LEFT JOIN dealers AS d1 ON ds.dealer_id = d1.id
LEFT JOIN dealers AS d2 ON ds.dealer2_id = d2.id
LEFT JOIN dealers AS d3 ON ds.dealer3_id = d3.id
LEFT JOIN dealers AS d4 ON ds.dealer4_id = d4.id
WHERE
ds.created BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'
GROUP BY
CONCAT_WS('-', d1.name, d2.name, d3.name, d4.name)
ORDER BY
(AVG(ds.scoresub > 0)) DESC,
SUM(ds.scoresub) DESC;
一見動いているように見えるが、範囲を絞る事ができない。
間違ったSQLを提示してくる。
・bing（より厳密に）
chatGPT4と全く同じ。
・bing（よりバランス）
使い勝手：×
SQLの出来：△
bardみたいに間違った答えを返す。
突っ込むと正しい答えが返ってくるが、より厳密にと同じ回答。
0 件のコメント:
コメントを投稿