使用Partition By动态计算员工每分钟产品售卖比率的SQL实现问题

现有数据集 #

date        store    employee     products     sales        
20210101       a        ben          5         laptop        
20210101       a        ben         10         monitor       
20210201       b        tim         15         laptop        
20210301       b        tim         10         tablet         
20210301       a        ann         30         monitor        

需求说明 #

• 基础计算规则：每个员工每个工作日的班次时长固定为6小时，单员工单日每分钟售卖比率为当日售卖产品总数除以总分钟数，示例：员工ben2021年1月1日的比率计算为 (5+10)/(6*60) = 0.04
• 动态适配筛选：支持不同维度筛选后的自动比率计算，示例：筛选门店a时，总售卖比率为 (5+10+30) / (6*60*2) = 0.06；筛选品类、日期等其他维度时也可输出对应结果。

原查询问题 #

你原来使用的固定PARTITION BY的窗口函数存在两个问题：

1. 聚合维度写死，只能计算单员工单日单品类的比率，无法跟随筛选条件动态调整聚合范围
2. 分母固定为360（6*60），没有统计当前筛选范围内的独立「日期+门店+员工」班次数量，多班次聚合时分母计算错误。

调整后实现方案 #

核心逻辑 #

不管筛选条件如何变化，统一使用以下公式计算：
符合筛选条件的产品总销量 / ( 筛选范围内独立[日期+门店+员工]班次数量 * 6 * 60 )

不同场景实现代码 #

SQL动态查询场景

直接通过聚合函数动态计算，筛选条件写在WHERE子句中即可自动适配：

SELECT 
  SUM(products) / (COUNT(DISTINCT CONCAT(date, '_', store, '_', employee)) * 360) AS 每分钟售卖比率
FROM 你的表名
-- 按需添加筛选条件即可
-- 例：筛选门店a：WHERE store = 'a'
-- 例：筛选laptop品类：WHERE sales = 'laptop'
-- 例：筛选2021年1月数据：WHERE LEFT(date,6) = '202101'

BI工具度量值场景（以Power BI DAX为例）

直接新建度量值，会自动适配报表页面的所有筛选器、行标签维度：

每分钟售卖比率 = 
VAR 总销量 = SUM('表名'[products])
VAR 总班次数量 = COUNTROWS(DISTINCT(SELECTCOLUMNS('表名', "日期", '表名'[date], "门店", '表名'[store], "员工", '表名'[employee])))
RETURN DIVIDE(总销量, 总班次数量 * 360, 0)

效果验证 #

• 筛选门店a：总销量=45，总班次=2（ben20210101、ann20210301），计算结果=45/(2*360)=0.0625，四舍五入后为0.06，符合示例要求
• 筛选laptop品类：总销量=20，总班次=2（ben20210101、tim20210201），计算结果=20/(2*360)≈0.028
• 筛选员工ben：总销量=15，总班次=1，计算结果=15/360≈0.04，符合示例要求

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