SQL-запрос — от родителей к детям

Question

[udi]

Есть таблица table1, в ней такие данные:

id   words                          cnt
1   слово1 слово2 слово3            10
2   слово3 слово5 слово1            15
3   слово2 слово2 слово8             7

и есть таблица table2 (дочерняя от table1 по колонке parent_id), в неё вынесены слова, встречающиеся в колонке table1:

id parent_id   word
1    1         слово1
2    1         слово2
3    1         слово3
4    2         слово3
5    2         слово5
6    2         слово1
7    3         слово2
8    3         слово2
9    3         слово8

Подскажите, пожалуйста, запрос, которым я могу сделать следующую выборку:

id    word      count_t1 sum_cnt
1     слово1      2       25
2     слово2      2       17
3     слово3      2       25
и т.д.

Здесь:
count_t1 - это в скольки родителях (table1) встречается ребенок (table2) - не сколько раз встречается, а именно в скольки родителях
sum_cnt - это сумма по колонке cnt тех родителей (table1), в которых встречается ребенок (table2)

Спасибо.

Answer 1

[Yuri Morozov]

А не жирновато так хранить?
Может, на многие-ко-многим перейти?

Comments

[udi]

Да, возможно... подскажете оптимальную структуру в таком случае?

[Yuri Morozov]

@udi нуууу, зависит от того, что там хранится.

Предположим, первая таблица - это тексты какие-то, а вторая - это словарь к ним. Тогда я бы сделал так: вторая таблица имеет вид (id, слово[, нормальная форма]), связь через пивот-таблицу (id_текста, id_слова, частота). Соответственно, при размещении нового текста (или обновлении существующего) фоновый обработчик разбивает текст на слова, ищет их во второй таблице (в перспективе - по нормальной форме) и создает связь в пивот-таблице.

Если требуется дополнительно хранить порядок следования - тут уже думать надо.

[Yuri Morozov]

(перед созданием связи, разумеется, можно посчитать частоту встречаемости слова)