5.2 交叉表决策分析方案

交叉表决策分析方案是一个很实用的决策分析方案，它还可以分为静态交叉表和动态交叉表。通过交叉表方案，可以在原有数据表的基础上，模拟生成新的数据表，对数据进行深入分析，最终找到决策方案。

5.2.1 利用静态交叉表进行决策分析

静态交叉表是交叉表的简单形式，它的使用范围具有一定的局限性，在使用前要充分考虑这一点。

1．方案分析

静态交叉表分析方案是一种相对比较简单的交叉表分析方案，生成交叉表的SQL语句由软件开发人员手动编写，在编写生成交叉表的SQL语句之前，需要先确定静态交叉表的生成方案，如图5.9所示。

图5.9 静态交叉表生成方案图解

确定静态交叉表的生成方案后，就可以编写生成静态交叉表的SQL语句了，不过需要明确的是，静态交叉表决策分析方案有一定的局限性，要求欲作为新生成交叉表列名的原始数据表的列的值是固定的，例如学历和月份等，而年度则不适合。

2．实施过程

这里将给出三种典型静态交叉表的实现方法，方法一中的分组列、分类列和统计列为三个不同的列，统计方式为最常用的求和（SUM）统计；方法二中的分组列和分类列是根据同一个日期列得到的，统计方式依然为最常用的求和（SUM）统计；方法三中对统计列的统计方式为记数（COUNT）统计。

l 方法一典型的静态交叉表统计

实例位置：光盘\mr\5\5.2\5.2.1\01

在某企业人事管理软件中需要加入薪水统计功能，对不同部门和学历的薪水发放情况进行统计，这就需要通过交叉表技术实现，在这里采用静态交叉表技术，最终生成的交叉表效果如图5.10所示。

图5.10 利用静态交叉表实现薪水统计功能

实现图5.10所示交叉表依据的数据表如图5.11所示。在生成交叉表时，需要以部门列（dept_id）的非重复值作为交叉表的主键列（部门名称列）的值，以学历列（school_age）的非重复值作为交叉表的非主键列（博士、硕士、本科、大专）的名称，以薪水列（salary）的值作为欲统计的数据，由于学历列的值是固定的，只能是博士、硕士、本科和大专，所以可以通过静态交叉表技术实现该功能。

图5.11 数据表tb_record

依据图5.11所示数据表实现图5.10所示交叉表的SQL语句如下：

SELECT dept_id AS [部门名称],

SUM(salary) AS [部门薪水总和],

SUM(CASE school_age WHEN '博士' THEN salary ELSE NULL END) AS [博士],

SUM(CASE school_age WHEN '硕士' THEN salary ELSE NULL END) AS [硕士],

SUM(CASE school_age WHEN '本科' THEN salary ELSE NULL END) AS [本科],

SUM(CASE school_age WHEN '大专' THEN salary ELSE NULL END) AS [大专]

FROM tb_record

GROUP BY dept_id

说明：图5.10中最后一行的总计功能不能通过上面的SQL语句实现，是在开发JSP页时添加的！

首先在Analyse类中通过JDBC方式执行上面生成交叉表的SQL语句，并将执行结果封装成List集合。需要注意的是，这里将列头信息也封装到了List集合中，具体代码如下：

例程5-1 代码位置：光盘\mr\5\5.2\5.2.1\01\src\com\mwq\database\Analyse.java

List<Object[]> notes = new ArrayList<Object[]>(); // 创建List集合

this.stmt = this.conn.createStatement();

this.rs = this.stmt // 执行上面的SQL语句

.executeQuery("SELECT dept_id AS [部门名称],"

+ "SUM(salary) AS [部门薪水总和],"

+ "SUM(CASE school_age WHEN '博士' THEN salary ELSE NULL END) AS [博士],"

+ "SUM(CASE school_age WHEN '硕士' THEN salary ELSE NULL END) AS [硕士],"

+ "SUM(CASE school_age WHEN '本科' THEN salary ELSE NULL END) AS [本科],"

+ "SUM(CASE school_age WHEN '大专' THEN salary ELSE NULL END) AS [大专] "

+ " FROM tb_record "

+ "GROUP BY dept_id;");

int l = rs.getMetaData().getColumnCount(); // 获得生成交叉表拥有的列数

Object[] note = new Object[l]; // 封装生成交叉表的列头信息

for (int i = 0; i < l; i++) {

note[i] = rs.getMetaData().getColumnName(i + 1);

}

notes.add(note);

while (rs.next()) { // 封装交叉表的数据信息

note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getObject(i + 1);

}

notes.add(note);

}

