ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите с запросом - 15 полей, несколько группировок
17 сообщений из 17, страница 1 из 1
Помогите с запросом - 15 полей, несколько группировок
    #38337725
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
Здравствуйте!

Помогите, пожалуйста, с реализацией запроса.

Не могу понять каким образом лучше сделать -

есть таблица

CREATE TABLE tbl(id INT PRIMARY KEY, p1 INT, p2 INT, p3 INT, p4 INT, ..., p15 INT);

Поля p(n) - принимают значения от [0;2]

Необходимо вывести записи, у которых все поля p(n) уникальные, две записи, которые различаются только по 1 полю (если таких пар больше чем одна, то вывести все, если записей отличающихся на одно поле больше чем 2, то не выводить), 3 записи, различающиеся по 2 полям и так до 5 полей

Т.е. в результате должно получиться что-то подобное

Единственные записи:
1 | 211111211121111
3 | 201010201020101
Записи различающиеся на одну
234 | 201111111011111
412 | 101111111011110
-----------------------
123 | 100000000000000
321 | 000000000000000
Записи, различающиеся на две
111 | 120000000000000
112 | 010000000000000
113 | 210000000000000
-----------------------
114 | 222122222201111
115 | 222022222211111
222 | 222222222211111

И т.д.

Что примерно можно сделать, оптимальное с точки зрения производительности?
С уникальными 15 записями все легко, выбрать записи с уникальным параметрами, но как сделать остальное.

Либо регулярки, либо группировки по всем возможным вариантам, больше ничего не приходит в голову

Заранее спасибо
...
Рейтинг: 0 / 0
20.07.2013, 04:52:42
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38337729
MicrosoftProjectRU
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MicrosoftProjectRU
Участник
Если записей не много, то я бы просто "перевернул" таблицу как (id, N, p)

Ранжированием перевернутой таблицы получил бы чего хотел, потом через join с основной вывел бы колонки как надо.
...
Рейтинг: 0 / 0
20.07.2013, 06:08:40
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38337731
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
ну порядка 100 тысяч записей...
...
Рейтинг: 0 / 0
20.07.2013, 06:46:35
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38337892
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
Нормализовал таблицу, сделав ее вида

id | n | p

Добавил такой запрос, на выборку всех записей с совпадающими значениями по 15 параметрам:

SELECT t1.id, t2.id, count(*) AS c FROM
test.T t1
INNER JOIN test.T as t2 on t1.n = t2.n AND t1.p = t2.p
GROUP BY t1.id, t2.id
HAVING c = 15

Но запрос получается очень тяжелый... выполняется слишком долго при 100 000 записях, можно ли это как-то оптимизировать?
...
Рейтинг: 0 / 0
20.07.2013, 15:37:09
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338041
MicrosoftProjectRU
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MicrosoftProjectRU
Участник
Тыкайте индексы.

Я в индексах MySQL не спец, но по аналогии с MS SQL сделал был составной индекс как в порядке аргументов запроса.

Не знаю есть ли в MySQL аналог "кластерного индекса" как MS SQL, т.е. с физическим упорядывачиванием записей.

Но в таких запросах я предпочитаю его, т.к. тогда SQL-сервер фактически почти как Table Scan по кусочку таблицы делает. В MS SQL очень быстро такое.
...
Рейтинг: 0 / 0
21.07.2013, 00:39:42
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338052
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ManWithCloseMind,

1. будет полезнее, если вы хотябы намекнете смысл запроса,
ну напромер: строин сайт знакомс и ишем совпадение профайлов
по всем N атрибутам, а затем по N-1, N-2, N-3 и N-4 совпадений.

2. можно сделаь примерно так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select user_id, count(1) AS sovpadeniya 
from user_profile
where
p1 = $p1_value OR
p2 = $p2_value OR
p3 = $p3_value OR
....
pN = $pN_value 
group by user_id
order by sovpadeniya desc



Это будет скорее всего фул скан

3. Перепишите предыдуший СКЛ в виде юниона и будет по индексу.

4. вертикально-сосисочный (EAV) вариант будет работать аналогично.
...
Рейтинг: 0 / 0
21.07.2013, 01:09:04
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338057
MicrosoftProjectRU
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MicrosoftProjectRU
Участник
В принципе есть еще один секретный приемчик "фальшивый ключ"

В смысле склеить стринг из p(N) отображающий их комбинации и по нему сделать индекс.

Когда много полей и таблица большая я это делал несколько раз. Причем если умело присобачить сбоку еще индекс заведомо усекающий таблицу до кусочка 100-1000 записей, то можно агрессивно использовать Like даже.

Просто SQL-сервер первым индексом оторвет нужные записи, а остальные переберет по Like. Не знаю как в MySQL, но до 1000 записей в MS SQL переборы даже быстрее индексов обычно.
...
Рейтинг: 0 / 0
21.07.2013, 01:29:59
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338078
Фотография ПЕНСИОНЕРКА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПЕНСИОНЕРКА
Участник
ManWithCloseMind,

