teratail
回答率
85.30%
質問するログイン新規登録
トップに関する質問OracleSQLのPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計をカラムの一番右に追加したい。

編集履歴

質問編集履歴

8

プライバシーの観点から、一部コードを削除

2017/10/22 08:00

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -1,94 +1,41 @@
1
1
  ###前提・実現したいこと
2
- 以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
2
+ 以下の4つのテーブルがあります。
3
3
 
4
4
  1
5
5
  DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
6
- ![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)
7
6
 
8
7
  2
9
- course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)＋その他
10
- ![イメージ説明](aede456b8c2145a7f1dc3c49a05dab1f.png)
8
+ Course：大学の講義のコードと講義名を持つ
9
+ 科目コード、コース名
11
10
 
12
11
  3
13
- section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
12
+ Section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
14
13
  ***ここではセクション＝各学期に開かれる講義と定義します。例えば、「数学１」という科目(Course)が２０１７年春と２０１７年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。
15
- ![イメージ説明](dc850902a5de834bc2517b804c5bc494.png)
16
14
 
17
15
  4
18
- GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)
16
+ GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)を持つ。
19
- ![イメージ説明](1b48b809e8d77150dedc8e7d0526b187.png)
20
17
 
21
18
 
22
19
  以下のようなテーブルを作成したいです。
20
+ |学部名|科目コード|科目名|A|B|C|履修生徒数
21
+ |:--|:--:|--:|
23
- ![イメージ説明](3b625958e95874d22f4675e0c7e0f4b7.png)
22
+ |chemistry|001|organic chemistry|3|5|5|16|
23
+ |biology|002|genetics|2|1|0|10|
24
+ |physics|003|quantum physics|0|3|3|6|
25
+
24
26
  科目をすべて列挙して、その科目を取った学生のGradeをGrade別にカウントして、さらにその合計を一番右に表示したいです。ただし、以下のような条件があります。
25
27
  0. まだどの学生も、その科目のGradeを持ってない場合は、その科目は表示しません。
26
28
  0. 一人の生徒は同じ科目を何度も受けられるとします(別セクションで)。その際、Gradeはそれぞれカウントします(e.g. CとBをとったらそれぞれ数えます)。
27
29
  0. もし、Grade Dをとった生徒の数またはGrade Fをとった生徒の数がGrade Cをとった生徒の数より多かった場合は、その科目を表示しません。
28
- 0. Dept Name, A, B, C, D, F, Course Numberの順でソートします(A, B, C, D, FはDesc、それ以外はAcsです)。
30
+ 0. Dept Name, A, B, C, D, F, Course Numberの順でソートします(A, B, C はDesc、それ以外はAcsです)。
29
31
  ###発生している問題・エラーメッセージ
30
32
 
31
- 自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、ごらんのとおりPIVOTがうまくいかず、Gradeの値とその合計がとれていません。
33
+ 自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、PIVOTがうまくいかず、Gradeの値とその合計がとれずすべて０になってしまいます。
32
- ![イメージ説明](4fe5253793307c20266f5af605509800.png)
33
34
 
34
35
  今の私のコードの問題として以下があると思っているのですが、それをどうやって解決すればよいのかわかりません。。。
35
36
  0. Inline selectの中のSQLではGradeのデータが取れているのに、Pivotではなぜかカウントされない。
36
37
  0. 一番右のカラム"TotalEnroll"がPivotが分類する要素の一つになっていて合計をとるようになってない。
37
38
 
