Re[2]: Сложный запрос на простой таблице. - Базы данных

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

ЗХ>И тут внезапно встает следующая задача: выбрать все 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

Здравствуйте, PNCHL, Вы писали:

PNC>У Тома Кайта эта тема давно раскрыта: здесь.
PNC>В общем, господа программисты "минус" разработчики, НИКОГДА, НИКОГДА так больше не делайте

Да собственно никто не сомневался, что это плохой, неудачный дизайн. Но раз уж автору темы от него теперь не деться (как я подозреваю), то почему бы не предложить способы работы с ним.

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

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>>

Здравствуйте, 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

так будет работать быстрее с тем-же результатом

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

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

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' ;

Господа, такая проблема. Имеется таблица, самая тупая какую можно придумать:

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

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

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

ЗХ>И тут внезапно встает следующая задача: выбрать все 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

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

Алексей

Здравствуйте, 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

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

Опять торможу

Нк обращайте внимание на сообщение выше pls...

ЗХ>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>>

Здравствуйте, retn, Вы писали:

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

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

ЗХ>>Last Name = Green AND Work = admin AND Age = 20
ЗХ>>

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

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

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

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

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

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

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

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

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

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>>

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

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

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


-- Просто выбрать подходящие(ИЛИ)
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>>

Здравствуйте, retn, Вы писали:

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

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

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

Посмотри, пожалуйста, еще мой вариант

Здравствуйте, MatFiz, Вы писали:

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

Видел,

здорово, то что "срезаем" лишнее, затем аггрегируем и отбираем.
На больших объемах, имхо, он поэффективнее будет.

... << RSDN@Home 1.2.0 alpha rev. 629>>

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

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

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

ЗХ>И тут внезапно встает следующая задача: выбрать все 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'

оба запроса эквивалентны... смотря что больше нравится с эстетичекой точки зрения

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

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

О чем думали при проектировании?

ЗХ>Вот. Как это сделать наиболее эффективно?
Предполагая, что имеется индекс по (property_name, property_value, obj_id), наиболее эффективным представляется подход с self-join (3-й вариант retn). При этом порядок соединения должен определяться селективностью предикатов (как, и возможно ли управлять им в SQLite, не в курсе). То есть упорядочиваем предикаты по убыванию селективности, в данном случае это видимо Last Name, Age, Work. И строго в таком порядке нужно соединять. Проблема в том, что для разных свойств порядок будет разным.

Здравствуйте, 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

Здравствуйте, wildwind, Вы писали:

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

О чем думали при проектировании?

У Тома Кайта эта тема давно раскрыта: здесь.

В общем, господа программисты "минус" разработчики, НИКОГДА, НИКОГДА так больше не делайте

Здравствуйте, PNCHL, Вы писали:

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

О чем думали при проектировании?

PNC>У Тома Кайта эта тема давно раскрыта: здесь.

PNC>В общем, господа программисты "минус" разработчики, НИКОГДА, НИКОГДА так больше не делайте

Не, господа, говорить "это плохой дизайн, потому что запросы неэффективны" я и сам могу. Но никто ведь не предложит альтернативы для случая, когда набор возможных полей у объекта действительно не известен по определению? Предложите более "умный" дизайн — я спасибо скажу.

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

ЗХ>Не, господа, говорить "это плохой дизайн, потому что запросы неэффективны" я и сам могу. Но никто ведь не предложит альтернативы для случая, когда набор возможных полей у объекта действительно не известен по определению? Предложите более "умный" дизайн — я спасибо скажу.

XML — если объемы не гигантские. Другое дело, что далеко не все умеют его индексировать. Но ведь выбор SQLite — тоже часть дизайна, не так ли?

Также стоит обратить внимание на не-SQL (и возможно даже нереляционные) дивжки, приспособленные для хранения данных нерегулярной или разреженной структуры.
Например тот же ESE (aka "JET Blue") — кстати бесплатен (встроен в Windows).
(здесь, здесь)

Здравствуйте, wildwind, Вы писали:

ЗХ>>Не, господа, говорить "это плохой дизайн, потому что запросы неэффективны" я и сам могу. Но никто ведь не предложит альтернативы для случая, когда набор возможных полей у объекта действительно не известен по определению? Предложите более "умный" дизайн — я спасибо скажу.

W>XML — если объемы не гигантские. Другое дело, что далеко не все умеют его индексировать. Но ведь выбор SQLite — тоже часть дизайна, не так ли?

W>Также стоит обратить внимание на не-SQL (и возможно даже нереляционные) дивжки, приспособленные для хранения данных нерегулярной или разреженной структуры.
W>Например тот же ESE (aka "JET Blue") — кстати бесплатен (встроен в Windows).
W>(здесь, здесь)

Мммм... XML мне тут несимпатичен совершенно (объемы — тысячи, но могут быть и десятки тысяч объектов). Поскольку одно из условий — частая модификация свойств объектов (что приведет к перезаписи всего XML-я).

Вообще говоря, исходная постановка звучит так примерно:
* есть объекты, у них есть текстовые свойства (имя=значение)
* количество свойств у одного объекта — не ограничено (хотя реально, как правило, невелико)
* длина имени, длина значения — неограничены (или ограничены чем-нибудь большим); реально там будет как правило недлинные текстовые строки, но могут быть и очень длинные (например description=<описание объекта на 8 страниц>)

В этих условиях, приближенных к боевым, нужно эффективно выполнять следующие операции:
* добавить объекту новое свойство
* изменить значение существующего свойства
* выбрать список возможных имен свойств
* выбрать список возможных значений свойства с заданным именем
* (то, что было в исходной задаче) выбрать список объектов по заданному набору пар имя=значение.

Вот. Внимание вопрос: какой движок тут подойдет /заметим, что дополнительное требование к движку — легковесность/

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

ЗХ>Мммм... XML мне тут несимпатичен совершенно (объемы — тысячи, но могут быть и десятки тысяч объектов). Поскольку одно из условий — частая модификация свойств объектов (что приведет к перезаписи всего XML-я).

Ну конечно я имел в виду не все в одном XML, а каждый объект в своем XML-е.

Здравствуйте, wildwind, Вы писали:

ЗХ>>Мммм... XML мне тут несимпатичен совершенно (объемы — тысячи, но могут быть и десятки тысяч объектов). Поскольку одно из условий — частая модификация свойств объектов (что приведет к перезаписи всего XML-я).

W>Ну конечно я имел в виду не все в одном XML, а каждый объект в своем XML-е.

Угу, я уже понял, когда сообщение отправил

Тем не менее, исходную задачу это не сильно облегчает

	От:	MatFiz
	Дата:	14.01.06 01:18
	Оценка:	6 (2) +1

	От:	wildwind
	Дата:	20.01.06 09:08
	Оценка:	4 (1)

	От:	MishaSt
	Дата:	15.01.06 13:35
	Оценка:	1 (1)

	От:	ilya_ny
	Дата:	15.01.06 23:35
	Оценка:	+1

	От:	Tourist
	Дата:	18.01.06 10:11
	Оценка:	+1

	От:	Зверёк Харьковский
	Дата:	14.01.06 00:24
	Оценка:

От:	Sinclair	https://github.com/evilguest/
Дата:	18.01.06 06:35
Оценка:

	От:	PNCHL
	Дата:	19.01.06 18:23
	Оценка:

	От:	DmitryMS
	Дата:	24.01.06 18:21
	Оценка: