Сложный запрос на простой таблице.
От: Зверёк Харьковский  
Дата: 14.01.06 00:24
Оценка:
Господа, такая проблема. Имеется таблица, самая тупая какую можно придумать:

create table objects (obj_id integer, property_name text, property_value text)


Каждому obj_id может соответствовать любое количество property_name, property_value.
Т.е. реальная таблица какая-то (грубо) такая:
1    "First Name"    "Andrew"
1    "Last Name"     "Blake"
1    "Address"       "Some Street, 18"
1    "Work"          "programmer"
1    "Work"          "admin"
1    "Age"           "35"
2    "First Name"    "Victor"
2    "Last Name"     "Green"
2    "Address"       "Some Other Street, 24
2    "Work"          "admin"
2    "Work"          "web-designer"
1    "Age"           "23"


И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
Last Name = Green AND Work = admin AND Age = 20


Вот. Как это сделать наиболее эффективно?
FAQ — це мiй ай-кью!
Re: Сложный запрос на простой таблице.
От: avs99 Южная Корея  
Дата: 14.01.06 00:38
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
ЗХ>
ЗХ>Last Name = Green AND Work = admin AND Age = 20
ЗХ>


ЗХ>Вот. Как это сделать наиболее эффективно?

А 

SELECT obj_id FROM table where Last Name = Green AND Work = admin AND Age = 20 GROUP BY obj_id


— это неэффективно? По каким причинам не подходит?


Алексей
Re[2]: Сложный запрос на простой таблице.
От: Зверёк Харьковский  
Дата: 14.01.06 00:53
Оценка:
Здравствуйте, avs99, Вы писали:

ЗХ>>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
ЗХ>>
ЗХ>>Last Name = Green AND Work = admin AND Age = 20
ЗХ>>


ЗХ>>Вот. Как это сделать наиболее эффективно?

A>А

A>
A>SELECT obj_id FROM table where Last Name = Green AND Work = admin AND Age = 20 GROUP BY obj_id
A>


A>- это неэффективно? По каким причинам не подходит?

Посмотри еще раз на структуру таблицы. Там нет полей "Last Name", "Work", "Age" — есть только property_name и property_value
FAQ — це мiй ай-кью!
Re[3]: Сложный запрос на простой таблице.
От: avs99 Южная Корея  
Дата: 14.01.06 01:14
Оценка:
A>>
A>>SELECT obj_id FROM table where Last Name = Green AND Work = admin AND Age = 20 GROUP BY obj_id
A>>


A>>- это неэффективно? По каким причинам не подходит?

ЗХ>Посмотри еще раз на структуру таблицы. Там нет полей "Last Name", "Work", "Age" — есть только property_name и property_value

Упсс... стормозил, признаю А так не пойдет:

SELECT obj_id FROM table WHERE
    (property_name = 'LastName' AND property_value = 'Green')
AND ( bla-bla-bla)

GROUP BY obj_id

?
Re: Сложный запрос на простой таблице.
От: MatFiz Россия  
Дата: 14.01.06 01:18
Оценка: 6 (2) +1
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
ЗХ>Last Name = Green AND Work = admin AND Age = 20


Даа. Структура таблицы ужасна, либо плохой пример ее использования.
Вот запрос, который тебе нужен:
SELECT Id FROM dbo.Dictionary
WHERE 
    Name = 'Age' AND Value='23'
    OR
    Name = 'Last name' AND Value='Green'
    OR
    Name = 'Work' AND Value='Admin'
GROUP BY
    Id
HAVING 
    COUNT(*) = 3
How are YOU doin'?
Re[4]: Сложный запрос на простой таблице.
От: avs99 Южная Корея  
Дата: 14.01.06 01:21
Оценка:
Опять торможу Нк обращайте внимание на сообщение выше pls...
Re: Сложный запрос на простой таблице.
От: retn нет
Дата: 14.01.06 03:01
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
ЗХ>
ЗХ>Last Name = Green AND Work = admin AND Age = 20
ЗХ>


ЗХ>Вот. Как это сделать наиболее эффективно?

Желательно писать какой сервер .
Только вариант с вложеными запросами приходит в голову
Для MSSQL(правда не совсем понял надо именно or или and)

create table [objects] (obj_id int, property_name varchar(125), property_value varchar(125))

insert into objects values(1,'First Name',        'Andrew')
insert into objects values(1,'Last Name',     'Blake')
insert into objects values(1,'Address',       'Some Street, 18')
insert into objects values(1,'Work',          'programmer')
insert into objects values(1,'Work',          'admin')
insert into objects values(1,'Age',           '35')

insert into objects values(2,'First Name',    'Victor')
insert into objects values(2,'Last Name',     'Green')
insert into objects values(2,'Address',       'Some Other Street, 24')
insert into objects values(2,'Work',          'admin')
insert into objects values(2,'Work',          'web-designer')
insert into objects values(2,'Age',           '23')

insert into objects values(3,'First Name',    'Asedora')
insert into objects values(3,'Last Name',     'Dunkan')
insert into objects values(3,'Address',       'Some Other Street, 24')
insert into objects values(3,'Work',          'secretary')
insert into objects values(3,'Work',          'swimmer')
insert into objects values(3,'Age',           '53')
 

select distinct
    [obj_id]
from
    [objects] omain
where
    --[property_value] = case [property_name] when 'Last Name' then 'Green' end
    exists (select * from [objects] o where [property_name] = 'Last Name' and o.[obj_id] = omain.[obj_id] and o.[property_value]='Green')
    and
    exists (select * from [objects] o where [property_name] = 'Work' and o.[obj_id] = omain.[obj_id] and o.[property_value]='admin')
    and
    exists (select * from [objects] o where [property_name] = 'Age' and o.[obj_id] = omain.[obj_id] and o.[property_value]='23')
... << RSDN@Home 1.2.0 alpha rev. 629>>
Re[2]: Сложный запрос на простой таблице.
От: Зверёк Харьковский  
Дата: 14.01.06 03:08
Оценка:
Здравствуйте, retn, Вы писали:

R>Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
ЗХ>>
ЗХ>>Last Name = Green AND Work = admin AND Age = 20
ЗХ>>


ЗХ>>Вот. Как это сделать наиболее эффективно?

R>Желательно писать какой сервер .
R>Только вариант с вложеными запросами приходит в голову

SQLite. exists, кажется, не поддерживает
FAQ — це мiй ай-кью!
Re[3]: Сложный запрос на простой таблице.
От: Зверёк Харьковский  
Дата: 14.01.06 03:14
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

R>>Желательно писать какой сервер .
R>>Только вариант с вложеными запросами приходит в голову

ЗХ>SQLite. exists, кажется, не поддерживает

Не, ты гля, поддерживает. В общем, похоже что проще не получится
FAQ — це мiй ай-кью!
Re[4]: Сложный запрос на простой таблице.
От: retn нет
Дата: 14.01.06 03:35
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>Здравствуйте, Зверёк Харьковский, Вы писали:

R>>>Желательно писать какой сервер .
R>>>Только вариант с вложеными запросами приходит в голову

ЗХ>>SQLite. exists, кажется, не поддерживает

ЗХ>Не, ты гля, поддерживает. В общем, похоже что проще не получится

А ANY там поддерживается?
можно ещё так
select distinct
    [obj_id]
from
    [objects] omain
where
    [obj_id] = any (select [obj_id] from [objects] o where [property_name] = 'Last Name' and o.[property_value]='Good')
    and
    [obj_id] = any (select [obj_id] from [objects] o where [property_name] = 'Work' and o.[property_value]='forester')
    and
    [obj_id] = any (select [obj_id] from [objects] o where [property_name] = 'Age' and o.[property_value]='127')


Хотя попробовал на MSSQL создать такую таблицу с ~1000000 записей(без индексов, мне кажется если их подкрутить быстрее будет), особой разницы не заметил.
... << RSDN@Home 1.2.0 alpha rev. 629>>
Re: Сложный запрос на простой таблице.
От: retn нет
Дата: 14.01.06 04:15
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>Вот. Как это сделать наиболее эффективно?

Пробило с утра на изучение 


-- Просто выбрать подходящие(ИЛИ)
select distinct [obj_id]
from [objects]
where
    [property_value] = case [property_name] when 'Last Name' then 'Green' end
    or [property_value] = case [property_name] when 'Work' then 'Admin' end
    or [property_value] = case [property_name] when 'Age' then '23' end


-- Только при совпадении всех контролируемых сущностей для объекта (И)
-- EXISTS
select distinct
    [obj_id]
from
    [objects] omain
