我有一个有四列的表,这就是它的样子 . 我称之为T_BPR_KPI_MONTHLY_VALUES
KPI_NAME_SHORT_S | MONTH_N | YEAR_N | VALUE_N
-----------------------------------------------
MY_KPI_1 | 1 | 2015 | 99.87
MY_KPI_2 | 1 | 2015 | 97.62
... | 1 | 2015 | ...
MY_KPI_1 | 2 | 2015 | ...
... | ... | 2015 | ...
每个kpi代表一个度量值,每个值都有每日值,这些值保存在另一个名为T_BPR_KPI_DY的表中 . 我的目标是计算并保存每个KPI的月度值 .
有可能在某一天,某些kpis的每日 Value 仍然缺失,为了精确计算月度值,我必须能够替换数据库中的现有值,并插入未来几个月和几年的新值 .
我认为oracle sql merge操作将是这项任务的不错选择 . 我们的想法是检查条目是否已经存在,如果是,则更新其值,如果不是,则插入新条目 .
这就是查询的样子
MERGE INTO T_BPR_KPI_MONTHLY_VALUE A
USING( SELECT 'MY_KPI_1' AS KPI_NAME_SHORT_S, 1 AS MONTH_N, 2014 AS YEAR_N FROM DUAL ) B
ON ( A.KPI_NAME_SHORT_S = B.KPI_NAME_SHORT_S )
WHEN MATCHED THEN
UPDATE SET VALUE_N = ( select AVG(MY_KPI_1) from T_BPR_KPI_DY where DAY_D between '01.01.2014' AND '31.01.2014')
WHEN NOT MATCHED THEN
INSERT (KPI_NAME_SHORT_S, MONTH_N, YEAR_N, VALUE_N) VALUES ('MY_KPI_1', 1, 2014, ( select AVG(MY_KPI_1) from T_BPR_KPI_DY where DAY_D between '01.01.2014' AND '31.01.2014') )
我认为在运行中计算平均每月值并不是一个坏主意,因此你可以看到我有另一个选择查询,它只计算特定kpi的avg monthy值 . 我不确定这是否是最好的实用解决方案,但是当我在oracle sql developer工具中执行此查询时它工作正常 . 但是,当我尝试从应用程序执行它时,它不起作用 .
这就是方法的样子
public static void storeValuesToDb(ArrayList<String> kpiNames) throws SQLException {
Connection conn = getOracleJDBCConnection_DASH();
int currentYear = cal.get(Calendar.YEAR);
int startYear = cal.get(Calendar.YEAR) - 1;
for (String kpiName : kpiNames) {
for (int i = startYear; i <= currentYear; i++) {
for (int j = 0; j < 12; j++) {
try {
String myMergeSQL = ""
+ "MERGE INTO T_BPR_KPI_MONTHLY_VALUE A "
+ "USING( SELECT '" + kpiName + "' AS KPI_NAME_SHORT_S, " + (j + 1) + " AS MONTH_N, " + i + " AS YEAR_N FROM DUAL ) B ON ( A.KPI_NAME_SHORT_S = B.KPI_NAME_SHORT_S ) "
+ "WHEN MATCHED THEN "
+ "UPDATE SET VALUE_N = ( select AVG(" + kpiName + ") from T_BPR_KPI_DY where DAY_D between '" + getFirstDateOfMonth(j, i) + "' AND '" + getLastDateOfMonth(j, i) + "') "
+ "WHEN NOT MATCHED THEN "
+ "INSERT (KPI_NAME_SHORT_S, MONTH_N, YEAR_N, VALUE_N) VALUES ('" + kpiName + "', " + (j + 1) + ", " + i + ", ( select AVG(" + kpiName + ") from T_BPR_KPI_DY where DAY_D between '" + getFirstDateOfMonth(j, i) + "' AND '" + getLastDateOfMonth(j, i) + "') )";
System.out.println(myMergeSQL);
Statement stmt_dash = conn.createStatement();
stmt_dash.executeUpdate(myMergeSQL);
conn.commit();
stmt_dash.close();
} catch (SQLException ex) {
conn.close();
}
}
}
}
conn.close();
}
在终端中,它只打印出第一个合并sql . 它既没有完成操作也没有抛出异常 . 它以某种方式阻塞,并且在db中也没有发生任何事情 . 我的合并查询可能不正确,或者无法使用语句对象执行此类操作 . 如果有人能够看到这个问题的情况,请帮忙 .
Thx提前
1 回答
我将首先重新构建您的合并查询并解决一些问题:
MERGE的USING部分实际上意味着你的"source of raw data" . 您正在使用具有硬编码值的双重选择 . 您应在此处选择所有KPI,并按KPI计算平均值 . 撰写您的查询,选择所有KPI及其相应的VALUE_N,并在USING部分中使用它
匹配时,UPDATE SET使用"source of raw data"中的值,这是代码中的别名B,而不是在UPDATE子句内部计算 .
如果不匹配则INSERT VALUES - 再次使用"source of raw data"中的值,即代码中的别名B,不要尝试计算插入内的VALUE_N - 至少不是那种方式我认为这是你查询的主要问题 .
MERGE INTO xxx使用()B你给你的表提供了2个别名,但是在WHEN MATCHED内部的行下方或者你没有使用别名 . 如果A和B具有相似的命名列,则会引发问题 .
我在 生产环境 中如何使用合并的一个示例:使用表格中的选择合并到目标中(在源选择内部,您也可以显然添加其他计算,在您的情况下为平均值)
T_REPORT_DAILY_SNAPSHOT_2G应该在您的代码中选择kpis名称,值和平均值或INSERT和UPDATE所需的任何内容
希望这在某种程度上有所帮助 .