teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップOracle Database 11gに関する質問集約関数と分析関数の併用はできる？

編集履歴

回答編集履歴

16

修正

2017/08/15 04:55

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -1,6 +1,6 @@
1
1
  質問されている、希望する出力結果のうちPARENT_NO=6という値が、TABLE_Aのどこをどうしたら出力されるのかよく分かりません。
2
2
 
3
- (ITEM_ID)ごとのQTYの計と(ITEM_ID,PARENT_NO)ごとのQTYの計を一覧にしたものとして回答すると以下の様になるかと。
3
+ (PARENT_NO)ごとのQTYの計と(ITEM_ID,PARENT_NO)ごとのQTYの計を一覧にしたものとして回答すると以下の様になるかと。
4
4
 
5
5
  ```SQL
6
6
  with iv_sub as (

15

訂正

2017/08/15 04:55

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -9,7 +9,7 @@
9
9
  group by ITEM_ID,PARENT_NO
10
10
  )
11
11
  select *
12
- ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.ITEM_ID) as total
12
+ ,(select sum(subtotal) from iv_sub where PARENT_NO=sub.PARENT_NO) as total
13
13
  from iv_sub as sub
14
14
  ```
15
15
  質問修正後追加その１
@@ -32,7 +32,7 @@
32
32
  分析関数を使うとしたら、
33
33
  ```SQL
34
34
  select ITEM_ID, PARENT_NO, subtotal
35
- , sum(subtotal) over(partition by ITEM_ID) as total
35
+ , sum(subtotal) over(partition by PARENT_NO) as total
36
36
  from (
37
37
  select ITEM_ID, PARENT_NO, sum(QTY) as subtotal
38
38
  from TABLE_A

14

訂正

2017/08/15 04:44

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -9,7 +9,7 @@
9
9
  group by ITEM_ID,PARENT_NO
10
10
  )
11
11
  select *
12
- ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.=ITEM_ID) as total
12
+ ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.ITEM_ID) as total
13
13
  from iv_sub as sub
14
14
  ```
15
15
  質問修正後追加その１

13

推敲

2017/08/15 04:38

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -12,7 +12,8 @@
12
12
  ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.=ITEM_ID) as total
13
13
  from iv_sub as sub
14
14
  ```
15
- ---質問修正後追加その１
15
+ 質問修正後追加その１
16
+ ---
16
17
  TABLE_Aの行毎に、その行のPARENT_NOと同じデータの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。
17
18
  ```SQL
18
19
  select ITEM_ID
@@ -25,7 +26,8 @@
25
26
  ) as caluc
26
27
  group by ITEM_ID, parent_total
27
28
  ```
28
- ------質問修正後追加その２
29
+ 質問修正後追加その２
30
+ ---
29
31
  修正後の結果であれば、最初に書いたSQLでも良さそう。
30
32
  分析関数を使うとしたら、
31
33
  ```SQL

12

修正

2017/08/14 16:40

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -26,7 +26,7 @@
26
26
  group by ITEM_ID, parent_total
27
27
  ```
28
28
  ------質問修正後追加その２
29
- 修正後の結果であれば、最初に書いたSQLで良さそう。
29
+ 修正後の結果であれば、最初に書いたSQLでも良さそう。
30
30
  分析関数を使うとしたら、
31
31
  ```SQL
32
32
  select ITEM_ID, PARENT_NO, subtotal

11

修正

2017/08/14 16:38

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -29,14 +29,12 @@
29
29
  修正後の結果であれば、最初に書いたSQLで良さそう。
30
30
  分析関数を使うとしたら、
31
31
  ```SQL
32
- with iv_sub as (
32
+ select ITEM_ID, PARENT_NO, subtotal
33
+ , sum(subtotal) over(partition by ITEM_ID) as total
34
+ from (
33
35
  select ITEM_ID, PARENT_NO, sum(QTY) as subtotal
34
36
  from TABLE_A
35
37
  group by ITEM_ID,PARENT_NO
36
- )
37
- select ITEM_ID, PARENT_NO, subtotal
38
- , sum(subtotal) over(partition by ITEM_ID) as total
39
- from iv_sub
38
+ ) as sub
40
39
  ```
41
- になるかな。
40
+ になるかな。
42
- CTEを使用しているので、コストはあまり変わらないと予想。

10

修正

2017/08/14 16:35

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -26,4 +26,17 @@
26
26
  group by ITEM_ID, parent_total
27
27
  ```
28
28
  ------質問修正後追加その２
29
- 修正後の結果であれば、最初に書いたSQLで良さそう。
29
+ 修正後の結果であれば、最初に書いたSQLで良さそう。
30
+ 分析関数を使うとしたら、
31
+ ```SQL
32
+ with iv_sub as (
33
+ select ITEM_ID, PARENT_NO, sum(QTY) as subtotal
34
+ from TABLE_A
35
+ group by ITEM_ID,PARENT_NO
36
+ )
37
+ select ITEM_ID, PARENT_NO, subtotal
38
+ , sum(subtotal) over(partition by ITEM_ID) as total
39
+ from iv_sub
40
+ ```
41
+ になるかな。
42
+ CTEを使用しているので、コストはあまり変わらないと予想。

9

修正

2017/08/14 16:30

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -12,7 +12,7 @@
12
12
  ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.=ITEM_ID) as total
13
13
  from iv_sub as sub
14
14
  ```
