按mysql / MariaDB中的变量范围进行分组 [英] Group by a variable range in mysql / MariaDB
本文介绍了按mysql / MariaDB中的变量范围进行分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有历史记录，我想总结一些条目。例如：
我有一个列表
date_start | date_end | count | somestring
2015-09-15 12:04:09 | 2015-09-15 12：04：09 | 1 | xyz
2015-09-15 12:05:09 | 2015-09-15 12：05：09 | 1 | xyz
2015-09-15 12:05:10 | 2015-09-15 12：05：10 | 1 | xyz
2015-09-15 12:05:11 | 2015-09-15 12：05：11 | 1 | xyz
2015-09-15 12:06:09 | 2015-09-15 12：06：09 | 1 | xyz
我现在想得到一个像
date_start | date_end | count | somestring
2015-09-15 12:04:09 | 2015-09-15 12：04：09 | 1 | xyz
2015-09-15 12:05:09 | 2015-09-15 12：05：11 | 3 | xyz< -
2015-09-15 12:06:09 | 2015-09-15 12：06：09 | 1 | xyz
因此，如果在5秒的时间间隔内有重复，我想将它分组到一个条目。但是如果在每个最多1小时内有几个条目。相隔5秒我也要计算所有这些条目。
有人知道一种方法吗？我现在在这工作了几个星期：（b / b
编辑：
Bernd的答案和评论：
非常感谢你，你的输出查询是：
谢谢@Bernd。问题是，你的查询输出是：
+ - ------- + --------------------- + -------------- + ----- ---------------- + ---- + --------------------- + ------ --------------- + ----------- + ------ +
| mycount | st | group_number | tmp_interv | id | start_date | end_date | some_text | cnt |
+ --------- + --------------------- + ----- --------- + --------------------- + ---- + ------------- -------- + --------------------- + ----------- ------ + +
| 2 | 2015-09-14 12:00:05 | 0 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:00:00 | 2015-09-14 12 ：00：00 | some | 1 |
| 4 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:01:08 | 3 | 2015-09-14 12:01 ：03 | 2015-09-14 12:01:03 | some | 1 |
| 1 | 2015-09-14 12:01:08 | 2 | 2015-09-14 12:01:14 | 7 | 2015-09-14 12:01:09 | 2015-09-14 12:01:09 |一些| 1 |
+ --------- + --------------------- + ------------- - + --------------------- + ---- + --------------------- + --------------------- + ----------- + ------ +
但它应该是这样的：
+ --------- + --------------------- + -------------- + --------------------- + ---- + --------------------- + - -------------------- + ----------- + ------ +
| mycount | st | group_number | tmp_interv | id | start_date | end_date | some_text | cnt |
+ --------- + --------------------- + ------------- - + --------------------- + ---- + --------------------- + --------------------- + ----------- + ------ +
| 2 | 2015-09-14 12:00:05 | 0 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:00:00 | 2015-09-14 12:00:03 |一些| 1 |
| 5 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:01:08 | 3 | 2015-09-14 12:01:03 | 2015-09-14 12:01:09 |一些| 1 |
+ --------- + --------------------- + ------------- - + --------------------- + ---- + -------------------- + ---------------------- + ----------- + ------ +
$ b 注意count和date_end：）
解决方案在这里我第一次尝试：
pre code CREATE TABLE`dtable`（
`id` int（11 ）unsigned NOT NULL AUTO_INCREMENT，
`start_date` timestamp NULL DEFAULT NULL，
`end_date` timestamp NULL DEFAULT NULL，
`some_text` varchar（32）DEFAULT NULL，
`cnt int（11）DEFAULT NULL，
PRIMARY KEY（`id`）
）ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = utf8;
插入`dtable`（`id`，`start_date`，`end_date`，`some_text`，`cnt`）
VALUES
（1，'2015-09 -14 12:00:00'，'2015-09-14 12:00:00'，'some'，1），
（2，'2015-09-14 12:00:03'，' 2015-09-14 12:00:03'，'some'，1），
（3，'2015-09-14 12:01:03'，'2015-09-14 12:01:03 '，'some'，1），
（4，'2015-09-14 12:01:04'，'2015-09-14 12:01:03'，'some'，1），
（5，'2015-09-14 12:01:05'，'2015-09-14 12:01:03'，'some'，1），
（6，'2015-09 -14 12:01:08'，'2015-09-14 12:01:08'，'some'，1），
（7，'2015-09-14 12:01:09'，' 2015-09-14 12:01:09'，'some'，1）;
SELECT sum（cnt）mycount，t。* FROM（
SELECT
@interval_end：= IF（@interval_end = 0，d.start_date + INTERVAL 5 SECOND，@interval_end）st，
@group_nr：= IF（d.start_date> @interval_end，@group_nr：= @ group_nr + 1，@group_nr）group_number，
@interval_end：= IF（d .start_date> @interval_end，d.start_date + INTERVAL 5 SECOND，@interval_end）tmp_interv，
d。* FROM dtable d，（SELECT @group_nr：= 0，@interval_end：= 0）tmp
）AS t
GROUP BY t.group_number;
请检查并说出最新消息
I have a history log and i want to summarize some entries. The interval should be 5 seconds
For example:
I have a list
date_start | date_end | count | somestring
2015-09-15 12:04:09 | 2015-09-15 12:04:09| 1 | xyz
2015-09-15 12:05:09 | 2015-09-15 12:05:09| 1 | xyz
2015-09-15 12:05:10 | 2015-09-15 12:05:10| 1 | xyz
2015-09-15 12:05:11 | 2015-09-15 12:05:11| 1 | xyz
2015-09-15 12:06:09 | 2015-09-15 12:06:09| 1 | xyz
I want now to have an output like
date_start | date_end | count | somestring
2015-09-15 12:04:09 | 2015-09-15 12:04:09| 1 | xyz
2015-09-15 12:05:09 | 2015-09-15 12:05:11| 3 | xyz <--
2015-09-15 12:06:09 | 2015-09-15 12:06:09| 1 | xyz
so if there is a duplicate in a 5 sec interval I want to group it to one entry. but if there are several entries in 1 hour with each max. 5 seconds apart I want to count all of these entries too.
does somebody know a way? I am working at this for weeks now :(
EDIT:
answer and comment for Bernd:
Thank you so much, the output of your query is:
thank you @Bernd. The Problem is, the output with your query is:
+---------+---------------------+--------------+---------------------+----+---------------------+---------------------+-----------+------+
| mycount | st | group_number | tmp_interv | id | start_date | end_date | some_text | cnt |
+---------+---------------------+--------------+---------------------+----+---------------------+---------------------+-----------+------+
| 2 | 2015-09-14 12:00:05 | 0 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:00:00 | 2015-09-14 12:00:00 | some | 1 |
| 4 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:01:08 | 3 | 2015-09-14 12:01:03 | 2015-09-14 12:01:03 | some | 1 |
| 1 | 2015-09-14 12:01:08 | 2 | 2015-09-14 12:01:14 | 7 | 2015-09-14 12:01:09 | 2015-09-14 12:01:09 | some | 1 |
+---------+---------------------+--------------+---------------------+----+---------------------+---------------------+-----------+------+
but it should be something like:
+---------+---------------------+--------------+---------------------+----+---------------------+---------------------+-----------+------+
| mycount | st | group_number | tmp_interv | id | start_date | end_date | some_text | cnt |
+---------+---------------------+--------------+---------------------+----+---------------------+---------------------+-----------+------+
| 2 | 2015-09-14 12:00:05 | 0 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:00:00 | 2015-09-14 12:00:03 | some | 1 |
| 5 | 2015-09-14 12:00:05 | 1 | 2015-09-14 12:01:08 | 3 | 2015-09-14 12:01:03 | 2015-09-14 12:01:09 | some | 1 |
+---------+---------------------+--------------+---------------------+----+--------------------+----------------------+-----------+------+
be aware of the count and date_end :)
解决方案 Here my first try:
CREATE TABLE `dtable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`start_date` timestamp NULL DEFAULT NULL,
`end_date` timestamp NULL DEFAULT NULL,
`some_text` varchar(32) DEFAULT NULL,
`cnt` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO `dtable` (`id`, `start_date`, `end_date`, `some_text`, `cnt`)
VALUES
(1, '2015-09-14 12:00:00', '2015-09-14 12:00:00', 'some', 1),
(2, '2015-09-14 12:00:03', '2015-09-14 12:00:03', 'some', 1),
(3, '2015-09-14 12:01:03', '2015-09-14 12:01:03', 'some', 1),
(4, '2015-09-14 12:01:04', '2015-09-14 12:01:03', 'some', 1),
(5, '2015-09-14 12:01:05', '2015-09-14 12:01:03', 'some', 1),
(6, '2015-09-14 12:01:08', '2015-09-14 12:01:08', 'some', 1),
(7, '2015-09-14 12:01:09', '2015-09-14 12:01:09', 'some', 1);
SELECT sum(cnt) mycount, t.* FROM (
SELECT
@interval_end:=IF(@interval_end = 0, d.start_date + INTERVAL 5 SECOND, @interval_end ) st,
@group_nr:= IF( d.start_date > @interval_end, @group_nr:=@group_nr+1, @group_nr ) group_number,
@interval_end:= IF( d.start_date > @interval_end, d.start_date + INTERVAL 5 SECOND , @interval_end ) tmp_interv,
d.* FROM dtable d,(SELECT @group_nr:=0, @interval_end:=0) tmp
) AS t
GROUP BY t.group_number;
Please check and say whats wrong
这篇关于按mysql / MariaDB中的变量范围进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
