Statistics

Given \(\sigma_a\) and \(\sigma_b\), Ask for \(\sigma\) 1. 簡化 \(\sigma\) 將 \(\sigma\) 乘開 \(\sigma = \sqrt{\frac{\sum{(x_i-\bar x)^2}}{n}}\) \(\sigma = \sqrt{\frac{\sum{x_i^2-2\bar x\sum{x_i}+n\bar x^2}}{n}}\) 平均等於總和除以個數 \(\frac{\sum x_i}{n}=\bar x\)，故 \(\sigma = \sqrt{\frac{\sum x_i^2}{n}-\frac{2\bar x\sum x_i}{n}+\frac{n\bar x^2}{n}}\) \(\sigma = \sqrt{\frac{\sum x_i^2}{n}-2\bar x^2+\bar x^2}\) 得 \(\boxed{\sigma = \sqrt{\frac{\sum x_i^2}{n}-\bar x^2}}-(1)\) 2. 求個別平方和 由\((1)\)式可推得各別的標準差為 \(\boxed{\sigma_a = \sqrt{\frac{\sum x_{ai}^2}{n_a}-\bar x_a^2}}-(2)\) 且 \(\boxed{n = n_a+n_b}-(3)\) \(\boxed{\sum x_i^2=\sum x_{ai}^2+\sum x_{bi}^2}-(4)\) 欲求 \(\sum x_{ai}^2\)，我們將\((2)\)式展開 \(\sigma_a^2 = \frac{\sum x_{ai}^2}{n_a}-\bar x_a^2\) \(\sigma_a^2+\bar x_a^2= \frac{\sum x_{ai}^2}{n_a}\) 得\(\boxed{\sum x_{ai}^2=n_a(\sigma_a^2+\bar x_a^2)}-(5)\) 3. 求總體標準差 由\((1)\)式展開 得 \(\boxed{\sigma = \sqrt{\frac{(\sum x_{ai}^2+\sum x_{bi}^2)}{n}-\bar x^2}}-(6)\) 將\((5)\)代入\((6)\) \(\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)\) 其中 \(\boxed{\bar x=\frac{n_a\bar x_a + n_b\bar x_b}{n}}-(8)\) 故我們可以從上式輾轉得通式： \(\boxed{\sigma=\sqrt{\frac{\sum(n_i(\sigma_i^2+\bar x_i^2))}{n}-\bar x^2}}-(9)\) 或寫成 \(\boxed{\sigma=\sqrt{\frac{\sum(n_i(\sigma_i^2+\bar x_i^2))-\sum n_i\bar x_i}{n}}}-(9)\) summary 個數 \(\boxed{n=n_a+n_b=\sum 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}}}\) 標準差 \(\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}}}\) 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