sql - 根据多个列值选择最具体的行匹配

Question

我目前正在研究一项内容定位功能，我们会根据医生的类别/国家/地区向他们展示不同的页面。

这就是我们的 Doctor 表的样子，

 id |  name  | age | region | country | doctor_category 
----+--------+-----+--------+---------+-----------------
  1 | Deepak |  25 | APAC   | JP      | GP
  2 | Anil   |  25 | APAC   | JP      | null
  3 | Ajay   |  27 | APAC   | IN      | null
  4 | Amit   |  28 | NA     | CN      | null

我们所有的页面都是动态生成的，这是我们的页表的样子，

 page_id | target_region | target_countries | target_categories 
---------+---------------+------------------+-------------------
       1 | APAC          | null             | null
       2 | APAC          | JP               | null
       3 | APAC          | JP               | GP
       4 | null          | null             | null

如果没有条件匹配，第4页是默认页

预期输出:(最佳特定匹配)

用户(1) => 页面(3)

用户(2) => 页面(2)

用户(3) => 页面(1)

User(4) => Page(4)

我在 posgresql 中尝试了很多 CASE/GROUP BY/DISTINCT ON ，但没有成功，因为一个或多个场景失败了。我不是核心数据库专家，所以我可能缺少一些内置函数来实现相同的目的。

如有任何建议，我们将不胜感激。

Answer 1

另一种解决方案，仅适用于 PostgreSQL:

SELECT DISTINCT
  id,
  first_value(page_id) OVER (
    PARTITION BY id
    ORDER BY target_categories IS NULL, target_countries IS NULL, target_region IS NULL
  )
FROM doctors
  LEFT JOIN pages ON (target_categories = doctor_category OR target_categories IS NULL)
    AND (target_countries = country OR target_countries IS NULL)
    AND (target_region = region OR target_region is null);

请注意，您的问题没有严格明确定义。好像一个页面匹配了国家但是没有分类，另一个页面匹配了分类但是没有国家，不知道该选哪个。

通过选择哪个参数具有最高优先级来打破平局。这是 ORDER BY 行的工作。第一列具有最高优先级。请随意更改它们出现的顺序。

否则查询会像这样:

• 选择医生可接受的所有页面
• 按每位医生的准确度排序
• 只为每位医生选择第一个值
• 由于查询会在每个可接受的页面中产生一行(相同的)行，因此我们使用 DISTINCT 删除重复的行。