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

Откуда: от верблюда
Сообщений: 428
субд MSSQL2000

дана таблица t1
f1  |f2  |f3  |f4  |f5
-----------------------
f1v1|f2v1|    |    |1
f1v2|    |    |    |1
f1v1|    |    |f4v1|2
    |    |    |f4v2|2
f1v1|    |    |    |3
f1v1|f2v2|    |    |4


f5 можно считать как ID

на всякий случай - количество строк с одним идентификатором равно количеству заполненных значений в одной колонке (в разрезе все того же идентификатора)

задача - выбрать из таблицы все строки, в которых значение поля либо равно заданному в условии, либо пустое, но при этом не должно быть повторений в разрезе f5.

например, если сделать выборку значения f2v2 по колонке f2, то результатом должны быть строки 2,3,4. по идее, в выборку должно было бы поспасть и ID1, т.к. одна из строк имеет пустое значение, но вся сложность в том, что если у строк, в разрезе одного идентификатора, заполненно хотя бы одно значение в конкретной колонке, то пустые для этой колонки уже игнорируются.

если пойти путем исключения, то можно сделать так:
SELECT f5
FROM t1
WHERE f5 NOT IN (SELECT f5
                 FROM t1
                 WHERE f2 <> @EmptyValue
                 GROUP BY f5)

UNION

SELECT f5
FROM t1
WHERE f5 = f2v2


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

такой путь, как я описал, вынуждает выполнить эти три запроса (каждый со своим именем поля) столько раз сколько полей в таблице и объединить результаты через UNION, а это как-то не очень...

какие будут мысли?
4 сен 12, 17:52    [13113475]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
TJ001
f5 можно считать как ID

Какой же это ID, если значения повторяются ?
4 сен 12, 17:56    [13113500]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
я образно его так назвал, ну пусть ключ, разве это важно в данном случае?
4 сен 12, 17:59    [13113515]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
SELECT DISTINCT f5
FROM t1
WHERE f2='f2v2' OR f2 IS NULL
4 сен 12, 18:06    [13113568]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Glory,

не подойдет, т.к. в выборку попадет строка, в которой f5=1, а ее там быть не должно
5 сен 12, 09:08    [13115121]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
user89
Member

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

не до конца понятно...
Из этого набора
f1  |f2  |f3  |f4  |f5
-----------------------
f1v1|f2v1|    |    |1
f1v2|    |    |    |1
f1v1|    |    |f4v1|2
    |    |    |f4v2|2
f1v1|    |    |    |3
f1v1|f2v2|    |    |4

что должно получиться?
5 сен 12, 09:14    [13115164]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
user89,

что имено непонятно?
5 сен 12, 09:16    [13115174]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
TJ001,

при выборке по столбцу f2 с условием f2v2, в результат выборки должны попасть строки в которых f5 = 2,3,4.
причина:
1) в строке, где f5 = 4, f2=f2v2, что удовлетворяет условию запроса
2) в строках, где f5 = 2,3 f2 = не заполнено
3) в строках, где f5 = 1, существует строка, в которой f2 заполнено и это значение не соответствует условию запроса. а из-за того, что существует хотя бы одно заполненное значение в колонке f2, в разрезе f5, то все строки f5 уже должны игнорироваться при поиске f2 с пустым значением

запрос, который я написал, работает корректно, но меня не устраивает его объем.

если сделать выборку по условиям для всех полей придется писать такой запрос
select * from
мойзапрос (с условиями для f1)
union
мойзапрос (с условиями для f2)
union
мойзапрос (с условиями для f3)
union
мойзапрос (с условиями для f4)
where (ряд условий для уже получившихся данных)
5 сен 12, 09:26    [13115248]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
user89
Member

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

declare @t table (f1 varchar(50), f2 varchar(50), f3 varchar(50), f4 varchar(50), f5 int)
insert @t values ('f1v1', 'f2v1', null, null, 1), ('f1v2', null, null, null, 1), ('f1v1', null, null, 'f4v1', 2),
(null, null, null, 'f4v2', 2), ('f1v1', null, null, null, 3), ('f1v1', 'f2v2', null, null, 4)
select * from @t

;with a as (
  select *, max(f2) over(partition by f5) [gr]
  from @t
)
select distinct f5 from a
where (gr = 'f2v2') or (gr is null)
5 сен 12, 10:15    [13115557]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
мои пять копеек
declare @t table (f1 varchar(50), f2 varchar(50), f3 varchar(50), f4 varchar(50), f5 int)
insert @t values ('f1v1', 'f2v1', null, null, 1), ('f1v2', null, null, null, 1), ('f1v1', null, null, 'f4v1', 2),
(null, null, null, 'f4v2', 2), ('f1v1', null, null, null, 3), ('f1v1', 'f2v2', null, null, 4)
select * from @t

