西夏普的部落格

1.前言:
如果要對DataTable中的特定欄位做資料分析，在不使用其他第三方函式庫的情況下，可以用Compute方法來做基本的數值計算。

2.說明:
用法: DataTable.Compute( string expression, string filter)
支援以下聚合函數:
•Sum (Sum) 
•Avg (Average) 
•Min (Minimum) 
•Max (Maximum) 
•Count (Count) 
•StDev (Statistical standard deviation) 
•Var (Statistical variance)
使用上要注意的是欄位的型態需要是數值型態，如果是字串型態，可以簡單定義一個新欄位為數值型，再複製要計算的欄位資料做資料分析，否則會出現彙總函式和型別的無效用法: String錯誤訊息。

有關DataTable.Compute()的說明可參考:
http://msdn.microsoft.com/en-us/library/system.data.datatable.compute(v=vs.100).aspx

程式碼:

private void btCalc_Click(object sender, EventArgs e)
{
	double mean, stdev, max, min, variance, count, sum;
	DataTable dt = TxtConvertToDataTable(@"d:\tmp\dt.csv", "tmp", ",");//讀取資料

	dt.Columns.Add("tmpColumn", typeof(double), "Convert(data, 'System.Double')");//加入暫存欄位，將資料中string的型態轉為double型態

	mean = (double)dt.Compute("Avg(tmpColumn)", string.Empty);
	stdev = (double)dt.Compute("Stdev(tmpColumn)", string.Empty);
	max = (double)dt.Compute("Max(tmpColumn)", string.Empty);
	min = (double)dt.Compute("Min(tmpColumn)", string.Empty);
	variance = (double)dt.Compute("Var(tmpColumn)", string.Empty);
	count = (int)dt.Compute("Count(tmpColumn)", string.Empty);
	sum = (double)dt.Compute("Sum(tmpColumn)", string.Empty);

	dt.Columns.Remove("tmpColumn");//移除暫存欄位        
	
	MessageBox.Show("Mean: "+mean+"\r\n"
		+ "Stdev: " + stdev + "\r\n"
		+ "Max: " + max + "\r\n"
		+ "Min: " + min + "\r\n"
		+ "Variance: " + variance + "\r\n"
		+ "Count: " + count + "\r\n"
		+ "Sum: " + sum + "\r\n"
		);
}

public DataTable TxtConvertToDataTable(string File, string TableName, string delimiter)
{
	DataTable dt = new DataTable();
	DataSet ds = new DataSet();
	StreamReader s = new StreamReader(File, System.Text.Encoding.Default);
	string[] columns = s.ReadLine().Split(delimiter.ToCharArray());
	ds.Tables.Add(TableName);
	foreach (string col in columns)
	{
		bool added = false;
		string next = "";
		int i = 0;
		while (!added)
		{
			string columnname = col + next;
			columnname = columnname.Replace("#", "");
			columnname = columnname.Replace("'", "");
			columnname = columnname.Replace("&", "");

			if (!ds.Tables[TableName].Columns.Contains(columnname))
			{
				ds.Tables[TableName].Columns.Add(columnname.ToUpper());
				added = true;
			}
			else
			{
				i++;
				next = "_" + i.ToString();
			}
		}
	}

	string AllData = s.ReadToEnd();
	string[] rows = AllData.Split("\n".ToCharArray());

	foreach (string r in rows)
	{
		string[] items = r.Split(delimiter.ToCharArray());
		ds.Tables[TableName].Rows.Add(items);
	}

	s.Close();

	dt = ds.Tables[0];

	return dt;
}

圖例: