&

SQL中利用OVER()等核心語法窗口函數計算OEE（設備綜合效率）、排名等

admin

2025年8月26日 0:36 本文熱度 34

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;