DBILITY

공정능력지수 PCI ( Process Capability Index ), 계수표, SQL예시 본문

statistics/SPC

공정능력지수 PCI ( Process Capability Index ), 계수표, SQL예시

DBILITY 2017. 2. 26. 11:36
반응형

공정능력,검사관련 프로젝트에 들어갈때 아는게 일천하여 조사하고 정리한 것이다.

작은 도움이라도 되시길~

  • 공정능력 ( Process Capability )
    공정이 최상,즉 관리상태에 있을때 각 제품의 변동이 어느 정도인가를 표시하는 양
    자연공차(Natural Tolerance)라는 용어를 사용하고 대개 6σ를 사용
  • 잠재적 공정능력지수 ( Cp,Potential Capability Index )
    동일한 일을 반복할 수 있는 공정의 잠재능력으로 공정이 관리되고 있을때 생산되는 제품의 품질변동을 나타내는 양
    공정너비에 대한 공차의 너비의 비로 크면 클수록 좋다.
  • x̄ 평균 ( Mean )
  • S 표준편차 ( Standard Deviation ) - Sigma
  • R 범위 ( Range = Max - Min )
  • M 중앙값 ( Median )
  • UCL 관리상한선 ( Upper Control Line = +3σ)
  • LCL 관리하한선 ( Lower Control Line -3σ)
  • CL 중심선 ( Center Line = )
  • USL 규격상한 ( Upper Specification Limit )
  • LSL 규격하한 ( Lower Specification Limit )
  • Tolerance 공차 USL-LSL
    Ppk 구하는 절차
  1. USL, LSL, 표준편차(σLT)를 구한다.
  2. 치우침(K)을 구한다.
     = ABS[(USL+LSL)/2-x̄ ]/[(USL-LSL)/2]
  3. Pp 산출
     Pp = (USL-LSL)/(6*σLT)
  4. Ppk산출
     Ppk = (1-K)*Pp , min(Ppl,Ppu)
     Ppu = (USL-x̄ )/(3*σLT)
     Ppl = (x̄ -LSL)/(3*σLT)

    반응형
    Cpk 구하는 절차
  1. USL, LSL, 표준편차(σST)를 구한다.
  2. 치우침(K)을 구한다.
     = ABS[(USL+LSL)/2-x̄ ]/[(USL-LSL)/2]
  3. Cp 산출
     Cp = (USL-LSL)/(6*σST)
  4. Cpk산출
     Cpk = (1-K)*Cp, min(Cpl,Cpu)
     Cpu = (USL-x̄ )/(3*σST)
     Cpl = (x̄ -LSL)/(3*σST)

σST = 단기표준편차, σLT = 장기표준편차

 

합동표준편차 = SQRT(SUM((군내관측치-군내관측치평균)2)/(부분군개수*(부분군크기-1)))

