首页 文章

评级查询 - 指定ID的评级百分比超过所有ID的评级

提问于
浏览
0

我继承了以下查询,它获得了指定“尝试”的平均评级(用于触地的橄榄球行话) . 希望我们仍然可以使用它 .

SELECT i.id, i.title,
        (
            CASE
                WHEN
                    COUNT( r.rating ) > 0
                THEN
                    (
                        SUM( r.rating ) / COUNT( r.rating )
                    )
                ELSE
                    0
            END
        ) AS rating,

        COALESCE( er.id, 0 ) AS has_existing_rating
    FROM
        (
            SELECT 1 AS id, 'Try 1 – Israel Dagg v Chiefs.' as title UNION ALL
            SELECT 2 AS id, 'Try 2 – Israel Dagg v Chiefs.' as title UNION ALL
            SELECT 3 AS id, 'Try 3 – Leilia Masaga v Crusaders.' as title UNION ALL
            SELECT 4 AS id, 'Try 4 – Israel Dagg v Chiefs.' as title UNION ALL
            SELECT 5 AS id, 'Try 5 – Fred Flintstone v Hurricanes.' as title UNION ALL
            SELECT 6 AS id, 'Try 6 – Israel Dagg v Chiefs.' as title UNION ALL
            SELECT 7 AS id, 'Try 7 – Israel Dagg v Chiefs.' as title
        ) AS i

    LEFT OUTER JOIN
        tryPoll r
    ON
        i.id = r.try_id

    <!--
        Join this to the rating table AGAIN to see if the current
        user has already rated the given try.
    -->
    LEFT OUTER JOIN
        tryPoll er
    ON
        (
                er.try_id = i.id
            AND
                er.ip_address = '#cgi.remote_addr#'
            AND
                er.user_agent = '#cgi.http_user_agent#'
        )

    GROUP BY
        i.id,
        r.try_id,
        er.id,
        i.title
    ORDER BY
        i.id ASC

因此,如下表所示(评级= 1仅表示在这种情况下单一投票)....

tryPoll

id     try_id     rating     ip_address     user_agent
    ------------------------------------------------------
    1      2          1          58.28.220.51   Mozilla/5.0 blah
    2      2          1          58.28.220.52   Mozilla/5.0 blah
    3      6          1          58.28.220.53   Mozilla/5.0 blah
    4      4          1          58.28.220.54   Mozilla/5.0 blah

...对于try_id#2,查询将返回(1 1)/ 2 = 1的平均等级

但是,我需要调整此查询以返回特定TRY的评级百分比超过所有尝试的评级 . 即,在上面的例子中,确定所有尝试的所有评级的百分比归因于try_id#2

我怎么做到这一点?

1 回答

  • 1

    你可以尝试这个解决方案:

    DECLARE @try_id INT;
    SET @try_id=2;
    
    SELECT  r.try_id,
            SUM(r.rating) AS ratings_per_try,
            SUM(SUM(r.rating)) OVER() AS ratings_overall,
            SUM(r.rating)*1.0 / NULLIF(SUM(SUM(r.rating)) OVER(), 0) AS percent_try     
    FROM    Table r
    GROUP BY r.try_id
    

相关问题