Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 culumn search  [new]
Siquel
Guest
есть 2 таблицы :

create table t1(f1 int, f2 int, f3 int, f4 int)


create table t2(C1 int, C2 int, C3 int)


ЗАДАЧА :

из первой таблицы отобрать строки , в которых нет совпадений записей из второй таблицы

или на пальцах , для каждой записи т1 - если :
(с1=f1 OR c1=f2 OR c1=f3 OR c1=f4) AND
(с2=f1 OR c2=f2 OR c2=f3 OR c2=f4) AND
(с3=f1 OR c3=f2 OR c3=f3 OR c3=f4)
тогда отбрасываем запись , иначе - оставляем.

Как это реализовать причем таблицы довольно большие и нужно решение оптимальное по скорости !?
23 апр 04, 03:39    [644884]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
Это ?


SELECT
t1.*
FROM t1
WHERE NOT EXISTS (
SELECT *
FROM t2 ON (
t2.c1 IN (t1.f1, t.f2, t.f3, t.f4)
OR t2.c2 IN (t1.f1, t.f2, t.f3, t.f4)
OR t2.c3 IN (t1.f1, t.f2, t.f3, t.f4)
)
)


Скорость выполнения запроса зависит от многих причин, поэтому сложно
предложить вариант оптимальный на все случаи жизни, всегда надо знать
точную ситуацию. Какой бы запрос Вы не написали, оптимизатор всегда
скажет свое последнее веское слово...
23 апр 04, 04:15    [644888]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
только наверно в данной конструкции WHERE а не ON

SELECT
t1.*
FROM t1
WHERE NOT EXISTS (
SELECT *
FROM t2 WHERE
t2.c1 IN (t1.f1, t.f2, t.f3, t.f4)
OR t2.c2 IN (t1.f1, t.f2, t.f3, t.f4)
OR t2.c3 IN (t1.f1, t.f2, t.f3, t.f4))
23 апр 04, 05:54    [644904]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
> LexusR
Не наверное, а точно. Начинал писать один запрос, а кончилось другим...
23 апр 04, 14:07    [646271]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
Спасибо , только откорректирую - не ИЛИ а И планировалось :

SELECT

t1.*
FROM t1
WHERE NOT EXISTS (
SELECT *
FROM t2 WHERE
t2.c1 IN (t1.f1, t.f2, t.f3, t.f4)
AND t2.c2 IN (t1.f1, t.f2, t.f3, t.f4)
AND t2.c3 IN (t1.f1, t.f2, t.f3, t.f4))


и еще хотелось бы чтобы в условии WHERE количество совпадений было определенное кол-во то есть что-то вроде

WHERE

case t2.c1 IN (t1.f1, t1.f2, t1.f3, t1.f4) then @counter=@counter+1
case t2.c1 IN (t1.f1, t1.f2, t1.f3, t1.f4) then @counter=@counter+1
-- и в итоге сопоставляем значение @counter для искомых значений ,

-- возвращаемых по условию WHERE

t2.ID<;10 AND @counter>;10


вот как правильно этот синтаксис прописать ?
23 апр 04, 15:06    [646572]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
Последнее желание, насчет счетчика, очень невнятно...
Может пример приведете ?
23 апр 04, 15:22    [646651]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
И еще. В таблицах есть PRIMARY KEY ? И что он из себя представляет ?
23 апр 04, 15:26    [646675]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
Что касается примари кей - на данный момент не существует. И реально таблицы между собой никак не связаны.

что касается счетчика :

возьмем более общий вариант с двумя таблицами
create table t1(f1 int, f2 int, f3 int, f4 int)


create table t2(f1 int, f2 int, f3 int, f4 int, ID int)


нужно сопоставить столбцы таблиц , сделать выборку , в которой совпадения чисел из т2 в т1 для каждой записи будут встречаться (к примеру):
N1 раз при 0<ID<5
N2 раз при 5<ID<10
N3 раз при 10<ID<15

надеюсь так понятно будет. (Вот для чего в первой постановке задачи т2 была трансформирована из размерности т1 (4 колонки) в размерность N1)
23 апр 04, 15:49    [646785]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
> Siquel
> Что касается примари кей - на данный момент не существует

Этот подход чреват многими опасностями. Любая таблица должна иметь
набор полей однозначно идентифицирующий строку. Если я правильно
Вас понял, то в любой из таблиц из 1 примера могут встречаться
дублирующие строки ?
Остальное позже...
23 апр 04, 16:06    [646858]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
автор
Этот подход чреват многими опасностями. Любая таблица должна иметь
набор полей однозначно идентифицирующий строку.


ну создать такое поле в принципе не проблема .. а в чем собственно опастность ?

автор
Если я правильно
Вас понял, то в любой из таблиц из 1 примера могут встречаться
дублирующие строки ?


1-5 совпадений на 10 000 записей
23 апр 04, 16:15    [646891]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
автор
Если я правильно
Вас понял, то в любой из таблиц из 1 примера могут встречаться
дублирующие строки ?


