Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Есть огромная таблица из 3 полей

Нужно сгруппировать повторы больше 4 и вывести

Вот так умею:

SELECT u1, MAX(u2), MIN(u2), COUNT(*) FROM u GROUP BY u1 HAVING COUNT(*) > 4

Но оно пишет только Мин u2 и Мах u2 - а мне нужно все

И лучше чтоб в 4 строки.

Спасибо.
1 ноя 20, 13:40    [22224308]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
kompospec,

Исходную таблицу нужно заджойнить на результат вашего запроса...
Или если версия MySQL позволяет - использовать оконные функции

Сообщение было отредактировано: 1 ноя 20, 14:40
1 ноя 20, 14:44    [22224346]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Щукина Анна
kompospec,

Исходную таблицу нужно заджойнить на результат вашего запроса...
Или если версия MySQL позволяет - использовать оконные функции


А кода нельзя привести? Спасибо.
1 ноя 20, 14:47    [22224348]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
kompospec,

select u.*, v.cnt
  from u
  join (
         SELECT u1
              , count(1) as cnt
           FROM u 
          GROUP BY u1 
         HAVING COUNT(1) > 4
        )  as v
    on u.u1 = v.u1


/* На версиях от 8.0*/
select u.*
  from (
         SELECT u1
              , count(1) over(partition by u1) as cnt 
           FROM u
        ) as v
 where cnt > 4
1 ноя 20, 14:59    [22224358]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Щукина Анна
kompospec,

select u.*, v.cnt
  from u
  join (
         SELECT u1
              , count(1) as cnt
           FROM u 
          GROUP BY u1 
         HAVING COUNT(1) > 4
        )  as v
    on u.u1 = v.u1


/* На версиях от 8.0*/
select v.*
  from (
         SELECT u.*
              , count(1) over(partition by u1) as cnt 
           FROM u
        ) as v
 where cnt > 4
Пара опечаток закралось.... "Исправленному - верить"(с)
1 ноя 20, 15:11    [22224369]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Щукина Анна,

ого! Спасибо огромное. Как проверю - обязательно отпишусь
1 ноя 20, 15:51    [22224386]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Щукина Анна,

Работает!!!
Спасибо огромнейшее.
Первый раз вижу девушку умнее меня.
Спасибо.
1 ноя 20, 19:52    [22224478]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Рано я радовался. На локальной, небольшой базе работает, а на сервере - тормознутые-тормоза. Уже 15 мин считает.
1 ноя 20, 20:10    [22224482]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
kompospec
а на сервере - тормознутые-тормоза. Уже 15 мин считает.
А создать индекс u(u1)?
1 ноя 20, 21:57    [22224513]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Akina
kompospec
а на сервере - тормознутые-тормоза. Уже 15 мин считает.
А создать индекс u(u1)?


CREATE INDEX u1 ON u(u1);

Ошибка

SQL запрос: Копировать

CREATE INDEX u1 ON u(u1)

Ответ MySQL: Документация
#1170 - Столбец типа BLOB 'u1' был указан в определении ключа без указания длины ключа
2 ноя 20, 03:41    [22224573]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Akina,

Что-то я в этих индексах вообще ничего не понял.

Если кто расскажет - то спасиб, большое.
2 ноя 20, 03:45    [22224574]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Так поле u1 ещё и текстовое? ну-ну... DDL полный показывайте.
2 ноя 20, 07:40    [22224593]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Akina
Так поле u1 ещё и текстовое? ну-ну... DDL полный показывайте.


Я не знаю где брать этот ваш ДДЛ. Структура:

-- phpMyAdmin SQL Dump
-- version 4.4.15.7
-- http://www.phpmyadmin.net
--
-- Хост: 
-- Время создания: Окт 30 2020 г., 14:07
-- Версия сервера: 5.5.39-cll-lve
-- Версия PHP: 5.3.29

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- База данных: `*******`
--

-- --------------------------------------------------------

--
-- Структура таблицы `u`
--

