SQL Chat で Median計算してみた

SQLでMedian(中央値)を求める方法を説明します。

テスト用テーブルの作成

SQL Chat を使い、テーブルを作るSQL文を生成してもらいました。（ChatGPTに聞いているのと同じ）

CREATE TABLE salary_info 
( id INT NOT NULL AUTO_INCREMENT,
 employee_id INT NOT NULL, 
salary INT NOT NULL, 
bonus INT NOT NULL, 
allowance INT NOT NULL, 
tax INT NOT NULL, 
total_salary INT NOT NULL, 
payment_date DATE NOT NULL, PRIMARY KEY (id) );

データの登録

insert文も SQL Chatにお願いしてみた。正解にいたるまで、いろいろエラーになる回答を出してきましたね。

DELIMITER //

CREATE PROCEDURE insert_salary_info()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 100000 DO
    INSERT INTO salary_info (employee_id, salary, bonus, allowance, tax, total_salary, payment_date)
    VALUES (
      FLOOR(RAND() * 1000) + 1,
      FLOOR(RAND() * 500000) + 100000,
      FLOOR(RAND() * 100000),
      FLOOR(RAND() * 50000),
      FLOOR(RAND() * 100000),
      (salary + bonus + allowance) - tax,
      DATE_ADD('2021-01-01', INTERVAL FLOOR(RAND() * 365) DAY)
    );
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;
CALL insert_salary_info();

Median 関数の本当の方法

中央値はMedian関数を使います。distinctしないと行数文出力されるから、注意しましょう。over()に何も指定しないと全行に対する中央値となります。

MariaDB [sqlchat]>  select distinct median(salary) over() from salary_info;
+-----------------------+
| median(salary) over() |
+-----------------------+
|     349909.0000000000 |
+-----------------------+
1 row in set (0.105 sec)

SQL ChatのMedian求め方

SQL Chatは、Median関数ではなく、ずいぶん複雑なSQLを出してきました。このあたり、まだまだですね。

しかも間違い