以下SQL语句,使用sqlserver数据库
方差的计算公式为: 方差 = [(x1 - 平均数)^2 + (x2 - 平均数)^2 + … + (xn - 平均数)^2] / n 其中 x1、x2、…、xn 是样本中的各个数据,n 是样本数量。
SELECT
AVG(valu) AS avg_valu,
COUNT(valu) AS cnt,
SUM(POWER(avg_valu-valu,2))/COUNT(valu) AS [方差],
AVG(POWER(avg_valu-valu,2)) AS [方差2],
SQRT(AVG(POWER(avg_valu-valu,2))) AS [标准差1],
stdevp(valu) AS [标准差2],
stdev(valu) AS [样本标准差]
FROM (
SELECT
valu,
AVG(valu)OVER() AS avg_valu
FROM (
VALUES
(10.12),
(10.32),
(10.22),
(10.52),
(10.42)
) AS out_tmp(valu)
) AS tmp
CPK 是一种用于衡量过程能力的指标, 它考虑了生产过程的均值和标准差与规格限的关系。 CPK(过程能力指数)的计算公式为:
CPK = min{(USL - X̄) / 3σ, (X̄ - LSL) / 3σ} 其中:
- USL 表示规格上限;
- LSL 表示规格下限;
- X̄ 表示样本平均值;
- σ 表示样本标准差。
这个指标用于衡量过程的稳定性和能力。
SELECT
'MIN((上限-平均值)/(3*标准差), (平均值-下限)/(3*标准差))' AS 'CPK公式',
([上限]-[平均值])/(3*[标准差]) AS [上],
([平均值]-[下限])/(3*[标准差]) AS [下],
CASE WHEN [标准差] >= 0 AND (([上限]-[平均值])/(3*[标准差])) > (([平均值]-[下限])/(3*[标准差]))
THEN ([平均值]-[下限])/(3*[标准差])
ELSE ([上限]-[平均值])/(3*[标准差])
END AS [cpk]
FROM ( SELECT
[上限],
[下限],
[平均值],
CASE WHEN 0= [标准差] THEN NULL ELSE [标准差] END AS [标准差]
FROM (
SELECT
11 AS [上限],
0 AS [下限],
avg(aaaa) AS [平均值],
stdevp(aaaa) AS [标准差],
stdev(aaaa) AS [样本标准差]
FROM (
VALUES
(10.022),
(10.3352),
(10.278),
(10.561),
(10.630)
) as out_tmp(aaaa)
) AS in_tmp
)as tmp