Как я могу сделать непрерывную группу в MySQL?
Как я могу вернуть то, что фактически было бы "непрерывным" GROUP BY в MySQL. Другими словами, GROUP BY, который уважает порядок набора записей?
Например, SELECT MIN(col1), col2, COUNT(*) FROM table GROUP BY col2 ORDER BY col1 из следующей таблицы, где col1 - уникальный упорядоченный индекс:
1 а 2 а 3 б 4 б 5 а 6 а
возвращает:
1 а 4 3 б 2
но мне нужно вернуть следующее:
1 а 2 3 б 2 5 а 2
6 ответов
Использование:
SELECT MIN(t.id) 'mi',
t.val,
COUNT(*)
FROM (SELECT x.id,
x.val,
CASE
WHEN xt.val IS NULL OR xt.val != x.val THEN
@rownum := @rownum+1
ELSE
@rownum
END AS grp
FROM TABLE x
JOIN (SELECT @rownum := 0) r
LEFT JOIN (SELECT t.id +1 'id',
t.val
FROM TABLE t) xt ON xt.id = x.id) t
GROUP BY t.val, t.grp
ORDER BY mi
Ключевым моментом здесь было создание искусственного значения, которое позволило бы группировать.
Ранее исправленный ответ Гуффы:
SELECT t.id, t.val
FROM TABLE t
LEFT JOIN TABLE t2 on t2.id + 1 = t.id
WHERE t2.val IS NULL
OR t.val <> t2.val
Если числа в столбце смежны, вы можете сделать это так:
select x.col1, x.col2
from table x
left join table y on x.col1 = y.col1 + 1
where x.col2 <> isnull(y.col2, '')
Это работает так:
-x- -y- out
1 a - - 1 a
2 a 1 a
3 b 2 a 3 b
4 b 3 b
5 a 4 b 5 a
6 a 5 a
Та же логика, что и у rexem, но работает на любой оконной СУБД с поддержкой окон (пока не будет работать на MySQL):
CREATE TABLE tbl
(
id INT,
val VARCHAR(1)
);
INSERT INTO tbl(id,val)
VALUES(1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'b'),(6,'b'),(7,'a'),(8,'a'),(9,'a');
источник:
1 a
2 a
3 a
4 a
5 b
6 b
7 a
8 a
9 a
Запрос в оконном стиле: (работает на оконно-способных rdbms):
WITH grouped_result AS
(
SELECT x.id, x.val,
COUNT(CASE WHEN y.val IS NULL OR y.val <> x.val THEN 1 END)
OVER (ORDER BY x.id) AS grp
FROM tbl x LEFT JOIN tbl y ON y.id + 1 = x.id
)
SELECT MIN(id) mi, val, COUNT(*)
FROM grouped_result
GROUP BY val, grp
ORDER BY mi
Выход:
1 a 4
5 b 2
7 a 3
Кстати, это результат grouped_result без GROUP BY:
1 a 1
2 a 1
3 a 1
4 a 1
5 b 2
6 b 2
7 a 3
8 a 3
9 a 3
Чувствует себя хорошо, переписывая mysqlism-запрос к ANSI-совместимому:-) На данный момент, хотя у mysql пока нет возможностей для работы с окнами, ответ rexem является лучшим. Rexem, это хороший метод mysql (JOIN (SELECT @rownum: = 0)), и afaik MSSQL и PostgreSQL не поддерживают неявно объявленную переменную, слава!:-)
Вот более длинное описание по существу того же (я думаю) решения, которое предлагает omg-ponies - "создать искусственную ценность, которая позволила бы группировать".
Это не сработает:
SELECT min_col1 = MIN(col1), col2
FROM table
GROUP BY col2
ORDER BY min_col1
Возможно это?
SELECT min_col1, col2
FROM ( SELECT min_col1 = MIN(col1), col2
FROM table
GROUP BY col2 ) x
ORDER BY min_col1
Создайте идентификатор сегмента, вычтя позицию в категории из общей позиции записи. Затем этот идентификатор сегмента можно использовать в GROUP BY.
create table temp1 (i int, c char);
insert into temp1 values (1,'a'),(2,'a'),(3,'b'),(4,'b'),(5,'a'),(6,'a');
with q1 as (
select *,
row_number() over (order by i) - row_number() over (partition by c order by i) as segment
from temp1 )
select min(i), c, count(*)
from q1
group by c, segment
order by min(i);
Я знаю, что этот вопрос был задан два с половиной года назад (и я не ожидаю никакого возражения), но я только что столкнулся с точно такой же проблемой, за исключением того, что "таблица" уже была очень сложным оператором SQL, поэтому я не мог сделать любое соединение без копирования-вставки
Таким образом, у меня была другая идея: упорядочить по col2 и вычесть текущий номер строки в значение col1
SELECT *, col1-(@rownum:=@rownum+1) FROM (SELECT * FROM table JOIN (SELECT @rownum:=0) AS i ORDER BY col2) AS t
Что дает такой результат:
1 a 0
2 a 0
5 a 2
6 a 2
3 b -2
4 b -2
Теперь вам просто нужно сгруппировать по значению последнего столбца
SELECT MIN(col1) AS mi, col2, COUNT(*) FROM
(SELECT *, col1-(@rownum:=@rownum+1) AS grp FROM (SELECT * FROM table JOIN (SELECT @rownum:=0) AS i ORDER BY col2) AS t) AS x
GROUP BY grp ORDER BY mi