- 由 黄平创建于六月 16, 2016
1、获取原始数据
背景:原始数据是从HBase中定制抽取回到关系数据库中的,一个主键关联的一个指标的原始数据一般都上万条,指标数据量也很大,为了日后抽取时插数据的性能等因素,故将单个指标的原始数据存储在单个字段中,值与值之间通过英文逗号分隔。
另外,本示例中用到的关系数据库为MySQL,而MySQL中没有内置的函数可以方便地将一个字段的值分隔成多行,尝试过自定义存储过程的方式实现分隔,但效率好像不高,所以本示例通过定制Java查询的方式来获取原始数据。
本Java查询示例代码如下:
展开源码
package smartbi.demo.javaquery; import java.io.BufferedReader; import java.io.Reader; import java.security.InvalidParameterException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Collections; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Map.Entry; import org.apache.log4j.Logger; import smartbi.connectionpool.ConnectionPool; import smartbi.freequery.metadata.IJavaQueryData; import smartbi.freequery.metadata.JavaQueryConfig; import smartbi.freequery.metadata.JavaQueryOutputField; import smartbi.freequery.metadata.JavaQueryParameter; import smartbi.freequery.querydata.CellData; import smartbi.freequery.querydata.GridData; import smartbi.net.sf.json.JSONObject; import smartbi.util.DbUtil; import smartbi.util.StringUtil; import smartbi.util.ValueType; public class CellStringSplitToRows implements IJavaQueryData { public static final String CN_DS_ID = "DS_ID"; // config name public static final String CN_QUERY_SQL = "QUERY_SQL"; // config name public static final String CN_IGNORE_EMPTY_VALUE = "IGNORE_EMPTY_VALUE"; // config name public static final String SPLIT_DELIMITER = ","; protected static final String ID_OUTPUT_FIELD_1 = "VALUES"; protected static final String NAME_OUTPUT_FIELD_1 = "values"; protected static final String ID_PARAMETER_1 = "SQL_PARAMETER_VALUES"; protected static final String NAME_PARAMETER_1 = "sqlParameterValues"; protected static final boolean USE_CACHE_DATA = false; protected static final Logger LOG = Logger.getLogger(CellStringSplitToRows.class); protected Map<String, JavaQueryConfig> configs = new LinkedHashMap<String, JavaQueryConfig>(); protected List<JavaQueryOutputField> outputFields; protected List<JavaQueryParameter> parameters; protected String sqlParameterValues; protected List<String> dataStore; public CellStringSplitToRows() { String sql = "select STAT_VALUE from QAR.HB_MONITOR_MAN where COND_ID=? and FLIGHT_DATE=?" + " and TO_AIRPORT_ID=? and LD_AIRPORT_ID=? and FLIGHT_NO=? and FLIGHT_SEGMENT=?"; addConfig(CN_DS_ID, "数据源ID", "执行SQL所对应的数据源ID", "DS.ODS", true); addConfig(CN_QUERY_SQL, "查询SQL", "获取待分隔字符串的查询SQL", sql, true); addConfig(CN_IGNORE_EMPTY_VALUE, "忽略空值", "分隔字符串后空白值是否忽略,需要忽略填"true"否则填"false"", "true", false); } /** * 添加一个配置项 * * @param name * 名称 * @param alias * 别名 * @param desc * 描述 * @param defaultValue * 默认值 * @param notNull * 是否允许为空 */ private void addConfig(String name, String alias, String desc, String defaultValue, boolean notNull) { JavaQueryConfig p = new JavaQueryConfig(); p.setName(name); p.setAlias(alias); p.setDesc(desc); p.setValue(defaultValue); p.setNotNull(notNull); configs.put(name, p); } /** * 从保存的字符串中恢复配置信息 * * @param configs * 配置字符串 */ public void loadConfigs(String configStr) { if (StringUtil.isNullOrEmpty(configStr)) { return; } JSONObject obj = JSONObject.fromString(configStr); configs.get(CN_DS_ID).setValue(obj.has(CN_DS_ID) ? obj.getString(CN_DS_ID) : null); configs.get(CN_QUERY_SQL).setValue( obj.has(CN_QUERY_SQL) ? obj.getString(CN_QUERY_SQL) : null); configs.get(CN_IGNORE_EMPTY_VALUE).setValue( obj.has(CN_IGNORE_EMPTY_VALUE) ? obj.getString(CN_IGNORE_EMPTY_VALUE) : null); } /** * 保存配置信息 * * @return 返回配置字符串 */ public String saveConfigs() { JSONObject json = new JSONObject(); for (JavaQueryConfig config : configs.values()) { json.put(config.getName(), config.getValue()); } return json.toString(); } /** * 获取Java查询需要的配置信息 */ public List<JavaQueryConfig> getConfigs() { return new ArrayList<JavaQueryConfig>(configs.values()); } /** * 设置配置信息 * * @param key * 名称 * @param value * 配置值 */ public void setConfigValue(String key, String value) { configs.get(key).setValue(value); } /** * 设置配置信息 */ public void setConfigValues(Map<String, String> configValues) { for (Entry<String, String> config : configValues.entrySet()) { configs.get(config.getKey()).setValue(config.getValue()); } } /** * 根据配置信息初始化Java查询对象 */ public void init() { initOutputFields(); initParameters(); } /** * 初始化输出字段 */ protected void initOutputFields() { outputFields = new ArrayList<JavaQueryOutputField>(); outputFields.add(new JavaQueryOutputField(ID_OUTPUT_FIELD_1, NAME_OUTPUT_FIELD_1, "values", "", ValueType.STRING, "")); } /** * 初始化查询参数 */ protected void initParameters() { parameters = new ArrayList<JavaQueryParameter>(); parameters.add(new JavaQueryParameter(ID_PARAMETER_1, NAME_PARAMETER_1, "SQL参数值", "执行查询SQL所需参数值,值与值之间英文逗号(,)分隔", ValueType.STRING, true)); } /** * 返回参数对象 */ public List<JavaQueryParameter> getParameters() { return parameters; } /** * 返回Java查询的输出字段 */ public List<JavaQueryOutputField> getOutputFields() { return outputFields; } /** * 设置参数值 */ public void setParameterValue(String id, String value, String displayValue) { if (ID_PARAMETER_1.equals(id)) { sqlParameterValues = value == null ? null : value.replaceAll("^\\'|\\'$", ""); dataStore = null; // reset } } /** * 返回总行数,返回Integer.MAX_VALUE表示未知总行数 */ public int getRowCount() { if (dataStore != null) { return dataStore.size(); } else { return Integer.MAX_VALUE; } } /** * 关闭Java查询对象,关闭必要的资源 */ public void close() { dataStore = null; } public synchronized List<String> getValues() throws Exception { if (dataStore == null || !USE_CACHE_DATA) { Connection conn = null; PreparedStatement prep = null; ResultSet rs = null; try { String dsId = configs.get(CN_DS_ID).getValue(); String sql = configs.get(CN_QUERY_SQL).getValue(); boolean ignoreEmptyValue = !"false".equals(configs.get(CN_IGNORE_EMPTY_VALUE) .getValue()); conn = ConnectionPool.getInstance().getConnection(dsId); prep = conn.prepareStatement(sql); if (!StringUtil.isNullOrEmpty(sqlParameterValues)) { int count = sql.length() - sql.replaceAll("\\?", "").length(); String[] values = sqlParameterValues.split(","); for (int i = 0, len = Math.min(count, values.length); i < len; i++) { prep.setString(i + 1, values[i]); } } LOG.debug(sql + "; params=" + sqlParameterValues); rs = prep.executeQuery(); List<String> list = new ArrayList<String>(); while (rs.next()) { Reader reader = rs.getCharacterStream(1); BufferedReader br = new BufferedReader(reader); String line; while ((line = br.readLine()) != null) { String[] items = line.split(SPLIT_DELIMITER); for (String item : items) { if (ignoreEmptyValue && StringUtil.isNullOrEmpty(item)) { continue; } list.add(ignoreEmptyValue ? item.trim() : item); } } } dataStore = Collections.unmodifiableList(list); } catch (Exception e) { LOG.error(e.getMessage(), e); throw e; } finally { DbUtil.closeDBObject(rs, prep, conn); } } return dataStore; } /** * 检测输入参数是否合法 * * @param from * @param count */ protected void assertGetDataParams(int from, int count) { if (from < 0 || count < 1) { throw new InvalidParameterException( "parameter [from] must greater -1 and [count] must greater 0. current from=" + from + ", count=" + count); } } /** * 获取指定行的数据 */ public GridData getGridData(int from, int count) { assertGetDataParams(from, count); List<String> values = null; try { values = getValues(); } catch (Exception e) { throw new RuntimeException(e.getMessage(), e); } List<List<CellData>> datas = new ArrayList<List<CellData>>(); if (values != null && values.size() > 0) { int toIndex = Math.min(values.size(), from + count); for (String value : values.subList(from, toIndex)) { List<CellData> rowData = new ArrayList<CellData>(); CellData cellData = new CellData(); cellData.setStringValue(value); Double d = 0.0; try { d = Double.parseDouble(value); } catch (NumberFormatException e) { } cellData.setDoubleValue(d.doubleValue()); rowData.add(cellData); datas.add(rowData); } } GridData grid = new GridData(); List<String> headers = new ArrayList<String>(); for (JavaQueryOutputField f : getOutputFields()) { headers.add(f.getName()); } grid.setStringHeaders(headers); grid.setData(datas); return grid; } }
新建Java查询对象效果如下:
Java查询执行效果如下:
2、计算作图数据
作图所需数据的计算也是通过Java查询来实现(其中的核心算法需要使用到commons-math3-3.3.jar这个工具包)。
本Java查询示例代码如下:
展开源码
package smartbi.demo.javaquery; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.List; import org.apache.commons.math3.distribution.NormalDistribution; import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics; import smartbi.freequery.metadata.JavaQueryOutputField; import smartbi.freequery.querydata.CellData; import smartbi.freequery.querydata.GridData; import smartbi.util.ValueType; /** * 通过CellStringSplitToRows的结果集计算正态分布的分组值等<br> * 分组数可认为是x轴刻度数量,分组中的值可认为是x轴刻度值(一系列等差的值) */ public class NormalDistributionGroup extends CellStringSplitToRows { /** * 初始化输出字段 */ protected void initOutputFields() { outputFields = new ArrayList<JavaQueryOutputField>(); outputFields.add(new JavaQueryOutputField("ORDER_NUMBER", "orderNumber", "序号", "", ValueType.INTEGER, "")); outputFields.add(new JavaQueryOutputField("GROUP_VALUE", "groupValue", "分组值", "", ValueType.DOUBLE, "")); outputFields.add(new JavaQueryOutputField("FREQUENCY", "frequency", "频率", "", ValueType.INTEGER, "")); outputFields.add(new JavaQueryOutputField("PROBABILITY_DENSITY", "probabilityDensity", "概率密度", "", ValueType.DOUBLE, "")); } /** * 获取指定行的数据 */ public GridData getGridData(int from, int count) { long t1 = System.currentTimeMillis(); assertGetDataParams(from, count); List<String> strValues = null; try { strValues = getValues(); } catch (Exception e) { throw new RuntimeException(e.getMessage(), e); } List<Double> values = new ArrayList<Double>(); DescriptiveStatistics ds = new DescriptiveStatistics(); for (String strValue : strValues) { Double v = 0.0; try { v = Double.parseDouble(strValue); } catch (NumberFormatException e) { } ds.addValue(v); values.add(v); } List<List<CellData>> datas = new ArrayList<List<CellData>>(); if (values.size() > 0) { Collections.sort(values, new Comparator<Double>() { public int compare(Double o1, Double o2) { return o1.compareTo(o2); /* order by asc */ } }); double max = ds.getMax(); // 最大值 double min = ds.getMin(); // 最小值 double offset = max - min; // 极差 = 最大值 - 最小值 Double gc = Math.sqrt(ds.getN()); long groupCount = gc.longValue(); // 分组数 = ROUNDUP( SQRT(values.size()), 0 ) if (gc - groupCount > 0.000001) { ++groupCount; } double groupStep = offset * 1.0 / groupCount; // 分组组距 = 极差 / 分组数 groupCount += 2; // 在x轴上加一个比最小值(min)小的值和一个比最大值(max)大的值 Double firstValue = min - groupStep / 3; // 比最小值(min)小的值 double mean = ds.getMean(); // 平均值(average) double sd = ds.getStandardDeviation(); // 标准偏差 NormalDistribution nd = new NormalDistribution(mean, sd); for (int i = 0, lastJ = 0; i < groupCount; i++) { int orderNumber = i + 1; Double groupValue = firstValue + groupStep * i; int freq = 0; for (int j = lastJ; j < values.size(); j++) { if (values.get(j) <= groupValue) { ++freq; } else { lastJ = j; break; } } // List<CellData> rowData = new ArrayList<CellData>(); CellData firstCellData = new CellData(); firstCellData.setIntValue(orderNumber); // 序号 rowData.add(firstCellData); // CellData secondCellData = new CellData(); secondCellData.setDoubleValue(groupValue); // 分组值 rowData.add(secondCellData); // CellData thirdCellData = new CellData(); thirdCellData.setIntValue(freq); // 频率 rowData.add(thirdCellData); // CellData fourthCellData = new CellData(); fourthCellData.setDoubleValue(nd.density(groupValue)); // 概率密度 rowData.add(fourthCellData); // datas.add(rowData); } } GridData grid = new GridData(); List<String> headers = new ArrayList<String>(); for (JavaQueryOutputField f : getOutputFields()) { headers.add(f.getName()); } grid.setStringHeaders(headers); grid.setData(datas); long t2 = System.currentTimeMillis(); LOG.debug("calc normal distribution values: " + (t2 - t1) + "ms."); return grid; } }
新建Java查询对象效果如下:
Java查询执行效果如下:
3、电子表格出图
正态分布图作图参考文档:http://jingyan.baidu.com/article/f3ad7d0fffa41509c2345b6e.html
示例数据:
- 无标签