부분군내 표준편차계산 = 합동표준편차 / c4((부분군개수*(부분군크기-1))+1)
 

    계수값 계산식(근사치)
  • A3 = 3 / (c4 √ n)
  • B4 = 1 + 3 / {c4 √ 2(n-1)}
  • B3 = 1 - 3 / {c4 √ 2(n-1)}
  • c4 = 4(n-1) / (4n-3)
  • 관리도 계수표
    N c4 c5 d2 d3 d4
    1 * * 1 0.82 1
    2 0.797885 0.603 1.128 0.8525 0.954
    3 0.886227 0.463 1.693 0.8884 1.588
    4 0.921318 0.389 2.059 0.8794 1.978
    5 0.939986 0.341 2.326 0.8641 2.257
    6 0.951533 0.308 2.534 0.848 2.472
    7 0.959369 0.282 2.704 0.8332 2.645
    8 0.96503 0.262 2.847 0.8198 2.791
    9 0.969311 0.246 2.97 0.8078 2.915
    10 0.972659 0.232 3.078 0.7971 3.024
    11 0.97535 0.22 3.173 0.7873 3.121
    12 0.977559 0.21 3.258 0.7785 3.207
    13 0.979406 0.202 3.336 0.7704 3.285
    14 0.980971 0.194 3.407 0.763 3.356
    15 0.982316 0.187 3.472 0.7562 3.422
    16 0.983484 0.181 3.532 0.7499 3.482
    17 0.984506 0.175 3.588 0.7441 3.538
    18 0.98541 0.17 3.64 0.7386 3.591
    19 0.986214 0.166 3.689 0.7335 3.64
    20 0.986934 0.161 3.735 0.7287 3.686
    21 0.987583 0.157 3.778 0.7242 3.73
    22 0.98817 0.153 3.819 0.7199 3.771
    23 0.988705 0.15 3.858 0.7159 3.811
    24 0.989193 0.147 3.895 0.7121 3.847
    25 0.98964 0.144 3.931 0.7084 3.883
    26 0.990052   3.965    
    27 0.990433   3.997    
    28 0.990786   4.028    
    29 0.991113   4.058    
    30 0.991418   4.086    
    31 0.991703   4.113    
    32 0.991969   4.139    
    33 0.992219   4.164    
    34 0.992454   4.189    
    35 0.992675   4.213    
    36 0.992884   4.236    
    37 0.99308   4.258    
    38 0.993267   4.28    
    39 0.993443   4.301    
    40 0.993611   4.322    
    41 0.99377   4.342    
    42 0.993922   4.361    
    43 0.994066   4.38    
    44 0.994203   4.398    
    45 0.994335   4.415    
    46 0.99446   4.432    
    47 0.99458   4.449    
    48 0.994695   4.466    
    49 0.994806   4.482    
    50 0.994911   4.498    
    51 0.995013        
    52 0.99511        
    53 0.995204        
    54 0.995294        
    55 0.995381        
    56 0.995465        
    57 0.995546        
    58 0.995624        
    59 0.995699        
    60 0.995772        
    61 0.995842        
    62 0.99591        
    63 0.995976        
    64 0.99604        
    65 0.996102        
    66 0.996161        
    67 0.996219        
    68 0.996276        
    69 0.99633        
    70 0.996383        
    71 0.996435        
    72 0.996485        
    73 0.996534        
    74 0.996581        
    75 0.996627        
    76 0.996672        
    77 0.996716        
    78 0.996759        
    79 0.9968        
    80 0.996841        
    81 0.99688        
    82 0.996918        
    83 0.996956        
    84 0.996993        
    85 0.997028        
    86 0.997063        
    87 0.997097        
    88 0.997131        
    89 0.997163        
    90 0.997195        
    91 0.997226        
    92 0.997257        
    93 0.997286        
    94 0.997315        
    95 0.997344        
    96 0.997372        
    97 0.997399        
    98 0.997426        
    99 0.997452        
    100 0.997478        