select
	f5
from
	@t
group by
	f5
having
	COUNT(distinct isnull(f2, 'null')) = 1
5 сен 12, 10:45    [13115786]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2392
Блог
user89,

declare @t table (f1 varchar(50), f2 varchar(50), f3 varchar(50), f4 varchar(50), f5 int)
insert @t values ('f1v1', 'f2v1', null, null, 1), ('f1v2', null, null, null, 1), ('f1v1', null, null, 'f4v1', 2),
(null, null, null, 'f4v2', 2), ('f1v1', null, null, null, 3), ('f1v1', 'f2v2', null, null, 4), ('f1v23', 'f2v5', null, null, 4)
select * from @t

;with a as (
  select *, max(f2) over(partition by f5) [gr]
  from @t
)
select distinct f5 from a
where (gr = 'f2v2') or (gr is null)
???

declare @t table (f1 varchar(50), f2 varchar(50), f3 varchar(50), f4 varchar(50), f5 int);
insert @t values ('f1v1', 'f2v1', null, null, 1), ('f1v2', null, null, null, 1), ('f1v1', null, null, 'f4v1', 2),
(null, null, null, 'f4v2', 2), ('f1v1', null, null, null, 3), ('f1v1', 'f2v2', null, null, 4), ('f1v23', 'f2v5', null, null, 4);

select * from @t t
where isnull(t.f2,'f2v2')='f2v2'
and not exists (select * from @t t2
where t2.f2 is not null
and t2.f5 = t.f5
and t2.f2 <> t.f2)
5 сен 12, 10:47    [13115807]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
к сожалению isnull не подходит, т.к. реально там не null, нулевой внешний ключ, поэтому я и обозначил в запросе не null, а @EmptyValue.
5 сен 12, 10:54    [13115861]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
TJ001
к сожалению isnull не подходит, т.к. реально там не null, нулевой внешний ключ, поэтому я и обозначил в запросе не null, а @EmptyValue.

ну так заменить isnull на case
5 сен 12, 10:56    [13115874]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2392
Блог
TJ001,

Да пожалуйста, как условие переписать - разберетесь поди.
5 сен 12, 10:56    [13115880]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
еще дурацкий вопрос, я пока еще туплю и не переварю как это работает... удастся ли в эту конструкцию добавить условия к нескольким полям? или так же через юнион объединять каждый запрос к каждому полю?
небейте :'(
5 сен 12, 11:12    [13116015]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
ну так огласите ожидаемый результат с объяснением почему так
5 сен 12, 11:28    [13116154]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
ну, скажем, в двух словах:
нужно выполнить выборку с условием ко всем полям, кроме f5

например, даны условия f1=f1v1, f2=f2v2, f3=f3v1,f4=f4v4

результатом должны быть строки в которых f5=2,3,4
(если брать одно условие f3=f3v1, то под него попадают все строки, т.к. ни в одной из строк это поле не заполнено, но меня интересуют условия ко всем полям)

для условий f1=f1v2, f2=f2v1, f3=f3v1,f4=f4v1
результирующая строка где f5=1. строка, где f5=2 в резльтат не попадет т.к. f1 уже заполнено (т.е. пустым уже считать его нельзя), а то чем заполнено поле <> f1v2. если бы f1 внутри f5=2 не было бы заполнено ни разу, то в результат бы попали и f5=2
5 сен 12, 11:49    [13116369]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
почему-то думается что нужно делать в связке с exists но как это написать я не представляю
5 сен 12, 11:58    [13116488]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
TJ001
ну, скажем, в двух словах:
нужно выполнить выборку с условием ко всем полям, кроме f5

например, даны условия f1=f1v1, f2=f2v2, f3=f3v1,f4=f4v4

результатом должны быть строки в которых f5=2,3,4
(если брать одно условие f3=f3v1, то под него попадают все строки, т.к. ни в одной из строк это поле не заполнено, но меня интересуют условия ко всем полям)

для условий f1=f1v2, f2=f2v1, f3=f3v1,f4=f4v1
результирующая строка где f5=1. строка, где f5=2 в резльтат не попадет т.к. f1 уже заполнено (т.е. пустым уже считать его нельзя), а то чем заполнено поле <> f1v2. если бы f1 внутри f5=2 не было бы заполнено ни разу, то в результат бы попали и f5=2

