select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number + 1)
where
fn2.Number is null
union
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number - 1)
where
fn2.Number is null
запрос Mysql - сложный
Приветствую всех!
Имеется БД в MYSQL содержащая номера и организации за которыми они закреплены.
Пример:
номер фирма
2000001 1
2000002 1
2000003 1
2000004 1
2000010 1
2000011 1
2000012 1
2000100 2
2000101 2
2000102 2
2000103 2
и т.д.
Нужно вытащить все диапазоны по каждой фирме:
2000001 1 - начало первого диапазона фирмы 1
2000004 1 - конец первого диапазона фирмы 1
2000010 1 - начало второго диапазона фирмы 1
2000012 1 - конец второго диапазона фирмы 1
2000100 2 - начало первого диапазона фирмы 2
2000103 2 - конец первого диапазона фирмы 2
и т.д.
Смысл думаю понятен.
Как это реализовать в MYSQL запросе???
Имеется БД в MYSQL содержащая номера и организации за которыми они закреплены.
Пример:
номер фирма
2000001 1
2000002 1
2000003 1
2000004 1
2000010 1
2000011 1
2000012 1
2000100 2
2000101 2
2000102 2
2000103 2
и т.д.
Нужно вытащить все диапазоны по каждой фирме:
2000001 1 - начало первого диапазона фирмы 1
2000004 1 - конец первого диапазона фирмы 1
2000010 1 - начало второго диапазона фирмы 1
2000012 1 - конец второго диапазона фирмы 1
2000100 2 - начало первого диапазона фирмы 2
2000103 2 - конец первого диапазона фирмы 2
и т.д.
Смысл думаю понятен.
Как это реализовать в MYSQL запросе???
Имхо, никак. Чтобы реализовать это необходимы итерации по всем номерам, что стандартным SQL естественно не поддерживается (в MSSQL это в принципе возможно с помощью курсоров)
Тут может помочь либо нормализация базы (т.к. такое ощущение, что структура таблиц не удовлетворяет требованиям предметной области), либо вычисление начала и конца интервала на клиенте...
Тут может помочь либо нормализация базы (т.к. такое ощущение, что структура таблиц не удовлетворяет требованиям предметной области), либо вычисление начала и конца интервала на клиенте...
Цитата: GreenRiver
Имхо, никак. Чтобы реализовать это необходимы итерации по всем номерам, что стандартным SQL естественно не поддерживается (в MSSQL это в принципе возможно с помощью курсоров)
Тут может помочь либо нормализация базы (т.к. такое ощущение, что структура таблиц не удовлетворяет требованиям предметной области), либо вычисление начала и конца интервала на клиенте...
Тут может помочь либо нормализация базы (т.к. такое ощущение, что структура таблиц не удовлетворяет требованиям предметной области), либо вычисление начала и конца интервала на клиенте...
А если запрос в запросе сделать?
наверное через темпорари таблицы можно извернуться...
если диапазон подразумевает то что значения идут подряд, то искомые данные может достать вот такой запрос:
Код:
Цитата: Smoke88
А если запрос в запросе сделать?
Можно найти верхнюю и нижнюю границу для конкретного диапазона (по разрядно, начиная с максимально возможного):
Код:
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) = 0) AND
(number % (10 ^ 1) / (10 ^ 0) <> 0) ORDER BY firm
(number % (10 ^ 5) / (10 ^ 4) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) <> 0) ORDER BY firm
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) = 0) AND
(number % (10 ^ 1) / (10 ^ 0) <> 0) ORDER BY firm
Т.е. идея выделять конкретный разряд начиная с самого старшего, и проверять не равен ли он нулю, из найденных группировать по фирме и выбирать наибольшее и наименьшее.
UPD: не очень помню синтаксис в MySQL - у меня знак деления подразумевает деление нацело, а % - остаток от деления.
GreenRiver:
#1221 - Incorrect usage of UNION and ORDER BY
#1221 - Incorrect usage of UNION and ORDER BY
SomewherSomehow
поясни, что такое @FirmNumber и fn1, fn2 ??
поясни, что такое @FirmNumber и fn1, fn2 ??
мда....
Это так таблица называется, тестовая, которую я использовал у себя, чтоб проверить запрос, синтаксис MS SQL. Вместо @FirmNumber, поставь название своей таблицы, а fn1, fn2 - альясы....
Это так таблица называется, тестовая, которую я использовал у себя, чтоб проверить запрос, синтаксис MS SQL. Вместо @FirmNumber, поставь название своей таблицы, а fn1, fn2 - альясы....
Цитата: SomewherSomehow
если диапазон подразумевает то что значения идут подряд, то искомые данные может достать вот такой запрос:
А если данные такие:
Код:
2000001 1
2000002 1
... и т.д. подряд
2000008 1
2000009 1
2000010 1
2000011 1
2000012 1
2000002 1
... и т.д. подряд
2000008 1
2000009 1
2000010 1
2000011 1
2000012 1
Но тем не менее интересный вариант!
ну а если вдруг в таблице не по порядку? может сначала сделать селект во временную таблицу с употреблением ORDER BY?
upd. только сегодня узнал что в мускуле нету циклов... конечно в МС Скуэле они извращенские, но они есть. А без них - фи... ))))
upd. только сегодня узнал что в мускуле нету циклов... конечно в МС Скуэле они извращенские, но они есть. А без них - фи... ))))
Ну если такие данные, то тоже выдаст правильно:
2000001 1
2000012 1
я так понял что автор подразумевает под диапазоном - непрерывность значений, по этому и уточнил...как только значения подряд прерываются - диапазон заканчивается.
2000001 1
2000012 1
я так понял что автор подразумевает под диапазоном - непрерывность значений, по этому и уточнил...как только значения подряд прерываются - диапазон заканчивается.
2Washington:
JOIN-у всеравно в каком порядке идут данные, можно и не по-порядку, результат не изменится...
Если конечно речь идет о финальном выводе, чтоб выводилось:
2000001 1
2000012 1
а не
2000021 1
2000001 1
то можно добавить в конце order by, но это уже частности, думаю основная идея понятна и без этого =)
JOIN-у всеравно в каком порядке идут данные, можно и не по-порядку, результат не изменится...
Если конечно речь идет о финальном выводе, чтоб выводилось:
2000001 1
2000012 1
а не
2000021 1
2000001 1
то можно добавить в конце order by, но это уже частности, думаю основная идея понятна и без этого =)
запустил вариант SomewherSomehow.
задумался mysql....
строк в бд порядка 8 млн. :)
задумался mysql....
строк в бд порядка 8 млн. :)
Цитата: Smoke88
запустил вариант SomewherSomehow.
задумался mysql....
строк в бд порядка 8 млн. :)
задумался mysql....
строк в бд порядка 8 млн. :)
Делайте индекс на номер.
UPD: хотя прошу прощения, индекс наверное уже есть - 8 млн. все-таки не шутки
Цитата: Smoke88
GreenRiver:
#1221 - Incorrect usage of UNION and ORDER BY
#1221 - Incorrect usage of UNION and ORDER BY
Действительно тупанул :) Тогда так:
Код:
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) = 0) AND
(number % (10 ^ 1) / (10 ^ 0) <> 0)
(number % (10 ^ 5) / (10 ^ 4) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) <> 0)
UNION
SELECT min(number), max(number), firm FROM table WHERE
(number % (10 ^ 5) / (10 ^ 4) = 0) AND
(number % (10 ^ 4) / (10 ^ 3) = 0) AND
(number % (10 ^ 3) / (10 ^ 2) = 0) AND
(number % (10 ^ 2) / (10 ^ 1) = 0) AND
(number % (10 ^ 1) / (10 ^ 0) <> 0)
Цитата: SomewherSomehow
если диапазон подразумевает то что значения идут подряд, то искомые данные может достать вот такой запрос:
Код:
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number + 1)
where
fn2.Number is null
union
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number - 1)
where
fn2.Number is null
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number + 1)
where
fn2.Number is null
union
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number - 1)
where
fn2.Number is null
Прошел час. Запрос так и не обработался. :(
Я его убил, ждать не стал, т.к. такое время недопустимо.
Тогда, как мне кажется, надо рассмотерть какие-то архитектурные изменения.
Быть может выполнить запрос один раз, а результаты куда-то сохранить, либо выполнять запрос не каждый раз по всему массиву данных, а частично...
Либо посмотреть в сторону индексов, как уже говорилось выше...Быть может посмотреть какие-то приемы оптимизации характерные именно для этой СУБД.
Т.к. по эффективности кода чистого SQL запроса тут особо наворотов нет, и тормозов из-за этого быть не должно, единственное что я сам вижу - используется лефт аутер джоин который медленнее иннер джойна, но в любом случае, даже если джойнить 8 млн с 8 млн записей - это в разы быстрее циклов и курсоров по данным (согласно оптимизатору MS SQL, за MySQL - ничего не могу сказать).
Быть может выполнить запрос один раз, а результаты куда-то сохранить, либо выполнять запрос не каждый раз по всему массиву данных, а частично...
Либо посмотреть в сторону индексов, как уже говорилось выше...Быть может посмотреть какие-то приемы оптимизации характерные именно для этой СУБД.
Т.к. по эффективности кода чистого SQL запроса тут особо наворотов нет, и тормозов из-за этого быть не должно, единственное что я сам вижу - используется лефт аутер джоин который медленнее иннер джойна, но в любом случае, даже если джойнить 8 млн с 8 млн записей - это в разы быстрее циклов и курсоров по данным (согласно оптимизатору MS SQL, за MySQL - ничего не могу сказать).
Решить эту задачку одним sql-запросом
на таблице с 8 млн записей
и всего то за 15 минут?
Запросто!
Купите мощный компьютер :)
Легче использовать временную таблицу и скрипт, выполняемый на клиенте. Для решения задачки потребуется одно полное сканирование таблицы.
на таблице с 8 млн записей
и всего то за 15 минут?
Запросто!
Купите мощный компьютер :)
Легче использовать временную таблицу и скрипт, выполняемый на клиенте. Для решения задачки потребуется одно полное сканирование таблицы.
Ну кстати даже 15 минут это перебор =)
Счас ради интереса создал в тестовой БД таблицу, индекс по полю номер, и залил в нее 7 млн записей.
СУБД MS SQL 2005, сервер 2 процессорный, 2 гб памяти, далеко не новый и тем не менее, вот результат рис.1 - время 2 минуты 9 секунд,
если немного оптимизировать при помощи хинтов в джойнах, получится еще чуть быстрее, время 1 минута 50 секунд...рис.2
Так что тут либо совсем убитое железо, либо сервер еле шевелится, либо, что вероятнее, какой-то косяк в самой бд =)
Счас ради интереса создал в тестовой БД таблицу, индекс по полю номер, и залил в нее 7 млн записей.
СУБД MS SQL 2005, сервер 2 процессорный, 2 гб памяти, далеко не новый и тем не менее, вот результат рис.1 - время 2 минуты 9 секунд,
если немного оптимизировать при помощи хинтов в джойнах, получится еще чуть быстрее, время 1 минута 50 секунд...рис.2
Так что тут либо совсем убитое железо, либо сервер еле шевелится, либо, что вероятнее, какой-то косяк в самой бд =)
SQLSERVER и MYSQL сравнивать как-то и нехорошо. :)
на SQLSERVER надо использовать индекс на обе колонки и minus - ещё быстрее будет
на SQLSERVER надо использовать индекс на обе колонки и minus - ещё быстрее будет
Думаете, что если создать на одинаковых машинах, одинаковые по размеру таблицы в 7 млн строк, но с использованием мускуль и сиквел сервер - результаты времени выполнения будут так разниться? =)
Давайте еще оракл приплетем и с помощью его фишек сделаем запрос - еще быстрее, только, думаю, автору темы от этого не станет легче =)
Я намекаю на то, что неплохо бы посмотреть в сторону оптимизации бд, софта или железок, если простые, по-сути, запросы так долго выполняются! Ну или на худой конец изменить архитектуру или логику работы...
Давайте еще оракл приплетем и с помощью его фишек сделаем запрос - еще быстрее, только, думаю, автору темы от этого не станет легче =)
Я намекаю на то, что неплохо бы посмотреть в сторону оптимизации бд, софта или железок, если простые, по-сути, запросы так долго выполняются! Ну или на худой конец изменить архитектуру или логику работы...
При таком объёме разница будет заметной.
Поддерживаю намек. :)
Поддерживаю намек. :)
Скорее всего нет индекса на поле с номером, тогда такие тормоза вполне логичны... Кстати что на счет этого?
Цитата: SomewherSomehow
Я намекаю на то, что неплохо бы посмотреть в сторону оптимизации бд, софта или железок, если простые, по-сути, запросы так долго выполняются! Ну или на худой конец изменить архитектуру или логику работы...
Железо - 2 ядра, 1 гиг памяти. думаю должно хватать.
По оптимизации БД давайте посмотрим (я не асс в sql и не гордый :) ):
БД содержит 2 колонки:
1. `num` - INT (10) - установлен первичный индекс
2. `zakrep` - INT (3)
Данные в поле `num` идут последовательно в сторону увеличения от 2000000 до 9999999.
Запрос типа
SELECT *
FROM `numer`
WHERE `zakrep` =0
LIMIT 0 , 30
запрос занял 0.0012 сек.)
Делаю вывод, значит я неверно задал сам запрос. В моих терминах и в терминах mysql он так должен выглядеть????
select
fn1.num,
fn1.zakrep
from
numer fn1
left join numer fn2 on (fn1.num= fn2.num+ 1)
where
fn2.num is null
union
select
fn1.num,
fn1.zakrep
from
numer fn1
left join numer fn2 on (fn1.num= fn2.num- 1)
where
fn2.num is null
может alias все же надо через "as" задавать?? Хотя синтаксис запроса по идее верный, ошибок то не вылезло никаких...
Раз никто пока не отвечает, решил еще раз попробовать дома.
запрос представил в таком виде:
запрос представил в таком виде:
Цитата:
select
fn1.num, fn1.zakrep
from
numer as fn1
left join numer as fn2 on (fn1.num = fn2.num + 1)
where
fn2.num is null
union
select
fn1.num, fn1.zakrep
from
numer as fn1
left join numer as fn2 on (fn1.num= fn2.num - 1)
where
fn2.num is null
fn1.num, fn1.zakrep
from
numer as fn1
left join numer as fn2 on (fn1.num = fn2.num + 1)
where
fn2.num is null
union
select
fn1.num, fn1.zakrep
from
numer as fn1
left join numer as fn2 on (fn1.num= fn2.num - 1)
where
fn2.num is null
Запустил. Жду когда отработает.
Прошло 12 минут. Загрузка ЦП 50%. Ответа нет :(
Ушел в себя :)
Ушел в себя :)
Незнаю я как у SomewherSomehow отработал запрос, но укоротив БД до 6500 записей и запустив такой запрос, он вернул неверный результат (первый и последний номер).
Исполнялся запрос 38 секунд. Полный запрос с такой скоростью был бы готов через 10 часов. :(
Исполнялся запрос 38 секунд. Полный запрос с такой скоростью был бы готов через 10 часов. :(
Пожалуйста, определи время выполнения на своей базе такого запроcа
select sum( zakrep) from numer;
и огласи его
select sum( zakrep) from numer;
и огласи его
2.1324 сек
Цитата: SomewherSomehow
если диапазон подразумевает то что значения идут подряд, то искомые данные может достать вот такой запрос:
Код:
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number + 1)
where
fn2.Number is null
union
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number - 1)
where
fn2.Number is null
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number + 1)
where
fn2.Number is null
union
select
fn1.Number,
fn1.FirmID
from
@FirmNumber fn1
left join @FirmNumber fn2 on (fn1.Number = fn2.Number - 1)
where
fn2.Number is null
Вопрос к SomewherSomehow: покажи исходные данные к твоему запросу?
Похоже на то, что у тебя диапазоны с разрывами идут.
При сплошном массиве номеров условие
left join @FirmNumber fn2 on (fn1.Number = fn2.Number + 1)
where fn2.Number is null
по идее не должно сработать! Запрос вернет только начало и конец всего массива номеров!
Или я не прав? :)
Все получилось!
СПАСИБО SomewherSomehow!!
Пусть даже таблица имеет сплошной спектр по нумерации, никто ведь не запрещает сделать выборку сначала по конкретной фирме, а потом уже, получив разрывы в нумерации, применять запрос SomewherSomehow!!
Буду использовать вариант с формированием временной таблицы. Формирование которой буду делать сам раз в неделю например, или реже :). И давать к ней доступ клиенту.
Так будет эффективнее.
Всем спасибо!
СПАСИБО SomewherSomehow!!
Пусть даже таблица имеет сплошной спектр по нумерации, никто ведь не запрещает сделать выборку сначала по конкретной фирме, а потом уже, получив разрывы в нумерации, применять запрос SomewherSomehow!!
Буду использовать вариант с формированием временной таблицы. Формирование которой буду делать сам раз в неделю например, или реже :). И давать к ней доступ клиенту.
Так будет эффективнее.
Всем спасибо!
Ого! Какие тут страсти кипели!=)
Да, если сплошной диапазон первое и последнее, т.к. я подразумевал под диапазоном непрерывность значений, о чем писал в самом первом ответе, так что если спошной массив - то первое и последнее, что и логично в общем-то. Т.е. диапазоном будет являться весь массив данных, если он непрерывен.
Кстати если таблица имеет сплошной спектр по нумерации (я-то думал что она разрывная всегда), и значения перемежаются только ид-шками фирм - т.е. например как-то так:
Number FirmID
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 1
и т.д.
то чтобы получить результаты не делая предварительного запроса по фирме, можно добавить в джоин условие соединение по ид-шке фирмы.
Ну и в любом случае, поздравляю с победой над MySQL =))
Да, если сплошной диапазон первое и последнее, т.к. я подразумевал под диапазоном непрерывность значений, о чем писал в самом первом ответе, так что если спошной массив - то первое и последнее, что и логично в общем-то. Т.е. диапазоном будет являться весь массив данных, если он непрерывен.
Кстати если таблица имеет сплошной спектр по нумерации (я-то думал что она разрывная всегда), и значения перемежаются только ид-шками фирм - т.е. например как-то так:
Number FirmID
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 1
и т.д.
то чтобы получить результаты не делая предварительного запроса по фирме, можно добавить в джоин условие соединение по ид-шке фирмы.
Код:
...on (fn1.Number = fn2.Number + 1 and fn1.FirmID = fn2.FirmID)
...on (fn1.Number = fn2.Number - 1 and fn1.FirmID = fn2.FirmID)
...on (fn1.Number = fn2.Number - 1 and fn1.FirmID = fn2.FirmID)
Ну и в любом случае, поздравляю с победой над MySQL =))
а если order by firma asc, numbers asc?
Как угодно...порядок следования рядов значения не имеет...