38
- Inline selectの中のコードは今のSQLで以下のような結果を返しています(これであっている、と思います。。。)。
39
- ![イメージ説明](3f49422cad337ee935e3676302df4dc1.png)
40
-
41
-
42
- ###該当のソースコード
43
- 「試したこと」の具体的内容として、コメントはあえてすべて残していましたがプライバシーの観点から一部削除しました。ご了承ください。
44
-
45
- select
46
- *
47
- from (
48
- select
49
- dptm.dname as "Dept Name"
50
- ,co.course_number as "Course Num"
51
- ,co.course_name as "Course Name"
52
- ,co.course_name as "Course Name for grade"
53
- -- ,"NumOfStEachCo"."numOfStEachCo" as "numOfStEachCo"
54
- ,gr.grade as "grade"
55
- -- ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnrollEachCourse"
56
- from
57
- eb.course co
58
- join EB.DEPARTMENT_TO_MAJOR dptm on (co.OFFERING_DEPT = dptm.DCODE)
59
- join eb.section se on (se.COURSE_NUM = co.COURSE_NUMBER)
60
- join EB.GRADE_REPORT gr on (gr.SECTION_ID = se.SECTION_ID)
61
- -- return the number of enrollments for each course
62
- left outer join (
63
- select
64
- co2.COURSE_NUMBER
65
- -- "If a student enrolls in two courses (or in the same course twice), do not count the student twice.". So not add distinct
66
- ,count(co2.COURSE_NUMBER) as "numOfStEachCo"
67
- from
68
- GRADE_REPORT gr2
69
- join SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
70
- join COURSE co2 on (se2.COURSE_NUM = co2.COURSE_NUMBER)
71
- where 1=1
72
- group by
73
- co2.COURSE_NUMBER
74
- ) "NumOfStEachCo" on ("NumOfStEachCo".COURSE_NUMBER = co.COURSE_NUMBER)
75
-
76
- )
77
- pivot(count("grade") for "Course Name for grade"
78
- in ('A' as A ,'B' as B,'C' as C,'D' as D,'F' as F, 'TotalEnroll' as "TotalEnroll")
79
- -- in ('A','B','C','D','F', 'TotalEnroll' as "TotalEnrollEachCourse")
80
- )
81
- order by
82
- "Dept Name" asc
83
- ,A desc
84
- ,B desc
85
- ,C desc
86
- ,D desc
87
- ,F desc
88
- ,"Course Num"
89
- ;
90
-
91
-
92
39
  ###試したこと
93
40
  0. Pivot関数の中やその前のSelect句やInlineの別名をいろいろ変えてみる。
94
41
  0. 下記のページを参考にいろいろ試してみる。でもうまくいきませんでした。

7

プライバシーの観点から、「該当のソースコードを一部削除」

2017/10/22 08:00

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -40,22 +40,12 @@
40
40
 
41
41
 
42
42
  ###該当のソースコード
43
- 「試したこと」の具体的内容として、コメントはあえてすべて残しています。醜いかもしれませんが、ご理解いただければと思います。
43
+ 「試したこと」の具体的内容として、コメントはあえてすべて残していましたがプライバシーの観点から一部削除しました。ご了承ください。
44
44
 
45
45
  select
46
46
  *
47
- -- "NumOfEnrollEachCo"."Dept Name" as "Dept Name"
48
- -- ,"NumOfEnrollEachCo"."Course Num" as "Course Num"
49
- -- ,"NumOfEnrollEachCo"."Course Name" as "Course Name"
50
- -- ,"NumOfEnrollEachCo"."Course Name for grade" as "Course Name for grade"
51
- ---- ,"NumOfStEachCo"."numOfStEachCo" as "numOfStEachCo"
52
- -- ,"NumOfEnrollEachCo"."grade" as "grade"
53
- -- ,"NumOfEnrollEachCo"."TotalEnrollEachCourse" as "TotalEnrollEachCourse"
54
47
  from (
55
48
  select
56
- -- dptm.dname as "Dept Name "
57
- -- ,co.course_number as "Course Num"
58
- -- ,co.course_name as "Course Name"
59
49
  dptm.dname as "Dept Name"
60
50
  ,co.course_number as "Course Num"
61
51
  ,co.course_name as "Course Name"
@@ -64,7 +54,6 @@
64
54
  ,gr.grade as "grade"
65
55
  -- ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnrollEachCourse"
66
56
  from
67
- -- Do not display courses where NO grades exist (i.e., no one has received an A, B, C, D, or F). So, not add left outer join
68
57
  eb.course co
69
58
  join EB.DEPARTMENT_TO_MAJOR dptm on (co.OFFERING_DEPT = dptm.DCODE)
70
59
  join eb.section se on (se.COURSE_NUM = co.COURSE_NUMBER)
@@ -76,9 +65,9 @@
76
65
  -- "If a student enrolls in two courses (or in the same course twice), do not count the student twice.". So not add distinct
77
66
  ,count(co2.COURSE_NUMBER) as "numOfStEachCo"
78
67
  from
79
- EB.GRADE_REPORT gr2
68
+ GRADE_REPORT gr2
80
- join EB.SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
69
+ join SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
81
- join EB.COURSE co2 on (se2.COURSE_NUM = co2.COURSE_NUMBER)
70
+ join COURSE co2 on (se2.COURSE_NUM = co2.COURSE_NUMBER)
82
71
  where 1=1
83
72
  group by
84
73
  co2.COURSE_NUMBER

6

「試したこと」を修正

2017/10/21 18:23

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -108,7 +108,7 @@
108
108
 
109
109
  0. 一番そとのSelect句で、具体的な列名を指定すると以下のエラーが出る(アスタリスクなら出ません)。
110
110
  ORA-00904: "NumOfEnrollEachCo"."TotalEnrollEachCourse": invalid identifier
111
- 00904. 00000 - "%s: invalid identifier"
111
+ 00904 00000 - "%s: invalid identifier"
112
112
  *Cause:
113
113
  *Action:
114
114
  Error at Line: 11 Column: 7
@@ -120,7 +120,7 @@
120
120
  ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnroll"
121
121
  と書いて外のSQLでも使えるようにすると、以下のエラーが発生する。
122
122
  ORA-00918: column ambiguously defined
123
- 00918. 00000 - "column ambiguously defined"
123
+ 00918 00000 - "column ambiguously defined"
124
124
  *Cause:
125
125
  *Action:
126
126
  Error at Line: 4 Column: 5

5

ソースを一部修正

2017/10/21 18:20

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -2,20 +2,20 @@
2
2
  以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
3
3
 
4
4
  1
5
- eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
5
+ DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
6
6
  ![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)
7
7
 
8
8
  2
9
- eb.course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)＋その他
9
+ course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)＋その他
10
10
  ![イメージ説明](aede456b8c2145a7f1dc3c49a05dab1f.png)
