数据库设计与 SQL 优化

-- 1. 产线维度表
CREATE TABLE dim_line (
    line_id        INT PRIMARY KEY,
    line_name      NVARCHAR(50) NOT NULL,
    workshop       NVARCHAR(100),
    target_oee     DECIMAL(5,4) DEFAULT 0.85
);

-- 2. 班次维度表
CREATE TABLE dim_shift (
    shift_id       INT PRIMARY KEY,
    shift_name     NVARCHAR(20) NOT NULL,
    start_time     TIME NOT NULL,
    end_time       TIME NOT NULL
);

-- 3. 产品维度表
CREATE TABLE dim_product (
    product_id     INT PRIMARY KEY IDENTITY(1,1),
    product_code   NVARCHAR(30) UNIQUE NOT NULL,
    product_name   NVARCHAR(100),
    category       NVARCHAR(30),
    target_cycle_time DECIMAL(6,1)
);

-- 4. 机台维度表
CREATE TABLE dim_machine (
    machine_id     INT PRIMARY KEY IDENTITY(1,1),
    machine_name   NVARCHAR(50) NOT NULL,
    line_id        INT REFERENCES dim_line(line_id),
    machine_type   NVARCHAR(30),
    status         NVARCHAR(10) DEFAULT '运行',
    install_date   DATE
);

-- 5. 产量事实表 (核心表)
CREATE TABLE fact_production (
    id             INT PRIMARY KEY IDENTITY(1,1),
    machine_id     INT NOT NULL REFERENCES dim_machine(machine_id),
    line_id        INT NOT NULL REFERENCES dim_line(line_id),
    shift_id       INT NOT NULL REFERENCES dim_shift(shift_id),
    product_id     INT NOT NULL REFERENCES dim_product(product_id),
    production_date DATETIME NOT NULL,
    good_count     INT DEFAULT 0,
    total_count    INT DEFAULT 0,
    yield_rate     AS (CASE WHEN total_count > 0
                     THEN CAST(good_count AS FLOAT) / total_count
                     ELSE 0 END)
);

-- 第2页，每页10条 (SQL Server 2012+)
SELECT *
FROM fact_production
ORDER BY production_date DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

-- 当前时间
SELECT GETDATE()              -- 2026-06-10 14:30:00

-- DATEADD(单位, 数量, 日期)
SELECT DATEADD(DAY, -7, GETDATE())    -- 7天前
SELECT DATEADD(HOUR, -8, GETDATE())   -- 8小时前
SELECT DATEADD(MONTH, -1, GETDATE())  -- 1个月前

-- DATEDIFF(单位, 开始, 结束)
SELECT DATEDIFF(DAY, downtime_start, downtime_end) AS days_down
FROM fact_downtime

-- 实战：查询最近7天的产量
SELECT *
FROM fact_production
WHERE production_date >= DATEADD(DAY, -7, GETDATE());

-- ISNULL(): SQL Server 专有，只支持2个参数
SELECT ISNULL(reason, '未记录') AS reason
FROM fact_downtime

-- COALESCE(): ANSI 标准，支持多个参数，返回第一个非NULL值
SELECT COALESCE(reason, downtime_type, '未知') AS reason
FROM fact_downtime

-- 把每条产线的所有机台名拼成一个字符串
SELECT
    l.line_name,
    STRING_AGG(m.machine_name, ', ') AS machines
FROM dim_line l
JOIN dim_machine m ON l.line_id = m.line_id
GROUP BY l.line_name;

-- 用 CTE 先算每条产线的基础指标
WITH line_kpi AS (
    SELECT
        line_id,
        SUM(good_count) AS total_good,
        SUM(total_count) AS total_output,
        COUNT(*) AS record_count
    FROM fact_production
    WHERE production_date >= DATEADD(DAY, -30, GETDATE())
    GROUP BY line_id
)
-- 然后用 CTE 结果做最终查询
SELECT
    l.line_name,
    k.total_good,
    k.total_output,
    CAST(k.total_good AS FLOAT) / NULLIF(k.total_output, 0)
        AS yield_rate