CREATE TABLE IF NOT EXISTS `u` (
  `id` int(11) NOT NULL,
  `u1` tinytext CHARACTER SET utf8 NOT NULL,
  `u2` tinytext CHARACTER SET utf8 NOT NULL,
  `u3` tinytext CHARACTER SET utf8 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `u`
--
ALTER TABLE `u`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `u`
--
ALTER TABLE `u`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


Сообщение было отредактировано: 2 ноя 20, 12:22
2 ноя 20, 12:25    [22224728]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
id	u1	u2	u3 	
1627920 	62_68_74_75_80 	28-10-2020-1632 	
1627919 	58_68_74_75_80 	28-10-2020-1632 	
1627918 	58_62_74_75_80 	28-10-2020-1632 	
1627917 	58_62_68_75_80 	28-10-2020-1632 	


Миллион шестьсот записей

u1 - это самый столбец по которому идёт вся работа. Это цифры, в конечном итоге. _ - просто группировка
u2 - Дата - тоже можно взять другой формат
u3 - примечание. Его можно вообще удалить.

Сейчас загрузка по варианту SELECT u1, MAX(u2), MIN(u2), COUNT(*) FROM u GROUP BY u1 HAVING COUNT(*) > 4 - 3 сек

По варианту Анны - 45 сек

Буду очень рад если поможете с любым ускорением.

1 600 000 записей - это только начало.

Спасибо. Огромное.
2 ноя 20, 12:36    [22224736]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
u1 определено как tinytext

Переопределите его с указанием макс. длины (tinytext(255) или менее), и используйте ту же длину префикса при создании индекса.

Хотя я бы рекомендовал подумать насчёт добавить вычисляемое поле, значение которого равно хэшу поля u1, именно его индексировать и использовать для группировки. Хэш, само собой, подобрать достаточной длины, чтобы не сильно бояться коллизий.

Но всё равно - группировка полутора миллионов записей дело небыстрое.
2 ноя 20, 15:34    [22224999]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Данные в Базу поступают порциями по 16 тысяч записей с одинаковой датой.

Вероятно что не нужно ведь перелопачивать всю таблицу. А можно ведь просто пройтись с новыми данными по старым данным и 1 раз по всей таблице.

Упс. Круто. Я сам до этого додумался?
2 ноя 20, 15:37    [22225001]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
в mySQL есть что то подобное?
2 ноя 20, 15:39    [22225005]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Кстати, если поле имеет данные строго по указанному шаблону, типа 58_62_68_75_80, его можно обжать в достаточно компактный BLOB... а если ещё и количество групп постоянно равно пяти - то и вовсе в BIGINT.
2 ноя 20, 15:43    [22225011]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
kompospec
Вероятно что не нужно ведь перелопачивать всю таблицу. А можно ведь просто пройтись с новыми данными по старым данным и 1 раз по всей таблице.

Упс. Круто. Я сам до этого додумался?

kompospec
в mySQL есть что то подобное?

А вот всё это Ваше "тихо сам с собою" - вообще непонятно куда прислонить...
2 ноя 20, 15:44    [22225015]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Akina
u1 определено как tinytext

Переопределите его с указанием макс. длины (tinytext(255) или менее), и используйте ту же длину префикса при создании индекса.

Хотя я бы рекомендовал подумать насчёт добавить вычисляемое поле, значение которого равно хэшу поля u1, именно его индексировать и использовать для группировки. Хэш, само собой, подобрать достаточной длины, чтобы не сильно бояться коллизий.

Но всё равно - группировка полутора миллионов записей дело небыстрое.


Вы знаете - помогло. Точнее стало всё наоборот:

Мой метод стал дольше.
Метод Анны выполняется за 5 сек

А мой почему стал хуже?

Спасибо огромное.
2 ноя 20, 16:54    [22225091]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
kompospec
Member

Откуда:
Сообщений: 39
Что точно стало хуже - запись в базу новых 16 тысяч значений - уже 45 секунд. Ростёт. Было 5 сек.
2 ноя 20, 17:00    [22225101]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Сотрудник Главного Управления
Member

Откуда: Главное Управление
Сообщений: 66
kompospec
Спасибо. Огромное.
Такое же огромное, как ваша таблица?
2 ноя 20, 17:19    [22225134]     Ответить | Цитировать Сообщить модератору
 Re: Сгруппировать и вывести. Как?  [new]
Arhat109
Member

Откуда: из СССР
Сообщений: 3399
Н-да, второй раз встретил девушку умнее и значительно "погоромистов". ;)
поржал..
4 ноя 20, 12:31    [22226186]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить