Given σ a \sigma_a σ a and σ b \sigma_b σ b , Ask for σ \sigma σ # 1. 簡化 σ \sigma σ # 將 σ \sigma σ 乘開σ = ∑ ( x i − x ˉ ) 2 n \sigma = \sqrt{\frac{\sum{(x_i-\bar x)^2}}{n}} σ = n ∑ ( x i − x ˉ ) 2 σ = ∑ x i 2 − 2 x ˉ ∑ x i + n x ˉ 2 n \sigma = \sqrt{\frac{\sum{x_i^2-2\bar x\sum{x_i}+n\bar x^2}}{n}} σ = n ∑ x i 2 − 2 x ˉ ∑ x i + n x ˉ 2 平均等於總和除以個數 ∑ x i n = x ˉ \frac{\sum x_i}{n}=\bar x n ∑ x i = x ˉ ,故σ = ∑ x i 2 n − 2 x ˉ ∑ x i n + n x ˉ 2 n \sigma = \sqrt{\frac{\sum x_i^2}{n}-\frac{2\bar x\sum x_i}{n}+\frac{n\bar x^2}{n}} σ = n ∑ x i 2 − n 2 x ˉ ∑ x i + n n x ˉ 2 σ = ∑ x i 2 n − 2 x ˉ 2 + x ˉ 2 \sigma = \sqrt{\frac{\sum x_i^2}{n}-2\bar x^2+\bar x^2} σ = n ∑ x i 2 − 2 x ˉ 2 + x ˉ 2 得 σ = ∑ x i 2 n − x ˉ 2 − ( 1 ) \boxed{\sigma = \sqrt{\frac{\sum x_i^2}{n}-\bar x^2}}-(1) σ = n ∑ x i 2 − x ˉ 2 − ( 1 ) 2. 求個別平方和# 由( 1 ) (1) ( 1 ) 式可推得各別的標準差為σ a = ∑ x a i 2 n a − x ˉ a 2 − ( 2 ) \boxed{\sigma_a = \sqrt{\frac{\sum x_{ai}^2}{n_a}-\bar x_a^2}}-(2) σ a = n a ∑ x ai 2 − x ˉ a 2 − ( 2 ) 且n = n a + n b − ( 3 ) \boxed{n = n_a+n_b}-(3) n = n a + n b − ( 3 ) ∑ x i 2 = ∑ x a i 2 + ∑ x b i 2 − ( 4 ) \boxed{\sum x_i^2=\sum x_{ai}^2+\sum x_{bi}^2}-(4) ∑ x i 2 = ∑ x ai 2 + ∑ x bi 2 − ( 4 ) 欲求 ∑ x a i 2 \sum x_{ai}^2 ∑ x ai 2 ,我們將( 2 ) (2) ( 2 ) 式展開σ a 2 = ∑ x a i 2 n a − x ˉ a 2 \sigma_a^2 = \frac{\sum x_{ai}^2}{n_a}-\bar x_a^2 σ a 2 = n a ∑ x ai 2 − x ˉ a 2 σ a 2 + x ˉ a 2 = ∑ x a i 2 n a \sigma_a^2+\bar x_a^2= \frac{\sum x_{ai}^2}{n_a} σ a 2 + x ˉ a 2 = n a ∑ x ai 2 得∑ x a i 2 = n a ( σ a 2 + x ˉ a 2 ) − ( 5 ) \boxed{\sum x_{ai}^2=n_a(\sigma_a^2+\bar x_a^2)}-(5) ∑ x ai 2 = n a ( σ a 2 + x ˉ a 2 ) − ( 5 ) 3. 求總體標準差# 由( 1 ) (1) ( 1 ) 式展開得 σ = ( ∑ x a i 2 + ∑ x b i 2 ) n − x ˉ 2 − ( 6 ) \boxed{\sigma = \sqrt{\frac{(\sum x_{ai}^2+\sum x_{bi}^2)}{n}-\bar x^2}}-(6) σ = n ( ∑ x ai 2 + ∑ x bi 2 ) − x ˉ 2 − ( 6 ) 將( 5 ) (5) ( 5 ) 代入( 6 ) (6) ( 6 ) σ = n a ( σ a 2 + x ˉ a 2 ) + n b ( σ b 2 + x ˉ n 2 ) n − x ˉ 2 − ( 7 ) \boxed{\sigma=\sqrt{\frac{n_a(\sigma_a^2+\bar x_a^2)+n_b(\sigma_b^2+\bar x_n^2)}{n}-\bar x^2}}-(7) σ = n n a ( σ a 2 + x ˉ a 2 ) + n b ( σ b 2 + x ˉ n 2 ) − x ˉ 2 − ( 7 ) 其中 x ˉ = n a x ˉ a + n b x ˉ b n − ( 8 ) \boxed{\bar x=\frac{n_a\bar x_a + n_b\bar x_b}{n}}-(8) x ˉ = n n a x ˉ a + n b x ˉ b − ( 8 ) 故我們可以從上式輾轉得通式:σ = ∑ ( n i ( σ i 2 + x ˉ i 2 ) ) n − x ˉ 2 − ( 9 ) \boxed{\sigma=\sqrt{\frac{\sum(n_i(\sigma_i^2+\bar x_i^2))}{n}-\bar x^2}}-(9) σ = n ∑ ( n i ( σ i 2 + x ˉ i 2 )) − x ˉ 2 − ( 9 ) 或寫成 σ = ∑ ( n i ( σ i 2 + x ˉ i 2 ) ) − ∑ n i x ˉ i n − ( 9 ) \boxed{\sigma=\sqrt{\frac{\sum(n_i(\sigma_i^2+\bar x_i^2))-\sum n_i\bar x_i}{n}}}-(9) σ = n ∑ ( n i ( σ i 2 + x ˉ i 2 )) − ∑ n i x ˉ i − ( 9 ) summary# 個數n = n a + n b = ∑ n i \boxed{n=n_a+n_b=\sum n_i} n = n a + n b = ∑ n i 平均數x ˉ = n a x ˉ a + n b x ˉ b n a + n b = ∑ n i x ˉ i ∑ n i \boxed{\bar x=\frac{n_a\bar x_a+n_b\bar x_b}{n_a+n_b}=\frac{\sum{n_i\bar x_i}}{\sum{n_i}}} x ˉ = n a + n b n a x ˉ a + n b x ˉ b = ∑ n i ∑ n i x ˉ i 標準差σ = n a i ( σ a i 2 + x ˉ a i 2 ) + n b i ( σ b i 2 + x ˉ b i 2 ) − ( n a x ˉ a + n b x ˉ b ) n a + n b = ∑ ( n i ( σ i 2 + x ˉ i 2 ) ) − ∑ n i x ˉ i ∑ n i \boxed{\sigma=\sqrt{\frac{n_{ai}(\sigma_{ai}^2+\bar x_{ai}^2)+n_{bi}(\sigma_{bi}^2+\bar x_{bi}^2)-(n_a\bar x_a+n_b\bar x_b)}{n_a+n_b}}=\sqrt{\frac{\sum(n_i(\sigma_i^2+\bar x_i^2))-\sum n_i\bar x_i}{\sum n_i}}} σ = n a + n b n ai ( σ ai 2 + x ˉ ai 2 ) + n bi ( σ bi 2 + x ˉ bi 2 ) − ( n a x ˉ a + n b x ˉ b ) = ∑ n i ∑ ( n i ( σ i 2 + x ˉ i 2 )) − ∑ n i x ˉ i 4. sql# 現有一 table 存有 avg_value
std_value
site_count
with stats as (
select
...
sum (site_count* avg_value)/ sum (site_count) as avg_value,
sqrt((sum (site_count* (square(std_value)+ square(avg_value)))- sum (site_count* avg_value))/ sum (site_count)) as std_value,
sum (site_count) as site_count
from data
where ...
group by ...
)
select * from stats
複製