最近因为业务需求,需要做一个交叉表的类,自己才疏学浅,写了一个基本的交叉表类。
但到了分组与分组统计的时候,却不知道如何下手了。在此我贴上自己的代码,并跪求高手指点。
/**
* 基本交叉表
* @author hugh
*
*/
class Pivot
{
private $HORIZONTAL_TOTAL_FIELD = 'total';
private $VERTICAL_TOTAL_FIELD = 'total';
private $data;
private $topPivot;
private $leftPivot;
private $measure;
private $horizontalColumn = array ();
private $verticalColumn = array ();
private $pivotValue = array ();
private $isHorizontalTotal = true;
private $isVerticalTotal = true;
private $horizontalTotal = null;
private $verticalTotal = null;
private $title = 'PivotTab';
/**
* 初始化交叉表
*/
private function InitPivot()
{
$this->topPivot;
foreach ( $this->data as $d )
{
$this->horizontalColumn [] = $d [$this->leftPivot];
$this->verticalColumn [] = $d [$this->topPivot];
}
$this->horizontalColumn = array_unique ( $this->horizontalColumn );
$this->verticalColumn = array_unique ( $this->verticalColumn );
$reasult = array ();
foreach ( $this->horizontalColumn as $h )
{
foreach ( $this->verticalColumn as $v )
{
$this->pivotValue [$h] [$v] = 0;
}
}
}
/**
* 填充数据
*/
private function fillData()
{
foreach ( $this->data as $row )
{
$this->pivotValue [$row [$this->leftPivot]] [$row [$this->topPivot]] += $row [$this->measure];
}
if ($this->isHorizontalTotal)
{
$this->setHorizontalTotal ();
}
if ($this->isVerticalTotal)
{
$this->setVerticalTotal ();
}
}
/**
* 设置纵向合计
*/
private function setVerticalTotal()
{
$this->verticalColumn [] = $this->VERTICAL_TOTAL_FIELD;
foreach ( $this->horizontalColumn as $i )
{
$rowsum = 0;
foreach ( $this->verticalColumn as $j )
{
$rowsum += $this->pivotValue [$i] [$j];
}
$this->pivotValue [$i] [$this->TOTAL_FIELD] = $rowsum;
}
}
/**
* 设置横向合计
*/
private function setHorizontalTotal()
{
$this->horizontalColumn [] = $this->HORIZONTAL_TOTAL_FIELD;
foreach ( $this->verticalColumn as $i )
{
$rowsum = 0;
foreach ( $this->horizontalColumn as $j )
{
$rowsum += $this->pivotValue [$j] [$i];
}
$this->pivotValue [$this->HORIZONTAL_TOTAL_FIELD] [$i] = $rowsum;
}
}
/**
* 渲染
*/
function Render()
{
echo '<pre>';
print_r ( $this->pivotValue );
}
/**
* 渲染为table
*/
function RenderToTable()
{
$resault = "<table border='1' width='250'>\n";
$resault .= "<tr><td>$this->title</td>\n";
foreach ( $this->verticalColumn as $value )
{
$resault .= "<td>$value</td>\n";
}
$resault .= "</tr>\n";
foreach ( $this->horizontalColumn as $i )
{
$resault .= "<tr><td>$i</td>\n";
foreach ( $this->pivotValue [$i] as $value )
{
$resault .= "<td>$value</td>\n";
}
$resault .= "</tr>\n";
}
$resault .= "</table>";
return $resault;
}
/**
* 构造交叉表
* @param $data 数据源
* @param $topPivot 头栏目字段
* @param $leftPivot 左栏目字段
* @param $measure 计算量
*/
function __construct(array $data, $topPivot, $leftPivot, $measure)
{
$this->data = $data;
$this->leftPivot = $leftPivot;
$this->topPivot = $topPivot;
$this->measure = $measure;
$this->horizontalColumn = array ();
$this->verticalColumn = array ();
$this->InitPivot ();
$this->fillData ();
}}
但到了分组与分组统计的时候,却不知道如何下手了。在此我贴上自己的代码,并跪求高手指点。
/**
* 基本交叉表
* @author hugh
*
*/
class Pivot
{
private $HORIZONTAL_TOTAL_FIELD = 'total';
private $VERTICAL_TOTAL_FIELD = 'total';
private $data;
private $topPivot;
private $leftPivot;
private $measure;
private $horizontalColumn = array ();
private $verticalColumn = array ();
private $pivotValue = array ();
private $isHorizontalTotal = true;
private $isVerticalTotal = true;
private $horizontalTotal = null;
private $verticalTotal = null;
private $title = 'PivotTab';
/**
* 初始化交叉表
*/
private function InitPivot()
{
$this->topPivot;
foreach ( $this->data as $d )
{
$this->horizontalColumn [] = $d [$this->leftPivot];
$this->verticalColumn [] = $d [$this->topPivot];
}
$this->horizontalColumn = array_unique ( $this->horizontalColumn );
$this->verticalColumn = array_unique ( $this->verticalColumn );
$reasult = array ();
foreach ( $this->horizontalColumn as $h )
{
foreach ( $this->verticalColumn as $v )
{
$this->pivotValue [$h] [$v] = 0;
}
}
}
/**
* 填充数据
*/
private function fillData()
{
foreach ( $this->data as $row )
{
$this->pivotValue [$row [$this->leftPivot]] [$row [$this->topPivot]] += $row [$this->measure];
}
if ($this->isHorizontalTotal)
{
$this->setHorizontalTotal ();
}
if ($this->isVerticalTotal)
{
$this->setVerticalTotal ();
}
}
/**
* 设置纵向合计
*/
private function setVerticalTotal()
{
$this->verticalColumn [] = $this->VERTICAL_TOTAL_FIELD;
foreach ( $this->horizontalColumn as $i )
{
$rowsum = 0;
foreach ( $this->verticalColumn as $j )
{
$rowsum += $this->pivotValue [$i] [$j];
}
$this->pivotValue [$i] [$this->TOTAL_FIELD] = $rowsum;
}
}
/**
* 设置横向合计
*/
private function setHorizontalTotal()
{
$this->horizontalColumn [] = $this->HORIZONTAL_TOTAL_FIELD;
foreach ( $this->verticalColumn as $i )
{
$rowsum = 0;
foreach ( $this->horizontalColumn as $j )
{
$rowsum += $this->pivotValue [$j] [$i];
}
$this->pivotValue [$this->HORIZONTAL_TOTAL_FIELD] [$i] = $rowsum;
}
}
/**
* 渲染
*/
function Render()
{
echo '<pre>';
print_r ( $this->pivotValue );
}
/**
* 渲染为table
*/
function RenderToTable()
{
$resault = "<table border='1' width='250'>\n";
$resault .= "<tr><td>$this->title</td>\n";
foreach ( $this->verticalColumn as $value )
{
$resault .= "<td>$value</td>\n";
}
$resault .= "</tr>\n";
foreach ( $this->horizontalColumn as $i )
{
$resault .= "<tr><td>$i</td>\n";
foreach ( $this->pivotValue [$i] as $value )
{
$resault .= "<td>$value</td>\n";
}
$resault .= "</tr>\n";
}
$resault .= "</table>";
return $resault;
}
/**
* 构造交叉表
* @param $data 数据源
* @param $topPivot 头栏目字段
* @param $leftPivot 左栏目字段
* @param $measure 计算量
*/
function __construct(array $data, $topPivot, $leftPivot, $measure)
{
$this->data = $data;
$this->leftPivot = $leftPivot;
$this->topPivot = $topPivot;
$this->measure = $measure;
$this->horizontalColumn = array ();
$this->verticalColumn = array ();
$this->InitPivot ();
$this->fillData ();
}}
$scores [0] ['Round_Name'] = 'Round 1';
$scores [0] ['Player_Name'] = 'Bob';
$scores [0] ['Team'] = 'T1';
$scores [0] ['Score'] = 10;$scores [1] ['Round_Name'] = 'Round 1';
$scores [1] ['Player_Name'] = 'Bob';
$scores [1] ['Team'] = 'T2';
$scores [1] ['Score'] = 7;$scores [2] ['Round_Name'] = 'Round 2';
$scores [2] ['Player_Name'] = 'Bob';
$scores [2] ['Team'] = 'T2';
$scores [2] ['Score'] = 6;$scores [3] ['Round_Name'] = 'Round 2';
$scores [3] ['Player_Name'] = 'Jack';
$scores [3] ['Team'] = 'T1';
$scores [3] ['Score'] = 1;$scores [4] ['Round_Name'] = 'Round 3';
$scores [4] ['Player_Name'] = 'Bob';
$scores [4] ['Team'] = 'T2';
$scores [4] ['Score'] = 3;$scores [5] ['Round_Name'] = 'Round 3';
$scores [5] ['Player_Name'] = 'Jack';
$scores [5] ['Team'] = 'T1';
$scores [5] ['Score'] = 5;$scores [6] ['Round_Name'] = 'Round 3';
$scores [6] ['Player_Name'] = 'Bob';
$scores [6] ['Team'] = 'T1';
$scores [6] ['Score'] = 6;$scores [7] ['Round_Name'] = 'Round 3';
$scores [7] ['Player_Name'] = 'Hugh';
$scores [7] ['Team'] = 'T1';
$scores [7] ['Score'] = 7;$scores [8] ['Round_Name'] = 'Round 3';
$scores [8] ['Player_Name'] = 'Hugh';
$scores [8] ['Team'] = 'T1';
$scores [8] ['Score'] = 9;/**************************************************************/$p = new Pivot ( $scores, 'Round_Name', 'Player_Name', 'Score' );echo $p->RenderToTable ();
先给点建议
1、你的类并无什么问题,但考虑到扩展的方便,建议将所有 private 改为 protected (保护)。以便类的继承
2、建议新增一个方法,比如 parse。将原来在构造函数完成的工作移到该方法中
调用方法也稍做改动,由
$p = new Pivot ( $scores, 'Round_Name', 'Player_Name', 'Score' );
echo $p->RenderToTable ();
改为
$p = new Pivot;
$p->parse ( $scores, 'Round_Name', 'Player_Name', 'Score' );
echo $p->RenderToTable ();
这样一来的附加好处是可以重复使用实例化后的对象
$p->parse ( $scores, 'Player_Name', 'Round_Name', 'Score' );
echo $p->RenderToTable ();
3、你原有的数据格式并没有问题,只要是从数据库中读取的,也就是这种格式了至于分组,你可以考虑从基类继承一个类
class TeamPivot extends Pivot {
}
来处理分组问题:
遍历原始数据,按分组键值提取属于该分组的数据集,交 parse 完成该分组交叉表计算
这可以通过函数 array_filter 轻松的实现
当然,你还需要按分组保存 pivotValue当存在多个分组时,传入的分组键应为数组。并可能需要用递归来完成每个分组的计算
借你点时间研究下,救救我啊..!!!
only you..