然后在Searches类中编写一个通过JDBC方式根据指定ID检索记录的方法。由于数据表tb_record的dept_id列作为外键参考数据表tb_dept的主键列ID，所以需要编写一个根据ID检索部门名称的方法，具体代码如下：

例程5-2 代码位置：光盘\mr\5\5.2\5.2.1\01\src\com\mwq\database\Searches.java

String deptName = null;

this.stmt = this.conn.createStatement();

this.rs = this.stmt.executeQuery("select name from tb_dept where id=" + id);

rs.next();

deptName = rs.getString(1);

最后在JSP页遍历上面的List集合。由于需要在交叉表的最后一行加入“总计”，如图5.10所示，所以在遍历List集合的过程中，需要统计每一列的各个部门的薪水总和，具体代码如下：

例程5-3 代码位置：光盘\mr\5\5.2\5.2.1\01\content.jsp

List notes=new Analyse().acrossTable(); // 获得List集合

String content;

Object[] note=(Object[])notes.get(0); // 获得表头数组

for(int column=0;column<note.length;column++){ // 遍历表头

content=note[column].toString();

}

</tr>

int[] stat=new int[note.length]; // 定义用来保存统计数据的数组

String bgcolor;

for(int row=1;row<notes.size()-1;row++){ // 遍历交叉表的行

note=(Object[])notes.get(row);

if(row%2==0)

bgcolor="#FFFFFF";

else

bgcolor="#FBFDF7";

<tr height="28" align="center" bgcolor="<%=bgcolor %>" class="STYLE2">

content=new Searches().dept(note[0].toString());

for(int column=1;column<note.length;column++){ // 遍历交叉表的列

if(note[column]!=null){

content=note[column].toString();

stat[column]+=Integer.valueOf(content); // 按列统计薪水

}

}

</tr>

}

if((notes.size()+1)%2==0)

bgcolor="#FFFFFF";

else

bgcolor="#FBFDF7";

for(int column=1;column<note.length;column++){ // 输出薪水统计信息

}

</tr>

</table>

l 方法二利用静态交叉表按日期统计

实例位置：光盘\mr\5\5.2\5.2.1\02

在某门户网站的后台管理模块需要加入网站流量统计功能，将网站每年的访问量按月统计，实现上述功能需要通过交叉表技术，在这里采用静态交叉表技术，最终生成的交叉表效果如图5.12所示。

图5.12 利用静态交叉表实现网站访问量统计功能

生成图5.12所示交叉表依据的数据表如图5.13所示。

图5.13 数据表tb_amount

在生成如图5.12所示交叉表时，需要以日期列（date）所包含年份的非重复值作为交叉表的主键列的值，以日期列（date）所包含月份的非重复值作为交叉表的非主键列的名称，以访问量列（amount）的值作为欲统计的数据，由于日期列所包含的月份是固定的，所以可以采用静态交叉表技术实现该功能。

依据图5.13所示数据表实现图5.12所示交叉表的SQL语句如下：

SELECT DATEPART(YY,date) AS [年份],

SUM(amount) AS [总访问量],

SUM(CASE DATEPART(MM,date) WHEN 1 THEN amount ELSE NULL END) AS [1月],

SUM(CASE DATEPART(MM,date) WHEN 2 THEN amount ELSE NULL END) AS [2月],

SUM(CASE DATEPART(MM,date) WHEN 3 THEN amount ELSE NULL END) AS [3月],

SUM(CASE DATEPART(MM,date) WHEN 4 THEN amount ELSE NULL END) AS [4月],

SUM(CASE DATEPART(MM,date) WHEN 5 THEN amount ELSE NULL END) AS [5月],

SUM(CASE DATEPART(MM,date) WHEN 6 THEN amount ELSE NULL END) AS [6月],

SUM(CASE DATEPART(MM,date) WHEN 7 THEN amount ELSE NULL END) AS [7月],

SUM(CASE DATEPART(MM,date) WHEN 8 THEN amount ELSE NULL END) AS [8月],

SUM(CASE DATEPART(MM,date) WHEN 9 THEN amount ELSE NULL END) AS [9月],

SUM(CASE DATEPART(MM,date) WHEN 10 THEN amount ELSE NULL END) AS [10月],

SUM(CASE DATEPART(MM,date) WHEN 11 THEN amount ELSE NULL END) AS [11月],

SUM(CASE DATEPART(MM,date) WHEN 12 THEN amount ELSE NULL END) AS [12月]

FROM tb_amount

GROUP BY DATEPART(YY,date)

说明：图5.12中最后一行的总计功能不能通过上面的SQL语句实现，是在开发JSP页时添加的！

