HOME»応用情報技術者試験掲示板»令和元年秋期午後問6設問2 GROUP BYはなぜ
投稿する
実際の処理の順序（※論理的な処理順序）は
フロム句
↓
ジョイン句
↓
ウェア句
↓
グループバイ句
↓
ハビング句
↓
セレクト句
↓
オーダーバイ句
なわけで、ウェアで測定日（対象月の）と従業員番号を絞ると、その月のその従業員のしか残らない。
その中でグループバイをしたい、ということになるが、実際
・グループバイを省略する
・従業員番号でグループ化する
でもできるが、その場合セレクトには
従業員番号, SUM(歩数)
としなければならない。
※グループバイ句に存在しない列名をセレクト句に指定するとエラーになる
じゃあ仮に空欄fがセレクト列全部書けよ、となったとしても、今度はアップデート セットの項目に対してどっちを当てるんだ？となるので
従業員番号があっては困る。
WHERE句内に記述できるものの認識があやふやのようです。
SQLでテーブルを結合する方法は2種類あります
・WHERE句内
・FROM句内のJOIN
・WHERE句でテーブルのカラム同士を'='でつなぐことによって
2つのテーブルを結合することができます
この方法は古い時代からの結合方法になります。
この記述方法は簡単なので、現在でもよく使用されます。
問題として、WHERE句には
・テーブル結合
・行の絞り込み
の２種類の性質の全く異なる式が混在してしまいます。
SQL学習の初期段階で、WHERE句が単なる行の絞り込みと考えていると
混乱する原因になります。
・FROM句内のJOIN
新しいテーブル結合の記述方法です。
FROM句内のテーブル名にJOINを指定して複数のテーブルを結合します。
WHERE句ではなくFROM句にテーブル結合条件を書くことにより、テーブルを
結合していることを明示的に示したい場合は有用です。
欠点として、WHERE句による結合よりもややマイナーな位置づけなことです。
SQLはいままで長い年月の間に何度も仕様変更されており、今となっては
わかりずらい点が多々あります。
特にSQLの歴史の中でoracleがメジャーになった際に、oracleでSQLを覚えた古い
人達のSQLの書き方がいまでも残っております。
そのため、今でも古い昔ながらのSQLが普通に使われてるといった背景もあります。
物事をシンプルにするために
上記は
テーブル1のカラム1とテーブル2のカラム1が等しいレコードに対して、
テーブル1のカラム2に、テーブル2のカラム1の値を代入する。
これを考えれば、図3の一つのSQLで対象月の全ての従業員の総歩数をそれぞれに更新を一撃でかけていることになる。そういう意味では、
とはならない（バインド変数を使っているから一見そう見えるだけで、シーケンシャルな処理）。
whereの結合はほんとにやめてほしい。特にoracleのプラス演算子で書かれているといらっとする。
・SUMで複数行を1行に集約する
・そのためには、暗黙の前提としてSUM前の全ての行の従業員番号が
同じでなくてはならない
・その状態を作るにはカーソルで月次レポ－トテーブルを1行づつ
処理する必要がある
と考えました。
月次レポートテーブル 1行に対して、歩数テーブル N行が結合するので
カーソルによる絞り込みが必要と思いました。
カーソルによる絞り込みが必要と思いました。
カーソルだと、図3にrowとして取得している従業員番号で絞りを入れているならそうだなあ、となるんですが、そう見えないんですよね。
すみません。私が勘違いしていたようです。
GinSanaさんが書かれた説明でいいと思います。
副問い合わせのWHEREで結合したテーブルが外側のUPDATEのテーブルへ作用するSQLです。
これにSUMが絡んだため、複雑に見えてしまったようです。
久々に難易度の高いSQLを見て、勉強させていただきました。
問題を切り分け、わかりやすくしていただき、ありがとうございます。
テーブル1のカラム2に、テーブル2のカラム1の値を代入する。
これを考えれば、図3の一つのSQLで対象月の全ての従業員の総歩数をそれぞれに更新を一撃でかけていることになる。
こちら詳しく書いていただいたおかげで、クリアにこの問題を理解することができました。
また、「カーソル処理か否か」についても勉強を深めることができました。
根気強くご教授いただき本当にありがとうございました。
迅速に、そして詳細に返答くださり、ありがとうございました。
ご指摘いただいたように、まだまだデータベースの知識が曖昧になっている部分が多いなと感じましたので、さらに理解を深められるようこれからも尽力いたします。
個人的にはSQLの歴史について知れたのもとてもおもしろかったです。
また、GinSanaさんとの「カーソル処理か否か」という議論もとても興味深かったです。
この度はたくさんの返答をいただき、誠にありがとうございました。
[4003] 令和元年秋期午後問6設問2 GROUP BYはなぜ
初投稿さん(No.1)
https://www.ap-siken.com/kakomon/01_aki/pm06.html
https://www.ap-siken.com/bbs/3199.html#sendform
以前にも、「GROUP BYはなぜ不要なのか」を質問されている方がいらっしゃるのですが、この投稿を読んでも、やはりまだ納得できない部分があったため、投稿させていただきます。
設問2の（２）fの答えはSUM(歩数.歩数)で、私は、「従業員番号でグループ化する必要があるのではないか」と考えています。
副問い合わせ内のWHERE句の条件を解いた結果は以下のようになるイメージを持っています。
※：レポート年月が2022/3の場合
従業員番号・測定日・歩数
1・2022/3/1・2000
1・2022/3/2・5000
1・2022/3/3・10000
2・2022/3/1・1000
2・2022/3/2・4500
以下省略
これをSUMで集計する場合、従業員番号でグループ化するべきではないのかと感じてしまっています。
SQLのシュミレーターを実行したところ、確かにGROUP BYは不要でした。
しかし、自分ではどこの認識が間違えているのかいまいちピンときていません。
(UPDATE文の性質、副問い合わせの性質等)
ご教授いただけると幸いです。
https://www.ap-siken.com/bbs/3199.html#sendform
以前にも、「GROUP BYはなぜ不要なのか」を質問されている方がいらっしゃるのですが、この投稿を読んでも、やはりまだ納得できない部分があったため、投稿させていただきます。
設問2の（２）fの答えはSUM(歩数.歩数)で、私は、「従業員番号でグループ化する必要があるのではないか」と考えています。
副問い合わせ内のWHERE句の条件を解いた結果は以下のようになるイメージを持っています。
※：レポート年月が2022/3の場合
従業員番号・測定日・歩数
1・2022/3/1・2000
1・2022/3/2・5000
1・2022/3/3・10000
2・2022/3/1・1000
2・2022/3/2・4500
以下省略
これをSUMで集計する場合、従業員番号でグループ化するべきではないのかと感じてしまっています。
SQLのシュミレーターを実行したところ、確かにGROUP BYは不要でした。
しかし、自分ではどこの認識が間違えているのかいまいちピンときていません。
(UPDATE文の性質、副問い合わせの性質等)
ご教授いただけると幸いです。
2023.03.03 12:34
GinSanaさん(No.2)
★AP プラチナマイスター
>設問2の（２）fの答えはSUM(歩数.歩数)で、私は、「従業員番号でグループ化する必要があるのではないか」と考えています。
実際の処理の順序（※論理的な処理順序）は
フロム句
↓
ジョイン句
↓
ウェア句
↓
グループバイ句
↓
ハビング句
↓
セレクト句
↓
オーダーバイ句
なわけで、ウェアで測定日（対象月の）と従業員番号を絞ると、その月のその従業員のしか残らない。
その中でグループバイをしたい、ということになるが、実際
・グループバイを省略する
・従業員番号でグループ化する
でもできるが、その場合セレクトには
従業員番号, SUM(歩数)
としなければならない。
※グループバイ句に存在しない列名をセレクト句に指定するとエラーになる
じゃあ仮に空欄fがセレクト列全部書けよ、となったとしても、今度はアップデート セットの項目に対してどっちを当てるんだ？となるので
従業員番号があっては困る。
2023.03.03 13:00
pixさん(No.3)
★AP シルバーマイスター
私も気になったのでこの問題を検討してみました。私の見解を投稿いたします。
間違っているかもしれませんので、ご参考までに。
この問題は見えない処理を想定する必要がある、厄介な問題かもしれません。
質問者様の思考は以下と思われます。
１.（1）の処理で月次レポート表に行をINSERTする時に全ての従業員数の行が
INSERTされる。従業員が100人いれば100行INSERTされる。
２．(2）の処理でINSERTしたレコードの更新を行う。
しかし、WHERE文に従業員番号を絞り込むような条件が書かれていないため、
100人全員のSUMが計算されてしまうのではないか？
そのため、GROUP BY句で従業員毎のSUMを計算する必要があるのではないか？
と考えているのではないでしょうか？
しかし、このUPDATEで純粋に従業員毎にレコードをUPDATEするには、
暗黙の処理が必要になります。
それは「(2)：(1)で挿入したレコードについて，次の処理を行う。」の
処理の①～⑤を実行するにあたり、更新用カーソルを利用し、1レコードずつ
更新する処理をループで回していると想定する必要があります。
そうでないと全体の処理として整合性がとれないと思われます。
（2）の文章から暗黙的にカーソル処理をしていることを読み取れという
出題者の意図かもしれません。
そうであるならば、実務レベルでの処理知識を求められるので、
APレベルではかなり高難易度の問題と思われます。
間違っているかもしれませんので、ご参考までに。
この問題は見えない処理を想定する必要がある、厄介な問題かもしれません。
質問者様の思考は以下と思われます。
１.（1）の処理で月次レポート表に行をINSERTする時に全ての従業員数の行が
INSERTされる。従業員が100人いれば100行INSERTされる。
２．(2）の処理でINSERTしたレコードの更新を行う。
しかし、WHERE文に従業員番号を絞り込むような条件が書かれていないため、
100人全員のSUMが計算されてしまうのではないか？
そのため、GROUP BY句で従業員毎のSUMを計算する必要があるのではないか？
と考えているのではないでしょうか？
しかし、このUPDATEで純粋に従業員毎にレコードをUPDATEするには、
暗黙の処理が必要になります。
それは「(2)：(1)で挿入したレコードについて，次の処理を行う。」の
処理の①～⑤を実行するにあたり、更新用カーソルを利用し、1レコードずつ
更新する処理をループで回していると想定する必要があります。
そうでないと全体の処理として整合性がとれないと思われます。
（2）の文章から暗黙的にカーソル処理をしていることを読み取れという
出題者の意図かもしれません。
そうであるならば、実務レベルでの処理知識を求められるので、
APレベルではかなり高難易度の問題と思われます。
2023.03.03 14:20
初投稿さん(No.4)
GinSana様
早速のご回答ありがとうございます。
SQLの実行順序並びに、
以下は全て理解できました。
誠にありがとうございます。
もう少しで、すごくスッキリできそうなのですが、
このUPDATE文は月次レポートエンティティの（全体nレコードあるうちの）1レコードずつ、副問い合わせの中で処理されて月間総歩数が算出されているイメージで合っているでしょうか？
これまで、自分はなんとなく月次エンティティと歩数エンティティを結合した状態で集計関数で総歩数を出すイメージを持ってしまっていましたが、そこが間違っている気がしています。
お手数おかけしますが、よろしくお願いします。
早速のご回答ありがとうございます。
SQLの実行順序並びに、
>その中でグループバイをしたい、ということになるが、実際...
以下は全て理解できました。
誠にありがとうございます。
もう少しで、すごくスッキリできそうなのですが、
このUPDATE文は月次レポートエンティティの（全体nレコードあるうちの）1レコードずつ、副問い合わせの中で処理されて月間総歩数が算出されているイメージで合っているでしょうか？
これまで、自分はなんとなく月次エンティティと歩数エンティティを結合した状態で集計関数で総歩数を出すイメージを持ってしまっていましたが、そこが間違っている気がしています。
お手数おかけしますが、よろしくお願いします。
2023.03.03 14:46
初投稿さん(No.5)
pix様
ご返信ありがとうございます。
こちらについてですが、
設問2.gで
月次レポート.従業員番号 = 歩数.従業員番号
従業員番号を条件指定していると思うのですが、こちらは、
には含まれないのでしょうか。
この問題は、問題文からUPDATEがループで回されてることを想定するべき問題（UPDATE文は従業員の数ぶん流される）なのか、図3の一つのSQLで対象月の全ての従業員の総歩数が出るのかまだ判断しかねています。
お手数おかけしますが、よろしくお願いします。
ご返信ありがとうございます。
>しかし、WHERE文に従業員番号を絞り込むような条件が書かれていないため、
こちらについてですが、
設問2.gで
月次レポート.従業員番号 = 歩数.従業員番号
従業員番号を条件指定していると思うのですが、こちらは、
>従業員番号を絞り込むような条件
には含まれないのでしょうか。
この問題は、問題文からUPDATEがループで回されてることを想定するべき問題（UPDATE文は従業員の数ぶん流される）なのか、図3の一つのSQLで対象月の全ての従業員の総歩数が出るのかまだ判断しかねています。
お手数おかけしますが、よろしくお願いします。
2023.03.03 14:56
GinSanaさん(No.6)
★AP プラチナマイスター
この投稿は投稿者により削除されました。(2023.03.03 18:24)
2023.03.03 18:24
pixさん(No.7)
★AP シルバーマイスター
>初投稿さん
>設問2.gで
>月次レポート.従業員番号 = 歩数.従業員番号
>従業員番号を条件指定していると思うのですが、こちらは、
>>従業員番号を絞り込むような条件
>には含まれないのでしょうか。
WHERE句内に記述できるものの認識があやふやのようです。
SQLでテーブルを結合する方法は2種類あります
・WHERE句内
・FROM句内のJOIN
・WHERE句でテーブルのカラム同士を'='でつなぐことによって
2つのテーブルを結合することができます
この方法は古い時代からの結合方法になります。
この記述方法は簡単なので、現在でもよく使用されます。
問題として、WHERE句には
・テーブル結合
・行の絞り込み
の２種類の性質の全く異なる式が混在してしまいます。
SQL学習の初期段階で、WHERE句が単なる行の絞り込みと考えていると
混乱する原因になります。
・FROM句内のJOIN
新しいテーブル結合の記述方法です。
FROM句内のテーブル名にJOINを指定して複数のテーブルを結合します。
WHERE句ではなくFROM句にテーブル結合条件を書くことにより、テーブルを
結合していることを明示的に示したい場合は有用です。
欠点として、WHERE句による結合よりもややマイナーな位置づけなことです。
SQLはいままで長い年月の間に何度も仕様変更されており、今となっては
わかりずらい点が多々あります。
特にSQLの歴史の中でoracleがメジャーになった際に、oracleでSQLを覚えた古い
人達のSQLの書き方がいまでも残っております。
そのため、今でも古い昔ながらのSQLが普通に使われてるといった背景もあります。
2023.03.03 18:14
GinSanaさん(No.8)
★AP プラチナマイスター
この投稿は投稿者により削除されました。(2023.03.03 18:48)
2023.03.03 18:48
GinSanaさん(No.9)
★AP プラチナマイスター
>この問題は、問題文からUPDATEがループで回されてることを想定するべき問題（UPDATE文は従業員の数ぶん流される）なのか、図3の一つのSQLで対象月の全ての従業員の総歩数が出るのかまだ判断しかねています。
物事をシンプルにするために
アップデート テーブル1
SET カラム2 = (
セレクト カラム1 FROM テーブル2
WHERE テーブル1.カラム1 = テーブル2.カラム1
);
を考える。SET カラム2 = (
セレクト カラム1 FROM テーブル2
WHERE テーブル1.カラム1 = テーブル2.カラム1
);
上記は
テーブル1のカラム1とテーブル2のカラム1が等しいレコードに対して、
テーブル1のカラム2に、テーブル2のカラム1の値を代入する。
これを考えれば、図3の一つのSQLで対象月の全ての従業員の総歩数をそれぞれに更新を一撃でかけていることになる。そういう意味では、
>（2）の文章から暗黙的にカーソル処理をしている
とはならない（バインド変数を使っているから一見そう見えるだけで、シーケンシャルな処理）。
whereの結合はほんとにやめてほしい。特にoracleのプラス演算子で書かれているといらっとする。
2023.03.03 18:48
pixさん(No.10)
★AP シルバーマイスター
>GinSanaさん
・SUMで複数行を1行に集約する
・そのためには、暗黙の前提としてSUM前の全ての行の従業員番号が
同じでなくてはならない
・その状態を作るにはカーソルで月次レポ－トテーブルを1行づつ
処理する必要がある
と考えました。
月次レポートテーブル 1行に対して、歩数テーブル N行が結合するので
カーソルによる絞り込みが必要と思いました。
2023.03.03 19:06
GinSanaさん(No.11)
★AP プラチナマイスター
>pixさん
>月次レポートテーブル 1行に対して、歩数テーブル N行が結合するので
カーソルによる絞り込みが必要と思いました。
カーソルだと、図3にrowとして取得している従業員番号で絞りを入れているならそうだなあ、となるんですが、そう見えないんですよね。
2023.03.03 19:14
GinSanaさん(No.12)
★AP プラチナマイスター
ちょっと後でsqliteで一括ではいるか実験してみます。
2023.03.03 19:27
pixさん(No.13)
★AP シルバーマイスター
>GinSanaさん
すみません。私が勘違いしていたようです。
GinSanaさんが書かれた説明でいいと思います。
副問い合わせのWHEREで結合したテーブルが外側のUPDATEのテーブルへ作用するSQLです。
これにSUMが絡んだため、複雑に見えてしまったようです。
久々に難易度の高いSQLを見て、勉強させていただきました。
2023.03.03 19:50
初投稿さん(No.14)
>GinSana様(No.9)
問題を切り分け、わかりやすくしていただき、ありがとうございます。
>テーブル1のカラム1とテーブル2のカラム1が等しいレコードに対して、
テーブル1のカラム2に、テーブル2のカラム1の値を代入する。
これを考えれば、図3の一つのSQLで対象月の全ての従業員の総歩数をそれぞれに更新を一撃でかけていることになる。
こちら詳しく書いていただいたおかげで、クリアにこの問題を理解することができました。
また、「カーソル処理か否か」についても勉強を深めることができました。
根気強くご教授いただき本当にありがとうございました。
2023.03.03 20:41
初投稿さん(No.15)
>pix様(No.13)
迅速に、そして詳細に返答くださり、ありがとうございました。
ご指摘いただいたように、まだまだデータベースの知識が曖昧になっている部分が多いなと感じましたので、さらに理解を深められるようこれからも尽力いたします。
個人的にはSQLの歴史について知れたのもとてもおもしろかったです。
また、GinSanaさんとの「カーソル処理か否か」という議論もとても興味深かったです。
この度はたくさんの返答をいただき、誠にありがとうございました。
2023.03.03 20:46