WITH T1 (ACTVALUE) AS (
    SELECT 0.529  FROM DUAL UNION ALL
    SELECT 0.55  FROM DUAL UNION ALL
    SELECT 0.555  FROM DUAL UNION ALL
    SELECT 0.541  FROM DUAL UNION ALL
    SELECT 0.559  FROM DUAL UNION ALL
    SELECT 0.543  FROM DUAL UNION ALL
    SELECT 0.557  FROM DUAL UNION ALL
    SELECT 0.559  FROM DUAL UNION ALL
    SELECT 0.581  FROM DUAL UNION ALL
    SELECT 0.551  FROM DUAL UNION ALL
    SELECT 0.493  FROM DUAL UNION ALL
    SELECT 0.534  FROM DUAL UNION ALL
    SELECT 0.527  FROM DUAL UNION ALL
    SELECT 0.511  FROM DUAL UNION ALL
    SELECT 0.565  FROM DUAL UNION ALL
    SELECT 0.559  FROM DUAL UNION ALL
    SELECT 0.519  FROM DUAL UNION ALL
    SELECT 0.562  FROM DUAL UNION ALL
    SELECT 0.551  FROM DUAL UNION ALL
    SELECT 0.53  FROM DUAL UNION ALL
    SELECT 0.545  FROM DUAL UNION ALL
    SELECT 0.588  FROM DUAL UNION ALL
    SELECT 0.544  FROM DUAL UNION ALL
    SELECT 0.561  FROM DUAL UNION ALL
    SELECT 0.573  FROM DUAL UNION ALL
    SELECT 0.607  FROM DUAL UNION ALL
    SELECT 0.532  FROM DUAL UNION ALL
    SELECT 0.562  FROM DUAL UNION ALL
    SELECT 0.542  FROM DUAL UNION ALL
    SELECT 0.549  FROM DUAL UNION ALL
    SELECT 0.577  FROM DUAL UNION ALL
    SELECT 0.526  FROM DUAL UNION ALL
    SELECT 0.546  FROM DUAL UNION ALL
    SELECT 0.557  FROM DUAL UNION ALL
    SELECT 0.548  FROM DUAL UNION ALL
    SELECT 0.546  FROM DUAL UNION ALL
    SELECT 0.56  FROM DUAL UNION ALL
    SELECT 0.53  FROM DUAL UNION ALL
    SELECT 0.564  FROM DUAL UNION ALL
    SELECT 0.514  FROM DUAL UNION ALL
    SELECT 0.527  FROM DUAL UNION ALL
    SELECT 0.545  FROM DUAL UNION ALL
    SELECT 0.513  FROM DUAL UNION ALL
    SELECT 0.557  FROM DUAL UNION ALL
    SELECT 0.525  FROM DUAL UNION ALL
    SELECT 0.557  FROM DUAL UNION ALL
    SELECT 0.559  FROM DUAL UNION ALL
    SELECT 0.529  FROM DUAL UNION ALL
    SELECT 0.539  FROM DUAL UNION ALL
    SELECT 0.591  FROM DUAL UNION ALL
    SELECT 0.538  FROM DUAL UNION ALL
    SELECT 0.557  FROM DUAL UNION ALL
    SELECT 0.517  FROM DUAL UNION ALL
    SELECT 0.521  FROM DUAL UNION ALL
    SELECT 0.568  FROM DUAL UNION ALL
    SELECT 0.544  FROM DUAL UNION ALL
    SELECT 0.55  FROM DUAL UNION ALL
    SELECT 0.562  FROM DUAL UNION ALL
    SELECT 0.54  FROM DUAL UNION ALL
    SELECT 0.537  FROM DUAL UNION ALL
    SELECT 0.558  FROM DUAL UNION ALL
    SELECT 0.548  FROM DUAL UNION ALL
    SELECT 0.532  FROM DUAL UNION ALL
    SELECT 0.57  FROM DUAL UNION ALL
    SELECT 0.567  FROM DUAL UNION ALL
    SELECT 0.56  FROM DUAL UNION ALL
    SELECT 0.533  FROM DUAL UNION ALL
    SELECT 0.538  FROM DUAL UNION ALL
    SELECT 0.567  FROM DUAL UNION ALL
    SELECT 0.557  FROM DUAL UNION ALL
    SELECT 0.541  FROM DUAL UNION ALL
    SELECT 0.534  FROM DUAL UNION ALL
    SELECT 0.544  FROM DUAL UNION ALL
    SELECT 0.537  FROM DUAL UNION ALL
    SELECT 0.574  FROM DUAL UNION ALL
    SELECT 0.572  FROM DUAL UNION ALL
    SELECT 0.556  FROM DUAL UNION ALL
    SELECT 0.56  FROM DUAL UNION ALL
    SELECT 0.52  FROM DUAL UNION ALL
    SELECT 0.578  FROM DUAL UNION ALL
    SELECT 0.543  FROM DUAL UNION ALL
    SELECT 0.544  FROM DUAL UNION ALL
    SELECT 0.541  FROM DUAL UNION ALL
    SELECT 0.526  FROM DUAL UNION ALL
    SELECT 0.518  FROM DUAL UNION ALL
    SELECT 0.521  FROM DUAL UNION ALL
    SELECT 0.532  FROM DUAL UNION ALL
    SELECT 0.524  FROM DUAL UNION ALL
    SELECT 0.544  FROM DUAL UNION ALL
    SELECT 0.523  FROM DUAL UNION ALL
    SELECT 0.55  FROM DUAL UNION ALL
    SELECT 0.544  FROM DUAL UNION ALL
    SELECT 0.545  FROM DUAL UNION ALL
    SELECT 0.571  FROM DUAL UNION ALL
    SELECT 0.527  FROM DUAL UNION ALL
    SELECT 0.536  FROM DUAL UNION ALL
    SELECT 0.554  FROM DUAL UNION ALL
    SELECT 0.569  FROM DUAL UNION ALL
    SELECT 0.531  FROM DUAL UNION ALL
    SELECT 0.534  FROM DUAL
), T2 (SPECVAL, SPEC_USL, SPEC_LSL) AS (
    SELECT 0.55, 0.6, 0.5 FROM DUAL
), T3 (STDDEV_WITHIN) AS (
    SELECT
        ROUND(SQRT(SUM(B)/(COUNT(*)-(COUNT(*)/5)))/0.996880,7)
    FROM
    (
        SELECT
         POWER(ACTVALUE-AVG(ACTVALUE) OVER(PARTITION BY CEIL(ROWNUM/5)),2) B
        FROM T1
    )
 ) 
