主页 / dba / 问题 / 222771
Accepted
Woton Sampaio
Asked: 2018-11-17 21:00:51 +0800 CST

按向上或向下 2h 的范围 DATIME 分组 - Mysql

  • 772

好吧，我有一个表，其中有一些列是重复的，但时间不完全相同，我需要做一个分组，范围为 2 小时。

请注意，所有标记为黑色的字段都重复，但 datetime 字段可能会相差几分钟，因此我需要在两个小时的间隔内加入每个人来组成一个组。

在此处输入图像描述

它会是这样的：SELECT * FROM table GROUP BY sinistro, prefixo, data AND horaIfull_oco range 2h up or down

他将加入所有最多相隔两个小时的人。如果有人知道该怎么做，我已经研究过并没有发现任何东西。

mysql select

2 个回答

  1. 抱歉 Woton，但您的回答有几个问题。

    首先，你的用法GROUP BY不对。随着GROUP BY您将一个组的几行折叠为一个。要获得有意义的结果，您的SELECT子句的所有列都必须包含在子句中，或者必须在其上应用类似orGROUP BY的聚合函数或。否则显示该组的随机行。所以 a总是错的。为确保您做对了，您可以执行（有关更多信息，您可以阅读手册的这一部分）MIN()AVG()SUM()SELECT * FROM foo GROUP BY barSET GLOBAL sql_mode = CONCAT(@@global.sql_mode, ',ONLY_FULL_GROUP_BY');

    您查询的第二个问题是，该HAVING子句是对GROUP BY. 您分组依据的列上的过滤器将在WHERE子句中。子句中的过滤器HAVING包括一个聚合函数，例如HAVING SUM(foo) > 5000.
    当您不使用聚合函数时，将再次使用随机行。因此，您的HAVING子句实际上根本没有过滤任何内容。x总是大于x - 5并且x总是小于x + 5。

    例如，适用于您的情况的过滤器（尽管我认为这不是您想要的）将是HAVING TIMESTAMPDIFF(MINUTE, MIN(horaIfull_oco), MAX(horaIfull_oco)) <= 150. 这不是你想要的，对吧？

    如果我理解正确，您想在连续行在某个时间范围内时建立一个组，对吗？为此，我们必须首先生成一个列，稍后我们可以使用它进行分组。

    让我们这样做。

    这是您的查询，解释如下：

    SELECT sinistro, prefixo, data
/*whatever else you need*/
, MIN(horaIfull_oco), MAX(horaIfull_oco), MIN(id_oco) AS id_oco, GROUP_CONCAT(inclusao_oco) AS incl 
FROM (
    SELECT 
    t.* 
    , @group_number := IF(
        @prev_sinistro != sinistro 
        OR @prev_prefixo != prefixo 
        OR @prev_data != data 
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        , @group_number + 1 
        , @group_number) AS gn 
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    , @prev_data := data 
    , @prev_horaIfull_oco := horaIfull_oco
    FROM your_table t 
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v 
    ORDER BY sinistro, prefixo, data, horaIfull_oco
) sq 
GROUP BY sinistro, prefixo, data, gn

    首先要注意的是，在关系数据库中没有行的顺序，除非您指定它。所以我们要在外部查询ORDER BY中使用我们想要使用的列GROUP BY，然后是时间戳/日期时间列。目标是，我们可以使用变量来访问前一行。
    我们不想在SET @prev_sinistro := NULL; SET @prev_prefixo := ...执行主查询之前每次都执行查询，所以我们在一个别名为vhere的子查询中初始化变量。
    然后，每次sinistroorprefixo或 ordata与前一行不同，或者horaIfull_oco当前一行与当前行中该列的值的差异大于 150 分钟时，我们递增@group_number变量，如果不是我们不这样做（我们只是将变量的当前值分配给变量）。以此类推，我们将当前行的值赋给变量
    。@prev_prefixo := sinistro重要的是，这是在IF()函数之后完成的。该SELECT子句按顺序处理。处理下一行时，变量仍保留前一行的值。

    最后，在外部查询中，我们可以按生成的gn列进行分组。

    编辑：为了满足您的进一步要求，您可以这样做：

    SELECT sinistro, prefixo, data, MIN(horaIfull_oco), MAX(horaIfull_oco)
FROM (
    SELECT 
    sinistro, prefixo, data, horaIfull_oco 
    , @group_number := IF(
        @prev_sinistro != sinistro 
        OR @prev_prefixo != prefixo 
        OR @prev_data != `data`
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150 
        OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)
        , @group_number + 1
        , @group_number) AS gn 
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    , @prev_data := `data`
    , @prev_horaIfull_oco := horaIfull_oco
    FROM ocorrencia t 
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v 
    ORDER BY sinistro, prefixo, data, horaIfull_oco
) sq 
GROUP BY sinistro, prefixo, data, gn;

    编辑2：

    所以，我排除data并使用了这个查询：

    SELECT sinistro, prefixo -- , data
/*whatever else you need*/
, MIN(horaIfull_oco), MAX(horaIfull_oco), GROUP_CONCAT(id_oco) AS id_oco
FROM (
    SELECT 
    id_oco,
    sinistro, prefixo, /*data,*/ horaIfull_oco
    , @group_number := IF(
        @prev_sinistro != sinistro 
        OR @prev_prefixo != prefixo 
        -- OR @prev_data != data 
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)
        , @group_number + 1 
        , @group_number) AS gn 
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    -- , @prev_data := data 
    , @prev_horaIfull_oco := horaIfull_oco
    FROM ocorrencia t 
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v 
    -- having id_oco in (254, 277)
    ORDER BY sinistro, prefixo, /*data,*/ horaIfull_oco
) sq 
GROUP BY sinistro, prefixo, /*data,*/ gn

    关于 id_oco 254 和 277 的问题，当您包括

    OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)

    在IF()函数中，这 2 条记录位于不同的组中，如果您不这样做，它们在同一组中，因为它们在 2.5 小时范围内。

    如果您仍有问题，请同时根据您的样本数据提供所需的结果，以避免进一步混淆。

    • 1
  2. Best Answer

    我做了：

    SELECT * FROM table GROUP BY sinistro, prefixo HAVING horaIfull_oco BETWEEN SUBTIME(horaIfull_oco, '02:30:00') and ADDTIME(horaIfull_oco, '02:30:00')

    • 0

相关问题