PHPでクロス集計したい

Question

MySQLのから後述のように抽出したデータを集計してブラウザ上に表示したいのですが、
途中で行き詰ってしまいました。

出力したいイメージのように2行あるデータを集計して1行にするところを
どのようにアプローチすればいいのかが特にわかりません。
アドバイスをどうぞよろしくお願いいたします。

※10/26サンプルのrank一部修正しました。
↓クエリ結果
|term|subject|name|rank|class|
|:--|:--:|--:|
|1|国語|田中|5|1-1|
|1|算数|田中|4|1-1|
|1|英語|田中|3|1-1|
|1|国語|山田|1|1-1|
|1|算数|山田|1|1-1|
|1|英語|山田|1|1-1|
|2|国語|田中|5|1-1|
|2|算数|田中|1|1-1|
|2|英語|田中|1|1-1|
|2|国語|山田|2|1-1|
|2|算数|山田|2|1-1|
|2|英語|佐藤|1|1-1|

↓出力したいイメージ

name	subject	term1	term2	trend
田中	国語	5	5	→
田中	算数	4	1	↑
田中	英語	3	1	↑
山田	国語	1	2	↓
山田	算数	1	2	↓
山田	英語	1	-	↓
佐藤	英語	-	1	↑

※trend列はterm1とterm2を比較した結果です。
-は9999と見なして、term1<term2の時↓、term1>term2の時↓、 term1=term2の時→を表示する。

$class= '1-1';
try{
	$dbh = new PDO($dsn, $user, $password);
	$sql = 'select term,subject,name,rank,class from t_scores WHERE class=:class';

	$statement = $dbh->prepare($sql);
	$statement->bindValue(':class', $class, PDO::PARAM_STR);
	$statement->execute();
	$result = array(); //初期化

	while($row = $statement->fetch(PDO::FETCH_ASSOC)){
		$name = $row['name'];
		$subject = $row['subject'];
		$rank = $row['rank'];

		$result[$name][$subject][$term] = $rank;
	} //while
   }catch (PDOException $e){
		print('Error:'.$e->getMessage());
		die();
	}
	$dbh = null;

$resultの中に入れ子の配列にすればうまく行くように思ったのですが、
思うような形でデータを取り出せずに行き詰っています。

解決方法、または考え方をお教えいただきたいです。
よろしくお願いいたします。

【追記】
沢山のアドバイスと不備の修正依頼ありがとうございます。
内容をゆっくりと確認できるのが今夜遅くになりますので、こちらで取り急ぎお礼申し上げます。

帰宅後にじっくりと読ませていただきます。
本当にありがとうございます。

Comments

[退会済みユーザー]

テーブル定義とサンプルデータを提示しましょう。誰も答えられませんよ。

[yambejp]

trendのロジックがわからない。全部下向きなら意味がない、term1とterm2は同値になる可能性は？

Answer 1

元データ

create table tbl(term int,subject varchar(20),name varchar(20),rank int,class varchar(20));
insert into tbl values
(1,'国語','田中',5,'1-1'),
(1,'算数','田中',4,'1-1'),
(1,'英語','田中',3,'1-1'),
(1,'国語','山田',1,'1-1'),
(1,'算数','山田',1,'1-1'),
(1,'英語','山田',1,'1-1'),
(2,'国語','田中',1,'1-1'),
(2,'算数','田中',1,'1-1'),
(2,'英語','田中',1,'1-1'),
(2,'国語','山田',2,'1-1'),
(2,'算数','山田',2,'1-1'),
(2,'英語','佐藤',1,'1-1');

集計

select subject,name
,sum(case when term=1 then rank else 0 end) as term1
,sum(case when term=2 then rank else 0 end) as term2
from tbl group by subject,name
order by term1 desc,term2 desc

追記

mysqlならこれでもよいかも

select subject,name
,sum((term=1)*rank) as term1
,sum((term=2)*rank) as term2
from tbl group by subject,name
order by term1 desc,term2 desc

Comments

[takeruwhite]

回答ありがとうございました。意図していたものを作ることができました。
SQLをちゃんと使えればシンプルに実現できたのですね。勉強します。

Answer 2

仕様がひどいので、適当に解釈しています。
php でやろうとすると無駄が多いですね。。。

