[MySQL] サブクエリがネストしたSQLの書き直しの練習

前回のエントリーで、書き方がわからなかったSQLというのは、そのエントリーの最後に書いたSQLの、"id"がややこしい式になる時のすっきりした書き方だった。

id	id2	COUNT(*)
NULL	ID_001	10
ID_002	NULL	26
NULL	ID_003	10
ID_004	NULL	3
...	...	...

例えば、このように、IDが"id"と"id2"のどちらかに入っているとする。それに対し、前回のコードのサブクエリ以下の"id"をほぼ単純に"IFNULL(id, id2)"に書き換えたものが以下のものである。

●コード

/* original */
SELECT
  _id,
  COUNT(*) as data_num,
  AVG(count) AS mean,
  MAX(count) AS max
FROM (
  SELECT IFNULL(id, id2) AS _id, date, count
  FROM testdata
  WHERE IFNULL(id, id2) IN (
    SELECT *
    FROM (
      SELECT DISTINCT(IFNULL(id, id2))
      FROM testdata
      WHERE '2022-04-01' <= date AND date < '2022-04-10'
      LIMIT 3
    ) AS t1
  ) AND WHERE '2022-04-01' <= date AND date < '2022-05-01'
) AS t2
GROUP BY _id
ORDER BY _id;

●実行結果

_id	data_num	mean	max
ID_002	26	4.9231	11
ID_006	24	8.5833	15
ID_007	25	7.9200	16

最も内側のSELECTでは2022-04-01〜2022-04-09のデータに含まれるIDの内3つを選択している。
その1つ外側のSELECT *は、前回のエントリーに記載したMySQLの制限対策である。
その1つ外側のSELECTでは、IDをその3つ、日付を2022-04-01〜2022-04-30に絞っている。
一番外側のSELECTでは、IDでGROUP BYして集計している。

このコードの何が不満かというと、IFNULL(id, id2)が3つあることと、やりたいことに対してサブクエリがネストし過ぎているように思えることである。
その為、個人的により良いと思える書き方を考えてみることにした。

まず、MySQLではSELECT節に書いた別名をGROUP BY節で使えるらしい（会社で使用している某DBMSでも使えた）ので、サブクエリを1つばらして"AS _id"とGROUP BYを一番外側のSELECT文で一緒にやるようにする。

●コード

/* v2 */
SELECT
  IFNULL(id, id2) AS _id,
  COUNT(*) as data_num,
  AVG(count) AS mean,
  MAX(count) AS max
FROM testdata
WHERE IFNULL(id, id2) IN (
  SELECT *
  FROM (
    SELECT DISTINCT(IFNULL(id, id2))
    FROM testdata
    WHERE '2022-04-01' <= date AND date < '2022-04-10'
    LIMIT 3
  ) AS t1
) AND '2022-04-01' <= date AND date < '2022-05-01'
GROUP BY _id
ORDER BY _id;

●実行結果（同じなので省略）

次に、先にIDをDISTINCTにしてLIMIT 3するのとGROUP BYして最後にLIMIT 3するのは結果としては同じなので、内側のLIMITを外側に出す。するとDISTINCTとMySQLの制限対策のSELECT *は不要になるので、消す。
（全IDについてGROUP BYすることになりそうで、この時点では速度が気になるが）

●コード

/* v3 */
SELECT
  IFNULL(id, id2) AS _id,
  COUNT(*) as data_num,
  AVG(count) AS mean,
  MAX(count) AS max
FROM testdata
WHERE IFNULL(id, id2) IN (
  SELECT IFNULL(id, id2)
  FROM testdata
  WHERE '2022-04-01' <= date AND date < '2022-04-10'
) AND '2022-04-01' <= date AND date < '2022-05-01'
GROUP BY _id
ORDER BY _id
LIMIT 3;

●実行結果（同じなので省略）

最後に、サブクエリのあるWHERE節の部分は、要するに2022-04-01〜2022-04-09のデータがあるグループを選択するものなので、GROUP BYのGROUPを絞るHAVING節に書き換える。

●コード

/* final */
SELECT
  IFNULL(id, id2) AS _id,
  COUNT(*) as data_num,
  AVG(count) AS mean,
  MAX(count) AS max
FROM testdata
WHERE '2022-04-01' <= date AND date < '2022-05-01'
GROUP BY _id
HAVING COUNT('2022-04-01' <= date AND date < '2022-04-10' OR NULL)
ORDER BY _id
LIMIT 3;

●実行結果（同じなので省略）

HAVING節の所は個人的にはSUM('2022-04-01' <= date AND date < '2022-04-10') > 0の方がわかりやすいが、意味的にはSUMでなくANYが欲しい所であり、COUNTの方がANYの代用に相応しそうだし、COUNTの方がSQLっぽくて応用が利きそうな感じがするので、COUNTにした。

気になる処理時間であるが、テーブルの行数や結果の行数を増やして、RESET QUERY CACHE;でクエリキャッシュをクリアしてテーブルを作り直しながら複数回計測してみた結果、上の"original"と"v2"と"v3"のコードは大体同じ時間で、"final"のコードは半分くらいの時間だった。ばらつきが大きく、あまり信頼できない計測結果だったが、"final"のコードが最速なのは間違いなさそうだった。
Pandasのように、なるべく先に処理対象の行数を絞るようにする方が速くなるとは限らないようだ。

●テストデータ作成用コード

DROP TABLE IF EXISTS testdata;
CREATE TABLE testdata (
  id CHAR(10),
  id2 CHAR(10),
  date DATE,
  count INT
-- PRIMARY KEY (id, date)
);

DROP PROCEDURE IF EXISTS make_testdata;
DELIMITER //
CREATE PROCEDURE make_testdata(IN id_num INT, date_num INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE j INT DEFAULT 0;
  DECLARE seed1 INT DEFAULT 6;
  DECLARE seed2 INT DEFAULT 5;

  /* stored procedures with a lot of INSERT without this is 20x slower */
  SET autocommit = 0;

  /* get reproducible random sequence as a table */
  DROP TABLE IF EXISTS rand_tmp;
  CREATE TABLE rand_tmp (
    id INT PRIMARY KEY,
    rand_val FLOAT
  );

  SET i = 1;
  WHILE i <= id_num DO
    INSERT INTO rand_tmp VALUES (i, 0);
    SET i = i + 1;
  END WHILE;
  UPDATE rand_tmp SET rand_val = RAND(seed1);

  /* make test data */
  SET i = 1;
  WHILE i <= id_num DO
    /* data starts from random day */
    SET j = (SELECT rand_val FROM rand_tmp WHERE id = i) * date_num;
    WHILE j < date_num DO
      INSERT INTO testdata VALUES(
        /* real ID in only one of "id" or "id2" */
        IF(MOD(i, 2) = 0, CONCAT('ID_', LPAD(i, 3, '0')), NULL),
        IF(MOD(i, 2) = 1, CONCAT('ID_', LPAD(i, 3, '0')), NULL),
        DATE_ADD('2022-4-1', INTERVAL j DAY),
        0  /* "count" values are set later */
      );
      SET j = j + 1;
    END WHILE;
  SET i = i + 1;
  END WHILE;
  UPDATE testdata SET count = FLOOR(RAND(seed2) * (10 + SUBSTRING(IFNULL(id, id2), 4)));

  DROP TABLE rand_tmp;
  SET autocommit = 1;
END
//
DELIMITER ;

CALL make_testdata(20, 30);
SELECT id, id2, COUNT(*) FROM testdata GROUP BY ifnull(id, id2);

Weblog on mebius.tokaichiba.jp

[MySQL] サブクエリがネストしたSQLの書き直しの練習

コメント