mysql数据库中有用户的玩游戏开始时间和结束时间，计算每个小时在玩游戏的人数，有跨小时、跨天的场景

假设用户的玩游戏的开始时间和结束时间分别存储在game表的start_time和end_time字段中，每个记录还有一个id字段表示用户ID，需要统计每个小时在玩游戏的人数，包括跨小时、跨天的情况。

可以使用MySQL的DATE_FORMAT函数提取小时部分，然后使用GROUP BY语句按小时分组，在每个小时内统计玩游戏的人数。

查询语句如下：

```

SELECT DATE_FORMAT(start_time, '%Y-%m-%d %H:00:00') AS hour_start,

COUNT(DISTINCT id) AS player_count

FROM game

GROUP BY hour_start;

```

这个查询语句将每个start_time转换为对应的小时开始时间，并统计每个小时内不同的id数（即玩游戏的人数）。结果类似这样：

```

+---------------------+-------------+

| hour_start | player_count|

+---------------------+-------------+

| 2022-01-01 00:00:00 | 10 |

| 2022-01-01 01:00:00 | 8 |

| 2022-01-01 02:00:00 | 12 |

| ... | ... |

```

这个查询语句可以应对大多数情况，但是有一种特殊情况需要注意：如果一个游戏开始时间跨越多个小时，那么它会被统计多次，而同一个用户可能会在不同的小时内都被统计。如果需要避免这种情况，可以使用时间序列表生成需要统计的小时，然后LEFT JOIN游戏表，只统计每个小时内的不同id数。

以下是示例代码：

```

CREATE TABLE hours (hour_start DATETIME);

INSERT INTO hours (hour_start)

SELECT CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d %H'), ':00:00') AS hour_start

FROM (

SELECT MIN(start_time) AS min_start, MAX(end_time) AS max_end

FROM game

) AS time_range

JOIN (

SELECT @row := @row + INTERVAL 1 HOUR AS hour

FROM information_schema.columns, (SELECT @row := time_range.min_start - INTERVAL 1 HOUR FROM (SELECT 1) AS init) AS vars

WHERE @row <= time_range.max_end

AND DATE_FORMAT(@row, '%i') = '00'

) AS time_seq

ON 1=1;

SELECT hours.hour_start,

COUNT(DISTINCT game.id) AS player_count

FROM hours

LEFT JOIN game

ON game.start_time <= hours.hour_start + INTERVAL 1 HOUR

AND game.end_time >= hours.hour_start

GROUP BY hours.hour_start;

```

这个查询语句使用了一个名为hours的时间序列表，它包含了需要统计的每个小时。首先查询game表中的最早开始时间和最晚结束时间，然后使用information_schema.columns生成时间序列，从最早开始时间的前一小时开始，每隔1小时生成一个时间，直到最晚结束时间为止。需要注意的是，如果不使用DATE_FORMAT函数将分钟部分设为0，时间序列会包含每个小时的60分钟，这会导致统计错误。

然后将时间序列表和game表LEFT JOIN，只统计在每个小时内在玩游戏的用户数。LEFT JOIN可以保证每个小时都被统计到，即使这个小时内没有用户在玩游戏。结果类似这样：

```

+---------------------+-------------+

| hour_start | player_count|

+---------------------+-------------+

| 2022-01-01 00:00:00 | 10 |

| 2022-01-01 01:00:00 | 8 |

| 2022-01-01 02:00:00 | 12 |

| ... | ... |

```

这个查询语句比第一个查询语句稍微复杂一点，但是可以应对跨小时、跨天的情况，统计的结果更加准确。 ♨