PostgreSQL如何统计表中所有行array字段各元素频率并计算自定义得分

核心实现逻辑 #

• 用PostgreSQL内置的unnest()函数将数组类型的phone_num_list字段拆分为单个手机号的行记录，同时保留对应的关联邮箱字段
• 按手机号分组，分别统计每个手机号关联的去重邮箱数量、总出现次数
• 按照公式得分=去重关联邮箱数×总出现次数计算得分，最后按得分降序排序

最终查询语句 #

将语句中的your_table_name替换为实际表名即可直接运行：

SELECT
  unnest(phone_num_list) AS phone,
  COUNT(DISTINCT email) * COUNT(*) AS score
FROM
  your_table_name
GROUP BY
  phone
ORDER BY
  score DESC;

如果需要查看中间统计维度（关联邮箱列表、出现次数），可以使用扩展版本：

SELECT
  phone,
  ARRAY_AGG(DISTINCT email) AS distinct_emails_associated,
  COUNT(*) AS freq_of_phone_num,
  COUNT(DISTINCT email) * COUNT(*) AS final_score
FROM
  your_table_name,
  unnest(phone_num_list) AS phone
GROUP BY
  phone
ORDER BY
  final_score DESC;

内容的提问来源于stack exchange，提问作者Abinash Biswal