如果想获取日期列中代表指定日期部分的整数，可以通过函数DATEPART(datepart,columnName)实现，其中第一个入口参数用来设置欲获取的日期部分，第二个入口参数用来设置欲从中获取指定日期的日期列的名称，第一个入口参数datepart的常用可选值请参见表5.14。

表5.14 函数DATEPART()的第一个入口参数的常用可选值

日期部分	常用缩写
Year	YY和YYYY
Month	MM和M
Day	DD和D
Hour	HH
Minute	MI和N
Second	SS和S

在Analyse类中通过JDBC方式执行上面生成交叉表的SQL语句的具体代码如下：

例程5-4 代码位置：光盘\mr\5\5.2\5.2.1\02\src\com\mwq\database\Analyse.java

List<Object[]> notes = new ArrayList<Object[]>();

this.stmt = this.conn.createStatement();

this.rs = this.stmt

.executeQuery("SELECT DATEPART(YY,date) AS [年份],"

+ "SUM(amount) AS [总访问量],"

+ "SUM(CASE DATEPART(MM,date) WHEN 1 THEN amount ELSE NULL END) AS [1月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 2 THEN amount ELSE NULL END) AS [2月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 3 THEN amount ELSE NULL END) AS [3月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 4 THEN amount ELSE NULL END) AS [4月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 5 THEN amount ELSE NULL END) AS [5月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 6 THEN amount ELSE NULL END) AS [6月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 7 THEN amount ELSE NULL END) AS [7月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 8 THEN amount ELSE NULL END) AS [8月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 9 THEN amount ELSE NULL END) AS [9月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 10 THEN amount ELSE NULL END) AS [10月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 11 THEN amount ELSE NULL END) AS [11月],"

+ "SUM(CASE DATEPART(MM,date) WHEN 12 THEN amount ELSE NULL END) AS [12月] "

+ "FROM tb_amount GROUP BY DATEPART(YY,date);");

int l = rs.getMetaData().getColumnCount();

Object[] note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getMetaData().getColumnName(i + 1);

}

notes.add(note);

while (rs.next()) {

note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getObject(i + 1);

}

notes.add(note);

}

在JSP页遍历上面的List集合。由于需要在交叉表的最后一行加入“总计”，如图5.12所示，所以在遍历List集合的过程中，需要统计每个月的访问总量，具体代码如下：

例程5-5 代码位置：光盘\mr\5\5.2\5.2.1\02\content.jsp

List notes=new Analyse().acrossTable();

String content;

Object[] note=(Object[])notes.get(0);

for(int column=0;column<note.length;column++){

content=note[column].toString();

}

</tr>

int[] stat=new int[note.length];

for(int row=1;row<notes.size();row++){

note=(Object[])notes.get(row);

content=note[0].toString();

for(int column=1;column<note.length;column++){

if(note[column]!=null){

content=note[column].toString();

stat[column]+=Integer.valueOf(content);

}else{

content="-";

}

}

</tr>

}

for(int column=1;column<note.length;column++){

}

</tr>

</table></td>

l 方法三静态交叉表扩展

实例位置：光盘\mr\5\5.2\5.2.1\03

前面的统计方式均是通过SUM()函数统计现有数据，下面将在静态交叉表中通过COUNT()函数统计满足条件的记录数。例如在某企业推出了人性化管理，为每个月中所有过生日的员工集体庆祝生日，所以在企业人事管理软件中需要加入员工出生日期按月统计功能，统计出每个部门和每个月过生日的总人数。实现上述功能需要通过交叉表技术，在这里采用静态交叉表技术，最终生成的交叉表如图5.14所示。

图5.14 利用静态交叉表实现公司员工出生月份统计功能

生成交叉表依据的数据表如图5.11所示，在生成交叉表时，需要以部门列（dept_id）的非重复值作为交叉表的主键列的值，以出生日期列（school_age）所包含月份的非重复值作为交叉表的非主键列的名称，由于出生日期列所包含的月份是固定的，所以可以采用静态交叉表技术实现该功能。

依据图5.11所示数据表实现图5.14所示交叉表的SQL语句如下：

SELECT dept_id AS [部门名称],

COUNT(birthday) AS [部门人数],

COUNT(CASE DATEPART(MM,birthday) WHEN 1 THEN 0 ELSE NULL END) AS [1月],

COUNT(CASE DATEPART(MM,birthday) WHEN 2 THEN 0 ELSE NULL END) AS [2月],

COUNT(CASE DATEPART(MM,birthday) WHEN 3 THEN 0 ELSE NULL END) AS [3月],

