坪效是销售额除以营业面积，需用窗口函数计算动态滚动均值而非累计值；必须用RANGE按真实日期跨度取数、补全缺漏日期、确保排序唯一性，并通过LAG精确对齐同比周期。
什么是坪效，以及为什么必须用窗口函数
坪效 = 销售额 ÷ 营业面积，但零售场景中，门店面积通常不变，而销售额按日/周/月滚动变化。要画出“动态变化曲线”，本质是计算每个时间点的累计或移动平均坪效，并对比历史同期——这要求在同一行数据中同时访问当前行和前后多行，GROUP BY做不到，JOIN太重且难对齐时间序列，只有窗口函数能自然表达这种“带上下文的逐行计算”。
关键不是“能不能算”，而是“怎么对齐时间粒度+怎么定义‘动态’”。常见错误是直接用SUM(sales) OVER (PARTITION BY store_id ORDER BY date)，结果得到累计坪效（越往后越大），而非真正反映经营质量的滚动坪效（比如近30天平均）。
用AVG() OVER算滚动坪效，但要注意日期范围边界
滚动坪效的核心是：每个日期对应一个“最近N天”的销售额均值，再除以固定面积。不能只依赖ROWS BETWEEN 29 PRECEDING AND CURRENT ROW，因为零售数据常有缺漏（如节假日闭店、系统故障导致某天无销售记录）。
推荐做法是用RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW（PostgreSQL/MySQL 8.0+ 支持），它按真实日期跨度取数，自动跳过空日期：
SELECT
date,
store_id,
AVG(sales) OVER (
PARTITION BY store_id
ORDER BY date
RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
) / area AS daily_efficiency
FROM sales_daily
JOIN stores ON sales_daily.store_id = stores.id;
- 如果数据库不支持
RANGE+INTERVAL（如旧版MySQL），必须先用GENERATE_SERIES或递归CTE补全日期，否则ROWS会把缺省日当作0，拉低均值 -
area必须来自维度表（stores），不能放在事实表里重复存储，否则窗口计算时可能因PARTITION BY粒度不一致导致面积错位 - 别在窗口函数里直接除法：写成
AVG(sales)/area会导致area被重复计算（窗口聚合后才除），应先算均值再除
同比变化率要用LAG()跨年对齐，而不是简单减一年日期
“动态变化曲线”必然包含同比（如2024-05-15 vs 2023-05-15）。但直接WHERE date = date - INTERVAL '1 year'无法在单次查询中产出双列；用LAG(sales, 365)又假设全年365天全有数据——现实中不可能。
正确方式是用LAG() OVER (PARTITION BY store_id ORDER BY date)配合精确的日期偏移：
SELECT
date,
store_id,
eff_30d,
(eff_30d - LAG(eff_30d, 365) OVER (
PARTITION BY store_id ORDER BY date
)) / NULLIF(LAG(eff_30d, 365) OVER (
PARTITION BY store_id ORDER BY date
), 0) AS yoy_change
FROM (
SELECT date, store_id,
AVG(sales) OVER (...) / area AS eff_30d
FROM ...
) t;
-
LAG(..., 365)只在日期严格连续时可靠；若数据缺2023-05-15，则LAG会取到2023-05-14，造成错位。更稳的做法是先用子查询把2023年数据按date + INTERVAL '1 year'映射到2024年时间轴，再JOIN -
NULLIF必须加，否则分母为0时整个表达式变NULL，图表会断线 - 闰年多出的2月29日需单独处理，否则2024-02-29的
LAG(..., 365)会指向2023-02-28，偏差一天
导出曲线前务必检查PARTITION BY和ORDER BY的组合是否唯一
窗口函数结果不稳定，最常见的原因是PARTITION BY store_id ORDER BY date中date不唯一——同一门店同一天有多条销售记录（如分时段POS流水），导致排序不确定，每次执行ROWS BETWEEN取的行可能不同。
解决方法只有两个：
- 确保
ORDER BY末尾加上唯一字段，例如ORDER BY date, sales_id（sales_id为主键或自增ID） - 或者预聚合：先
GROUP BY store_id, date汇总当日总销售额，再在其上开窗。这是生产环境更推荐的做法，避免窗口计算放大IO压力
实际跑出来曲线抖动大？先查SELECT COUNT(<em>) FROM sales_daily GROUP BY store_id, date HAVING COUNT(</em>) > 1，确认有没有未聚合的明细数据混入窗口计算。
窗口函数本身不难，难的是让每一行的“上下文”真正符合业务定义——日期是否连续、分区是否干净、排序是否确定，这三个点漏掉任何一个，曲线就只是好看，不是真相。
