質問編集履歴
8
プライバシーの観点から、一部コードを削除
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
|
-

|
7
6
|
|
8
7
|
2
|
9
|
-
course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)+その他
|
10
|
-
|
8
|
+
Course:大学の講義のコードと講義名を持つ
|
9
|
+
科目コード、コース名
|
11
10
|
|
12
11
|
3
|
13
|
-
|
12
|
+
Section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)+その他
|
14
13
|
***ここではセクション=各学期に開かれる講義と定義します。例えば、「数学1」という科目(Course)が2017年春と2017年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。
|
15
|
-

|
16
14
|
|
17
15
|
4
|
18
|
-
GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade:履修中は空欄)
|
16
|
+
GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade:履修中は空欄)を持つ。
|
19
|
-

|
20
17
|
|
21
18
|
|
22
19
|
以下のようなテーブルを作成したいです。
|
20
|
+
|学部名|科目コード|科目名|A|B|C|履修生徒数
|
21
|
+
|:--|:--:|--:|
|
23
|
-
|
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
|
30
|
+
0. Dept Name, A, B, C, D, F, Course Numberの順でソートします(A, B, C はDesc、それ以外はAcsです)。
|
29
31
|
###発生している問題・エラーメッセージ
|
30
32
|
|
31
|
-
自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、
|
33
|
+
自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、PIVOTがうまくいかず、Gradeの値とその合計がとれずすべて0になってしまいます。
|
32
|
-

|
33
34
|
|
34
35
|
今の私のコードの問題として以下があると思っているのですが、それをどうやって解決すればよいのかわかりません。。。
|
35
36
|
0. Inline selectの中のSQLではGradeのデータが取れているのに、Pivotではなぜかカウントされない。
|
36
37
|
0. 一番右のカラム"TotalEnroll"がPivotが分類する要素の一つになっていて合計をとるようになってない。
|
37
38
|
|
38
|
-
Inline selectの中のコードは今のSQLで以下のような結果を返しています(これであっている、と思います。。。)。
|
39
|
-

|
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
プライバシーの観点から、「該当のソースコードを一部削除」
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
|
-
|
68
|
+
GRADE_REPORT gr2
|
80
|
-
join
|
69
|
+
join SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
|
81
|
-
join
|
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
「試したこと」を修正
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
|
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
|
123
|
+
00918 00000 - "column ambiguously defined"
|
124
124
|
*Cause:
|
125
125
|
*Action:
|
126
126
|
Error at Line: 4 Column: 5
|
5
ソースを一部修正
title
CHANGED
File without changes
|
body
CHANGED
@@ -2,20 +2,20 @@
|
|
2
2
|
以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
|
3
3
|
|
4
4
|
1
|
5
|
-
|
5
|
+
DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
|
6
6
|

|
7
7
|
|
8
8
|
2
|
9
|
-
|
9
|
+
course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)+その他
|
10
10
|

|
11
11
|
|
12
12
|
3
|
13
|
-
|
13
|
+
section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)+その他
|
14
14
|
***ここではセクション=各学期に開かれる講義と定義します。例えば、「数学1」という科目(Course)が2017年春と2017年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。
|
15
15
|

|
16
16
|
|
17
17
|
4
|
18
|
-
|
18
|
+
GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade:履修中は空欄)
|
19
19
|

|
20
20
|
|
21
21
|
|
4
プライバシーの観点から、一部内容を削除
title
CHANGED
File without changes
|
body
CHANGED
@@ -1,8 +1,6 @@
|
|
1
1
|
###前提・実現したいこと
|
2
|
-
以下の4つのテーブルがあります(画像では一部を表示しています。
|
2
|
+
以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
|
3
|
-
)。
|
4
3
|
|
5
|
-
|
6
4
|
1
|
7
5
|
eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
|
8
6
|

|
3
試したことを追加。タイトル変更。
title
CHANGED
@@ -1,1 +1,1 @@
|
|
1
|
-
OracleSQL
|
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
データファイルのリンク追加
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
|

|
1
データファイルのリンクを追加(実験)
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
|

|