//得到分析时间段股票本身平均涨幅 public static double GetAverageGrowth(int startdate, int enddate) { if (startdate < Util.getIntDate(Util.getDate(StockApp.getAnalysisStartDate).AddMonths(StockApp.STOCK_START_DATE_SHITE_MONTH))) { startdate = Util.getIntDate(Util.getDate(StockApp.getAnalysisStartDate).AddMonths(StockApp.STOCK_START_DATE_SHITE_MONTH)); } string sql = "select isnull(max(date),0) from stock_full where [date] < " + startdate + ";"; object ret = db.GetFirstRow(sql)[0]; int minid = Convert.ToInt32(ret); if (enddate < startdate) { enddate = startdate; } sql = "select isnull(max(date),0) from stock_full where [date] <= " + enddate + ";"; ret = db.GetFirstRow(sql)[0]; int maxid = Convert.ToInt32(db.GetFirstRow(sql)[0]); UtilLog.AddInfo(TAG, "Calculate ave growth start"); //pivot 用法 /* * select code, "20151231", "20180126" from * ( * select code,[date], [end] from stock_Full where [DATE] in ('20151231', '20180126') * and code in (select code from stock_full group by code having MIN([date])< '20151231') * ) T1 pivot (MAX([end]) for [date] in ("20151231", "20180126")) as tp; */ sql = "select code, "; sql += " isnull(SUM(CASE [date] WHEN " + minid + " THEN [end] END),0) AS 'start', "; sql += " isnull(SUM(CASE [date] WHEN " + maxid + " THEN [end] END),0) AS 'end' "; sql += " from ( "; sql += " select code,[date], [end] from stock_Full where [DATE] in ('" + minid + "', '" + maxid + "') "; sql += " and code in (select code from stock_full group by code having MIN([date])< '" + minid + "') "; sql += " ) T1 group by code "; System.Data.DataTable dt = db.GetTable(sql); int count = 0; double retvalue = 0; for (int i = 0; i < dt.Rows.Count; i++) { System.Data.DataRow row = dt.Rows[i]; double start = Convert.ToDouble(row["start"]); double end = Convert.ToDouble(row["end"]); if (start < 0.1 || end < 0.1) { continue; } count++; retvalue += end / start; } try { return(retvalue / count); } catch (Exception e) { Console.WriteLine(e.ToString()); return(0); } finally { UtilLog.AddInfo(TAG, "Calculate ave growth end"); } }