where
    --[property_value] = case [property_name] when 'Last Name' then 'Green' end
    exists (select * from [objects] o where [property_name] = 'Last Name' and o.[obj_id] = omain.[obj_id] and o.[property_value] in ('Green'))
    and
    exists (select * from [objects] o where [property_name] = 'Work' and o.[obj_id] = omain.[obj_id] and o.[property_value] in ('admin'))
    and
    exists (select * from [objects] o where [property_name] = 'Age' and o.[obj_id] = omain.[obj_id] and o.[property_value] in ('23'))

-- ANY
select distinct
    [obj_id]
from
    [objects] omain
where
    [obj_id] = any (select [obj_id] from [objects] o where [property_name] = 'Last Name' and o.[property_value] in ('Green'))
    and
    [obj_id] = any (select [obj_id] from [objects] o where [property_name] = 'Work' and o.[property_value] in ('admin'))
    and
    [obj_id] = any (select [obj_id] from [objects] o where [property_name] = 'Age' and o.[property_value] in ('23'))

-- INNER JOIN
select distinct
    om.[obj_id]
from
    [objects] om inner join [objects] o1 on om.[obj_id] = o1.[obj_id] and o1.[property_name] = 'Last Name' and o1.[property_value] in ('Green')
    inner join [objects] o2 on om.[obj_id] = o2.[obj_id] and o2.[property_name] = 'Work' and o2.[property_value] in ('admin')
    inner join [objects] o3 on om.[obj_id] = o3.[obj_id] and o3.[property_name] = 'Age' and o3.[property_value] in ('23')



Как видим есть три варианта, анализ плана выполнения показал(ИМХО) что третий вроде самый оптимальный вариант.
... << RSDN@Home 1.2.0 alpha rev. 629>>
Re[2]: Сложный запрос на простой таблице.
От: MatFiz Россия  
Дата: 14.01.06 11:59
Оценка:
Здравствуйте, retn, Вы писали:

R>Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>>Вот. Как это сделать наиболее эффективно?

R>Пробило с утра на изучение

Посмотри, пожалуйста, еще мой вариант
Автор: MatFiz
Дата: 14.01.06
How are YOU doin'?
Re[3]: Сложный запрос на простой таблице.
От: retn нет
Дата: 14.01.06 12:29
Оценка:
Здравствуйте, MatFiz, Вы писали:

MF>Посмотри, пожалуйста, еще мой вариант
Автор: MatFiz
Дата: 14.01.06


Видел,
здорово, то что "срезаем" лишнее, затем аггрегируем и отбираем.
На больших объемах, имхо, он поэффективнее будет.
... << RSDN@Home 1.2.0 alpha rev. 629>>
Re: Сложный запрос на простой таблице.
От: MishaSt  
Дата: 15.01.06 13:35
Оценка: 1 (1)
Вот тоже, правда может не эффективно

SELECT DISTINCT obj_id FROM objects o
LEFT JOIN objects o1 ON o.obj_id = o1.obj_id AND o1.property_name = "Name" AND o1.property_value = "Green"
LEFT JOIN objects o2 ON o.obj_id = o2.obj_id AND o2.property_name = "Work" AND o2.property_value = "admin"
LEFT JOIN objects o3 ON o.obj_id = o3.obj_id AND o3.property_name = "Age" AND o3.property_value = "20"
WHERE o1.obj_id NOT NULL AND o2.obj_id NOT NULL AND o3.obj_id NOT NULL;
... << RSDN@Home 1.1.4 stable rev. 625>>
Re: Сложный запрос на простой таблице.
От: Аноним  
Дата: 15.01.06 21:42
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>Господа, такая проблема. Имеется таблица, самая тупая какую можно придумать:
ЗХ>Вот. Как это сделать наиболее эффективно?
Попробую ответить на первую часть вопроса 
select * from ane_objects;

OBJ_ID PROPERTY_NAME        PROPERTY_VALUE
------ -------------------- ----------------------
     1 First Name           Andrew
     1 Last Name            Blake
     1 Address              Some Street, 18
     1 Work                 programmer
     1 Work                 admin
     1 Age                  35
     2 First Name           Victor
     2 Last Name            Green
     2 Address              Some Other Street, 24
     2 Work                 admin
     2 Work                 web-designer
     2 Age                  20

12 rows selected

select obj_id from ane_objects
where (property_name = 'Last Name' and property_value = 'Green')
   OR (property_name = 'Work' and property_value = 'admin')
   OR (property_name = 'Age' and property_value = '20')
group by obj_id
having count(obj_id)>=3
;

OBJ_ID
------
     2
