Databricks SQL需求：筛选同一客户下特定代理条件的保单

初始需求实现 #

不需要用collect_list聚合，用窗口函数可以更高效地实现需求。核心思路是按客户分组，统计每组内不同AgentId的数量，以此判断是否需要标记：

SELECT
  PolicyId,
  CustomerId,
  AgentId,
  -- 标记同一客户下是否存在不同AgentId的保单
  CASE
    WHEN COUNT(DISTINCT AgentId) OVER (PARTITION BY CustomerId) > 1 THEN '不同Agent'
    ELSE '同一Agent'
  END AS AgentStatus
FROM POLICY;

说明 #

• COUNT(DISTINCT AgentId) OVER (PARTITION BY CustomerId)：按CustomerId分组，计算每组内唯一AgentId的数量
• 如果数量大于1，说明该客户有不同Agent的保单，标记为不同Agent；否则标记为同一Agent

需求变更实现 #

新增Agent分组表后，需要先关联保单表和分组表，再通过窗口函数判断客户的Agent是否属于同一分组：

方案1：直接筛选出符合条件的保单 #

WITH PolicyWithGroup AS (
  -- 关联保单表和Agent分组表，获取每个保单对应的GroupCode
  SELECT
    p.PolicyId,
    p.CustomerId,
    p.AgentId,
    ag.GroupCode
  FROM POLICY p
  INNER JOIN AgentGroup ag 
    ON p.AgentId = ag.AgentId
)
SELECT
  PolicyId,
  CustomerId,
  AgentId,
  GroupCode,
  '不同Agent但同组' AS AgentGroupStatus
FROM PolicyWithGroup
WHERE
  -- 同一客户下存在不同AgentId
  COUNT(DISTINCT AgentId) OVER (PARTITION BY CustomerId) > 1
  -- 同一客户下所有Agent的GroupCode完全相同
  AND COUNT(DISTINCT GroupCode) OVER (PARTITION BY CustomerId) = 1;

方案2：标记所有保单的状态 #

如果需要给所有保单标记状态（包括同一Agent、不同Agent同组、不同Agent不同组），可以用CASE分支：

WITH PolicyWithGroup AS (
  SELECT
    p.PolicyId,
    p.CustomerId,
    p.AgentId,
    ag.GroupCode
  FROM POLICY p
  INNER JOIN AgentGroup ag 
    ON p.AgentId = ag.AgentId
)
SELECT
  PolicyId,
  CustomerId,
  AgentId,
  GroupCode,
  CASE
    WHEN COUNT(DISTINCT AgentId) OVER (PARTITION BY CustomerId) = 1 THEN '同一Agent'
    WHEN COUNT(DISTINCT GroupCode) OVER (PARTITION BY CustomerId) = 1 THEN '不同Agent但同组'
    ELSE '不同Agent且不同组'
  END AS AgentGroupStatus
FROM PolicyWithGroup;

说明 #

• 先通过CTE关联两张表，获取每个保单对应的分组信息
• 用两个窗口函数分别统计每个客户的唯一Agent数和唯一分组数，组合判断状态

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