怎样在SQL中使用窗口函数分析社交网络的用户关系密度？-SQL-PHP中文网

窗口函数计算用户关系密度需用count() over(partition by user_id)统计每人好友数，除以count() over()得全局用户总数，并须先用distinct去重边、where过滤自环，再cast转浮点避免整除。

怎样在sql中使用窗口函数分析社交网络的用户关系密度？

窗口函数怎么算每个用户的“关系密度”

关系密度不是直接存的字段，得靠窗口函数动态计算：对每个用户，统计其好友数占全站总用户数的比例。核心是用 COUNT(*) OVER (PARTITION BY user_id) 算每人好友数，再除以 COUNT(*) OVER () 得全局用户总数。

常见错误是误用 GROUP BY 后再套窗口——这样会先聚合丢数据，导致无法关联原始关系边。正确做法是：从 friendships 表（每行代表一条单向或双向关系）出发，不提前分组，直接开窗。

若关系表是双向存储（A→B 和 B→A 都存在），用 COUNT(*) OVER (PARTITION BY user_id) 即可
若是单向存储（只存 A→B），需先 UNION ALL 反向边，再开窗，否则漏算入度
除法前务必用 CAST(... AS FLOAT) 或乘以 1.0，避免整数除法截断为 0

如何避免密度值被重复边或自环污染

社交图里常有脏数据：同一对用户多次加好友、用户自己关注自己。这些会让 COUNT 失真，窗口函数不会自动去重。

必须在开窗前清洗：

用 DISTINCT user_id, friend_id 去重边（注意：不能只去 user_id，要对关系对去重）
加 WHERE user_id != friend_id 过滤自环
如果用 CTE 预处理，记得把清洗后的结果作为窗口函数的输入源，而不是在窗口内再过滤

示例片段：

WITH clean_edges AS (
  SELECT DISTINCT user_id, friend_id
  FROM friendships
  WHERE user_id != friend_id
)
SELECT 
  user_id,
  CAST(COUNT(*) OVER (PARTITION BY user_id) AS FLOAT) / COUNT(*) OVER () AS density
FROM clean_edges;

为什么用 `RANK()` 或 `PERCENT_RANK()` 比直接排序更可靠

单纯按密度 ORDER BY density DESC 只能看 Top N，但运营常需要“密度前 10% 的用户”这类相对定位——这时 PERCENT_RANK() 比手算百分位更稳。

链接到社交网络的动态图特效

下载

关键差异：

PERCENT_RANK() 返回的是“比当前值小的占比”，范围是 [0,1)，天然适配分位阈值判断
RANK() 在并列时跳名次（比如两个第一，下一个就是第三），而 DENSE_RANK() 不跳，选哪个取决于业务是否允许并列用户共享同一档位
所有排名函数必须配合 ORDER BY 子句，且该子句作用于整个窗口，不能只写字段名，得写完整表达式如 ORDER BY CAST(COUNT(*) OVER (PARTITION BY user_id) AS FLOAT) / COUNT(*) OVER () DESC

MySQL 8.0+ 和 PostgreSQL 的语法雷区

PostgreSQL 对窗口函数支持更宽松，MySQL 则在子查询和派生表中限制较多。最常踩的坑是：MySQL 不允许在 SELECT 里定义窗口函数后，同一层 WHERE 中引用它。

例如这个写法在 MySQL 中报错：

SELECT user_id, density 
FROM (
  SELECT user_id, COUNT(*) OVER (PARTITION BY user_id) / COUNT(*) OVER () AS density
  FROM friendships
) t 
WHERE density > 0.01;

解决办法只有两个：