如何从数据库表中查询点赞与点踩数量最接近的高争议视频

核心计算逻辑 #

争议性的判断要优先使用相对差值而非绝对差值，才能覆盖100000赞、99000踩这类高互动且评价接近的场景，具体规则如下：

• 先按VideoID分组，统计每个视频的点赞数、点踩数、总互动量
• 争议度计算公式：ABS(点赞数 - 点踩数) / 总互动量，数值越小代表点赞点踩比例越接近，争议性越高
• 过滤掉仅有点赞/仅有点踩的无争议视频
• 相同争议度下，总互动量越高的视频优先级越高

方案1：基于原始表直接查询（无需拆分点赞、点踩表） #

不需要提前拆分两个独立表，直接从原始行为表查询即可，SQL示例（兼容大多数关系型数据库）：

SELECT 
    VideoID,
    SUM(CASE WHEN LikeType = 'Like' THEN 1 ELSE 0 END) AS like_cnt,
    SUM(CASE WHEN LikeType = 'DisLike' THEN 1 ELSE 0 END) AS dislike_cnt,
    -- 计算争议度，数值越小争议越高
    ABS(SUM(CASE WHEN LikeType = 'Like' THEN 1 ELSE 0 END) - SUM(CASE WHEN LikeType = 'DisLike' THEN 1 ELSE 0 END)) 
    / (SUM(1)) AS controversy_score
FROM 
    原始行为表名
WHERE 
    LikeType IN ('Like','DisLike')
GROUP BY 
    VideoID
-- 过滤仅有一种评价的无争议视频
HAVING 
    like_cnt > 0 AND dislike_cnt > 0
ORDER BY 
    controversy_score ASC, -- 争议度从小到大排
    (like_cnt + dislike_cnt) DESC -- 同争议度下互动量高的优先
LIMIT 1;

该语句在你提供的样本数据中运行，会返回VideoID=2的结果，和示例预期完全一致。

方案2：基于已拆分的点赞、点踩表查询 #

假设你拆分生成的点赞表名为like_table、点踩表名为dislike_table，两个表均包含VideoID、UserID字段，可使用如下查询：

SELECT 
    COALESCE(l.VideoID, d.VideoID) AS VideoID,
    COUNT(l.UserID) AS like_cnt,
    COUNT(d.UserID) AS dislike_cnt,
    ABS(COUNT(l.UserID) - COUNT(d.UserID)) / (COUNT(l.UserID) + COUNT(d.UserID)) AS controversy_score
FROM 
    like_table l
FULL OUTER JOIN 
    dislike_table d ON l.VideoID = d.VideoID
-- 过滤仅有一种评价的视频
WHERE 
    l.VideoID IS NOT NULL AND d.VideoID IS NOT NULL
GROUP BY 
    COALESCE(l.VideoID, d.VideoID)
ORDER BY 
    controversy_score ASC,
    (COUNT(l.UserID) + COUNT(d.UserID)) DESC
LIMIT 1;

如果你的数据库不支持FULL OUTER JOIN，可以先通过UNION ALL提取两个表的所有VideoID，再分别关联统计点赞、点踩数即可。

内容的提问来源于stack exchange，提问作者Aman Aggarwal