ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Сложный фильтр
19 сообщений из 19, страница 1 из 1
Сложный фильтр
    #38716601
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
Привет всем!

Нужен совет. Есть сущность "песни" (таблица "songs")
Код: sql
1.
| song_id | song_title | tempo_id | ... |

и как всем известно песня может иметь много разных характеристик. В моем случае характеристики хранятся в отдельных таблицах, например:

1) Песня может находится в одной или одновременно в нескольких категориях (например одновременно в "Pop" и "Pop rock"). Таблица "songs_categories"
Код: sql
1.
| song_id | category_id |

всего около 50 категорийю



2) Песня может быть записана на нескольких музыкальных инструментах (например, барабаны, гитара, дудук итд.). Таблица "songs_instruments"
Код: sql
1.
| song_id | instrument_id |

всего около 100 инструментов.

3) Песня может иметь одновременно несколько разных эмоций (например энергичная, динамичная, позитивная итд.). Таблица "songs_emotions"
Код: sql
1.
| song_id | emotion_id |

всего около 100 эмоций.

4) Песня может быть оч. медленная, медленная, средняя, быстрая и очень быстрая (поле tempo_id в таблице songs)


Есть запросы, которые ищут песни по совокупности разных параметров. Например:

Код: sql
1.
2.
3.
4.
SELECT * FROM songs 
LEFT JOIN songs_categories ON songs.song_id = songs_categories.song_id 
LEFT JOIN songs_emotions ON songs.song_id = songs_emotions.song_id 
WHERE songs_categories.category_id = 3 AND songs_emotions.emotion_id = 80



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

Понятно, что одним запросом не выйдет. Если перебирать таблицы характеристик по найденным id песен, тоже не камильфо, т.к найденных песен может быть десятки тысяч.

Пока есть только одна идея - создание отдельной таблицы измерений со столбцами:

Код: sql
1.
| song_id | category_id | instrument_id | emotion_id | tempo_id |



c уникальным индексом, включающим все поля. Но тогда таблица получается огромная при среднем кол-ве характеристик на песню. Например у песни в среднем будет 2 категории x 5 инструментов x 8 эмоций x 1 скорость ))) = 80 строк на одну песню. Всего песен около 100000. Т.е. будет 8.000.000 строк и только для того, чтобы сделать фильтр.

Есть ли решение попроще?

Спасибо!
...
Рейтинг: 0 / 0
09.08.2014, 19:37
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716609
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
GordenЕсть ли решение попроще?
Есть: объединить все таблицы характеристик в одну и тогда задача сводится к каноническому EAV.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
09.08.2014, 20:14
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716640
R7
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
R7
Гость
Dimitry Sibiryakovзадача сводится к каноническому EAV

Математический анекдот.
Дано: Чайник, вода, плита, газ, спички.
Задача: Вскипятить воду.
Решение: Наливаем воду в чайник, ставим на плиту, включаем газ, зажигаем газ. Вода кипит.

Задача усложняется: Вода уже в чайнике.
Решение: Выливаем воду из чайника. Задача сведена к предыдущей.
...
Рейтинг: 0 / 0
09.08.2014, 21:54
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716642
R7
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
R7
Гость
GordenПосле каждого такого запроса нужно получать список категорий, эмоций и муз. инструментов, которые существуют в пределах найденных результатов (т.е. только тех, которые есть у найденных песен).

Почему это надо делать "после каждого запроса", а не когда-угодно по ИД песни?
...
Рейтинг: 0 / 0
09.08.2014, 21:59
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716666
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
Dimitry SibiryakovGordenЕсть ли решение попроще?
Есть: объединить все таблицы характеристик в одну и тогда задача сводится к каноническому EAV.


Будьте добры пример, если можно? Возможно я не правильно выразил свою мысль или меня не поняли, или я ничего не понимаю.

Смотрите:

1) Есть в базе 100.000 песен. Изначально пользователю отображаются все имеющиеся характеристики для поиска - все категории, эмоции, инструменты итд. Пользователь просит показать все песни, которые находятся в категории "pop" и имеют эмоцию "'энергичная". Результат дает 20.000 песен.