FROM line_kpi k
JOIN dim_line l ON k.line_id = l.line_id
ORDER BY yield_rate DESC;

-- 把临时表的数据同步到产量表：有就更新，没有就插入
MERGE INTO fact_production AS target
USING staging_production AS src
    ON target.machine_id = src.machine_id
    AND target.production_date = src.production_date
WHEN MATCHED THEN
    UPDATE SET
        good_count  = src.good_count,
        total_count = src.total_count
WHEN NOT MATCHED THEN
    INSERT (machine_id, line_id, shift_id, product_id,
            production_date, good_count, total_count)
    VALUES (src.machine_id, src.line_id, src.shift_id,
            src.product_id, src.production_date,
            src.good_count, src.total_count);

SELECT
    l.line_name,
    p.production_date,
    CAST(p.good_count AS FLOAT) / NULLIF(p.total_count, 0) AS oee,
    RANK() OVER (
        PARTITION BY p.production_date
        ORDER BY CAST(p.good_count AS FLOAT)
                   / NULLIF(p.total_count, 0) DESC
    ) AS oee_rank
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE p.production_date >= DATEADD(DAY, -7, GETDATE());

-- LAG: 看前一行   LEAD: 看后一行
SELECT
    m.machine_name,
    d.downtime_start,
    d.downtime_end,
    d.duration_min,
    -- 上一次停机结束时间
    LAG(d.downtime_end) OVER (
        PARTITION BY d.machine_id
        ORDER BY d.downtime_start
    ) AS prev_end,
    -- 计算两次停机间的运行时间(分钟)
    DATEDIFF(MINUTE,
        LAG(d.downtime_end) OVER (
            PARTITION BY d.machine_id
            ORDER BY d.downtime_start
        ),
        d.downtime_start
    ) AS run_time_between_failures
FROM fact_downtime d
JOIN dim_machine m ON d.machine_id = m.machine_id
ORDER BY m.machine_name, d.downtime_start;

SELECT
    CONVERT(DATE, production_date) AS prod_date,
    l.line_name,
    SUM(total_count) AS daily_output,
    -- 累计产量：从月初到当前行的总和
    SUM(SUM(total_count)) OVER (
        PARTITION BY l.line_name,
                       YEAR(production_date),
                       MONTH(production_date)
        ORDER BY CONVERT(DATE, production_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_output
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE production_date >= DATEADD(MONTH, -1, GETDATE())
GROUP BY CONVERT(DATE, production_date), l.line_name
ORDER BY l.line_name, prod_date;

WITH daily AS (
    SELECT
        CONVERT(DATE, production_date) AS prod_date,
        line_id,
        SUM(total_count) AS daily_output
    FROM fact_production
    GROUP BY CONVERT(DATE, production_date), line_id
)
SELECT
    d.prod_date,
    l.line_name,
    d.daily_output,
    -- 昨天的产量
    LAG(d.daily_output) OVER (
        PARTITION BY d.line_id
        ORDER BY d.prod_date
    ) AS prev_output,
    -- 环比增长率
    CAST(d.daily_output - LAG(d.daily_output) OVER (
        PARTITION BY d.line_id ORDER BY d.prod_date
    ) AS FLOAT)
    / NULLIF(LAG(d.daily_output) OVER (
        PARTITION BY d.line_id ORDER BY d.prod_date
    ), 0) AS mom_rate
FROM daily d
JOIN dim_line l ON d.line_id = l.line_id
ORDER BY l.line_name, d.prod_date;

-- ============ 今日产量 ============
SELECT l.line_name, SUM(p.total_count) AS today_output
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE CONVERT(DATE, p.production_date) = CONVERT(DATE, GETDATE())
GROUP BY l.line_name;

-- ============ 本周产量 ============
SELECT l.line_name, SUM(p.total_count) AS week_output
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE p.production_date >= DATEADD(DAY,
    -- DATEFIRST 决定一周的第一天 (1=周一)
    1 - DATEPART(WEEKDAY, GETDATE()),
    CONVERT(DATE, GETDATE()))
GROUP BY l.line_name;

-- ============ 本月产量 ============
SELECT l.line_name, SUM(p.total_count) AS month_output
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE YEAR(p.production_date)  = YEAR(GETDATE())
  AND MONTH(p.production_date) = MONTH(GETDATE())
GROUP BY l.line_name;

-- ============ 本季度产量 ============
SELECT l.line_name, SUM(p.total_count) AS quarter_output
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE YEAR(p.production_date)   = YEAR(GETDATE())
  AND DATEPART(QUARTER, p.production_date)
     = DATEPART(QUARTER, GETDATE())
GROUP BY l.line_name;

-- 同比：今年6月 vs 去年6月
WITH monthly AS (
    SELECT
        YEAR(production_date)  AS yr,
        MONTH(production_date) AS mo,
        line_id,
        SUM(total_count) AS total_output
    FROM fact_production
    GROUP BY YEAR(production_date),
             MONTH(production_date), line_id
)
SELECT
    cur.yr, cur.mo, l.line_name,
    cur.total_output  AS current_month,
    prev.total_output AS same_month_last_year,
    -- 同比增长率
    CAST(cur.total_output - prev.total_output AS FLOAT)
        / NULLIF(prev.total_output, 0) AS yoy_rate
FROM monthly cur
JOIN monthly prev
    ON cur.line_id = prev.line_id
    AND cur.mo = prev.mo
    AND cur.yr = prev.yr + 1  -- 去年同月
JOIN dim_line l ON cur.line_id = l.line_id
ORDER BY l.line_name, cur.yr, cur.mo;

WITH daily AS (
    SELECT
        CONVERT(DATE, production_date) AS prod_date,
        line_id,
        SUM(total_count) AS daily_output
    FROM fact_production
    GROUP BY CONVERT(DATE, production_date), line_id
)
SELECT
    d.prod_date,
    l.line_name,
    d.daily_output,
    -- 7天移动平均 = 当天+前6天的平均值
    AVG(d.daily_output) OVER (
        PARTITION BY d.line_id
        ORDER BY d.prod_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7day
FROM daily d
JOIN dim_line l ON d.line_id = l.line_id
ORDER BY l.line_name, d.prod_date;

-- 方法1: JOIN 班次维度表
SELECT
    s.shift_name,
    l.line_name,
    SUM(p.total_count) AS shift_output,
    AVG(CAST(p.good_count AS FLOAT)
        / NULLIF(p.total_count, 0)) AS avg_yield
FROM fact_production p
JOIN dim_shift s ON p.shift_id = s.shift_id
JOIN dim_line l ON p.line_id = l.line_id
WHERE CONVERT(DATE, p.production_date) = CONVERT(DATE, GETDATE())
GROUP BY s.shift_name, l.line_name;

-- 方法2: 用 CASE 根据小时判断班次（没有班次表时）
SELECT
    CASE
        WHEN DATEPART(HOUR, production_date) BETWEEN 8 AND 19
            THEN '白班'
        ELSE '夜班'
    END AS shift,
    l.line_name,
    SUM(total_count) AS output
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
WHERE CONVERT(DATE, p.production_date) = CONVERT(DATE, GETDATE())
GROUP BY CASE
        WHEN DATEPART(HOUR, production_date) BETWEEN 8 AND 19
            THEN '白班'
        ELSE '夜班'
    END, l.line_name;

-- 方法1: 文本形式的执行计划
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM fact_production
WHERE production_date >= '2026-06-01';
GO
SET SHOWPLAN_TEXT OFF;

-- 方法2: XML 形式 (更详细)
SET STATISTICS XML ON;
SELECT * FROM fact_production
WHERE production_date >= '2026-06-01';
SET STATISTICS XML OFF;

-- 方法3: SSMS 图形化 (推荐)
-- 在 SQL Server Management Studio 中
-- 点击工具栏的 "Include Actual Execution Plan" 按钮
-- 或按 Ctrl+M

-- 1. 聚集索引 (Clustered) — 物理排序，每表只能一个
-- 通常用主键或最常用的排序字段
CREATE CLUSTERED INDEX ix_prod_date
ON fact_production(production_date);

-- 2. 非聚集索引 (Nonclustered) — 最常用
CREATE NONCLUSTERED INDEX ix_prod_line_date
ON fact_production(line_id, production_date);

-- 3. 覆盖索引 (Covering Index) — 包含所有需要的列
-- 查询不需要回表 (Bookmark Lookup)，速度最快
CREATE NONCLUSTERED INDEX ix_prod_covering
ON fact_production(line_id, production_date)
INCLUDE (good_count, total_count);

-- 4. 唯一索引 — 保证数据不重复
CREATE UNIQUE INDEX ix_product_code
ON dim_product(product_code);

-- 仪表盘分页加载 (Keyset Pagination 更高效)
-- 方法1: OFFSET FETCH (简单但大数据量偏慢)
SELECT line_id, production_date, total_count
FROM fact_production
ORDER BY production_date DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;

-- 方法2: Keyset Pagination (推荐，大数据量时快)
-- 第一页
SELECT TOP 50 line_id, production_date, total_count
FROM fact_production
ORDER BY production_date DESC;

-- 下一页 (假设上一页最后一条的日期是 @last_date)
SELECT TOP 50 line_id, production_date, total_count
FROM fact_production
WHERE production_date < @last_date
ORDER BY production_date DESC;

-- 视图1: 每日产量汇总 (仪表盘首页用)
CREATE VIEW v_daily_output AS
SELECT
    CONVERT(DATE, p.production_date) AS prod_date,
    l.line_name,
    s.shift_name,
    m.machine_name,
    pr.product_name,
    SUM(p.total_count) AS total_output,
    SUM(p.good_count) AS good_output,
    CAST(SUM(p.good_count) AS FLOAT)
        / NULLIF(SUM(p.total_count), 0) AS yield_rate
FROM fact_production p
JOIN dim_line l     ON p.line_id    = l.line_id
JOIN dim_shift s    ON p.shift_id   = s.shift_id
JOIN dim_machine m  ON p.machine_id = m.machine_id
JOIN dim_product pr ON p.product_id = pr.product_id
GROUP BY CONVERT(DATE, p.production_date),
         l.line_name, s.shift_name,
         m.machine_name, pr.product_name;
GO

-- 视图2: OEE 汇总 (OEE 仪表盘用)
CREATE VIEW v_oee_summary AS
SELECT
    l.line_name,
    CONVERT(DATE, p.production_date) AS prod_date,
    SUM(p.good_count) AS good,
    SUM(p.total_count) AS total,
    CAST(SUM(p.good_count) AS FLOAT)
        / NULLIF(SUM(p.total_count), 0) AS quality_rate,
    -- 简化的 OEE = 良率 x 可用率 x 表现率
    -- 这里只展示良率部分，完整 OEE 需要更多数据
    COUNT(DISTINCT p.machine_id) AS active_machines
FROM fact_production p
JOIN dim_line l ON p.line_id = l.line_id
GROUP BY l.line_name,
         CONVERT(DATE, p.production_date);
GO

-- 前端只需要这样写，不用关心底层 JOIN
SELECT *
FROM v_daily_output
WHERE prod_date >= DATEADD(DAY, -7, GETDATE())
  AND line_name = 'A线'
ORDER BY prod_date DESC;

-- 修改视图 (业务逻辑变了)
ALTER VIEW v_daily_output AS
-- 新的查询逻辑...
-- 前端代码完全不需要改！