トップに関する質問 OracleSQLのPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計をカラムの一番右に追加したい。

編集履歴

質問編集履歴

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

2017/10/22 08:00

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,6 +1,6 @@
 ###前提・実現したいこと
-以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
+以下の4つのテーブルがあります。
@@ -8,33 +8,27 @@
 DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
-![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)
 2
-course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)＋その他
+Course：大学の講義のコードと講義名を持つ
-![イメージ説明](aede456b8c2145a7f1dc3c49a05dab1f.png)
+科目コード、コース名
 3
-section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
+Section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
 ***ここではセクション＝各学期に開かれる講義と定義します。例えば、「数学１」という科目(Course)が２０１７年春と２０１７年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。
-![イメージ説明](dc850902a5de834bc2517b804c5bc494.png)
 4
-GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)
+GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)を持つ。
-![イメージ説明](1b48b809e8d77150dedc8e7d0526b187.png)
@@ -42,7 +36,17 @@
 以下のようなテーブルを作成したいです。
+|学部名|科目コード|科目名|A|B|C|履修生徒数
+|:--|:--:|--:|
-![イメージ説明](3b625958e95874d22f4675e0c7e0f4b7.png)
+|chemistry|001|organic chemistry|3|5|5|16|
+|biology|002|genetics|2|1|0|10|
+|physics|003|quantum physics|0|3|3|6|
 科目をすべて列挙して、その科目を取った学生のGradeをGrade別にカウントして、さらにその合計を一番右に表示したいです。ただし、以下のような条件があります。
@@ -52,15 +56,13 @@
 0. もし、Grade Dをとった生徒の数またはGrade Fをとった生徒の数がGrade Cをとった生徒の数より多かった場合は、その科目を表示しません。
-0. Dept Name, A, B, C, D, F, Course Numberの順でソートします(A, B, C, D, FはDesc、それ以外はAcsです)。
+0. Dept Name, A, B, C, D, F, Course Numberの順でソートします(A, B, C はDesc、それ以外はAcsです)。
 ###発生している問題・エラーメッセージ
-自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、ごらんのとおりPIVOTがうまくいかず、Gradeの値とその合計がとれていません。
+自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、PIVOTがうまくいかず、Gradeの値とその合計がとれずすべて０になってしまいます。
-![イメージ説明](4fe5253793307c20266f5af605509800.png)
@@ -69,114 +71,6 @@
 0. Inline selectの中のSQLではGradeのデータが取れているのに、Pivotではなぜかカウントされない。
 0. 一番右のカラム"TotalEnroll"がPivotが分類する要素の一つになっていて合計をとるようになってない。
-Inline selectの中のコードは今のSQLで以下のような結果を返しています(これであっている、と思います。。。)。
-![イメージ説明](3f49422cad337ee935e3676302df4dc1.png)
-###該当のソースコード
-「試したこと」の具体的内容として、コメントはあえてすべて残していましたがプライバシーの観点から一部削除しました。ご了承ください。
-select
-    *
-from (
-    select
-         dptm.dname as "Dept Name"
-         ,co.course_number as "Course Num"
-         ,co.course_name as "Course Name"
-         ,co.course_name as "Course Name for grade"
---         ,"NumOfStEachCo"."numOfStEachCo" as "numOfStEachCo"
-         ,gr.grade as "grade"
---         ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnrollEachCourse"
-    from
-        eb.course co
-            join EB.DEPARTMENT_TO_MAJOR dptm on (co.OFFERING_DEPT = dptm.DCODE)
-            join eb.section se on (se.COURSE_NUM = co.COURSE_NUMBER)
-            join EB.GRADE_REPORT gr on (gr.SECTION_ID = se.SECTION_ID)
---            return the number     of enrollments for each course
-            left outer join (
-                select
-                    co2.COURSE_NUMBER
-    --                "If a student enrolls in two courses (or in the same course twice), do not count the student twice.". So not add distinct
-                    ,count(co2.COURSE_NUMBER) as "numOfStEachCo"
-                from
-                    GRADE_REPORT gr2
-                        join SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
-                        join COURSE co2 on (se2.COURSE_NUM = co2.COURSE_NUMBER)
-                where 1=1
-                group by
-                    co2.COURSE_NUMBER
-            ) "NumOfStEachCo" on ("NumOfStEachCo".COURSE_NUMBER = co.COURSE_NUMBER)
-)
-pivot(count("grade") for "Course Name for grade"
-        in ('A' as A ,'B' as B,'C' as C,'D' as D,'F' as F, 'TotalEnroll' as "TotalEnroll")
---        in ('A','B','C','D','F', 'TotalEnroll' as "TotalEnrollEachCourse")
-)
-order by
-    "Dept Name" asc
-    ,A desc
-    ,B desc
-    ,C desc
-    ,D desc
-    ,F desc
-    ,"Course Num"
-;

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

2017/10/22 08:00

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -82,7 +82,7 @@
 ###該当のソースコード
-「試したこと」の具体的内容として、コメントはあえてすべて残しています。醜いかもしれませんが、ご理解いただければと思います。
+「試したこと」の具体的内容として、コメントはあえてすべて残していましたがプライバシーの観点から一部削除しました。ご了承ください。
@@ -90,30 +90,10 @@
     *
