SQL如何根据行内最小值获取对应列名：分时外呼统计场景实现

方案1：LATERAL JOIN实现（推荐，易扩展、易处理并列场景） #

WITH time_stats AS (
-- 此处直接粘贴你原有的完整SQL查询即可
SELECT 
    leads.id,
    COALESCE(morning, 0) morning,
    COALESCE(afternoon, 0) afternoon,
    COALESCE(evening, 0) evening,
    COALESCE(weekend, 0) weekend
FROM leads
LEFT OUTER JOIN (
    SELECT lead_id, COUNT(*) AS morning
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 0 AND extract('hour' from created_at) < 12)
    GROUP BY lead_id
) morning ON morning.lead_id = leads.id
LEFT OUTER JOIN (
    SELECT lead_id, COUNT(*) AS afternoon
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 12 AND extract('hour' from created_at) < 17)
    GROUP BY lead_id
) afternoon ON afternoon.lead_id = leads.id
LEFT OUTER JOIN (
    SELECT lead_id, COUNT(*) AS evening
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 17 AND extract('hour' from created_at) < 25)
    GROUP BY lead_id
) evening ON evening.lead_id = leads.id
LEFT OUTER JOIN (
    SELECT lead_id, COUNT(*) AS weekend
    FROM lead_activities
    WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (6,7)
    GROUP BY lead_id
) weekend ON weekend.lead_id = leads.id
)
SELECT 
  ts.id,
  t.time_of_day
FROM time_stats ts
CROSS JOIN LATERAL (
  SELECT time_of_day
  FROM (
    VALUES 
      ('morning', ts.morning),
      ('afternoon', ts.afternoon),
      ('evening', ts.evening),
      ('weekend', ts.weekend)
  ) AS v(time_of_day, cnt)
  ORDER BY cnt ASC
  -- 如果需要指定并列最小值的优先级，可补充排序规则，例如优先返回早时段：
  -- ORDER BY cnt ASC, array_position(array['morning','afternoon','evening','weekend'], time_of_day)
  LIMIT 1
) t;

注：原SQL子查询中DISTINCT ON (lead_id)为冗余语法，GROUP BY lead_id已经保证每个lead_id仅返回一行，已直接删除提升性能。

方案2：CASE WHEN实现（语法更简洁，适合固定列场景） #

SELECT 
  id,
  CASE 
    WHEN morning = min_val THEN 'morning'
    WHEN afternoon = min_val THEN 'afternoon'
    WHEN evening = min_val THEN 'evening'
    ELSE 'weekend'
  END AS time_of_day
FROM (
  SELECT 
    *,
    LEAST(morning, afternoon, evening, weekend) AS min_val
  FROM (
    -- 此处粘贴你原有的完整SQL查询即可，同样可删除冗余的DISTINCT ON
    SELECT 
        leads.id,
        COALESCE(morning, 0) morning,
        COALESCE(afternoon, 0) afternoon,
        COALESCE(evening, 0) evening,
        COALESCE(weekend, 0) weekend
    FROM leads
    LEFT OUTER JOIN (
        SELECT lead_id, COUNT(*) AS morning
        FROM lead_activities
        WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 0 AND extract('hour' from created_at) < 12)
        GROUP BY lead_id
    ) morning ON morning.lead_id = leads.id
    LEFT OUTER JOIN (
        SELECT lead_id, COUNT(*) AS afternoon
        FROM lead_activities
        WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 12 AND extract('hour' from created_at) < 17)
        GROUP BY lead_id
    ) afternoon ON afternoon.lead_id = leads.id
    LEFT OUTER JOIN (
        SELECT lead_id, COUNT(*) AS evening
        FROM lead_activities
        WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (0,1,2,3,4,5) AND (extract('hour' from created_at) >= 17 AND extract('hour' from created_at) < 25)
        GROUP BY lead_id
    ) evening ON evening.lead_id = leads.id
    LEFT OUTER JOIN (
        SELECT lead_id, COUNT(*) AS weekend
        FROM lead_activities
        WHERE lead_activities.modality = 'Call' AND lead_activities.bound_type = 'outbound' AND extract('dow' from created_at) IN (6,7)
        GROUP BY lead_id
    ) weekend ON weekend.lead_id = leads.id
  ) t1
) t2;

注：该方案出现并列最小值时，会优先返回CASE判断顺序靠前的列，可自行调整CASE分支顺序自定义优先级。

额外逻辑校验提示 #

PostgreSQL中extract('dow' from 日期)返回值规则为：周日=0、周一=1、周六=6，你原SQL中工作日时段的过滤条件为dow IN (0,1,2,3,4,5)，包含了周日，如果业务需求的工作日仅指周一到周五，可将该条件修改为dow IN (1,2,3,4,5)。

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