A composite score ranks related query pairs by combining co-occurrence, which reflects popularity, with PMI2, which reflects relatedness beyond popularity. section 6.3.1
The approach uses ranked lists to produce a blended ranking, with lower composite scores appearing earlier in the results.
- It aims to favor query pairs that rank well on both measures, while balancing their different strengths. section 6.3.1
- Sparse co-occurrence counts can limit reliability, and PMI2 results can include noisy pairs. section 6.3.1
Related product queries
“green lantern” and “captain america” rank highly by co-occurrence; “hp laptops” and “dell laptops” appear as related queries in the PMI2 results. section 6.3.1
Listing 6.8 Calculating a composite score from co-occurrence and PMI
query = """
SELECT *, (r1 + r2 / (r1 * r2)) / 2 AS comp_score #1
FROM (
SELECT *,
RANK() OVER (PARTITION BY 1 #2
ORDER BY users_cooc DESC) r1, #2
RANK() OVER (PARTITION BY 1 #3
ORDER BY pmi2 DESC) r2 #3
FROM user_related_keywords_pmi)"""
spark.sql(query).createOrReplaceTempView("users_related_keywords_comp_score")
spark.sql("""SELECT k1, k2, users_cooc, ROUND(pmi2, 3) as pmi2,
r1, r2, ROUND(comp_score, 3) as comp_score
FROM users_related_keywords_comp_score
ORDER BY comp_score ASC, pmi2 ASC""").show(20)
See the SQL example for ranking query pairs by co-occurrence and PMI2, calculating a composite score, and ordering the results. section 6.3.1