SELECT
      TRUNC(AVG(ACTVALUE),5) AVG
    , TRUNC(VAR_POP(ACTVALUE),5) POP
    , T3.STDDEV_WITHIN
    , ROUND(STDDEV_SAMP(ACTVALUE),7) STDDEV_OVERALL
    , SPECVAL
    , SPEC_USL
    , SPEC_LSL
    , ABS( (SPEC_USL+SPEC_LSL)/2-AVG(ACTVALUE) ) / ( (SPEC_USL-SPEC_LSL)/2 ) K
    , TRUNC((SPEC_USL-SPEC_LSL)/(6*ROUND(STDDEV_SAMP(ACTVALUE),4)),2) PP        
    , TRUNC((AVG(ACTVALUE)-SPEC_LSL)/(3*ROUND(STDDEV_SAMP(ACTVALUE),4)),2) PPL
    , TRUNC((SPEC_USL-AVG(ACTVALUE))/(3*ROUND(STDDEV_SAMP(ACTVALUE),4)),2) PPU
    , TRUNC((1-ABS( (SPEC_USL+SPEC_LSL)/2-AVG(ACTVALUE) ) / ( (SPEC_USL-SPEC_LSL)/2 ))*ROUND((SPEC_USL-SPEC_LSL)/(6*ROUND(STDDEV_SAMP(ACTVALUE),4)),4),2) PPK
    , TRUNC(LEAST((SPEC_USL-AVG(ACTVALUE))/(3*ROUND(STDDEV_SAMP(ACTVALUE),4)),(AVG(ACTVALUE)-SPEC_LSL)/(3*ROUND(STDDEV_SAMP(ACTVALUE),4))),2) PPK2    
    , TRUNC((SPEC_USL-SPEC_LSL)/(6*ROUND(T3.STDDEV_WITHIN,4)),2) CP    
    , TRUNC((AVG(ACTVALUE)-SPEC_LSL)/(3*ROUND(T3.STDDEV_WITHIN,4)),2) CPL
    , TRUNC((SPEC_USL-AVG(ACTVALUE))/(3*ROUND(T3.STDDEV_WITHIN,4)),2) CPU
    , TRUNC((1-ABS( (SPEC_USL+SPEC_LSL)/2-AVG(ACTVALUE) ) / ( (SPEC_USL-SPEC_LSL)/2 ))*ROUND((SPEC_USL-SPEC_LSL)/(6*ROUND(T3.STDDEV_WITHIN,4)),4),2) CPK
    , TRUNC(LEAST((SPEC_USL-AVG(ACTVALUE))/(3*ROUND(T3.STDDEV_WITHIN,4)),(AVG(ACTVALUE)-SPEC_LSL)/(3*ROUND(T3.STDDEV_WITHIN,4))),2) CPK2 
    , SUM(CASE WHEN ACTVALUE BETWEEN SPEC_LSL AND SPEC_USL THEN 1 ELSE 0 END)/COUNT(1)*100 PASS_RATE
    , COUNT(*) OBS
FROM T1,T2,T3 GROUP BY 0,T3.STDDEV_WITHIN;

 

PCI_공정능력지수_ORACLE.sql
0.01MB
관리도계수표.txt
0.00MB

반응형
Comments