PostgreSQL自连接查询平台剧集占比超50%的配对组合

需求说明 #

需要筛选符合规则的平台配对：平台2与平台1的共有剧集数，占平台1自身总剧集数的比例超过50%。最终输出重命名后的platform1、platform2字段，以及两个平台的共有剧集占比numcommon。

问题场景 #

统计多平台播出剧集的跨平台重合度，所用的基础数据集结构如下：

|seriesid|platform       |
|--------|---------------|
|4       |Netflix        |
|6       |Netflix        |
|8       |Netflix        |
|13      |Netflix        |
|13      |TNT            |
|13      |Free Services  |
|16      |Hulu           |
|16      |Netflix        |
|16      |Free Services  |
|20      |Netflix        |

现有实现 #

参考社区用户@Julius的查询思路，当前使用的查询语句如下：

select 
  sp1.platform as platform1, 
  sp2.platform as platform2, 
  count(*) as numcommon 
from 
  seriesonplatform AS sp1
  JOIN seriesonplatform AS sp2 
    ON sp1.seriesid = sp2.seriesid 
    AND sp1.platform <> sp2.platform
group by
  sp1.platform,
  sp2.platform
order by sp1.platform, sp2.platform, numcommon

现存问题 #

该查询仅统计了平台间的共有剧集绝对数量，既没有计算占比，也没有按50%的阈值做筛选，输出示例片段：

|platform1|platform2      |numcommon|
|---------|---------------|---------|
|A&E      |Free Services  |1        |
|A&E      |Prime Video    |1        |
|AcornTV  |CBS All Access |1        |
|AcornTV  |Free Services  |1        |
|AcornTV  |Hoopla         |2        |
|AcornTV  |Hulu           |1        |
|AcornTV  |Netflix        |6        |
|AcornTV  |Prime Video    |2        |
|Adult Swim|Free Services  |1        |
|Adult Swim|HBO MAX        |1        |
|BET      |BET+           |1        |
|BET      |Free Services  |1        |
|BET+     |BET            |1        |
|BET+     |Free Services  |1        |
|Bravo    |Free Services  |1        |
|Bravo    |Netflix        |2        |
|BritBox  |Hoopla         |2        |
|CBS All Access|AcornTV        |1        |
|CBS All Access|Free Services  |6        |
|CBS All Access|Hulu           |1        |
|CBS All Access|Netflix        |7        |
|CBS All Access|fuboTV         |2        |
|Cartoon Network|Free Services  |2        |
|Cartoon Network|HBO MAX        |2        |
|Cartoon Network|Hulu           |1        |
|Cartoon Network|Netflix        |1        |
|Comedy Central|Hoopla         |1        |
|Comedy Central|Netflix        |1        |
|Crunchyroll|Free Services  |3        |

优化方案 #

要满足输出要求，需要补两处核心逻辑：

• 预先统计每个平台自身的总剧集数，作为占比计算的分母
• 用共有剧集数除以平台总剧集数得到占比，通过HAVING子句筛选占比超50%的配对

优化后的查询语句：

-- 预先统计每个平台的总剧集数，避免重复计算
WITH platform_total AS (
  SELECT 
    platform,
    COUNT(DISTINCT seriesid) AS total_series
  FROM seriesonplatform
  GROUP BY platform
)
SELECT 
  sp1.platform AS platform1,
  sp2.platform AS platform2,
  -- 计算占比时乘1.0转浮点数，避免整数除法截断，保留两位小数
  ROUND(COUNT(DISTINCT sp1.seriesid) * 1.0 / pt1.total_series, 2) AS numcommon
FROM seriesonplatform sp1
-- 关联平台1的总剧集数
JOIN platform_total pt1 
  ON sp1.platform = pt1.platform
-- 自连接匹配跨平台播出的共有剧集
JOIN seriesonplatform sp2 
  ON sp1.seriesid = sp2.seriesid
  AND sp1.platform <> sp2.platform
GROUP BY 
  sp1.platform, 
  sp2.platform, 
  pt1.total_series
-- 筛选占比超过50%的平台配对
HAVING numcommon > 0.5
ORDER BY sp1.platform, sp2.platform;

预期输出 #

调整后仅保留占比超50%的平台配对，numcommon字段直接返回占比值，示例片段：

platform1    |   platform2   | numcommon 
-----------------+---------------+-----------
 AcornTV         | Netflix       |      0.60
 Adult Swim      | Free Services |      1.00
 Adult Swim      | HBO MAX       |      1.00
 BET             | BET+          |      1.00
 BET             | Free Services  |      1.00
 BET+            | BET           |      1.00
 BET+            | Free Services  |      1.00
 Bravo           | Netflix       |      1.00
 Cartoon Network  | Free Services |      0.67
 Cartoon Network  | HBO MAX       |      0.67
 CBS All Access   | Netflix       |      0.58
 Crunchyroll      | Free Services |      1.00
 Disney           | Disney+       |      1.00
 Disney           | Free Services |      1.00
 fuboTV           | Netflix       |      0.60
 Funimation       | Free Services |      0.67
 HBO              | HBO MAX       |      1.00
 History          | Hulu          |      0.67
 History          | Netflix       |      0.67
 IFC              | fuboTV        |      0.67
 IFC              | Netflix       |      0.67
 IndieFlix        | Free Services |      1.00
 IndieFlix        | Hoopla        |      1.00
 Lifetime         | Netflix       |      0.67
 Showtime         | fuboTV        |      1.00
 Showtime         | Netflix       |      0.67
 Sundance         | Free Services |      1.00
 Sundance         | Prime Video   |      1.00

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