11
11
 
12
12
  3
13
- eb.section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
13
+ section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
14
14
  ***ここではセクション＝各学期に開かれる講義と定義します。例えば、「数学１」という科目(Course)が２０１７年春と２０１７年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。
15
15
  ![イメージ説明](dc850902a5de834bc2517b804c5bc494.png)
16
16
 
17
17
  4
18
- eb.GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)
18
+ GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)
19
19
  ![イメージ説明](1b48b809e8d77150dedc8e7d0526b187.png)
20
20
 
21
21
 

4

プライバシーの観点から、一部内容を削除

2017/10/21 02:50

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -1,8 +1,6 @@
1
1
  ###前提・実現したいこと
2
- 以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはこちらのリンクにまとめています：　https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
2
+ 以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
3
- )。
4
3
 
5
-
6
4
  1
7
5
  eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
8
6
  ![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)

3

試したことを追加。タイトル変更。

2017/10/21 02:49

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
@@ -1,1 +1,1 @@
1
- OracleSQLでPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計を列に追加したい。
1
+ OracleSQLのPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計をカラムの一番右に追加したい。
body CHANGED
@@ -115,6 +115,19 @@
115
115
  *Action:
116
116
  Error at Line: 11 Column: 7
117
117
 
118
+ 0. Inline selectですでに取得したGradeの合計値を
119
+ ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnrollEachCourse"
120
+ と書いて外のSQLでも使えるようにすると、そのカラムが”A”カラムの左に来てしまう。
121
+ 0. Inline selectですでに取得したGradeの合計値を
122
+ ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnroll"
123
+ と書いて外のSQLでも使えるようにすると、以下のエラーが発生する。
124
+ ORA-00918: column ambiguously defined
125
+ 00918. 00000 - "column ambiguously defined"
126
+ *Cause:
127
+ *Action:
128
+ Error at Line: 4 Column: 5
129
+
130
+
118
131
  ###補足情報(言語/FW/ツール等のバージョンなど)
119
132
  Oracle 12c を使ってOracle Developer上で動かしています。
120
133
  ほかに何か必要な情報があれば、すぐに用意します。

2

データファイルのリンク追加

2017/10/20 10:53

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -1,7 +1,8 @@
1
1
  ###前提・実現したいこと
2
- 以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはExcelFile"dataForTeratail.xlsx"にまとめているのですが、ファイルを添付する方法がわかりません。。。今探しています)。
3
- https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
2
+ 以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはこちらのリンクにまとめています：　https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
3
+ )。
4
4
 
5
+
5
6
  1
6
7
  eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
7
8
  ![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)

1

データファイルのリンクを追加(実験)

2017/10/20 09:57

投稿

Ryuichi_Yamamot
Ryuichi_Yamamot

スコア11

title CHANGED
File without changes
body CHANGED
@@ -1,5 +1,7 @@
1
1
  ###前提・実現したいこと
2
2
  以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはExcelFile"dataForTeratail.xlsx"にまとめているのですが、ファイルを添付する方法がわかりません。。。今探しています)。
3
+ https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
4
+
3
5
  1
4
6
  eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
5
7
  ![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)