COUNT(CASE DATEPART(MM,birthday) WHEN 4 THEN 0 ELSE NULL END) AS [4月],

COUNT(CASE DATEPART(MM,birthday) WHEN 5 THEN 0 ELSE NULL END) AS [5月],

COUNT(CASE DATEPART(MM,birthday) WHEN 6 THEN 0 ELSE NULL END) AS [6月],

COUNT(CASE DATEPART(MM,birthday) WHEN 7 THEN 0 ELSE NULL END) AS [7月],

COUNT(CASE DATEPART(MM,birthday) WHEN 8 THEN 0 ELSE NULL END) AS [8月],

COUNT(CASE DATEPART(MM,birthday) WHEN 9 THEN 0 ELSE NULL END) AS [9月],

COUNT(CASE DATEPART(MM,birthday) WHEN 10 THEN 0 ELSE NULL END) AS [10月],

COUNT(CASE DATEPART(MM,birthday) WHEN 11 THEN 0 ELSE NULL END) AS [11月],

COUNT(CASE DATEPART(MM,birthday) WHEN 12 THEN 0 ELSE NULL END) AS [12月]

FROM tb_record

GROUP BY dept_id

说明：图5.14中最后一行的总计功能不能通过上面的SQL语句实现，是在开发JSP页时添加的！

注意：从上面的SQL语句中可以看出，当通过COUNT()函数生成交叉表时，并不需要指定统计列，将统计列设置为“0”即可！

在Analyse类中通过JDBC方式执行上面生成交叉表的SQL语句的具体代码如下：

例程5-6 代码位置：光盘\mr\5\5.2\5.2.1\03\src\com\mwq\database\Analyse.java

List<Object[]> notes = new ArrayList<Object[]>();

this.stmt = this.conn.createStatement();

this.rs = this.stmt

.executeQuery("SELECT dept_id AS [部门名称],"

+ "COUNT(birthday) AS [部门人数],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 1 THEN 0 ELSE NULL END) AS [1月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 2 THEN 0 ELSE NULL END) AS [2月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 3 THEN 0 ELSE NULL END) AS [3月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 4 THEN 0 ELSE NULL END) AS [4月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 5 THEN 0 ELSE NULL END) AS [5月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 6 THEN 0 ELSE NULL END) AS [6月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 7 THEN 0 ELSE NULL END) AS [7月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 8 THEN 0 ELSE NULL END) AS [8月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 9 THEN 0 ELSE NULL END) AS [9月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 10 THEN 0 ELSE NULL END) AS [10月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 11 THEN 0 ELSE NULL END) AS [11月],"

+ "COUNT(CASE DATEPART(MM,birthday) WHEN 12 THEN 0 ELSE NULL END) AS [12月] "

+ "FROM tb_record GROUP BY dept_id;");

int l = rs.getMetaData().getColumnCount();

Object[] note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getMetaData().getColumnName(i + 1);

}

notes.add(note);

while (rs.next()) {

note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getObject(i + 1);

}

notes.add(note);

}

在JSP页遍历上面的List集合。由于需要在交叉表的最后一行加入“总计”，如图5.14所示，所以在遍历List集合的过程中，需要统计每个月出生的总人数，具体代码如下：

例程5-7 代码位置：光盘\mr\5\5.2\5.2.1\03\index.jsp

List notes=new Analyse().acrossTable();

String content;

Object[] note=(Object[])notes.get(0);

for(int column=0;column<note.length;column++){

content=note[column].toString();

}

</tr>

int[] stat=new int[note.length];

String bgcolor="#FBEAF0";

for(int row=1;row<notes.size();row++){

note=(Object[])notes.get(row);

if(row%2==0)

bgcolor="#FBEAF0";

else

bgcolor="#FFFFFF";

<tr height="28" align="center" bgcolor="<%=bgcolor %>" class="STYLE2">

content=new Searches().dept(note[0].toString());

for(int column=1;column<note.length;column++){

if(note[column]!=null){

content=note[column].toString();

stat[column]+=Integer.valueOf(content);

}else{

content="-";

}

}

</tr>

}

if(bgcolor.equals("#FFFFFF"))

bgcolor="#FBEAF0";

else

bgcolor="#FBEAF0";

for(int column=1;column<note.length;column++){

}

</tr>

</table>

这样，几个典型的应用静态交叉表技术开发数据统计模块的例子就完成了。通过上面的例子，相信读者已经看出了它的严重不足，就是当用来生成交叉表非主键列的数据列的值是不确定的，利用静态交叉表技术就很难实现数据统计了，在这种情况下可以采用动态交叉表技术。

