mariadb 如何将列列表中的每两个元素分组

tyky79it 于 2023-11-19 发布在其他

关注(0)|答案(2)|浏览(145)

我请求一个按升序排列的DATETIME(3)列表，例如：

> SELECT occurred_at FROM events ORDER BY ASC;
+-------------------------+
| occurred_at             |
+-------------------------+
| 2023-11-07 10:21:34.517 |
| 2023-11-07 10:20:34.524 |
| 2023-11-07 10:19:34.514 |
| 2023-11-07 10:18:34.517 |
| 2023-11-07 10:17:34.512 |
| 2023-11-07 10:16:34.517 |
| 2023-11-07 10:15:34.518 |
| 2023-11-07 10:14:34.515 |
+-------------------------+

字符串
这个要求很简单，但我想测量每两个日期之间的时间（意思是比较一行中第一个结果和第二个结果之间的运行时间，然后是另一行中的第三个和第四个，等等.而不比较第二个和第三个，第四个和第五个，等等.）原生TIMEDIFF(date1, date2)函数可以帮助我实现这个目标，但由于我的目标是将它们按两个分组（可能使用GROUP_CONCAT()？），我希望有一个如下所示的列表：

> THE_QUERY;
+---------------------------------------------------+
| occurred_at                                       |
+---------------------------------------------------+
| 2023-11-07 10:21:34.517, 2023-11-07 10:20:34.524 |
| 2023-11-07 10:19:34.514, 2023-11-07 10:18:34.517 |
| 2023-11-07 10:17:34.512, 2023-11-07 10:16:34.517 |
| 2023-11-07 10:15:34.518, 2023-11-07 10:14:34.515 |
+---------------------------------------------------+

型
或者更好的是，date1和date2的TIMEDIFF()结果在一行中，date3和date4在另一行中，等等。
由于我可以有很多行，我使用模2和co搜索解决方案，但没有成功使其工作。

mariadb

来源：https://stackoverflow.com/questions/77437518/how-to-group-a-column-list-each-2-elements

2条答案

按热度按时间

7eumitmz1#

通常你有一些信息，哪些行属于一起。但如果你想只依赖于列，你可以这样做：

select min(occurred_at), max(occurred_at), timediff(max(occurred_at), min(occurred_at))
from (
  select occurred_at, cast((row_number() over (order by occurred_at))/2 as unsigned) as rn
  from events
)  as q 
group by rn desc
order by 1

字符串
参见dbfiddle group by rn desc order by 1

赞(0）回复(0）举报 2023-11-19

qpgpyjmq2#

我终于通过这样的查询实现了我的目标：

WITH data AS (
  SELECT occurred_at, ROW_NUMBER() OVER (ORDER BY occurred_at) AS row_num FROM `events`
)
SELECT
  CONCAT(a.occurred_at, ', ', b.occurred_at) AS occurred_at,
  TIMESTAMPDIFF(SECOND, a.occurred_at, COALESCE(b.occurred_at, NOW()))) AS amount
FROM data AS a LEFT JOIN data AS b
ON a.row_num = b.row_num - 1
WHERE a.row_num % 2 = 1;

字符串
注意，LEFT JOIN对于处理不存在@B的情况很重要，因为还没有发生关闭事件
所以我的最后一个查询，它将总结每个events之间的所有时间，并以小时的形式返回，看起来像这样：

WITH data AS (
  SELECT occurred_at, ROW_NUMBER() OVER (ORDER BY occurred_at) AS row_num FROM `events`
)
SELECT TRUNCATE(
  COALESCE(
    SUM(
      TIMESTAMPDIFF(SECOND, a.occurred_at, COALESCE(b.occurred_at, NOW()))
    ), 0) / (60 * 60), 2
) AS amount
FROM data AS a LEFT JOIN data AS b
ON a.row_num = b.row_num - 1
WHERE a.row_num % 2 = 1;

型
我使用TIMESTAMPDIFF()而不是TIMEDIFF()，因为前者不受TIME类型的限制。对于TIMEDIFF()，你不能有大于838:59:59.999小时的值，如果你这样做，你仍然会得到838:59:59.999并警告。

赞(0）回复(0）举报 2023-11-19

我来回答

mariadb 如何将列列表中的每两个元素分组

2条答案

相关问题

热门标签

最新问答