如何计算按工作日、小时维度的日均、小时均联系量

核心计算逻辑：你需要的时段平均联系量，本质是「指定时间范围内，同工作日同时段的总联系量 ÷ 该工作日+时段在时间范围内出现的总天数」，比如过去3个月共有13个周日，周日12AM的平均联系量就是该时段总联系量除以13。以下是不同场景下的实现方法：

1. Excel/Power Query 实现（适合无代码基础用户） #

• 第一步：把全量contact表导入Power Query，筛选你需要统计的时间范围（比如过去3个月）
• 第二步：添加两个自定义计算列：
  • 工作日列：公式为= Date.DayOfWeekName([开始日期])，直接返回周日/周一这类文本
  • 时段列：把数值格式的开始小时转为12AM/1AM这类格式，公式示例：

    = if [开始小时] = 0 then "12AM" else if [开始小时] <12 then Text.From([开始小时])&"AM" else if [开始小时] =12 then "12PM" else Text.From([开始小时]-12)&"PM"
    

• 第三步：分组聚合：按「工作日」「时段」分组，添加两个聚合指标：
  • 总联系量：对联系人数字段求和
  • 有效天数：对「开始日期」去重计数
• 第四步：添加自定义列计算平均值：= [总联系量]/[有效天数]
• 第五步：生成透视表，行维度选「工作日」，列维度选「时段」，值选刚计算的平均值，即可得到你需要的展示格式。后续数据更新只需点击刷新按钮，不需要手动处理。

2. SQL 实现（适合数据存储在数据库的场景） #

直接执行查询即可得到结果，不同数据库的日期函数可以对应替换：

SELECT 
    -- 提取工作日名称：MySQL用DAYNAME，PostgreSQL用TO_CHAR(开始日期, 'Day')，SQL Server用DATENAME(WEEKDAY, 开始日期)
    DAYNAME(开始日期) AS 工作日,
    -- 两种平均逻辑按需选择：AVG(联系人数)是单条记录的平均，SUM(联系人数)/COUNT(DISTINCT 开始日期)是每日该时段总联系量的平均
    SUM(联系人数)/COUNT(DISTINCT 开始日期) AS 平均联系量,
    -- 格式化时段为12AM/1AM格式
    CASE 
        WHEN 开始小时 = 0 THEN '12AM'
        WHEN 开始小时 < 12 THEN CONCAT(开始小时, 'AM')
        WHEN 开始小时 = 12 THEN '12PM'
        ELSE CONCAT(开始小时 - 12, 'PM')
    END AS 时段
FROM contact
WHERE 开始日期 >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH) -- 筛选过去3个月数据，按需调整时间范围
GROUP BY 工作日, 时段

查询结果导出到Excel做简单透视即可得到目标格式的表格。

3. Python Pandas 实现（适合大数据量场景） #

示例代码可直接复用：

import pandas as pd

# 读取contact表，可替换为read_sql从数据库读数据
df = pd.read_excel("你的contact表存储路径.xlsx")
# 筛选过去3个月的数据，按需调整时间范围
df = df[df['开始日期'] >= pd.Timestamp.now() - pd.DateOffset(months=3)]
# 新增工作日列
df['工作日'] = df['开始日期'].dt.day_name(locale='zh_CN')
# 新增格式化时段列
def hour_to_period(hour):
    if hour == 0:
        return '12AM'
    elif hour < 12:
        return f'{hour}AM'
    elif hour == 12:
        return '12PM'
    else:
        return f'{hour-12}PM'
df['时段'] = df['开始小时'].apply(hour_to_period)
# 按工作日、时段分组计算平均值
result = df.groupby(['工作日', '时段'])['联系人数'].mean().reset_index()
# 转成你需要的透视表格式
pivot_result = result.pivot(index='工作日', columns='时段', values='联系人数').reset_index()
# 导出为Excel
pivot_result.to_excel("时段平均联系量统计结果.xlsx", index=False)

内容的提问来源于stack exchange，提问作者D.Fernandes