3．补充说明

在编写实现静态交叉表的SQL语句时，上面的3个案例只使用到了SUM()和COUNT()函数，也可以通过交叉表统计最大值、最小值和平均值，依次采用函数MAX()、MIN()和AVG()，在使用这5个聚集函数进行交叉表统计时，只有函数COUNT()对统计列的值的类型没有要求，其它4个函数均要求统计列的值的类型为数值型，否则在执行SQL语句生成交叉表时将抛出异常。

5.2.2 利用动态交叉表进行决策分析

动态交叉表是交叉表的复杂形式，它的使用范围与静态交叉表相比要大很多，但是由于受到SQL语句最大长度的限制，依然具有一定的局限性，在使用前依然要充分考虑这一点。

1．方案分析

动态交叉表分析方案是一种相对比较复杂的交叉表分析方案，生成交叉表的SQL语句由软件开发人员编写的存储过程动态生成，在编写生成动态交叉表SQL语句的存储过程之前，需要先确定该存储过程的生成方案，如图5.15所示。

图5.15 动态交叉表生成方案图解

确定动态交叉表的生成方案后，就可以编写生成动态交叉表的存储过程了，不过需要明确的是，动态交叉表决策分析方案也有一定的局限性，虽然它对欲作为新生成交叉表列名的原始数据表的列的值的个数没有要求，但是由于生成的SQL语句的最大长度只能是4000，所以从这个角度讲对该列值的个数仍有限制，但是对于列值内容和个数不确定的情况，采用动态交叉表分析方案仍是一个不错的选择。

2．实施过程

这里将给出一个适合采用动态交叉表技术实现的案例，以此来介绍适合于采用动态交叉表技术实现的情况，以及动态交叉表技术的使用方法。还利用动态交叉表技术实现了前面利用静态交叉表技术实现的一个案例，以此来介绍动态交叉表更人性化的一面。

l 方法一典型的动态交叉表统计

实例位置：光盘\mr\5\5.2\5.2.2\01

在开发电子商务网站后台时，通常情况下需要加入商品销售统计功能，用来统计商品的销售情况和订单的形成情况。例如在某电子商务网站中需要加入这样的统计功能，统计方式是按订单和商品进行统计，统计每件商品和每个订单的净收益，最终实现的功能页效果如图5.16所示。

图5.16 利用动态交叉表实现商品销售情况统计分析

生成图5.16所示交叉表依据的数据表如图5.1所示，因为订单的数量是不断增加的，所以通过静态交叉表技术是很难实现的，在这种情况下，必须采用动态交叉表技术。确定实施方案之后，首先要编写实现动态交叉表的存储过程，具体编写方法在5.1.1节已经做了详细介绍，这里使用的也是在5.1.1节给出的存储过程。在程序中调用存储过程，并将得到的交叉表数据及列名封装到List集合的完整代码如下。

例程5-8 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\database\Analyse.java

List<Object[]> notes = new ArrayList<Object[]>();

this.cablStmt = this.conn

.prepareCall("{call procedure_across_table_a(?,?,?,?,?)}");

this.cablStmt.setString(1, "tb_orderForGoods");

this.cablStmt.setString(2, "order_info_id");

this.cablStmt.setString(3, "merchandise_info_id");

this.cablStmt.setString(4, "amount");

this.cablStmt.setString(5, "SUM");

this.rs = this.cablStmt.executeQuery();

int l = rs.getMetaData().getColumnCount();

Object[] note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getMetaData().getColumnName(i + 1);

}

notes.add(note);

while (rs.next()) {

note = new Object[l];

for (int i = 0; i < l; i++) {

note[i] = rs.getObject(i + 1);

}

notes.add(note);

}

因为订单和商品的数量均不是固定的，所以在开发软件时，均需要提供分页功能，如图5.16所示。这里需要两个负责分页功能的Bean，它们的开发思想完全相同，内部结构也只有很小的变化，一个负责对List集合进行分页，另一个负责对Object数组进行分页，下面是负责对List集合分页的Bean的关键代码。

例程5-9 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\tool\ListPagination.java