<?php
$t_scores =[
    [
        'term' => 1,
        'subject' => '国語',
        'name' => '田中',
        'rank' => 5,
        'class' => '1-1',
    ],
    [
        'term' => 1,
        'subject' => '算数',
        'name' => '田中',
        'rank' => 4,
        'class' => '1-1',
    ],
    [
        'term' => 1,
        'subject' => '英語',
        'name' => '田中',
        'rank' => 3,
        'class' => '1-1',
    ],
    [
        'term' => 1,
        'subject' => '国語',
        'name' => '山田',
        'rank' => 1,
        'class' => '1-1',
    ],
    [
        'term' => 1,
        'subject' => '算数',
        'name' => '山田',
        'rank' => 1,
        'class' => '1-1',
    ],
    [
        'term' => 1,
        'subject' => '英語',
        'name' => '山田',
        'rank' => 1,
        'class' => '1-1',
    ],
    [
        'term' => 2,
        'subject' => '国語',
        'name' => '田中',
        'rank' => 1,
        'class' => '1-1',
    ],
    [
        'term' => 2,
        'subject' => '算数',
        'name' => '田中',
        'rank' => 1,
        'class' => '1-1',
    ],
    [
        'term' => 2,
        'subject' => '英語',
        'name' => '田中',
        'rank' => 1,
        'class' => '1-1',
    ],
        [
        'term' => 2,
        'subject' => '国語',
        'name' => '山田',
        'rank' => 2,
        'class' => '1-1',
    ],
    [
        'term' => 2,
        'subject' => '算数',
        'name' => '山田',
        'rank' => 2,
        'class' => '1-1',
    ],
    [
        'term' => 2,
        'subject' => '英語',
        'name' => '佐藤',
        'rank' => 1,
        'class' => '1-1',
    ],
    [
        'term' => 1,
        'subject' => '英語',
        'name' => '佐藤2',
        'rank' => 1,
        'class' => '1-1',
    ],
];

foreach ($t_scores as $t_score) {
    $term_1 = null;
    $term_2 = null;
    if(isset($result[$t_score['name']][$t_score['subject']]['term_1'])){
        $term_1 = $result[$t_score['name']][$t_score['subject']]['term_1'];
    }
    if(isset($result[$t_score['name']][$t_score['subject']]['term_2'])){
        $term_2 = $result[$t_score['name']][$t_score['subject']]['term_2'];
    }
    if($t_score['term']===1){
        $term_1 = $t_score['rank'];
        $trend = '↑';
    }
    if($t_score['term']===2){
        $term_2 = $t_score['rank'];
        $trend = '↓';
    }
    if(isset($term_1) && isset($term_2)){
        if($term_1 ==='-' && $term_2 ==='-'){
            $trend = '-';
        }else if($term_1 === '-'){
            $trend = '↓';
        }else if($term_2 === '-'){
            $trend = '↑';
        }else if($term_1 === $term_2){
            $trend = '-';
        }else if($term_1 > $term_2){
            $trend = '↓';
        }else if($term_1 < $term_2){
            $trend = '↑';
        }
    }
    $result[$t_score['name']][$t_score['subject']] = [
        'term_1' => isset($term_1)?$term_1:'-',
        'term_2' => isset($term_2)?$term_2:'-',
        'trend' => isset($trend)?$trend:'-'
    ];
}
echo '<table>';
echo '<tr>
<th>name</th>
<th>subject</th>
<th>term1</th>
<th>term2</th>
<th>trend</th>
</tr>';
foreach ($result as $key1 => $vals) {
    foreach ($vals as $key2 => $val) {
echo "<tr>
<td>$key1</td>
<td>$key2</td>
<td>{$val['term_1']}</td>
<td>{$val['term_2']}</td>
<td>{$val['trend']}</td>
</tr>";
    }
}
echo '</table>';

name	subject	term1	term2	trend
田中	国語	5	1	↓
田中	算数	4	1	↓
田中	英語	3	1	↓
山田	国語	1	2	↑
山田	算数	1	2	↑
山田	英語	1	-	↑
佐藤	英語	-	1	↓
佐藤2	英語	1	-	↑

sql を整理するのがまず先ですね。

Comments

[takeruwhite]

あいまいな質問から意図を組んでいただきありがとうございました。
PHPでの書き方勉強になりました。sqlの使い方勉強します。
ありがとうございました。

[退会済みユーザー]

曖昧というか、trend の表現方法が定義されていないこと、理解しています？

Answer 3

PHP側で集計するよりかは、SQLで集計した方が良くないでしょうか。

select A.name, A.subject, ifnull(T1.rank, '-') as 'term1', ifnull(T2.rank, '-') as 'term2',
        case when T1.rank < T2.rank then '↑' when T1.rank > T2.rank then '↓' else '－' end as 'trend'
from
(
    select name, subject from t_scores group by name, subject
) A left join
(
    select rank, name, subject, term from t_scores
) T1 ON A.name=T1.name AND A.subject=T1.subject AND T1.term=1
left join
(
    select rank, name, subject, term from t_scores
) T2 ON A.name=T2.name AND A.subject=T2.subject AND T2.term=2

Comments

[takeruwhite]

SQLをちゃんと使えればシンプルに実現できるのですね。
SQLの使い方勉強していきます。ご回答ありがとうございました。

Answer 4

動作環境がないので確認なのですが、現在結果はどうなっていますか？
単純にコードを見ると$termを設定している場所がなかったりしているのですが
これは今実行しているものではないのでしょうか。

Comments

[takeruwhite]

ご指摘ありがとうございました。
作っているものからサンプルに置き換えてながら書いている際に色々とおかしくなっていました。
お手間とらせて申し訳ありませんでした。

[yuki-saito]

SQLでよかったのですね。
何であっても解決してよかったです????