---     "NumOfEnrollEachCo"."Dept Name" as "Dept Name"
---     ,"NumOfEnrollEachCo"."Course Num" as "Course Num"
---     ,"NumOfEnrollEachCo"."Course Name" as "Course Name"
---     ,"NumOfEnrollEachCo"."Course Name for grade" as "Course Name for grade"
-----         ,"NumOfStEachCo"."numOfStEachCo" as "numOfStEachCo"
---     ,"NumOfEnrollEachCo"."grade" as "grade"
---     ,"NumOfEnrollEachCo"."TotalEnrollEachCourse" as "TotalEnrollEachCourse"
 from (
     select
-    --     dptm.dname as "Dept Name "
-    --     ,co.course_number as "Course Num"
-    --     ,co.course_name as "Course Name"
          dptm.dname as "Dept Name"
          ,co.course_number as "Course Num"
@@ -130,8 +110,6 @@
     from
-    --    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
         eb.course co
             join EB.DEPARTMENT_TO_MAJOR dptm on (co.OFFERING_DEPT = dptm.DCODE)
@@ -154,11 +132,11 @@
                 from
-                    EB.GRADE_REPORT gr2
+                    GRADE_REPORT gr2
-                        join EB.SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
+                        join SECTION se2 on (gr2.SECTION_ID = se2.SECTION_ID)
-                        join EB.COURSE co2 on (se2.COURSE_NUM = co2.COURSE_NUMBER)
+                        join COURSE co2 on (se2.COURSE_NUM = co2.COURSE_NUMBER)
                 where 1=1

「試したこと」を修正

2017/10/21 18:23

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -218,7 +218,7 @@
 ORA-00904: "NumOfEnrollEachCo"."TotalEnrollEachCourse": invalid identifier
-00904. 00000 -  "%s: invalid identifier"
+00904 00000 -  "%s: invalid identifier"
 *Cause:
@@ -242,7 +242,7 @@
 ORA-00918: column ambiguously defined
-00918. 00000 -  "column ambiguously defined"
+00918 00000 -  "column ambiguously defined"
 *Cause:

ソースを一部修正

2017/10/21 18:20

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -6,7 +6,7 @@
 1
-eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
+DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)
 ![イメージ説明](3ad496f27a76d8ef6212558de113f004.png)
@@ -14,7 +14,7 @@
 2
-eb.course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)＋その他
+course: 大学の科目(Course)のコード(COURSE_NUMBER)と名前(COURSE_NAME)と科目の属している学部コード(OFFERING_DEPT)＋その他
 ![イメージ説明](aede456b8c2145a7f1dc3c49a05dab1f.png)
@@ -22,7 +22,7 @@
 3
-eb.section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
+section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)＋その他
 ***ここではセクション＝各学期に開かれる講義と定義します。例えば、「数学１」という科目(Course)が２０１７年春と２０１７年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。
@@ -32,7 +32,7 @@
 4
-eb.GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)
+GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade：履修中は空欄)
 ![イメージ説明](1b48b809e8d77150dedc8e7d0526b187.png)

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

2017/10/21 02:50

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,10 +1,6 @@
 ###前提・実現したいこと
-以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはこちらのリンクにまとめています：　https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
+以下の4つのテーブルがあります(画像では一部を表示しています。すべてのデータを共有したいのですが、プライバシーの都合上できません。ご必要の場合は直接連絡いただければと思います)。
-)。

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

2017/10/21 02:49

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

	@@ -1 +1 @@
1	- OracleSQLでPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計を列に追加したい。
1	+ OracleSQLのPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計をカラムの一番右に追加したい。

test CHANGED Viewed

@@ -232,6 +232,32 @@
+0. Inline selectですでに取得したGradeの合計値を
+         ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnrollEachCourse"
+と書いて外のSQLでも使えるようにすると、そのカラムが”A”カラムの左に来てしまう。
+0. Inline selectですでに取得したGradeの合計値を
+         ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnroll"
+と書いて外のSQLでも使えるようにすると、以下のエラーが発生する。
+ORA-00918: column ambiguously defined
+00918. 00000 -  "column ambiguously defined"
+*Cause:
+*Action:
+Error at Line: 4 Column: 5
 ###補足情報(言語/FW/ツール等のバージョンなど)
 Oracle 12c を使ってOracle Developer上で動かしています。

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

2017/10/20 10:53

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,8 +1,10 @@
 ###前提・実現したいこと
-以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはExcelFile"dataForTeratail.xlsx"にまとめているのですが、ファイルを添付する方法がわかりません。。。今探しています)。
-https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
+以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはこちらのリンクにまとめています：　https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
+)。

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

2017/10/20 09:57

投稿

Ryuichi_Yamamot

スコア11

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -2,6 +2,10 @@
 以下の4つのテーブルがあります(画像では一部を表示しています。推定しているすべてのデータはExcelFile"dataForTeratail.xlsx"にまとめているのですが、ファイルを添付する方法がわかりません。。。今探しています)。
+https://drive.google.com/file/d/0B1ahJBHhcbDLWVlmQzRFcnlZZVk/view?usp=sharing
 1
 eb.DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)