mysql - 所有行(非WHERE或HAVING)上的MySQL GROUP BY条件

我认为这很困难（仅适用于SQL硬核），但提出的问题很简单。
我有一个日志表，用于记录Unix时间戳中用户的连接和断开连接时间：

USER_ID  |   CONN_TIME   |   DISC_TIME
1        |   1533742332  |   1533744332
2        |   1533744232  |   1533744337
1        |   1533744132  |   1533754332
3        |   1533714332  |   1533744532
3        |   1533744032  |   1533744532

现在，我需要一种按10分钟间隔对行进行分组并计算每个间隔中的在线用户数的方法。我知道如何GROUP BY CONN_TIME或DISC_TIME，但是通过这种方式，我只能在十分钟的间隔内获得多少用户连接或断开连接，而不是多少用户在线(CONN_TIME - DISC_TIME)。

我当前的SQL语句如下：

SELECT DATE_FORMAT(FROM_UNIXTIME(CONN_TIME), '%Y-%m-%d %H:%i') as date, COUNT(*) as hits
FROM Stats
GROUP BY FLOOR(CONN_TIME / 600)

如您所见，我只能获得点击计数，这对于显示用户的活动是完全不令人满意的。我想知道他在那个时期是否仍然在线。

最佳答案

好的，几个小时后，我认为我终于有了类似您问题的答案的东西。但是，我尚未对其进行全面的测试，因此我无法保证其功能正常且完全按您的需要使用，后果自负。

Here it is as a fiddle，现在我做了些解释：

首先，我创建了两个视图，第一个视图生成从0到16的数字，第二个视图生成从0到256的数字。（您可以内联这些视图，但我没有）。

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
FROM generator_16 lo, generator_16 hi;

好的，现在我们可以生成从最低CONN_TIME到最高DISC_TIME的时间戳记：

SELECT UNIX_TIMESTAMP(DATE_ADD(@start_date, INTERVAL @i MINUTE)) AS result_date
FROM generator_256
CROSS JOIN
  (
     SELECT
       @i:=-10,
       @start_date:= FROM_UNIXTIME(FLOOR((SELECT MIN(CONN_TIME) FROM Stats) / 600) * 600)
  ) AS init
WHERE UNIX_TIMESTAMP(DATE_ADD(@start_date, INTERVAL@ i: = @i + 10 MINUTE))
  BETWEEN(FLOOR((SELECT MIN(CONN_TIME) FROM Stats) / 600) * 600)
  AND(CEIL((SELECT MAX(DISC_TIME) FROM Stats) / 600) * 600)

然后，您需要将统计信息加入其中

JOIN Stats
    ON Stats.CONN_TIME = Stats.CONN_TIME # Condition which is always true
    AND time.result_date + 600 > Stats.CONN_TIME # Logon is before end of timespan
    AND time.result_date < Stats.DISC_TIME  # Logoff is after beginning of timespan

并按result_date将整个内容分组。

我在本文中省略了一些部分，建议您看一下上面链接的小提琴。

希望这可以帮助。

关于mysql - 所有行(非WHERE或HAVING)上的MySQL GROUP BY条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51751394/