PHPでクロス集計したい
- 評価
- クリップ 2
- VIEW 2,976
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の中に入れ子の配列にすればうまく行くように思ったのですが、
思うような形でデータを取り出せずに行き詰っています。
解決方法、または考え方をお教えいただきたいです。
よろしくお願いいたします。
【追記】
沢山のアドバイスと不備の修正依頼ありがとうございます。
内容をゆっくりと確認できるのが今夜遅くになりますので、こちらで取り急ぎお礼申し上げます。
帰宅後にじっくりと読ませていただきます。
本当にありがとうございます。
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
checkベストアンサー
+4
元データ
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投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+3
仕様がひどいので、適当に解釈しています。
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 を整理するのがまず先ですね。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+2
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投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
0
動作環境がないので確認なのですが、現在結果はどうなっていますか？
単純にコードを見ると$termを設定している場所がなかったりしているのですが
これは今実行しているものではないのでしょうか。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 88.35%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
質問への追記・修正、ベストアンサー選択の依頼
退会済みユーザー
2017/10/24 00:57
テーブル定義とサンプルデータを提示しましょう。誰も答えられませんよ。
yambejp
2017/10/24 13:08
trendのロジックがわからない。全部下向きなら意味がない、term1とterm2は同値になる可能性は？