如何为多值列对应新列分配单值并设置每日固定20个预约槽位

一、每日固定20个预约槽位的正确计算 #

SQL层面实现 #

先提取所有唯一日期，再为每个日期生成20个独立槽位，最后关联客户数据统计已用/可用数，彻底避免重复日期的干扰：

WITH unique_dates AS (
    SELECT DISTINCT date FROM customer_interactions
),
daily_slots AS (
    SELECT 
        ud.date,
        s.slot_number
    FROM unique_dates ud
    -- 生成1到20的槽位编号
    CROSS JOIN (
        SELECT 1 AS slot_number UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
        SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
        SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
        SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
    ) s
)
SELECT 
    ds.date,
    COUNT(ci.customer_id) AS used_slots,
    20 - COUNT(ci.customer_id) AS available_slots
FROM daily_slots ds
LEFT JOIN customer_interactions ci ON ds.date = ci.date
GROUP BY ds.date
ORDER BY ds.date;

可视化工具（Power BI/Tableau）层面实现 #

1. 创建日期维度表：包含所有需要统计的日期（可通过工具自带的日期生成功能创建）
2. 为日期表添加槽位行：用工具的“生成行”功能，给每个日期生成20条记录，每条对应一个槽位编号
3. 关联客户表：将日期表与客户表按date字段关联
4. 创建计算字段：
   • 已用槽位：COUNT(customer_id)
   • 可用槽位：20 - COUNT(customer_id)

二、多值列对应新列单个值的分配 #

根据不同场景，有以下几种实现方式：

场景1：按规则匹配固定值 #

比如根据客户标签中的关键词分配优先级：

SELECT 
    customer_id,
    date,
    customer_tags,
    CASE
        WHEN customer_tags LIKE '%VIP%' THEN 'High'
        WHEN customer_tags LIKE '%Referral%' THEN 'Medium'
        ELSE 'Low'
    END AS priority
FROM customer_interactions;

场景2：提取多值中的指定项 #

如果多值以分隔符（如逗号）分隔，提取第一个值：

SELECT 
    customer_id,
    date,
    customer_tags,
    SUBSTRING_INDEX(customer_tags, ',', 1) AS primary_tag
FROM customer_interactions;

场景3：按聚合规则取最具代表性的值 #

比如同一客户有多个交互类型，取出现次数最多的类型：

WITH customer_type_stats AS (
    SELECT 
        customer_id,
        interaction_type,
        COUNT(*) AS type_count
    FROM customer_interactions
    GROUP BY customer_id, interaction_type
)
SELECT 
    customer_id,
    FIRST_VALUE(interaction_type) OVER (PARTITION BY customer_id ORDER BY type_count DESC) AS primary_interaction_type
FROM customer_type_stats;

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