public class ListPagination {

private int currentPage; // 当前页

private int totalPages; // 总页数

private int pageRows; // 每页记录数

private int totalRows; // 总记录数

private int pageStartRow; // 每页开始记录

private int pageEndRow; // 每页结束记录

private boolean hasPreviousPage;// 是否有上一页

private boolean hasNextPage; // 是否有下一页

private List<Object[]> totalList; // 要分页的数据

// 初始化分页信息

public void initPageBean(List<Object[]> totalList, int pageRows) {

this.totalList = totalList;

this.pageRows = pageRows;

this.totalRows = totalList.size();

this.currentPage = 1;

if ((totalRows % pageRows) == 0) {

totalPages = totalRows / pageRows;

if (this.totalRows == 0)

this.totalPages = 1;

} else {

totalPages = totalRows / pageRows + 1;

}

this.hasPreviousPage = false;

if (currentPage == totalPages) {

hasNextPage = false;

} else {

hasNextPage = true;

}

this.pageStartRow = 1;

if (totalRows < pageRows) {

this.pageEndRow = totalRows;

} else {

this.pageEndRow = pageRows;

}

// 获得当前页信息

public List<Object[]> getCurrentPageList() {

if (currentPage * pageRows < totalRows) {

pageEndRow = currentPage * pageRows;

pageStartRow = pageEndRow - pageRows;

} else {

pageEndRow = totalRows;

pageStartRow = pageRows * (totalPages - 1);

}

List<Object[]> pageList = new ArrayList<Object[]>(pageEndRow - pageStartRow);

if (totalRows != 0) {

for (int i = pageStartRow; i < pageEndRow; i++) {

pageList.add(totalList.get(i));

}

return pageList;

}

// 获得上一页信息

public List<Object[]> getPreviousPageList() {

currentPage = currentPage - 1;

if (currentPage < 1)

currentPage = 1;

if (currentPage >= totalPages) {

hasNextPage = false;

} else {

hasNextPage = true;

}

if ((currentPage - 1) > 0) {

hasPreviousPage = true;

} else {

hasPreviousPage = false;

}

List<Object[]> pageList = this.getCurrentPageList();

return pageList;

}

// 获得下一页信息

public List<Object[]> getNextPageList() {

currentPage = currentPage + 1;

if (currentPage > totalPages)

currentPage = totalPages;

if ((currentPage - 1) > 0) {

hasPreviousPage = true;

} else {

hasPreviousPage = false;

}

if (currentPage >= totalPages) {

hasNextPage = false;

} else {

hasNextPage = true;

}

List<Object[]> pageList = this.getCurrentPageList();

return pageList;

}

public int getTotalRows() { // 返回总记录数

return totalRows;

}

public boolean isHasPreviousPage() { // 返回是否有上一页

return hasPreviousPage;

}

public boolean isHasNextPage() { // 返回是否有下一页

return hasNextPage;

}

在如图5.16所示的统计页面中，其中最后一行的“总计”功能和最后一列的“净收益”功能是后添加的，并不是通过交叉表实现的。实现对每个订单“订货数”总计功能的SQL语句如下。

例程5-10 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\database\Searches.java

select order_info_id,sum(amount) as amount from tb_orderForGoods group by order_info_id

实现对每个订单“净收益”总计功能的SQL语句和实现最后一列“净收益”功能的SQL语句有些类似，只是最后依据的分组（group by）字段不同。

下面是实现对每个订单“净收益”总计功能的SQL语句，依据的分组字段为order_info_id。

例程5-11 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\database\Searches.java

select collect.order_info_id,sum(proceeds) from (

select merchandise_info_id,order_info_id,(price-

(select price from tb_merchandiseInfo where id=merchandise_info_id)

)*amount as proceeds from tb_orderForGoods

) as collect group by order_info_id

下面是实现最后一列“净收益”功能的SQL语句，依据的分组字段为merchandise_info_id。

例程5-12 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\database\Searches.java

select collect.merchandise_info_id,sum(proceeds) from (

select merchandise_info_id,order_info_id,(price-

(select price from tb_merchandiseInfo where id=merchandise_info_id)

)*amount as proceeds from tb_orderForGoods

) as collect group by merchandise_info_id

下面是实现对所有商品“销售总量”总计功能的SQL语句。

例程5-13 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\database\Searches.java

select sum(amount) from tb_orderForGoods

下面是实现对所有商品销售“净收益”总计功能的SQL语句，与实现最后一列“净收益”功能的SQL语句类似，只是取消了分组（group by）关键字。

例程5-14 代码位置：光盘\mr\5\5.2\5.2.2\01\src\com\mwq\database\Searches.java

select sum(collect.proceeds) from (

select merchandise_info_id,order_info_id,(price-

(select price from tb_merchandiseInfo where id=merchandise_info_id)

)*amount as proceeds from tb_orderForGoods

) as collect

实现例程5-11、例程5-12和例程5-14中SQL语句的基本SQL语句为：

select merchandise_info_id,order_info_id,(price-

(select price from tb_merchandiseInfo where id=merchandise_info_id)

)*amount as proceeds from tb_orderForGoods

在SQL Server查询分析器上执行上面的代码，将得到如图5.17所示的数据，统计出的为每个商品在每个订单中的净收益。

图5.17 利用SQL语句统计商品销售情况

完成上面的准备工作，就可以开发JSP页了，首先调用存储过程，获得交叉表数据，以及调用其它方法获得相关数据，并通过这些数据初始化分页信息，关键代码如下。

例程5-15 代码位置：光盘\mr\5\5.2\5.2.2\01\index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="GB2312"%>

<jsp:useBean id="rowPage" class="com.mwq.tool.ListPagination" scope="session"/>

<jsp:useBean id="columnPage" class="com.mwq.tool.ArrayPagination" scope="session"/>

int columnDel=2;

int columnNum=4;

int columnMin=columnDel;

int columnMax=columnNum+columnMin;

Object[] columnNames=null;

List<Object[]> pageList=null;

Searches searches=new Searches();

if(request.getParameter("requestPage")==null){

List notes=new Analyse().acrossTable(); // 调用存储过程，获得交叉表数据

Object[] columnNamesAll=(Object[]) notes.get(0); // 获得交叉表的列头名称

notes.remove(0);

columnNames=new Object[columnNamesAll.length-columnDel];

for(int i=columnDel;i<columnNamesAll.length;i++){

columnNames[i-columnDel]=columnNamesAll[i];

}

columnPage.initPageBean(columnNames,columnNum); // 根据列头名称初始化分页Bean

columnNames=columnPage.getCurrentPageArray();

rowPage.initPageBean(notes,10); // 根据交叉表数据初始化分页Bean

pageList=rowPage.getCurrentPageList();

// 此处省略了部分代码

}else{

String requestPage=request.getParameter("requestPage");

if(requestPage.equals("previousColumnPage")){

// 此处省略了部分代码

}else if(requestPage.equals("nextColumnPage")){

// 此处省略了部分代码

}else if(requestPage.equals("previousRowPage")){

// 此处省略了部分代码

}else if(requestPage.equals("nextRowPage")){

// 此处省略了部分代码

}else{

}

下面的代码负责处理表头信息，因为对订单进行了分页处理，所以需要根据分页Bean，动态处理表头中的订单编号，关键代码如下。

例程5-16 代码位置：光盘\mr\5\5.2\5.2.2\01\index.jsp

<td rowspan="2"><%if(columnPage.isHasPreviousPage())

out.print("<a href='index.jsp?requestPage=previousColumnPage'>上一批</a>");

else out.print("<font color='#FFCCFF'>上一批</font>"); %></td>

int columns=columnNames.length;

for(int i=0;i<columns;i++){

}

<td rowspan="2"><%if(columnPage.isHasNextPage())

out.print("<a href='index.jsp?requestPage=nextColumnPage'>下一批</a>");

else out.print("<font color='#FFCCFF'>下一批</font>"); %></td>

</tr>

for(int i=0;i<columns;i++){

}

</tr>

下面的代码负责遍历交叉表，在遍历的过程中，需要根据商品编号、订单编号和订货数确定商品的进价，最终计算出该商品的净收益。关键代码如下。

例程5-17 代码位置：光盘\mr\5\5.2\5.2.2\01\index.jsp

for(int r=0;r<pageList.size();r++){

Object[] note=pageList.get(r);

String merchName=searches.MerchandiseInfo(note[0].toString())[2].toString();

for(int c=columnMin;c<columnMax;c++){

String amountS="-";

String incomeS="-";

if(note[c]!=null){

amountS=note[c].toString();

Object[] merchInfo=searches.MerchandiseInfo(note[0].toString());

Object[] orderForGoods=searches.OrderForGoods(note[0].toString(),

columnNames[c-columnMin].toString());

int amount=Integer.valueOf(amountS);

double cost=(Double)merchInfo[6]; // 获得商品的进价

double sale=(Double)orderForGoods[3]; // 获得商品的销售价

incomeS=(sale-cost)*amount+""; // 计算商品的净收益

}

}

</tr>

}

下面的代码负责输出总计信息，总计信息是通过执行前面介绍的SQL语句获得的，并通过分页Bean进行了处理，此时数组中的统计数据只是需要在当前页显示的，具体代码如下。

例程5-18 代码位置：光盘\mr\5\5.2\5.2.2\01\index.jsp

for(int i=0;i<columns;i++){

}

Object collect[]=searches.Collect();

</tr>

l 方法二动态交叉表扩展

实例位置：光盘\mr\5\5.2\5.2.2\02

在5.2.1节中利用静态交叉表技术实现了对公司员工出生月份的统计，通过动态交叉表技术也可以实现，并且统计结果将更人性化，图5.18为通过动态交叉表技术得到的交叉表，对比图5.18和图5.16，会发现在图5.18中没有7月份的数据，这是因为公司没有在7月份出生的员工，被动态生成的交叉表过滤掉了。

图5.18 利用动态交叉表实现公司员工出生月份统计功能

这里将5.2.1节实现动态交叉表的存储过程做了简单的修改，又添加了几个参数，用来设置交叉表的列名，这样得到的交叉表列名将更准确，修改后的存储过程的完整代码如下。

CREATE procedure procedure_across_table_b

@TableName as varchar(50), --生成交叉表依据的表名

@NewColumn as varchar(50), --生成表头依据的字段名

@BeforeColumnName as varchar(50), --定义动态列开始名称

@AfterColumnName as varchar(50), --定义动态列结束名称

@GroupColumn as varchar(50), --分组依据的字段名

@FirstColumnName as varchar(50), --定义第一列名称

@StatColumn as varchar(50), --欲统计的字段名

@SecondColumnName as varchar(50), --定义第二列名称

@Operator as varchar(10) --统计的运算方式

DECLARE @SQL as varchar(1000), @Column as varchar(50) --定义参数

EXECUTE ('DECLARE cursor_new_column CURSOR FOR SELECT DISTINCT ' +

@NewColumn + ' from ' + @TableName + ' for read only ') --定义游标

begin

SET nocount ON

SET @SQL='select ' + @GroupColumn + ' as '+ @FirstColumnName + ', ' + @Operator +

'(' + @StatColumn + ') AS [' + @SecondColumnName + ']' --定义SQL语句头

OPEN cursor_new_column

while (0=0)

BEGIN --遍历游标

FETCH NEXT FROM cursor_new_column INTO @Column --通过游标获取列头信息

if (@@fetch_status<>0) break

SET @SQL = @SQL + ', ' + @Operator + '(CASE ' + @NewColumn + ' WHEN ''' +

@Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' +

@BeforeColumnName + @Column + @AfterColumnName + ']' --循环追加SQL语句

END

SET @SQL = @SQL + ' from ' + @TableName +

' group by ' + @GroupColumn --定义SQL语句尾

EXECUTE(@SQL) --执行SQL语句

PRINT @SQL --输出SQL语句

IF @@error <>0 RETURN @@error --如果出错，则返回错误代码

CLOSE cursor_new_column --关闭游标

DEALLOCATE cursor_new_column RETURN 0 --释放游标，释放成功则返回0

end

调用上面存储过程的关键代码如下。

例程5-19 代码位置：光盘\mr\5\5.2\5.2.2\02\src\com\mwq\database\Analyse.java

this.cablStmt = this.conn

.prepareCall("{call procedure_across_table_b(?,?,?,?,?,?,?,?,?)}");

this.cablStmt.setString(1, "tb_record");

this.cablStmt.setString(2, "DATEPART(MM,birthday)");

this.cablStmt.setString(3, "");

this.cablStmt.setString(4, "月");

this.cablStmt.setString(5, "dept_id");

this.cablStmt.setString(6, "部门名称");

this.cablStmt.setString(7, "0");

this.cablStmt.setString(8, "部门人数");

this.cablStmt.setString(9, "COUNT");

this.rs = this.cablStmt.executeQuery();

在调用存储过程时，将通过SQL函数处理的列名作为参数传入是有效的，例如第二个参数；第3、4个参数用来格式化动态列的列名，第3个参数用来格式化动态列名的前面，第4个参数用来格式化动态列名的后面，这里只在后面加了一个“月”字；第6个参数用来设置第一列的名称；第8个参数用来设置第二列的名称；当第9个参数传入的SQL函数为“COUNT”时，并不需要为第7个参数传入确切的列名，在这种情况下必须设置为“0”，否则必须传入存在的列名。

3．补充说明

动态交叉表决策分析方案依赖的是动态交叉表分析技术，动态交叉表分析技术在根据指定条件生成SQL语句时，由于生成SQL语句的长度与分类列中非重复值的个数成正比，所以当分类列中非重复值的个数超过一定数量时，最终生成SQL语句的长度将超出SQL语句的最大长度，对于超出的部分将自动被截取掉，导致SQL语句不完整而不能正常执行，最终导致生成交叉表失败，对于这一点，在使用动态交叉表时要注意，在设计软件时要充分考虑分类列中非重复值的个数。

【查看所有评论(0)条】