php - 根据站点日期创建数据透视比较表

Question

我在 mysql 中有一个名为“sitetotals”的表，其结构和数据如下

sitetotals_id | sitetotals_date | sitetotals_site | sitetotals_total  
            1 | 2015-08-08      | siteA           |               50  
            2 | 2015-08-08      | siteB           |               40  
            3 | 2015-08-08      | siteC           |               30  
            4 | 2015-08-08      | siteD           |               20  
            5 | 2015-08-08      | siteE           |               10   
            6 | 2015-08-01      | siteB           |                3  
            7 | 2015-08-01      | siteC           |                2  
            8 | 2015-08-01      | siteD           |                1  

这是我目前拥有的 html 表格

site  | 2015-08-08 | 2015-08-01  
siteA | 50         |  
siteB | 40         |  
siteC | 30         |  
siteD | 20         |  
siteE | 10         | 

我希望实现以下结果

site  | 2015-08-08 | 2015-08-01  
siteA | 50         | 0  
siteB | 40         | 3  
siteC | 30         | 2  
siteD | 20         | 1  
siteE | 10         | 0  

以下是我当前的查询

    <h2>GET LATEST DATE AND PREVIOUS DATE</h2>
    <?php
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $stmt = $conn->prepare('SELECT * FROM sitetotals 
    ORDER BY sitetotals_wc DESC, sitetotals_total DESC
    LIMIT 1');
    $stmt->execute();
    $row = $stmt->fetch(); // Use fetchAll() if you want all results
    $date_raw = date('d-m-Y', strtotime($row['sitetotals_wc']));
    echo $date_raw." | ".$date_raw7 = date('d-m-Y', strtotime('-7 day', strtotime($date_raw)));
    ?>
    <h2>TABLE DATA</h2>
    <?php
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $stmt = $conn->prepare('SELECT * FROM sitetotals
    ORDER BY sitetotals_wc DESC, sitetotals_total DESC LIMIT 50');
    $stmt->execute();
    $result = $stmt->fetchAll();
    echo "<table class='paginate'><tr><th>Date</th><th>Worst Site</th>
    <th>".$date_raw."</th>
    <th>".$date_raw7."</th>
    </tr>\n";
    foreach($result as $row) {
    echo "<tr>";
    echo "<td>".date_format($date=(date_create($row['sitetotals_wc'])),'d-m-Y')."</td>";
    echo "<td>".$row['sitetotals_site']."</td>";
    echo "<td>".$row['sitetotals_total']."</td>";
    echo "<td>        HERE IS WHERE THE ANSWER SHOULD GO       </td>";
    echo "</tr>\n";
    }
    echo "</table>\n";
    ?>

if possible I would also like to be able to extend this pivot with multiple report periods

Answer 1

以动态日期数量为中心

直接在mysql中

计划

• construct dates dimension [ dim_site_dates_v ] containing all date periods where there is sitetotals data
• construct sites dimension [ dim_sites_v ] containing all sites with data in sitetotals
• set number of periods [ @num_periods ] to report against
• join dates and site names over reporting period and write summary values to table [ sites_summary_tmp ]
• use dynamic query template and loop over the reporting space to parameterise query
• prepare+execute query

设置

create table sitetotals
(
  sitetotals_id integer primary key not null,
  sitetotals_date date not null,
  sitetotals_site varchar(22) not null,
  sitetotals_total integer not null
);

insert into sitetotals
( sitetotals_id, sitetotals_date, sitetotals_site, sitetotals_total )
values
(1 , '2015-08-08' , 'siteA' , 50 ),  
(2 , '2015-08-08' , 'siteB' , 40 ),
(3 , '2015-08-08' , 'siteC' , 30 ),
(4 , '2015-08-08' , 'siteD' , 20 ),
(5 , '2015-08-08' , 'siteE' , 10 ),
(6 , '2015-08-01' , 'siteB' , 3 ),
(7 , '2015-08-01' , 'siteC' , 2 ),
(8 , '2015-08-01' , 'siteD' , 1 ),
(9 , '2015-08-01' , 'siteF' , 1 ),
(10 , '2015-07-25' , 'siteA' , 22 ),
(11 , '2015-07-25' , 'siteC' , 20 ),
(12 , '2015-07-25' , 'siteD' , 10 ),
(13 , '2015-07-25' , 'siteF' , 5 ),
(14 , '2015-07-18' , 'siteA' , 2 ),
(15 , '2015-07-18' , 'siteC' , 5 ),
(16 , '2015-07-18' , 'siteD' , 3 ),
(17 , '2015-07-18' , 'siteE' , 5 )
;

create view dim_sites_v
as
select distinct sitetotals_site
from sitetotals
;

create view dim_site_dates_v
as
select distinct sitetotals_date
from sitetotals
;

create table sites_summary_tmp
(
  date_nmbr integer not null,
  site_name varchar(22) not null,
  total integer not null,
  primary key ( date_nmbr, site_name )
);

set @num_periods := 4;

-- if you want to reuse this table, truncate/delete previous summary data first..
insert into sites_summary_tmp
( date_nmbr, site_name, total )
select dates.row_num, sites.sitetotals_site, coalesce(sd.sitetotals_total, 0) as total
from dim_sites_v sites
cross join 
(
select sitetotals_date,
@row_num := @row_num + 1 as row_num
from dim_site_dates_v
cross join ( select @row_num := 0 ) as params
order by sitetotals_date desc
) dates
left join sitetotals sd
on sites.sitetotals_site = sd.sitetotals_site
and dates.sitetotals_date = sd.sitetotals_date
where dates.row_num <= @num_periods
;

-- set variables
-- @num_periods should only need to be set once
-- however sqlfiddle uses separate connection for
-- building schema and running sql. so it must be set in this connection
set @num_periods := 4;
set @join_template := ' inner join sites_summary_tmp prev#curr#
on  prev#prev#.site_name = prev#curr#.site_name
and prev#prev#.date_nmbr = prev#curr#.date_nmbr - 1
';
set @field_template := '`#prev#nbr##`,';
set @field_alias_template := 'prev#nbr#.total as `#prev#nbr##`,';
-- pivot report can be generated by joining summary results to itself
-- for each date period. and dynamically aliasing the titles to the 
-- reporting dates.
set @query := 'select site_name,
#select_fields#
from
(
select prev0.site_name, 
#alias_fields#
from sites_summary_tmp prev0
#all_joins#
where prev0.date_nmbr = 1
) pivot
;'
;

查询

-- replace parameters in dynamic @query
select count(*)
from
(
select 
@query := replace(@query, '#alias_fields#', 
                  concat(replace(@field_alias_template, '#nbr#', dates.row_num), ' #alias_fields#')),
@query := replace(@query, '#select_fields#', 
                  concat(replace(@field_template, '#nbr#', dates.row_num), ' #select_fields#')),
@query := replace(@query, concat('#prev', dates.row_num, '#'), sitetotals_date),
@query := if(dates.row_num < @num_periods - 1, replace(@query, '#all_joins#', 
                         concat(replace(replace(@join_template, '#prev#', dates.row_num), '#curr#', dates.row_num + 1), 
                         ' #all_joins#')), 
              @query)
from
(
select sitetotals_date,
@row_num := @row_num + 1 as row_num
from dim_site_dates_v
cross join ( select @row_num := -1 ) as params
order by sitetotals_date desc
) dates
where dates.row_num < @num_periods
) suppress_output
into @ignore
;

set @query := replace(replace(replace(@query, '#all_joins#', ''), ', #alias_fields#', ' '), ', #select_fields#', ' ');

PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;
deallocate prepare dynamic_statement;

输出

+-----------+------------+------------+------------+------------+
| site_name | 2015-08-08 | 2015-08-01 | 2015-07-25 | 2015-07-18 |
+-----------+------------+------------+------------+------------+
| siteA     |         50 |          0 |         22 |          2 |
| siteB     |         40 |          3 |          0 |          0 |
| siteC     |         30 |          2 |         20 |          5 |
| siteD     |         20 |          1 |         10 |          3 |
| siteE     |         10 |          0 |          0 |          5 |
| siteF     |          0 |          1 |          5 |          0 |
+-----------+------------+------------+------------+------------+

sqlfiddle

<小时/>

使用 PHP

计划

• construct dates dimension [ dim_site_dates_v ] containing all date periods where there is sitetotals data
• construct sites dimension [ dim_sites_v ] containing all sites with data in sitetotals
• set number of periods [ @num_periods ] to report against
• join dates and site names over reporting period and order by date and site return resultset to php
• php to pivot on date fields

php

<?php

/**
 * Mysqli initial code
 * 
 * User permissions of database
 * Create, Alter and Index table, Create view, and Select, Insert, Update, Delete table data
 * 
 * @package         PhpFiddle
 * @link            http://phpfiddle.org
 * @since           2012
*/

require "util/public_db_info.php";

$short_connect = new mysqli($host_name, $user_name, $pass_word, $database_name, $port);

$sql = "set @num_periods := 4";
$result = $short_connect->query($sql) or die(mysqli_error($short_connect));

$sql = "select dates.sitetotals_date, sites.sitetotals_site, coalesce(sd.sitetotals_total, 0) as total " .
"from dim_sites_v sites " .
"cross join  " .
"( " .
"select sitetotals_date, " .
"@row_num := @row_num + 1 as row_num " .
"from dim_site_dates_v " .
"cross join ( select @row_num := 0 ) as params " .
"order by sitetotals_date desc " .
") dates " .
"left join sitetotals sd " .
"on sites.sitetotals_site = sd.sitetotals_site " .
"and dates.sitetotals_date = sd.sitetotals_date " .
"where dates.row_num <= @num_periods order by 1 desc,2";
$result = $short_connect->query($sql) or die(mysqli_error($short_connect));

$table = array();
$dates = array();
$sites = array();

if (($result) && ($result->num_rows > 0))
{
    $results = array();

    //convert query result into an associative array
    while ($row = $result->fetch_assoc())
    {
        if(!in_array($row["sitetotals_date"], $dates))$dates[] = $row["sitetotals_date"];
        if(!in_array($row["sitetotals_site"], $sites))$sites[] = $row["sitetotals_site"];
        $table[$row["sitetotals_site"]][$row["sitetotals_date"]] = $row["total"];
    }

    $result->free();
}

echo "<table>";
echo "<tr><td>site_name</td>";
foreach($dates as $date)
{
    echo "<td>" . $date . "</td>";
}
echo "</tr>";

foreach ($sites as $site)
{
    echo "<tr><td>" . $site . "</td>";
    foreach($dates as $date)
    {
        echo "<td>" . $table[$site][$date] . "</td>";
    }
    echo "</tr>";
}
echo "</table>";

$short_connect->close();


?>

输出

<table>
  <tbody>
    <tr>
      <td>site_name</td>
      <td>2015-08-08</td>
      <td>2015-08-01</td>
      <td>2015-07-25</td>
      <td>2015-07-18</td>
    </tr>
    <tr>
      <td>siteA</td>
      <td>50</td>
      <td>0</td>
      <td>22</td>
      <td>2</td>
    </tr>
    <tr>
      <td>siteB</td>
      <td>40</td>
      <td>3</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <td>siteC</td>
      <td>30</td>
      <td>2</td>
      <td>20</td>
      <td>5</td>
    </tr>
    <tr>
      <td>siteD</td>
      <td>20</td>
      <td>1</td>
      <td>10</td>
      <td>3</td>
    </tr>
    <tr>
      <td>siteE</td>
      <td>10</td>
      <td>0</td>
      <td>0</td>
      <td>5</td>
    </tr>
    <tr>
      <td>siteF</td>
      <td>0</td>
      <td>1</td>
      <td>5</td>
      <td>0</td>
    </tr>
  </tbody>
</table>

这两种解决方案都可以通过调整@num_periods的值来动态扩展报告周期的数量。如果@num_periods大于数据中的周期总数，则不会返回任何行。