SQL中利用OVER()等核心语法窗口函数计算OEE（设备综合效率）、排名等

admin

2025年8月26日 0:36 本文热度 139

OVER() 是窗口函数（Window Function）的核心语法，用于在不改变行数的情况下，对一组行（称为“窗口”）进行聚合、排序或排名等操作。

基本用法如下：

函数名(列名) OVER (    [PARTITION BY 分组列]     [ORDER BY 排序列]     [ROWS/RANGE 滑动窗口范围])

2. 常见用法示例

（1）聚合函数 + OVER()

计算每个部门的平均工资，同时保留每一行：

SELECT     name,    department,    salary,    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salaryFROM employees;

结果：

（2）排名函数 + OVER()

按工资降序排名：

SELECT     name,    salary,    RANK() OVER (ORDER BY salary DESC) AS salary_rankFROM employees;

（3）滑动窗口计算

计算最近3行的移动平均：

SELECT     date,    revenue,    AVG(revenue) OVER (        ORDER BY date         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW    ) AS moving_avgFROM sales;

ROWS 和 RANGE 是窗口函数中用来定义 “滑动窗口”范围的语法，告诉数据库从哪一行到哪一行参与计算。ROWS是按物理行数（前N行、后N行），RANGE是按逻辑值范围（前N个值、后N个值）。

PRECEDING 是窗口函数中用来定义 “边界从当前行往前数” 的关键词，表示“往前多少行/值”。

所以

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

的意思就是对每一行，取“它自己”加上“往前数 2 行”这一共 3 行的数据，用来做计算。

4. 常见窗口函数

5.计算OEE

这里举一个关于按照班次对OEE计算的案例。

OEE（Overall Equipment Effectiveness，设备综合效率）是衡量一台设备真正有效生产时间占理论最大生产时间的百分比。

OEE的计算公式是:

OEE = 实际的节拍时间 * (实际产出 - 在break down时间的产出) / 计划工作时间

则按班次时聚合的代码为

SELECT    equipment,    shift_date,    shift_id,
    -- 累计计划工作时间    SUM(plan_min) OVER (        PARTITION BY equipment        ORDER BY shift_date, shift_id        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    ) AS cum_plan_min,
    -- 累计 (实际产出 - BreakDown 产出损失)    SUM(actual_output_qty - breakdown_output_loss_qty) OVER (        PARTITION BY equipment        ORDER BY shift_date, shift_id        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    ) AS cum_net_output,
    -- 当前班次的实际节拍（如需用最新节拍，可改用 LAST_VALUE）    actual_cycle_time_min,
    -- 滚动 OEE = 节拍 × 累计净产出 ÷ 累计计划时间    actual_cycle_time_min    * cum_net_output    / NULLIF(cum_plan_min, 0) AS rolling_oeeFROM shift_oee;