по-первому условию: не понял почему должно вернуться 2,3,4
ведь для f5=2 два значения у f4
5 сен 12, 12:30    [13116813]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
declare @t table (f1 varchar(50), f2 varchar(50), f3 varchar(50), f4 varchar(50), f5 int);

insert @t
values
 ('f1v1', 'f2v1', null, null, 1),
 ('f1v2', null, null, null, 1),
 ('f1v1', null, null, 'f4v1', 2),
 (null, null, null, 'f4v2', 2),
 ('f1v1', null, null, null, 3),
 ('f1v1', 'f2v2', null, null, 4),
 ('f1v23', 'f2v5', null, null, 4);

declare
 @f1 varchar(50) = 'f1v1', @f2 varchar(50) = 'f2v2', @f3 varchar(50) = 'f3v1', @f4 varchar(50) = 'f4v4';

with x as
(
 select
  *,
  count(f1) over (partition by f5) as cf1,
  count(f2) over (partition by f5) as cf2,
  count(f3) over (partition by f5) as cf3,
  count(f4) over (partition by f5) as cf4
 from
  @t
)
select
 f1, f2, f3, f4, f5
from
 x
where
 exists(
  select
   case when cf1 = 0 then isnull(f1, @f1) else f1 end,
   case when cf2 = 0 then isnull(f2, @f2) else f2 end,
   case when cf3 = 0 then isnull(f3, @f3) else f3 end,
   case when cf4 = 0 then isnull(f4, @f4) else f4 end

  intersect

  select isnull(@f1, f1), isnull(@f2, f2), isnull(@f3, f3), isnull(@f4, f4)
 );
?
5 сен 12, 12:38    [13116891]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
у меня SQL2000, часть транслировал, а вот over partition by чет не могу пока...
5 сен 12, 14:00    [13117714]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
HandKot,

ууупс, очепяточка)))
в условии я хотел написать f4=f4v1, а не f4v4, в этом случае f5=2,3,4
в случае для f4=f4v4 результат будет f5=3,4

условие считается выполняющимся если:
1) условие в запросе = значению в таблице
2) значение в конкретной колонке не заполнено ни разу (считается пустым), в разрезе f5
5 сен 12, 14:11    [13117774]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
declare @t table (f1 varchar(50), f2 varchar(50), f3 varchar(50), f4 varchar(50), f5 int);

insert @t values ('f1v1', 'f2v1', '     0   ', '     0   ', 1)
insert @t values ('f1v2', '     0   ', '     0   ', '     0   ', 1)
insert @t values ('f1v1', '     0   ', '     0   ', 'f4v1', 2)
insert @t values ('     0   ', '     0   ', '     0   ', 'f4v2', 2)
insert @t values ('f1v1', '     0   ', '     0   ', '     0   ', 3)
insert @t values ('f1v1', 'f2v2', '     0   ', '     0   ', 4)
insert @t values ('f1v1', 'f2v5', '     0   ', '     0   ', 4)

select * from @t

declare @x table (cf1 varchar(50), cf2 varchar(50), cf3 varchar(50), cf4 varchar(50), cf5 int);
insert into @x (cf1,cf2,cf3,cf4, cf5)
select 
       count(ALL CASE WHEN f1 <> '     0   ' THEN 'NULL' END) as cf1,
       count(ALL CASE WHEN f2 <> '     0   ' THEN 'NULL' END) as cf2,
       count(ALL CASE WHEN f3 <> '     0   ' THEN 'NULL' END) as cf3,
       count(ALL CASE WHEN f4 <> '     0   ' THEN 'NULL' END) as cf4,
       f5 as cf5
from @t
GROUP BY f5

select f1,f2,f3,f4,f5 from
(select * from @t t
inner join @x x ON t.f5=x.cf5) a
where 
(f1='f1v1' or cf1=0) and (f2  = 'f2v1' or cf2 = 0) and (f3  = 'f3v1' or cf3 = 0) and (f4  = 'f4v1' or cf4 = 0)


аааа!! рабооотаееет!!!!))

что скажете, уважаемые?)))
5 сен 12, 16:13    [13118905]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
user89
Member

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

я не гуру, но почему здесь строковое значение NULL. Оно НЕ есть тот самый null (пустое значение).
count(ALL CASE WHEN f1 <> '     0   ' THEN 'NULL' END)


З.Ы. А зачем здесь ALL ? Из той же оперы, что SOME | ANY ?
5 сен 12, 16:27    [13119024]     Ответить | Цитировать Сообщить модератору
 Re: помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
согласен ALL не нужен, остался от эксперементов, NULL тоже, на его месте должен быть 0
5 сен 12, 16:31    [13119068]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить