时间戳

首页 > 解决方案 > 根据值获取名称列

sql - 根据值获取名称列

问题描述

我有一个表格，可以计算符合每个父记录条件的关联记录数。请参见下面的示例：

注意 - 早上、下午和晚上只是工作日

| id | morning | afternoon | evening | weekend |
| -- | ------- | --------- | ------- | ------- |
|  1 |    0    |     2     |    3    |    1    |
|  2 |    2    |     9     |    4    |    6    |

我想要实现的是确定哪些列具有最低值并获取它们的列名：

| id | time_of_day |
| -- | ----------- |
|  1 |  morning    |
|  2 |  afternoon  |

这是我当前生成第一个表的 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 DISTINCT ON (lead_id) 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 DISTINCT ON (lead_id) 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 DISTINCT ON (lead_id) 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 DISTINCT ON (lead_id) 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

标签： sqldatabasepostgresqljoinaggregate-functions

解决方案

您可以使用CASE//来检查特定条件并产生不同的值WHEN。ELSE例如：

with
q as (
  -- your query here
)
select
  id,
  case
    when morning <= least(afternoon, evening, weekend) then 'morning'
    when afternoon <= least(morning, evening, weekend) then 'afternoon'
    when evening <= least(morning, afternoon, weekend) then 'evening'
    else 'weekend'
  end as time_of_day
from q

推荐阅读