2) Так вот здесь нужно узнать какие категории, эмоции, инструменты остались в совокупности у этих 20.000 песен. Это нужно для того, чтобы сократить список отображаемых (возможных) характеристик для дальнейшего, более глубокого и детального поиска.
...
Рейтинг: 0 / 0
09.08.2014, 23:10
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716669
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
Да, и еще раз напомню, что песня может иметь одновременно несколько характеристик одного типа, например эмоции "релаксирующая" и "задумчивая"
...
Рейтинг: 0 / 0
09.08.2014, 23:14
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716680
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Gordenздесь нужно узнать какие категории, эмоции, инструменты остались в
совокупности у этих 20.000 песен.
Если эти 20 000 песен уже легли во временную таблицу, то это делается одним простеньким
запросом в один JOIN. В чём проблема-то?
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
09.08.2014, 23:51
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716688
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
Dimitry SibiryakovЕсть: объединить все таблицы характеристик в одну...


Не могли бы вы привести пример структуры такой таблицы для моего случая. Буду оч. благодарен.
...
Рейтинг: 0 / 0
10.08.2014, 00:29
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716690
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
GordenНе могли бы вы привести пример структуры такой таблицы для моего случая.
А зачем? Все EAV-схемы одинаковы. Если ты не смог нагуглить ни одну, тебе уже ничто не
поможет.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
10.08.2014, 00:33
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716695
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
Dimitry SibiryakovА зачем? Все EAV-схемы одинаковы. Если ты не смог нагуглить ни одну, тебе уже ничто не поможет.


Я знаю что такое EAV. К сожалению EAV мне не подходит. Сейчас 100.000 песен и уже все захлебывается. База сидит на сайте. Кол-во песен растет и хотелось бы найти быстрое решение для 1.000.000 песен и огромным кол-вом характеристик. Поэтому готов на денормализацию или NoSQL решения. Просто пытаюсь найти наиболее оптимальное для себя решение.
...
Рейтинг: 0 / 0
10.08.2014, 00:45
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716707
R7
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
R7
Гость
Gorden,
У вас имеется обычная модель для хранения данных n..n. Под "обычной" я понимаю наличие проиндексированных соотношений типа ПесняИД-ИнструментИД.
Вы 1м запросом получаете список песен по критериям.
2м запросом получаете дерево значений атрибутов, которое строится по тем же критериям, только теперь выводятся не песни, а атрибуты.

Вас не устраивает производительность "обычной" модели? Ну, тогда 8ГБ индексов может и не плохое решение.
...
Рейтинг: 0 / 0
10.08.2014, 01:19
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716709
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
GordenСейчас 100.000 песен и уже все захлебывается.
Это не сюда. Это в раздел СУБД. Там Вам расскажут про индексы, кэш, оптимизацию и
правильное построение запросов.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
10.08.2014, 01:30
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716710
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
R7Вас не устраивает производительность "обычной" модели? Ну, тогда 8ГБ индексов может и не плохое решение.

Да, именно так. Поэтому первое что пришло в голову - это (как я для себя называю) таблица измерений. Получается большое кол-во данных, но производительность довольно неплохая. Вопрос в перспективе. Скорее всего будут добавлены еще и другие характеристики, такие как авторы (композиторы и издатели), а это еще многократное увеличение размеров таблицы. Получается для 100.000 песен (как я писал выше) 8.000.000 умножить еще на 2 измерения, ну и если кол-во песен увеличится хотя-бы до 300.000, то кол-во строк уже под 100.000.000 может выйти. Как то необычно для фильтра поиска.

Это меня и пугает. В этом и весь вопрос. Хочу найти компромиссное решение.
...
Рейтинг: 0 / 0
10.08.2014, 01:31
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716713
Gorden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gorden
Гость
Dimitry SibiryakovЭто не сюда. Это в раздел СУБД. Там Вам расскажут про индексы, кэш, оптимизацию и правильное построение запросов.


Wow, а я и не слыхивал про такое :-))))))))))
...
Рейтинг: 0 / 0
10.08.2014, 01:39
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716750
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
GordenWow, а я и не слыхивал про такое
Заметно.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
10.08.2014, 11:35
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716761
Кот Матроскин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кот Матроскин
Участник
Gorden
Код: sql
1.
2.
3.
4.
SELECT * FROM songs 
LEFT JOIN songs_categories ON songs.song_id = songs_categories.song_id 
LEFT JOIN songs_emotions ON songs.song_id = songs_emotions.song_id 
WHERE songs_categories.category_id = 3 AND songs_emotions.emotion_id = 80



