デモテーブルの作成：

CREATE TABLE `history_records` (
  `record_id` INT(11) NOT NULL AUTO_INCREMENT,
  `record_key` CHAR(7) NOT NULL,
  `value1` INT(11) DEFAULT NULL,
  `value2` INT(11) DEFAULT NULL,
  `value3` INT(11) DEFAULT NULL,
  `value4` INT(11) DEFAULT NULL,
  `value5` INT(11) DEFAULT NULL,
  `value6` INT(11) DEFAULT NULL,
  `special_number` INT(11) DEFAULT NULL,
  `special_note` VARCHAR(10) DEFAULT NULL,
  `total_sales` INT(11) DEFAULT NULL,
  `primary_win_count` INT(11) DEFAULT NULL,
  `primary_bonus` INT(11) DEFAULT NULL,
  `secondary_win_count` INT(11) DEFAULT NULL,
  `secondary_bonus` INT(11) DEFAULT NULL,
  `total_pool` INT(11) DEFAULT NULL,
  `record_date` DATETIME DEFAULT NULL,
  PRIMARY KEY (`record_id`),
  UNIQUE KEY `record_key` (`record_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

1. 識別連続していないID行：

SELECT 
    record_id, COUNT(*)
FROM
    lottery.history_records a
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            lottery.history_records b
        WHERE
            a.record_id + 1 = b.record_id)

2. 特殊番号の連続性を評価して最大ギャップ算出：

SELECT 
    *, MAX(record_id - nextId)
FROM
    (SELECT 
        record_id, record_key, special_number, @tmp AS nextId, @tmp:=record_id
    FROM
        lottery.history_records a, (SELECT @tmp:=0) b
    WHERE
        special_number = 5) t

3. 各特殊番号グループで連続性を評価するクエリ（最適化中）：

SELECT 
    special_number, MAX(current_id - previous_id)
FROM
    (SELECT DISTINCT
        a.*, @tmp AS previous_id, @tmp:=a.record_id AS current_id
    FROM
        lottery.history_records a
    LEFT JOIN lottery.history_records b ON (a.record_id > b.record_id), (SELECT @tmp:=0) t
    WHERE
        a.special_number = b.special_number
    ORDER BY a.special_number) grouped_data
GROUP BY special_number

4.重複データ検出：

SELECT 
    *
FROM
    lottery.history_records
WHERE
    (value1 , value2, value3, value4, value5, value6) IN (SELECT 
            value1, value2, value3, value4, value5, value6
        FROM
            lottery.history_records
        GROUP BY value1 , value2 , value3 , value4 , value5 , value6
        HAVING COUNT(*) > 1);

5. 日付フォーマット指定：

6. 異常間隔カウント：

SET @num01 = 0;
SET @num02 = 0;
SET @num03 = 0;
SET @num04 = 0;
SET @num05 = 0;
SET @num06 = 0;
SET @num07 = 0;
SET @num08 = 0;
SET @num09 = 0;
SET @num10 = 0;
SET @num11 = 0;
SET @num12 = 0;
SET @num13 = 0;
SET @num14 = 0;
SET @num15 = 0;
SET @num16 = 0;
SET @total = 0;
SET @miss_count = 0;

SELECT record_id, 
       (CASE WHEN special_number = 01 THEN @num01 := 0 ELSE @num01 := @num01 + 1 END) AS n01,
       (CASE WHEN special_number = 02 THEN @num02 := 0 ELSE @num02 := @num02 + 1 END) AS n02,
       (CASE WHEN special_number = 03 THEN @num03 := 0 ELSE @num03 := @num03 + 1 END) AS n03,
       (CASE WHEN special_number = 04 THEN @num04 := 0 ELSE @num04 := @num04 + 1 END) AS n04,
       (CASE WHEN special_number = 05 THEN @num05 := 0 ELSE @num05 := @num05 + 1 END) AS n05,
       (CASE WHEN special_number = 06 THEN @num06 := 0 ELSE @num06 := @num06 + 1 END) AS n06,
       (CASE WHEN special_number = 07 THEN @num07 := 0 ELSE @num07 := @num07 + 1 END) AS n07,
       (CASE WHEN special_number = 08 THEN @num08 := 0 ELSE @num08 := @num08 + 1 END) AS n08,
       (CASE WHEN special_number = 09 THEN @num09 := 0 ELSE @num09 := @num09 + 1 END) AS n09,
       (CASE WHEN special_number = 10 THEN @num10 := 0 ELSE @num10 := @num10 + 1 END) AS n10,
       (CASE WHEN special_number = 11 THEN @num11 := 0 ELSE @num11 := @num11 + 1 END) AS n11,
       (CASE WHEN special_number = 12 THEN @num12 := 0 ELSE @num12 := @num12 + 1 END) AS n12,
       (CASE WHEN special_number = 13 THEN @num13 := 0 ELSE @num13 := @num13 + 1 END) AS n13,
       (CASE WHEN special_number = 14 THEN @num14 := 0 ELSE @num14 := @num14 + 1 END) AS n14,
       (CASE WHEN special_number = 15 THEN @num15 := 0 ELSE @num15 := @num15 + 1 END) AS n15,
       (CASE WHEN special_number = 16 THEN @num16 := 0 ELSE @num16 := @num16 + 1 END) AS n16,
       @total := @num01 +@num02 +@num03 +@num04 +@num05 +@num06 +@num07 +@num08 +@num09 +@num10 +@num11 +@num12 +@num13 +@num14 +@num15 +@num16 AS total,
       @miss_count + 15 - @total AS missing, 
       @miss_count := @total AS previous
FROM lottery.history_records 
GROUP BY record_id, special_number;