стоп - если из первого - там где размерность таблиц разная - то да , но скажем так - вхождение полей т2 в записи таблицы т1, и встречаться это может чаще чем приведенные мной данные равноразмерных таблиц
23 апр 04, 16:19    [646909]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
автор

а в чем собственно опастность ?


С точки зрения теории - нарушение 2НФ, с точки зрения практики -
попробуйте обновить или удалить только одну из дублирующих строчек
с помощью чистого SQL, без всяких set rowcount и курсоров.
По поводу счетчика, пока так и не понял сути, но даже в этом случае,
как посчитать значение для каждой строки, если они дублируются.
Можете на предметном уровне объяснить, что Вы хотите ?
23 апр 04, 16:27    [646944]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
автор
с точки зрения практики -
попробуйте обновить или удалить только одну из дублирующих строчек
с помощью чистого SQL, без всяких set rowcount и курсоров


Ну во-первых ни в первой ни во второй таблице нет дублирующих строчек для каждой таблицы соответственно. Есть дубли т2 в т1.

По счетчику -

например :
т1
1,2,5,7
2,4,8,1

т2
2,5,9,6 (ID)1
7,3,4,5 (ID)2
4,3,7,9 (ID)3
6,2,4,8 (ID)4

из т1 нужно выбрать записи , в которых : встречаются 2 повтора в столбцах из т2 при ID<=3

или по другому - ищется условие когда в поле записей т2 найдено 2 цифры из диапазона записей 0<ID<=3 имеющихся в сопоставляемой записи из т1

имеем :
1,2,5,7


таким образом - делается или выборка из т1 или флаг прописывается в поле т1 при выполнении вышеобозначенного условия - то что быстрее может решить эту задачу.
23 апр 04, 16:47    [647013]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
причем таких условий нужно сделать несколько - по крайней мере 3 - вот для этих целей счетчик и предполагался.
23 апр 04, 16:58    [647058]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
Сказать что логика нестандартная, это значит ничего не сказать...
Может быть что-то в таком духе ?

SELECT
t1.*
,SUM(CASE WHEN t1.f1 IN (t2.f1, t2.f2, t2.f3, t2.f4) THEN 1 END) AS CntT1F1
,SUM(CASE WHEN t1.f2 IN (t2.f1, t2.f2, t2.f3, t2.f4) THEN 1 END) AS CntT1F2
,SUM(CASE WHEN t1.f3 IN (t2.f1, t2.f2, t2.f3, t2.f4) THEN 1 END) AS CntT1F3
,SUM(CASE WHEN t1.f4 IN (t2.f1, t2.f2, t2.f3, t2.f4) THEN 1 END) AS CntT1F4
FROM t1
INNER JOIN t2 ON (
t2.f1 IN (t1.f1, t1.f2, t1.f3, t1.f4)
AND t2.f2 IN (t1.f1, t1.f2, t1.f3, t1.f4)
AND t2.f3 IN (t1.f1, t1.f2, t1.f3, t1.f4)
AND t2.f4 IN (t1.f1, t1.f2, t1.f3, t1.f4)
)
WHERE t2.ID <=3
GROUP BY t1.f1, t1.f2, t1.f3, t1.f4
-- HAVING SUM(CASE WHEN t1.f1 IN (t2.f1, t2.f2, t2.f3, t2.f4) THEN 1 END) = 2

-- OR SUM(CASE WHEN t1.f1 IN (t2.f1, t2.f2, t2.f3, t2.f4) THEN 1 END) = 2

-- ...



IMHO, для подобного рода задачи было бы лучше вместо

create table t (f1 int, f2 int, f3 int, f4 int)

сделать

create table t (NumF tinyint, F int)

Запросы были бы куда как проще...
23 апр 04, 17:58    [647268]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
автор
сделать
create table t (NumF tinyint, F int)


я только что сам обдумывал сделать и такой вариант .. но как получить искомое в таком случае - не могу понять.
23 апр 04, 18:04    [647290]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
ChA

IMHO, для подобного рода задачи было бы лучше вместо
create table t (f1 int, f2 int, f3 int, f4 int)
сделать
create table t (NumF tinyint, F int)

Немного наврал в горячке, последний вариант читать как

create table t (NumGroup int, NumF tinyint, F int)

Смысл в том, чтобы вытянуть таблицы в длину, что более естественно
для реляционных БД.
NumGroup - группа параметров(идентификатор строки таблицы,
которого не было в прошлом )
NumF - 1, 2, 3, 4 номер поля
F - значение поля
Основной ключ (NumGroup, NumF)
23 апр 04, 18:19    [647338]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
автор
Смысл в том, чтобы вытянуть таблицы в длину, что более естественно
для реляционных БД.


ну это понятно - просто не могу смысл дальше уловить ..
вы предполагаете обе таблицы вытянуть ? И что дальше - как группировать это все правильно ?

кстати :

автор
create table t (NumGroup int, NumF tinyint, F int)


позиция элемента в таблице в общем-то не критична так что полагаю можно без NumF обойтись
23 апр 04, 18:28    [647363]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
Вы с запросом поигрались ?
автор

позиция элемента в таблице в общем-то не критична так что полагаю можно без NumF обойтись