15
- ---質問修正後追加
15
+ ---質問修正後追加その１
16
16
  TABLE_Aの行毎に、その行のPARENT_NOと同じデータの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。
17
17
  ```SQL
18
18
  select ITEM_ID
@@ -24,4 +24,6 @@
24
24
  from TABLE_A as item
25
25
  ) as caluc
26
26
  group by ITEM_ID, parent_total
27
- ```
27
+ ```
28
+ ------質問修正後追加その２
29
+ 修正後の結果であれば、最初に書いたSQLで良さそう。

8

修正

2017/08/14 16:16

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -24,7 +24,4 @@
24
24
  from TABLE_A as item
25
25
  ) as caluc
26
26
  group by ITEM_ID, parent_total
27
- ```
27
+ ```
28
- 上記が正しいなら、希望の結果は結合条件（相関問い合わせでの条件）なしには行えないので、分析関数は目的には合わないと思われます。
29
-
30
- ※sum()でover(PRTITION BY)を使用したい場合、通常はselect 項目にもPRTITIONする項目があるはずで、DISTINCTで重複が省略できたとしても、それはデータがたまたまそうだっただけではないでしょうか。

7

修正

2017/08/14 15:52

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -27,5 +27,4 @@
27
27
  ```
28
28
  上記が正しいなら、希望の結果は結合条件（相関問い合わせでの条件）なしには行えないので、分析関数は目的には合わないと思われます。
29
29
 
30
- ※sum()でover(PRTITION BY)を使用しgroup byを省略したいなら、select 項目にもPRTITIONする項目が無いと意味は無いものと思います。
30
+ ※sum()でover(PRTITION BY)を使用したい場合、通常はselect 項目にもPRTITIONする項目があるはずで、DISTINCTで重複が省略できたとしても、それはデータがたまたまそうだっただけではないでしょうか。
31
- DISTINCTで重複が省略できたとしても、それはデータがたまたまそうだっただけのことだと思われます。

6

修正

2017/08/14 15:40

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -25,4 +25,7 @@
25
25
  ) as caluc
26
26
  group by ITEM_ID, parent_total
27
27
  ```
28
- 上記が正しいなら、希望の結果は結合条件（相関問い合わせでの条件）なしには行えないので、分析関数は目的には合わないと思われます。
28
+ 上記が正しいなら、希望の結果は結合条件（相関問い合わせでの条件）なしには行えないので、分析関数は目的には合わないと思われます。
29
+
30
+ ※sum()でover(PRTITION BY)を使用しgroup byを省略したいなら、select 項目にもPRTITIONする項目が無いと意味は無いものと思います。
31
+ DISTINCTで重複が省略できたとしても、それはデータがたまたまそうだっただけのことだと思われます。

5

修正

2017/08/14 15:29

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -25,4 +25,4 @@
25
25
  ) as caluc
26
26
  group by ITEM_ID, parent_total
27
27
  ```
28
- 上記が正しいなら、希望の結果は結合条件なしには行えないので、分析関数は目的には合わないと思われます。
28
+ 上記が正しいなら、希望の結果は結合条件（相関問い合わせでの条件）なしには行えないので、分析関数は目的には合わないと思われます。

4

修正

2017/08/14 15:09

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -24,4 +24,5 @@
24
24
  from TABLE_A as item
25
25
  ) as caluc
26
26
  group by ITEM_ID, parent_total
27
- ```
27
+ ```
28
+ 上記が正しいなら、希望の結果は結合条件なしには行えないので、分析関数は目的には合わないと思われます。

3

修正

2017/08/14 15:04

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -13,7 +13,7 @@
13
13
  from iv_sub as sub
14
14
  ```
15
15
  ---質問修正後追加
16
- TABLE_Aの行毎に、その行のPARENT_NOの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。
16
+ TABLE_Aの行毎に、その行のPARENT_NOと同じデータの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。
17
17
  ```SQL
18
18
  select ITEM_ID
19
19
  , sum(QTY) as item_total

2

追記

2017/08/14 15:01

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -11,4 +11,17 @@
11
11
  select *
12
12
  ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.=ITEM_ID) as total
13
13
  from iv_sub as sub
14
+ ```
15
+ ---質問修正後追加
16
+ TABLE_Aの行毎に、その行のPARENT_NOの集計をサブクエリーで取り、そのサブクエリーをITEM_IDとPARENT_NOの集計でグルーピングし、QTYの集計を取る。
17
+ ```SQL
18
+ select ITEM_ID
19
+ , sum(QTY) as item_total
20
+ , parent_total
21
+ from (
22
+ select ITEM_ID, QTY
23
+ , (select sum(QTY) from TABLE_A where PARENT_NO=item.PARENT_NO) as parent_total
24
+ from TABLE_A as item
25
+ ) as caluc
26
+ group by ITEM_ID, parent_total
14
27
  ```

1

推敲

2017/08/14 14:59

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -11,5 +11,4 @@
11
11
  select *
12
12
  ,(select sum(subtotal) from iv_sub where ITEM_ID=sub.=ITEM_ID) as total
13
13
  from iv_sub as sub
14
- コード
15
14
  ```