Re: Сложный запрос на простой таблице.
От: ilya_ny  
Дата: 15.01.06 23:05
Оценка:
ЗХ>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
ЗХ>
ЗХ>Last Name = Green AND Work = admin AND Age = 20
ЗХ>


ЗХ>Вот. Как это сделать наиболее эффективно?



я не знаю насчет эффективности, но это делается так:

select distinct T1.obj_id
from 
   (select obj_id from objects where property_name = 'Last Name' and property_value = 'Green')  T1
   inner join 
   (select obj_id from objects where property_name = 'Work' and property_value = '20')  T2
   on T1.obj_id = T2.obj_id


или 


select distinct T1.obj_id
from 
   (select * from objects)  T1 
   inner join 
   (select * from objects)  T2
   on
   T1.obj_id = T2.obj_id
   and T1.property_name = 'Last Name' and T1.property_value = 'Green'
   and T1.property_name = 'Work' and T1.property_value = '20'


оба запроса эквивалентны... смотря что больше нравится с эстетичекой точки зрения
Re[2]: поправочки... оптимальность
От: ilya_ny  
Дата: 15.01.06 23:35
Оценка: +1
Здравствуйте, ilya_ny, Вы писали:

_>или 


select distinct T1.obj_id
from 
   (select * from objects)  T1 
   inner join 
   (select * from objects)  T2
   on
   T1.obj_id = T2.obj_id
   and T1.property_name = 'Last Name' and T1.property_value = 'Green'
   and T2.property_name = 'Work' and T2.property_value = '20'  -- тут  была неточность


насчет оптимальности:

1. убрать distinct
2. добавить and T1.property_name > T2.property_name

так будет работать быстрее с тем-же результатом
Re: Сложный запрос на простой таблице.
От: wildwind Россия  
Дата: 16.01.06 08:53
Оценка:
Здравствуйте, Зверёк Харьковский, Вы писали:

ЗХ>И тут внезапно встает следующая задача: выбрать все obj_id, для которых верны следующие условия:
Прям-таки внезапно? О чем думали при проектировании?

ЗХ>Вот. Как это сделать наиболее эффективно?
Предполагая, что имеется индекс по (property_name, property_value, obj_id), наиболее эффективным представляется подход с self-join (3-й вариант retn). При этом порядок соединения должен определяться селективностью предикатов (как, и возможно ли управлять им в SQLite, не в курсе). То есть упорядочиваем предикаты по убыванию селективности, в данном случае это видимо Last Name, Age, Work. И строго в таком порядке нужно соединять. Проблема в том, что для разных свойств порядок будет разным.
Re[2]: Сложный запрос на простой таблице.
От: Sinclair Россия https://github.com/evilguest/
Дата: 18.01.06 06:35
Оценка:
Здравствуйте, MishaSt, Вы писали:

MS>Вот тоже, правда может не эффективно

MS>SELECT DISTINCT obj_id FROM objects o
MS>LEFT JOIN objects o1 ON o.obj_id = o1.obj_id AND o1.property_name = "Name" AND o1.property_value = "Green"
MS>LEFT JOIN objects o2 ON o.obj_id = o2.obj_id AND o2.property_name = "Work" AND o2.property_value = "admin"
MS>LEFT JOIN objects o3 ON o.obj_id = o3.obj_id AND o3.property_name = "Age" AND o3.property_value = "20"
MS>WHERE o1.obj_id NOT NULL AND o2.obj_id NOT NULL AND o3.obj_id NOT NULL;
а собственно зачем? Разве вот это — не то же самое?

SELECT o.obj_id FROM 
(select distinct obj_id from objects) o
inner JOIN objects o1 ON o.obj_id = o1.obj_id AND o1.property_name = "Name" AND o1.property_value = "Green" 
inner JOIN objects o2 ON o.obj_id = o2.obj_id AND o2.property_name = "Work" AND o2.property_value = "admin"
inner JOIN objects o3 ON o.obj_id = o3.obj_id AND o3.property_name = "Age" AND o3.property_value = "20"
1.1.4 stable rev. 510
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Re: Сложный запрос на простой таблице.
От: Tourist Россия  
Дата: 18.01.06 10:11
Оценка: +1
Здравствуйте, Зверёк Харьковский, Вы писали:

можно еще взять такой вариант

select  obj_id from objects where property_name = 'Last Name' and property_value = 'Green'
intersect
select  obj_id from objects where property_name = 'Work' and property_value = 'admin'
intersect
select  obj_id from objects where property_name = 'Age' and property_value = '23' ;
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.