按月滚动平均值计算：基于DAX度量值实现ASMP指标计算

计算规则梳理 #

• 核心公式：ASMP = 统计周期内发货总数量 / 12，支持单月/自定义周期统计
• 发货量统计逻辑：统计table1表中ship_date字段非空的记录数，范围为当前滚动周期内全部数据
• 滚动规则：统计周期随时间自动向后延展，累计发货量随周期长度同步更新

示例验证：1月发货5单、2月6单、3月2单，3月统计时总发货量为13，ASMP=13/12≈1.08；4月新增5单后，4月统计总发货量为18，ASMP=18/12=1.5

实现方案 #

方案1：SQL滚动计算（适用于数仓/数据库直接统计） #

按自然月滚动计算的通用代码，适配MySQL、PostgreSQL、Hive等多数主流数据库：

-- 按月统计各月的滚动累计发货量及ASMP
WITH monthly_ship AS (
    -- 先统计每个月的有效发货量
    SELECT 
        DATE_TRUNC('month', ship_date) AS stat_month,
        COUNT(*) AS month_ship_cnt
    FROM table1
    WHERE ship_date IS NOT NULL
    GROUP BY DATE_TRUNC('month', ship_date)
)
SELECT 
    stat_month,
    -- 从最早统计月份到当前月的累计发货量
    SUM(month_ship_cnt) OVER (ORDER BY stat_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_ship_cnt,
    -- 计算ASMP，保留2位小数
    ROUND(SUM(month_ship_cnt) OVER (ORDER BY stat_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 12.0, 2) AS ASMP
FROM monthly_ship
ORDER BY stat_month;

如果需要自定义周期计算，直接调整时间过滤条件即可，示例统计2023年1-6月的ASMP：

SELECT ROUND(COUNT(*) / 12.0, 2) AS custom_period_ASMP
FROM table1
WHERE ship_date IS NOT NULL
AND ship_date BETWEEN '2023-01-01' AND '2023-06-30';

方案2：Python离线计算（适用于数据分析场景） #

import pandas as pd

# 读取table1数据，过滤ship_date非空的记录
df = pd.read_sql("SELECT * FROM table1 WHERE ship_date IS NOT NULL", con=db_connection)
# 转换ship_date为日期格式
df['ship_date'] = pd.to_datetime(df['ship_date'])

# 按月滚动计算ASMP
df['stat_month'] = df['ship_date'].dt.to_period('M')
monthly_cnt = df.groupby('stat_month').size().reset_index(name='month_cnt')
# 计算累计发货量
monthly_cnt['total_cnt'] = monthly_cnt['month_cnt'].cumsum()
# 计算ASMP
monthly_cnt['ASMP'] = round(monthly_cnt['total_cnt'] / 12, 2)

# 输出结果
print(monthly_cnt[['stat_month', 'total_cnt', 'ASMP']])

注意事项 #

• 如果需求为近12个月滚动周期而非全量累计，仅需调整SQL窗口函数的范围、或Pandas的滚动窗口参数即可
• 单月统计时直接统计当月ship_date非空的记录数除以12，完全符合公式要求

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