К слову говоря, странный запрос - какой смысл делать left join, если потом указывать столбцы этих таблиц в where?


Gorden Если перебирать таблицы характеристик по найденным id песен, тоже не камильфо, т.к найденных песен может быть десятки тысяч.


Почему, собственно? залил 20 тыс. id во временную таблицу, сделал соединение с одной таблицей характеристик - получил одно измерение характеристик. Потом точно также - с другой таблицей/измерением. Все это должно работать достаточно быстро.
...
Рейтинг: 0 / 0
10.08.2014, 12:28
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38716767
Фотография ПЕНСИОНЕРКА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПЕНСИОНЕРКА
Участник
Gorden,

мое видение задачи --3 таблицы и 2 формы, может что и забыла
таб1-песни
id1 song_title avtor_id год написания1катюшаааа12арлекинааа23колыбельнаяааа34


таб2-характеристики песен
id2id1id3значение31 1 category_id рop"2 1 category_id Pop rock").3141 instrument_id барабаны5 1 instrument_id гитара6 1 instrument_id , дудук7 1 instrument_id ,8191 emotion_id энергичная101 emotion_id динамичная11 1 emotion_id позитивная122category_id""Pop rock").122 instrument_id гитара131 instrument_id колокольчик

общий справочник3

id3наим3значение31 category_id Pop"2 category_id Pop rock").34 instrument_id барабаны5 instrument_id гитара6 instrument_id дудук7 instrument_id 8
...
Рейтинг: 0 / 0
10.08.2014, 13:09
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38717034
Фотография DirksDR
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DirksDR
Участник
ПЕНСИОНЕРКА,

в таблице 2 убрать колонку "значения"
id2 id1 id3 1 1 1 2 1 5 3 1 74 2 2 5 2 4
и добавить индексы
(id1,id3) и
(id3,id1)
Больше всего записей в таб2, но они короткие, и индексы тоже не займут много Гб.
Максимум скорости.
ТС помянул NoSQL, рекомендую заглянуть в этот форум и изучить пост и статью
Статья на Хабре: "Почему вы никогда не должны использовать MongoDB"
...
Рейтинг: 0 / 0
11.08.2014, 10:36
| Ответить | Цитировать | Написать  
Сложный фильтр
    #38719940
Гхостик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гхостик
Гость
Синтаксис оракловый, извиняйте за смесь своей и вашей нотации (единственное/множественное число в названиях таблиц, id в именовании полей-ссылок, 1=1 и тд).

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
create table search (id integer not null primary key, user integer not null references users(id), start_time timestamp default systimestamp, end_time timestamp);
create table search_result (search integer not null, song integer not null, primary key(search, song));

-- ищем песни и сохраняем результат в таблицу
create procedure find_songs(
  i_temp varchar2, -- id через запятую
  i_category varchar2, -- тоже
  i_emotion varchar2, -- тоже
  o_search out number
) is
begin
  insert into search (user) values (get_current_user()) returning id into o_search;
  insert into search_result (search, song)
    select o_search, s.id
    from songs s
    where
      -- для производительности or лучше поменять на отсутствие/включение условия через динамический sql
      (i_temp is null or s.tempo_id in (select column_value from table(split_strings(i_temp)))) and
      -- тоже что и в предыдущем случае для or
      (i_category is null or exists (
        select null
        from songs_categories sc
        join (select column_value c from table(split_strings(i_category))) c on c.c = sc.category_id
        where sc.song = s.id
      )) and
      -- тоже что и предыдущий способ для emotion
      1=1;  
  update search s
    set s.end_time = systimestamp
    where s.id = o_search;
end;

-- все эмоции в результатах одного поиска
procedure cr_search_emotions (o_result sys_refcursor, i_search number) is
begin
  open o_result for
    select distinct e.id, e.name
    from search_result r
    join songs_emotions se on se.song = r.song
    join emotions e on e.id = se.emotion_id
    where
      r.id = i_search and
      1=1
    order by e.name;
  ;
end;
...
Рейтинг: 0 / 0
14.08.2014, 08:14
| Ответить | Цитировать | Написать  
19 сообщений из 19, страница 1 из 1
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Сложный фильтр
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=32&msg=38717034&tid=1540824]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 57ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 229ms
total:  374ms
созд. / загр.: 374ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]