Тогда

create table t (NumGroup int, F int, PRIMARY KEY (NumGroup, F))

автор

И что дальше - как группировать это все правильно ?


Сливать по полю F, делая GROUP по NumGroup, COUNT и HAVING, если
необходимо и в зависимости от условий... Я все равно не понимаю,
что Вы делаете, а просто исхожу из здравого смысла. Вашу задачу
лучше Вас все равно вряд ли кто знает. Если бы я знал предметную суть,
может что и подсказал, если бы понял :) А так мне даже в голову не
приходит что представляют собой значения полей f1, ...
23 апр 04, 18:42    [647404]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
автор
Вы с запросом поигрались ?


пока нет - честно говоря боюсь - ибо простейшая конструкция типа

SELECT * from TBL where f1<10 AND f2>15 and f3>20

колбасит много часов .. таблица в десяток миллионов записей , записи не дублируются .. может присоветуете как ее подготовить к такого рода запросам ?
23 апр 04, 18:51    [647415]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
А что у нас с индексами ? Можете сгенерите скрипт создания таблиц
через EM ? Список характерных запросов ? Приблизительно "железо",
На каких дисках живут БД, логи, tempdb ? И, наконец, Вы можете
все-таки объяснить, какой смысл имеют данные ? Может можно все
сделать прощее...
23 апр 04, 19:09    [647458]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
Индексов нет - просто мне в данной ситуации не понятно по какому принципу их формировать - данные неповторяющиеся, и сами по себе систематизированы.

характерный запрос - я привел
SELECT * from TBL where f1<;10 AND f2>;15 and f3>;20 


железо - Целерон 400 128 РАМ

Сиквел стоит на С с 2ГБ свободжными , ДБ, логи, темпДБ - на Д с 15 гигами свободными

смысл данных - ну просто прописаны числа , порядок следования (полей) в записи значения не имеет . Нужно выделить и з Т1 повторяющиеся в 2-х, 3-х цифрах строки находящиеся в Т2. В Т1 записи уникальны (не повторяются, как уже говорилось)
23 апр 04, 19:23    [647469]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
по структурам данных :


CREATE TABLE [dbo].[t1] (

[n1] [int] NOT NULL ,
[n2] [int] NOT NULL ,
[n3] [int] NOT NULL ,
[n4] [int] NOT NULL ,
[cs] AS ([n1] + [n2] + [n3] + [n4]) ,
[last] AS ([dbo].[last]([n1], [n2], [n3], [n4]))
) ON [PRIMARY]


CREATE TABLE [dbo].[t2] (

[n1] [int] NOT NULL ,
[n2] [int] NOT NULL ,
[n3] [int] NOT NULL ,
[n4] [int] NOT NULL ,
[ft] [char] (2),
[lt] [char] (4)
) ON [PRIMARY]
23 апр 04, 19:31    [647475]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
Siquel
Guest
Ваш запрос отработал минут 17 (что в принципе здорово) выдал в результате 3 строки - что неверно видимо Иннер Джоин с условием WHERE t2.ID <=3 обрезает все лишнее и значения полей CntT1F1..CntT1F4 все равны 1 в то время как предполагалось они должны представлять собой количество совпавших полей из обоих таблиц.
23 апр 04, 19:40    [647488]     Ответить | Цитировать Сообщить модератору
 Re: culumn search  [new]
ChA
Member

Откуда: Москва
Сообщений: 11124
Сначала касательно "железа":
Если диски C и D это логические диски на одном физическом, то пока о них
забудем. Остальное воспринимаю как данность, хотя для сервера конечно
откровенно слабовато, особенно RAM, тем более для десятков миллионов
записей.

автор

Индексов нет...

Это Ваша основная проблема, так как в таком случае серверу приходится
все делать через Table scan, что допустимо при малом количестве записей,
но никак не миллионов абсолютно неупорядоченных.
Тут возникает дополнительные вопросы:
1. Обе ли таблицы имеют количество записей порядка 10 миллионов ?
2. Как попадают туда записи ? Происходят ли обновление строк,
или только вставка и удаление ? Сколь часто и какими порциями ?
3. Каковы максимальные и минимальные значения полей Ni
4. Приблизительная статистика значений полей.
5. В характерных запросах есть или нет поля, которые используются всегда,
например: условие на поле n1 всегда попадает в запросы, или во всех
запросах всегда используюся условия на все поля и т.д.
6. Для таблицы t2 какой смысл имеют поля [ft] и [lt] ? Есть ли запросы
которые имеют на них условие, или они только информационные.
По этой информации уже можно будет построить более-менее подходящие
индексы.
Кстати, даже создание

CREATE INDEX T1N1 ON T1 (n1)

уже должен ускорить запросы, в условиях которых есть проверка есть по
полю n1, особенно если разных значений этого поля больше 2, хотя это
очень оптимистичный взгляд, обычно использование некластерного индекса
начинается приблизительно когда записей удовлетворяющих условию на
поле ~15%.
По поводу запроса чуть позже, но ошибка моя...
24 апр 04, 02:35    [647696]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить