面试综合指南

/* 面试回答模板 — 大约2分钟说完 */

"我们的系统分为四个层次："

1. 数据源层
   "MES系统产生生产数据，存储在SQL Server数据库中。"
   "有些设备数据通过OPC UA协议实时采集。"

2. ETL/数据处理层
   "用Python编写ETL脚本，定时从MES库抽取数据，"
   "经过清洗、聚合后写入数据仓库。"
   "关键指标缓存到Redis，保证查询速度。"

3. 后端服务层
   "Node.js + Express提供RESTful API，前端调用获取图表数据。"
   "实时数据用WebSocket推送，确保大屏秒级刷新。"
   "复杂的数据计算逻辑放在Python微服务中处理。"

4. 前端展示层
   "Vue 3 + Vite构建SPA应用，ECharts渲染各类图表。"
   "组件化开发，一个图表一个组件，便于复用和维护。"
   "部署在Docker容器中，Nginx做反向代理。"

<div id="gauge" style="width:400px;height:400px"></div>

<script>
  var chart = echarts.init(document.getElementById('gauge'));

  chart.setOption({
    series: [{
      type: 'gauge',
      min: 0,
      max: 100,
      splitNumber: 10,
      axisLine: {
        lineStyle: {
          width: 20,
          color: [
            [0.6, '#ff6b6b'],   // 0-60 红色
            [0.85, '#ffd93d'],  // 60-85 黄色
            [1, '#6bcb77']      // 85-100 绿色
          ]
        }
      },
      pointer: { width: 5 },
      axisTick: { distance: -20, length: 6 },
      splitLine: { distance: -20, length: 14 },
      axisLabel: { distance: -25, fontSize: 11 },
      detail: {
        valueAnimation: true,
        formatter: '{value}%',
        fontSize: 24,
        offsetCenter: [0, '70%']
      },
      title: {
        offsetCenter: [0, '90%'],
        fontSize: 14
      },
      data: [{ value: 92.5, name: '良率' }]
    }]
  });

  // 窗口自适应
  window.addEventListener('resize', () => chart.resize());
</script>

<template>
  <div class="gauge-card">
    <h3>{{ lineName }} 产线良率</h3>
    <div ref="chartRef" style="width:100%;height:300px"></div>
    <button @click="handleRefresh">刷新数据</button>
  </div>
</template>

<script setup>
import { ref, onMounted, onUnmounted, watch } from 'vue'
import * as echarts from 'echarts'

// Props 定义
const props = defineProps({
  lineName: { type: String, required: true },
  yieldValue: { type: Number, required: true }
})

// Emit 定义
const emit = defineEmits(['refresh'])

const chartRef = ref(null)
let chartInstance = null

function renderChart() {
  if (!chartInstance) return
  chartInstance.setOption({
    series: [{
      type: 'gauge',
      detail: { formatter: '{value}%' },
      data: [{ value: props.yieldValue }]
    }]
  })
}

function handleRefresh() {
  emit('refresh', props.lineName)
}

onMounted(() => {
  chartInstance = echarts.init(chartRef.value)
  renderChart()
})

watch(() => props.yieldValue, () => renderChart())

onUnmounted(() => {
  chartInstance?.dispose()
})
</script>

const express = require('express')
const sql = require('mssql')
const app = express()

// 数据库配置
const dbConfig = {
  server: '192.168.1.100',
  database: 'MES_DB',
  user: 'sa',
  password: '******',
  options: { encrypt: false }
}

// GET /api/production/:lineId — 查询产线生产数据
app.get('/api/production/:lineId', async (req, res) => {
  try {
    const { lineId } = req.params
    const { startDate, endDate } = req.query

    // 参数校验
    if (!startDate || !endDate) {
      return res.status(400).json({
        code: 400,
        message: '缺少 startDate 或 endDate 参数'
      })
    }

    // 连接数据库（参数化查询防 SQL 注入）
    const pool = await sql.connect(dbConfig)
    const result = await pool.request()
      .input('lineId', sql.Int, parseInt(lineId))
      .input('start', sql.DateTime, new Date(startDate))
      .input('end', sql.DateTime, new Date(endDate))
      .query(`
        SELECT ProductionDate, Output, Yield, DefectCount
        FROM ProductionRecords
        WHERE LineId = @lineId
          AND ProductionDate BETWEEN @start AND @end
        ORDER BY ProductionDate
      `)

    res.json({
      code: 200,
      data: result.recordset,
      message: 'ok'
    })
  } catch (err) {
    console.error('查询失败:', err)
    res.status(500).json({
      code: 500,
      message: '服务器内部错误'
    })
  }
})

app.listen(3000, () => console.log('Server running on :3000'))

-- 产线月度产量 + 环比增长率
WITH MonthlyOutput AS (
  SELECT
    LineName,
    FORMAT(ProductionDate, 'yyyy-MM') AS Month,
    SUM(Output) AS TotalOutput,
    AVG(Yield) AS AvgYield
  FROM ProductionRecords
  WHERE ProductionDate >= '2025-01-01'
  GROUP BY LineName, FORMAT(ProductionDate, 'yyyy-MM')
)
SELECT
  LineName,
  Month,
  TotalOutput,
  AvgYield,
  -- 上月产量（LAG 向下偏移一行）
  LAG(TotalOutput, 1) OVER (
    PARTITION BY LineName ORDER BY Month
  ) AS PrevOutput,
  -- 环比增长率
  CAST(
    (CAST(TotalOutput AS FLOAT) -
     LAG(TotalOutput, 1) OVER (PARTITION BY LineName ORDER BY Month))
    / NULLIF(LAG(TotalOutput, 1) OVER (PARTITION BY LineName ORDER BY Month), 0)
    * 100 AS DECIMAL(5,2)
  ) AS GrowthRate,
  -- 产线内排名
  RANK() OVER (PARTITION BY Month ORDER BY TotalOutput DESC) AS RankInMonth
FROM MonthlyOutput
ORDER BY LineName, Month;

# ===== 阶段1：构建 =====
FROM node:18-alpine AS builder

WORKDIR /app

# 先复制依赖文件（利用 Docker 缓存层）
COPY package.json package-lock.json ./
RUN npm ci

# 复制源代码并构建
COPY . .
RUN npm run build

# ===== 阶段2：运行 =====
FROM nginx:alpine

# 复制构建产物到 Nginx 目录
COPY --from=builder /app/dist /usr/share/nginx/html

# 复制 Nginx 配置（支持 Vue Router history 模式）
COPY nginx.conf /etc/nginx/conf.d/default.conf

EXPOSE 80

CMD ["nginx", "-g", "daemon off;"]

server {
    listen 80;
    server_name localhost;

    location / {
        root /usr/share/nginx/html;
        index index.html;
        try_files $uri $uri/ /index.html;  # Vue Router history 模式
    }

    # API 反向代理到后端服务
    location /api/ {
        proxy_pass http://backend:3000/;
    }
}