次の時間帯の勝率の合計を求めるSQL文

以下の内容のテーブルで

CREATE TABLE `test` (
`id` int(11) NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`win_or_lose` enum('win','lose')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (1, '2023-05-23 00:00:00', 'lose');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (2, '2023-05-23 00:01:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (3, '2023-05-23 00:02:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (4, '2023-05-23 01:00:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (5, '2023-05-23 02:00:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (6, '2023-05-23 02:01:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (7, '2023-05-23 02:02:00', 'lose');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (8, '2023-05-23 02:03:00', 'lose');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (9, '2023-05-23 04:00:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (10, '2023-05-23 04:01:00', 'lose');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (11, '2023-05-23 04:02:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (12, '2023-05-23 05:00:00', 'lose');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (13, '2023-05-23 05:01:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (14, '2023-05-23 06:01:00', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (15, '2023-05-23 06:01:01', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (16, '2023-05-23 06:01:02', 'win');
INSERT INTO `test` (`id`, `created`, `win_or_lose`) VALUES (17, '2023-05-23 07:01:02', 'win');

1時間ごとの勝率（winの行数　÷（winの行数＋loseの行数））が60％以上でかつ3行以上の場合のみ、
次の時間帯の勝率の合計を求めるSQL文を教えてください。

この場合だと、
0時台　win2行、lose1行　合計3行　勝率60％以上　（この時間帯の勝率は含めない、しかし条件にマッチしたので次の時間帯の勝率を含める）
1時台　win1行、lose0行　合計1行　勝率60％以上　（この時間帯の勝率は含める、しかし条件にマッチしなかったので次の時間帯の勝率は含めない）
2時台　win2行、lose2行　合計4行　勝率60％未満　（この時間帯の勝率は含めない、そして条件にマッチしなかったので次の時間帯の勝率は含めない）
3時台　win0行、lose0行　合計0行　勝率60％未満　（この時間帯の勝率は含めない、そして条件にマッチしなかったので次の時間帯の勝率は含めない）
4時台　win2行、lose1行　合計3行　勝率60％以上　（この時間帯の勝率は含めない、しかし条件にマッチしたので次の時間帯の勝率を含める）
5時台　win1行、lose1行　合計2行　勝率60％未満　（この時間帯の勝率は含める、しかし条件にマッチしなかったので次の時間帯の勝率は含めない）
6時台　win3行、lose0行　合計3行　勝率60％以上　（この時間帯の勝率は含めない、しかし条件にマッチしたので次の時間帯の勝率を含める）
7時台　win1行、lose0行　合計1行　勝率60％以上　（この時間帯の勝率は含める、しかし条件にマッチしなかったので次の時間帯の勝率は含めない）
そして得たい結果は、

win1行（1時台） ＋　win1行（5時台）　＋　win1行（7時台）　＝　win3行
lose0行（1時台） ＋　lose1行（5時台）＋　lose0行（7時台）　＝　lose1行
win3行 ÷ （win3行 ＋ lose1行）　＝　0.75（←これ）

になります。

例えば、

SELECT DATE_FORMAT(created, '%Y-%m-%d %H:00:00') AS hour,
COUNT(CASE WHEN win_or_lose = 'win' THEN 1 END) AS wins,
COUNT(CASE WHEN win_or_lose = 'lose' THEN 1 END) AS losses
FROM test
GROUP BY hour
HAVING wins + losses >= 3 AND wins / (wins + losses) >= 0.6
ORDER BY hour;

で1時間ごとの勝率（winの行数　÷（winの行数＋loseの行数））が60％以上でかつ3行以上の取得はできたのですが、
ここからこの結果をどのように利用すれば良いのかが分からないです。

No.1 ベストアンサー 回答者： yambejp 回答日時： 2023/07/04 19:53

まず集計用に生成列を設定するのがベターです。

create table tbl (
`id` int primary key,
`created` datetime not null default current_timestamp(),
`win_or_lose` enum('win','lose'),
`d` datetime as (concat(year(created),'-',month(created),'-',day(created),' ',hour(created),':00:00'))
);

insert into tbl (id,created,win_or_lose) values
( 1,'2023-05-23 00:00:00','lose'),
( 2,'2023-05-23 00:01:00','win'),
( 3,'2023-05-23 00:02:00','win'),
( 4,'2023-05-23 01:00:00','win'),
( 5,'2023-05-23 02:00:00','win'),
( 6,'2023-05-23 02:01:00','win'),
( 7,'2023-05-23 02:02:00','lose'),
( 8,'2023-05-23 02:03:00','lose'),
( 9,'2023-05-23 04:00:00','win'),
(10,'2023-05-23 04:01:00','lose'),
(11,'2023-05-23 04:02:00','win'),
(12,'2023-05-23 05:00:00','lose'),
(13,'2023-05-23 05:01:00','win'),
(14,'2023-05-23 06:01:00','win'),
(15,'2023-05-23 06:01:01','win'),
(16,'2023-05-23 06:01:02','win'),
(17,'2023-05-23 07:01:02','win');

あとはd列を使って集計します。
select sum(win_or_lose='win')/count(*) as ratio from tbl
where d in(
select d+interval 1 hour
from tbl
group by d
having count(*)>=3
and sum(win_or_lose='win')/count(*)>=.6
)

この回答へのお礼

ご回答ありがとうございます。
なるほど、intervalを使えばいいんですね。
参考になりました。

お礼日時：2023/07/05 11:29