решение из другой республики
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT (t1.[z1]=t2.z1)+(t1.[z2]=t2.z2)+(t1.[z3]=t2.z3)
+(t1.[z4]=t2.z4)+(t1.[z5]=t2.z5)+(t1.[z6]=t2.z6)
+(t1.[z7]=t2.z7)+(t1.[z8]=t2.z8)+(t1.[z9]=t2.z9)
+(t1.[z10]=t2.z10)+(t1.[z11]=t2.z11)+(t1.[z12]=t2.z12)
+(t1.[z13]=t2.z13)+(t1.[z14]=t2.z14)+(t1.[z15]=t2.z15)+15 AS ne_sowpadenii, t1.z0, t2.z0
FROM t1, t1 AS t2
WHERE ((((t1.[z1]=t2.z1)+(t1.[z2]=t2.z2)+(t1.[z3]=t2.z3)
+(t1.[z4]=t2.z4)+(t1.[z5]=t2.z5)+(t1.[z6]=t2.z6)
+(t1.[z7]=t2.z7)+(t1.[z8]=t2.z8)+(t1.[z9]=t2.z9)
+(t1.[z10]=t2.z10)+(t1.[z11]=t2.z11)+(t1.[z12]=t2.z12)
+(t1.[z13]=t2.z13)+(t1.[z14]=t2.z14)+(t1.[z15]=t2.z15)+15)<5) AND ((t1.z0)<[t2].[z0]))
ORDER BY (t1.[z1]=t2.z1)+(t1.[z2]=t2.z2)+(t1.[z3]=t2.z3)
+(t1.[z4]=t2.z4)+(t1.[z5]=t2.z5)+(t1.[z6]=t2.z6)
+(t1.[z7]=t2.z7)+(t1.[z8]=t2.z8)+(t1.[z9]=t2.z9)
+(t1.[z10]=t2.z10)+(t1.[z11]=t2.z11)+(t1.[z12]=t2.z12)
+(t1.[z13]=t2.z13)+(t1.[z14]=t2.z14)+(t1.[z15]=t2.z15)+15, t1.z0, t2.z0;
...
Рейтинг: 0 / 0
21.07.2013, 02:54:32
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338079
Фотография ПЕНСИОНЕРКА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПЕНСИОНЕРКА
Участник
ManWithCloseMind,

результат
ne_sowpadeniit1.z0t2.z01111123111211311123211115222112332121111122111113211132121121232113123211332121141152114222223441241234
...
Рейтинг: 0 / 0
21.07.2013, 02:57:21
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338087
MicrosoftProjectRU
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MicrosoftProjectRU
Участник
В принципе MS SQL по такой запрос сам сделает Work Table и даже накрутит сверху виртуальных индексов.

Сделает ли так оптимайзер MySQL?

Но если это не делать, то мне кажется он будет выполняться очень долго.
...
Рейтинг: 0 / 0
21.07.2013, 04:14:17
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338309
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
Попробовал сделать в MS SQL Server 2008, создал кластерный индекс по id и p, все-равно запрос выполнения для 5000 записей (в развернутом виде 75000) выполняется все долго... порядка часа на выполнение =(
...
Рейтинг: 0 / 0
21.07.2013, 18:55:51
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338310
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
тестируется все на corei5 2.5gz, 4 гб рам
...
Рейтинг: 0 / 0
21.07.2013, 18:56:57
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338317
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
javajdbc,

к сожалению, нужно искать не по параметру... был бы параметр, было бы намного легче, нужно именно искать количество совпадения строк
...
Рейтинг: 0 / 0
21.07.2013, 19:08:19
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338321
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
ПЕНСИОНЕРКА,

в принципе, как альтернатива, только по оптимальности, такой запрос вряд ли выйдет лучше...

Мне вообще кажется, что для данной задачи нужны большие вычислительные мощности, хотелось бы, чтобы 5000 записей с 15 параметрами считались хотя бы за минут 10. Как индексы не расставляй, все равно выходит около часа((
...
Рейтинг: 0 / 0
21.07.2013, 19:13:00
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338329
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ManWithCloseMindjavajdbc,

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

опять же, лучше выдавать бизнес смысл задачи.
Будет намного понятнее что к чему.

Похоже что ПЕНСИОНЕРКА вам выдала нормальное решение --
если задача действительно перебрать ВСЕ пары в базе
и найти какие-то совпадения (или какието несовпадения).
Щта задача не имеет оптимизации ибо надо перебрать ВСЕ пары,
полный картезиан.
Скорее всего репение и не требует оптимизации ибо
имеет смысл как одноразовая задача.
хоть час, хоть 10 часов одноразово.
Если вам надо гонять эту задачу раз в 10 минут --- значит что
то не то в голове у бизнесс аналиста.
...
Рейтинг: 0 / 0
21.07.2013, 19:24:48
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338338
ManWithCloseMind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ManWithCloseMind
Гость
К сожалению, бизнес-условия я рассказать не могу, просто примите это условие - загружается матрица из 100 записей и количество совпадений должно рассчитываться за указанное время.

Я просто предполагал, что может есть какое-то решение, позволяющее эффективно выполнить эту задачу

Но как не крути, все-равно выходит картезиан =/
...
Рейтинг: 0 / 0
21.07.2013, 19:41:01
| Ответить | Цитировать | Написать  
Помогите с запросом - 15 полей, несколько группировок
    #38338515
MicrosoftProjectRU
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MicrosoftProjectRU
Участник
ManWithCloseMindПопробовал сделать в MS SQL Server 2008, создал кластерный индекс по id и p, все-равно запрос выполнения для 5000 записей (в развернутом виде 75000) выполняется все долго... порядка часа на выполнение =(

Вы не умеете его готовить :)

Вам нужно кластерный индекс не по id, а так как в порядке group by. Точнее первые 2 уровня обычно прикинув, что при применении такого индекса осталось бы в листе не более 100-1000 записей.

Не помешает принудительно указать MS SQL такой индекс, т.к. оптимизатор к составным индексам относится хмуро и может его отбросить. Поэтому заглянуть в Query Plan не помешает.
...
Рейтинг: 0 / 0
22.07.2013, 00:50:17
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите с запросом - 15 полей, несколько группировок
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38338329&tid=1836414]:
 0ms
get settings:
 7ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 41ms
get topic data:
 6ms
get forum data:
 2ms
get page messages:
 30ms
get tp. blocked users:
 1ms
others: 220ms
total:  320ms
созд. / загр.: 320ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]