Remarque: Cette question semble avoir obtenu des vues décentes, alors j'ai pensé qu'il valait mieux mettre à jour cette question pour plus de clarté. La plupart des changements sont cosmétiques, mais le seul changement majeur est que j'ai ajouté la colonne des mois à la table weights_table. Les tableaux de pondérations sont des tableaux mensuels donc techniquement cela n'a pas d'importance, mais j'imagine que le fait d'avoir une colonne mois dans les deux tableaux rendrait la relation entre les tableaux plus évidente et logique
Problème
J'ai cette requête qui utilise deux tables [ person_table ] et [ weights_table ].
+-------+-------+-----+----------+-------+ | 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 | +-------+-------+-----+----------+-------+
Ce que je veux est de changer ce dernier calcul de telle sorte que le dénominateur sum (sum (b.weight)) over () soit calculé comme la somme de la somme des poids pour (person_ids distincts par mois) au lieu de la somme des somme des poids pour (person_ids distincts par film et par mois). Existe-t-il un moyen plus simple de gérer cela sans ajouter une autre sous-requête?
Exemple de table_personne
+-------+-----------+--------+ | month | person_id | weight | +-------+-----------+--------+ | 1 | 1 | 12 | | 1 | 2 | 34 | | 1 | 3 | 65 | | 1 | 4 | 76 | | 1 | 7 | 96 | +-------+-----------+--------+
Exemple de weights_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 | +-------+-------+-----------+
Résultat attendu
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;
Définitions des métriques: strong >
Raw : nombre de tous les person_ids distincts par mois et par film)
Pondéré : somme des poids des person_ids distincts par mois par film).
Partager : rapport de Pondéré à (Somme des poids de person_ids distincts par mois mis en correspondance avec la table persons_table)
3 Réponses :
Peut-être quelque chose comme:
select a.month,
a.movie,
count(a.person_id) as raw,
sum(b.weight) as weighted,
100*weighted/c.ttl_weight as share
from (select distinct month, movie, person_id from person_table) a
inner join weights_table b on a.person_id=b.person_id
cross join (select sum(weight) as ttl_weight from weights_table w
where exists (select 1
from person_table p
where w.person_id=p.person_id)
) c
group by a.month, a.movie, c.ttl_weight
;
si vous ajoutez une ligne utilisateur à la pondération, qui n'est pas présente dans les données mensuelles, cela additionne cette ligne.
Dans le cas où cette solution de contournement laide aiderait quelqu'un - ce que j'ai fait a été de réduire les pondérations d'une sous-requête / CTE pour imiter l'effet de la somme des pondérations uniques dans la requête externe.
select month,
movie,
count(distinct person_id) as raw,
sum(w1) as weighted,
sum(w1)/1.0/sum(sum(w2)) over() as share
from (select a.*,
b.weight/count(*) over (partition by a.month, a.movie, a.person_id) w1,
b.weight/count(*) over (partition by a.month, a.person_id) w2
from person_table a
join weights_table b on a.month=b.month and a.person_id=b.person_id) t
group by t.month, t.movie;
I Je ne peux pas dire que je suis fier de cette solution, car elle n'est utile que si j'interroge fréquemment ces données, auquel cas stocker le résultat de la sous-requête dans des tables mensuelles permanentes aurait du sens. Mais comme je ne l'utilise qu'une ou deux fois par mois, avoir une structure de requête plus efficace en place, même au prix d'être verbeux, est ce vers quoi je me sentais le plus enclin.
ce code ne fonctionne pas réellement, car vos partages "mois" (poids_total) sont pour tous les mois. select t. *, weighted, total_weight, weighted / total_weight as share montre que le total est toujours de 187, ce qui n'est pas ce que vous décrivez.
soupir dans les données d'exemple, il n'y a qu'un mois, donc c'est correct, mais sur plusieurs mois, ce code est erroné.
Ah, avec seulement un mois de données dans le tableau, et en décomposant la sous-sélection en CTE pour voir si je peux voir un modèle. Je n'en vois aucun .. et donc il semble que vous aimiez votre truc SQL (pour moi)
with person_table as (
select column1 as month, column2 as movie, column3 as person_id, column4 as unique_visit_id
from values (1, 'a', 1, 1),
(1, 'b', 1, 2),
(1, 'b', 2, 3),
(1, 'a', 2, 4),
(1, 'c', 3, 5),
(1, 'd', 4, 6),
(1, 'a', 2, 7),
(1, 'c', 3, 8),
(1, 'a', 6, 9)
), weight_table as (
select column1 as person_id, column2 as weight
from values (1, 12), (2, 34), (3, 65), (4, 76), (999,999)
), dis_month_people as (
select distinct month, person_id
from person_table
), month_share as (
select month, sum(weight) as total_weight
from dis_month_people dp
join weight_table w on dp.person_id = w.person_id
group by 1
), dis_month_movie_people as (
select distinct month, movie, person_id
from person_table
)
select t.* --, weighted, total_weight
,t.weighted/m.total_weight as share
from (
select
a.month,
a.movie,
count(a.person_id) as raw,
sum(b.weight) as weighted
from dis_month_movie_people a
join weight_table b on a.person_id = b.person_id
group by 1,2
) AS t
join month_share m on t.month = m.month
order by 1,2;
Des exemples de données et les résultats souhaités seraient vraiment utiles, tout comme les définitions des mesures que vous souhaitez calculer.
Laissez-moi les ajouter
Donc, si je comprends cela. Vous voulez que vos données brutes et pondérées ne comptent qu'une seule personne si elle a vu le même film plus d'une fois par mois? Cependant, vous les voulez tous pour le partage. Est-ce correct?
@MikeWalton Pour le brut et le poids, c'est correct, mais si la même personne regarde un autre film, cela compte pour 2. Mais pour le dénominateur de la part, peu importe le film qu'ils regardent. Je veux additionner les poids correspondants de seulement person_id distinct pour le mois.
Pouvez-vous nous montrer le résultat que vous attendez de cet échantillon de données?