Примечание. Похоже, этот вопрос получил приличное количество просмотров, поэтому я подумал, что лучше обновить этот вопрос для ясности. Большинство изменений носит косметический характер, но единственное серьезное изменение состоит в том, что я добавил столбец месяца в weights_table. Таблицы весов - это месячные таблицы, поэтому технически это не имеет значения, но я полагаю, что наличие столбца месяца в обеих таблицах сделало бы взаимосвязь между таблицами более очевидной и логичной
Проблема
У меня есть этот запрос, в котором используются две таблицы [person_table] и [weights_table].
select
a.month,
a.movie,
count(a.person_id) as raw,
sum(b.weight) as weighted,
sum(b.weight)/sum(sum(b.weight)) over () as share -- I need to change this calculation
from (select distinct month,
movie,
person_id
from person_table) a
join weights_table b on a.month=b.month and a.person_id=b.person_id
group by a.month, a.movie;
Я хочу изменить это последнее вычисление так, чтобы знаменатель sum(sum(b.weight)) over ()
рассчитывался как сумма суммы весов для (различных person_ids в месяц) вместо суммы суммы весов для (различных person_ids на фильм в месяц). Есть ли более простой способ учесть это, не добавляя еще один подзапрос?
Образец person_table
+-------+-------+-----------+
| month | movie | person_id |
+-------+-------+-----------+
| 1 | a | 1 |
| 1 | b | 1 |
| 1 | b | 2 |
| 1 | a | 2 |
| 1 | c | 3 |
| 1 | d | 4 |
| 1 | a | 2 |
| 1 | c | 3 |
| 1 | a | 6 |
+-------+-------+-----------+
Образец таблицы весов
+-------+-----------+--------+
| month | person_id | weight |
+-------+-----------+--------+
| 1 | 1 | 12 |
| 1 | 2 | 34 |
| 1 | 3 | 65 |
| 1 | 4 | 76 |
| 1 | 7 | 96 |
+-------+-----------+--------+
Ожидаемый результат
+-------+-------+-----+----------+-------+
| month | movie | raw | weighted | share |
+-------+-------+-----+----------+-------+
| 1 | a | 2 | 46 | 0.25 | --(12+34)/(12+34+65+76)=0.25
| 1 | b | 2 | 46 | 0.25 |
| 1 | c | 1 | 64 | 0.35 |
| 1 | d | 1 | 76 | 0.41 |
+-------+-------+-----+----------+-------+
Определения показателей:
Необработанные: количество всех отдельных идентификаторов person_id за месяц для каждого фильма)
Взвешенный: сумма весов различных идентификаторов person_id за месяц для каждого фильма).
Доля: отношение Взвешенного к (Сумма весов отдельных идентификаторов лиц в месяц, сопоставленных с таблицей лиц)