C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[10]: レコード値の合計が一定値を超えるまで抽出


(過去ログ 127 を表示中)

[トピック内 16 記事 (1 - 16 表示)]  << 0 >>

■75313 / inTopicNo.1)  レコード値の合計が一定値を超えるまで抽出
  
□投稿者/ モー (1回)-(2015/03/13(Fri) 10:11:29)

分類:[データベース全般] 

データの抽出方法について教えてください。
DBはSQL Server2012です。

下記のような親子関係テーブルがあり、子テーブルはpIDで外部キー関係にあるとします。
pID:cID(テーブル間データの関係)は1:Nの関係です。
(データは一例です。)

・親テーブル
pID | status
1 | 0
2 | 0
3 | 0
  〜

・子テーブル
cID | pID | Number
1 | 1 | 10
2 | 1 | 7
3 | 1 | 8
4 | 2 | 1
5 | 3 | 3
6 | 3 | 8
  〜

上記のうち子テーブルのNumberをcIDの昇順で合計していき一定値(@Max)を超えない
範囲"かつ"同一pIDに連結するcID全てが一定値内にある、pIDデータの集合を
SELECTで(できれば1SQL文で)取得したいのですが、何か良い方法はありますでしょうか?

例) @Max=30の場合、期待値は"pID:1,2"です。


よろしくお願いします。

引用返信 編集キー/
■75315 / inTopicNo.2)  Re[1]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ WebSurfer (524回)-(2015/03/13(Fri) 12:24:04)
マルチポストのようですので他のサイトへのリンクを張っておきます。

https://social.msdn.microsoft.com/Forums/ja-JP/9e7155fb-e70a-4269-bef8-56d414c6870d?forum=sqlserverja

No75313 (モー さん) に返信

ネット初心者の方でしょうか? マルチポストはこういう場所では歓迎されないことはご存知ですか?
引用返信 編集キー/
■75316 / inTopicNo.3)  Re[1]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ 魔界の仮面弁士 (229回)-(2015/03/13(Fri) 12:27:42)
No75313 (モー さん) に返信
> 下記のような親子関係テーブルがあり、子テーブルはpIDで外部キー関係にあるとします。
> (データは一例です。)
今回の条件では、「親」テーブルは一切使われていない気が…。


> 上記のうち子テーブルのNumberをcIDの昇順で合計していき
Number を足していくと

cID|pID|Number┃累積|pID別合計|pID別累積
 1 | 1 |  10  ┃ 10 |    25   |  10
 2 | 1 |   7  ┃ 17 |    25   |  17
 3 | 1 |   8  ┃ 25 |    25   |  25
 4 | 2 |   1  ┃ 26 |     1   |   1
 5 | 3 |   3  ┃ 29 |    11   |   3
 6 | 3 |   8  ┃ 37 |    11   |  11

になりますね。上記の表は

SELECT cID, pID, Number
, SUM(Number) OVER (ORDER BY cID ) 
, SUM(Number) OVER (PARTITION BY pID ) 
, SUM(Number) OVER (PARTITION BY pID ORDER BY cID ) 
FROM 子 ORDER BY cID

で取得できます。


> 例) @Max=30の場合、期待値は"pID:1,2"です。
ということは、こうかな。

WITH AGGREGATE (pID, F1, F2, F3) AS (
  SELECT pID
  , SUM(Number) OVER (ORDER BY cID ) 
  , SUM(Number) OVER (PARTITION BY pID ) 
  , SUM(Number) OVER (PARTITION BY pID ORDER BY cID ) 
  FROM 子
) SELECT pID FROM AGGREGATE
WHERE F1 <= @Max AND F2 = F3

引用返信 編集キー/
■75317 / inTopicNo.4)  Re[1]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ 魔界の仮面弁士 (230回)-(2015/03/13(Fri) 12:32:58)
No75313 (モー さん) に返信
> 例) @Max=30の場合、期待値は"pID:1,2"です。

今回のデータに、
 cID=7、pID=1、Number=1
が追加された場合、どうなって欲しいのでしょうか?

"pID:1,2" となるのか "pID:2" となるのか、あるいはそういうデータは無いという前提なのか…。
引用返信 編集キー/
■75318 / inTopicNo.5)  Re[2]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ ???[ (1回)-(2015/03/13(Fri) 12:37:08)
No75315 (WebSurfer さん) に返信

初心者ではありませんが、マルチポストという言葉は初めて聞きました。(少し急いでいたので、行ったのも初めてです。)

どちらかを削除すれば良いですか?
引用返信 編集キー/
■75319 / inTopicNo.6)  Re[2]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ モー (2回)-(2015/03/13(Fri) 12:43:24)
2015/03/13(Fri) 13:15:06 編集(投稿者)


魔界の仮面弁士 さん

返信ありがとうございます。

先に質問に回答致します。


>今回のデータに、cID=7、pID=1、Number=1
>が追加された場合、どうなって欲しいのでしょうか?

データ的には想定しますが、cID昇順に計算していく方針ですので、
cID=6の時点で@Maxを超えることから、期待値は"pID:1,2"です。

pIDは小さい方から条件内の全てを抽出していきたいです。



> , SUM(Number) OVER (PARTITION BY pID )
, SUM(Number) OVER (PARTITION BY pID ORDER BY cID )

↑こちらORDER BY cIDをつけるか否かで結果が変わるのですね。
勉強になります。GROUP BY しか使用したことがありませんでした。


>関連して質問なのですが、
 Numberが全て"1"の場合(→実質レコード数と同等)、より簡単なSQLに書き換える方法はありますか?
引用返信 編集キー/
■75320 / inTopicNo.7)  Re[3]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ WebSurfer (525回)-(2015/03/13(Fri) 12:52:19)
No75318 (???[ さん) に返信
>
> どちらかを削除すれば良いですか?

今さら削除するのはかえってよろしくないと思います。

すべてのポスト先できちんと最後までフォローして、ある掲示板で解決した
なら、他の掲示板に解決した掲示板の URL を書いて、クローズするようにし
ていただければと思います。
引用返信 編集キー/
■75323 / inTopicNo.8)  Re[3]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ 魔界の仮面弁士 (233回)-(2015/03/13(Fri) 15:31:21)
No75319 (モー さん) に返信
>> , SUM(Number) OVER (PARTITION BY pID )
> , SUM(Number) OVER (PARTITION BY pID ORDER BY cID )
> ↑こちらORDER BY cIDをつけるか否かで結果が変わるのですね。
> 勉強になります。GROUP BY しか使用したことがありませんでした。

上記は「集計関数」の SUM ではなく、「ウィンドウ関数」の SUM です。

ウィンドウ関数の OVER 句には、 GROUP BY を指定できません。
(グルーピングするという意味では、PARTITION BY がそれに近いかな)



> >関連して質問なのですが、
>  Numberが全て"1"の場合(→実質レコード数と同等)、より簡単なSQLに書き換える方法はありますか?

SELECT COUNT(*) - SUM(CASE Number WHEN 1 THEN 1 ELSE 0 END) FROM 子

とすれば、
 = 0 なら、全ての Recored の Number が "1" である
 ≠ 0 なら、"1" でない Number が 1 件以上存在する
 NULL なら、そもそもデータが無い( 0 件 )
ということになりますね。
引用返信 編集キー/
■75324 / inTopicNo.9)  Re[4]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ ???[ (2回)-(2015/03/13(Fri) 16:31:08)
2015/03/13(Fri) 16:33:13 編集(投稿者)

No75323 (魔界の仮面弁士 さん) に返信
ありがとうございます。

「ウィンドウ関数」という名称があるのですね、初めて知りました。



> SELECT COUNT(*) - SUM(CASE Number WHEN 1 THEN 1 ELSE 0 END) FROM 子
>
> とすれば、
>  = 0 なら、全ての Recored の Number が "1" である
>  ≠ 0 なら、"1" でない Number が 1 件以上存在する
>  NULL なら、そもそもデータが無い( 0 件 )
> ということになりますね。


なるほど、
色々条件変えてすみませんがNumberのpID単位合計値ではなく、子テーブルのpID単位レコード数
(レコード数pID:1→3件, pID:2→1件・・・。)の合計値を@Maxと比較するとしたら、

SELECT sum1, sum2
, SUM(Count(*)) OVER (ORDER BY cID )
, SUM(Count(*)) OVER (PARTITION BY pID )
FROM 子 ORDER BY cID

になるかと思ったのですが、Group By句が必要とエラーが出てしまいました。

この場合、全く別の記述方法が必要になりますでしょうか?
引用返信 編集キー/
■75325 / inTopicNo.10)  Re[5]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ 魔界の仮面弁士 (234回)-(2015/03/13(Fri) 17:57:09)
モーさん=???[さんかな?

ここの掲示板、Fiddler や Firefox を使っていると、
Cookie が腐って名前が崩れてしまうことがあるようで。

No75324 (???[ さん) に返信
> SELECT sum1, sum2
> , SUM(Count(*)) OVER (ORDER BY cID )
> , SUM(Count(*)) OVER (PARTITION BY pID )
> FROM 子 ORDER BY cID

SUM() の方は OVER 句があるので ウィンドウ関数。
COUNT() の方はそれが無いので、集計関数。

集計関数の場合、非集計列は GROUP BY しなければなりません。


> 色々条件変えてすみませんが
では、条件変更はこれが最後と言うことで。


> NumberのpID単位合計値ではなく、子テーブルのpID単位レコード数
> (レコード数pID:1→3件, pID:2→1件・・・。)の合計値を@Maxと比較するとしたら、

すみません、どういう意味でしょうか? 質問の意図を読み取れませんでした。


No75313 の 子テーブルに対して、『pID単位レコード数』を求めるなら

pID 件数
 1 3
 2 1
 3 2

ですよね。すなわち「SELECT pID, COUNT(*) FROM 子 GROUP BY pID」。

これらの件数合計を求めるというのであれば、3+1+2 = 6 なわけですが、
それって要するに、「SELECT COUNT(*) FROM 子」でしか無いわけで…。


> (レコード数pID:1→3件, pID:2→1件・・・。)の合計値を@Maxと比較するとしたら、
上記が「総件数を @Max と比較」という話なのだとしたら、たとえば
HAVING COUNT(*) <= @Max などの条件を用意することはできますが、
そういう話では無いでしょうし。
引用返信 編集キー/
■75326 / inTopicNo.11)  Re[6]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ モー (4回)-(2015/03/13(Fri) 19:08:37)
No75325 (魔界の仮面弁士 さん) に返信

返信ありがとうございます。

> モーさん=???[さんかな?
 はい、そうです。

>では、条件変更はこれが最後と言うことで。
ありがとうございます。


>すみません、どういう意味でしょうか?
説明不足ですみません。

↓こちらをお借りするなら、

pID 件数
 1 3
 2 1
 3 2

SELECT文の結果期待値として
@Max=3の場合に、"pID=1"
@Max=4の場合に、"pID=1,2"
@Max=5の場合に、"pID=1,2"を期待します。
[@Max=5の例]
○ 3(件) ≦ 5
○ 3+1(件) ≦ 5
× 3+1+2(件) ≦ 5

@Maxと比較して該当するpIDデータを取得するのは、Numberの時と同様の条件です。
引用返信 編集キー/
■75327 / inTopicNo.12)  Re[7]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ 魔界の仮面弁士 (235回)-(2015/03/13(Fri) 23:06:49)
No75326 (モー さん) に返信
> pID 件数
>  1 3
>  2 1
>  3 2
>
> SELECT文の結果期待値として
> @Max=3の場合に、"pID=1"
> @Max=4の場合に、"pID=1,2"
> @Max=5の場合に、"pID=1,2"を期待します。

ということなら、
 「SELECT pID, COUNT(*) FROM 子 GROUP BY pID」
をさらに再集計して、件数の累積を見れば OK ということになりますね。


残念ながら、SUM(COUNT(*)…) のような表現はできないため、
再集計する場合は、上記の結果を SELECT しなおして


SELECT
 pID, SUM(F) OVER (ORDER BY pID)
FROM (
 SELECT pID, COUNT(*) AS F
 FROM 子
 GROUP BY pID
) AS T

などのようにします。(もしくは、WITH を使う手法もあります)
これにより、

pID 累積件数
 1 3
 2 4
 3 6

という結果を得られますので、あとはこれを @MAX で判断すればよいわけです。


HAVING のように、条件分の中で SUM が使えれば話が早いのですが、
ウィンドウ関数は SELECT 句でしか使えないため、この結果を
もう一度 SELECT しなおす必要があります。


という事で素案。
他の書き方もあるとは思うので。



-- 案1
SELECT pID FROM (
 SELECT
  pID, SUM(F) OVER (ORDER BY pID) AS F
 FROM (
  SELECT pID, COUNT(*) AS F
  FROM 子
  GROUP BY pID
 ) AS T
) AS T2
WHERE F <= @Max


-- 案2
WITH T (pID, F) AS (
 SELECT
  pID, SUM(F) OVER (ORDER BY pID)
 FROM (
  SELECT pID, COUNT(*) AS F
  FROM 子
  GROUP BY pID
 ) AS T
)
SELECT pID FROM T WHERE F <= @Max


-- 案3
WITH
 T1 (pID, F1) AS
 (
  SELECT pID, COUNT(*) AS F
  FROM 子
  GROUP BY pID
 ),
 T2 (pID, F2) AS
 (
  SELECT pID, SUM(F1) OVER (ORDER BY pID)
  FROM T1
 )
SELECT pID FROM T2 WHERE F2 <= @Max




=== 別案 ===

-- 案4
SELECT pID FROM (
 SELECT pID,
  CASE WHEN LEAD(pID, 1) OVER (PARTITION BY pID ORDER BY cID) IS NULL
  THEN COUNT(*) OVER (ORDER BY cID) ELSE NULL END AS F
 FROM 子
) AS T
WHERE F <= @Max


-- 案5
WITH T (pID, F) AS (
 SELECT pID,
  CASE WHEN LEAD(pID, 1) OVER (PARTITION BY pID ORDER BY cID) IS NULL
  THEN COUNT(*) OVER (ORDER BY cID) END
 FROM 子
) SELECT pID FROM T WHERE F <= @Max
引用返信 編集キー/
■75328 / inTopicNo.13)  Re[7]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ 魔界の仮面弁士 (236回)-(2015/03/14(Sat) 16:03:53)
No75326 (モー さん) に返信
> > SELECT文の結果期待値として
> > @Max=3の場合に、"pID=1"
> > @Max=4の場合に、"pID=1,2"
> > @Max=5の場合に、"pID=1,2"を期待します。
>
> ということなら、
>  「SELECT pID, COUNT(*) FROM 子 GROUP BY pID」
> を再集計して、累積を見れば OK かと。

おっと。cID=7、pID=1 が来たパターンを考慮忘れていました。

cID|pID
1|1
2|1
3|1
4|2
5|3
6|3
7|1

この場合、@Max と比較する『pID単位レコード数』をどう考えるのでしょうか?


単純に件数カウントすれば
 【表1】
 pID=1 は 4 件、累積4件
 pID=2 は 1 件、累積5件
 pID=3 は 2 件、累積7件
となります。また、cID 順に「現レコードまでの出現件数」を数えていく場合は
 【表2】
 1行目までに、pID=1 が 1件
 2行目までに、pID=1 が 2件
 3行目までに、pID=1 が 3件
 4行目までに、pID=1 が 3件、pID=2 が 1件
 5行目までに、pID=1 が 3件、pID=2 が 1件、pID=3 が 1件
 6行目までに、pID=1 が 3件、pID=2 が 1件、pID=3 が 2件
 7行目までに、pID=1 が 4件、pID=2 が 1件、pID=3 が 2件
ですね。ここに、pID ごとの総件数も考慮するとすれば
 【表3】
 1行目までに、pID=1 は 1/4件、pID=2 は 0/1件、pID=3 は 0/2件
 2行目までに、pID=1 は 2/4件、pID=2 は 0/1件、pID=3 は 0/2件
 3行目までに、pID=1 は 3/4件、pID=2 は 0/1件、pID=3 は 0/2件
 4行目までに、pID=1 は 3/4件、pID=2 は 1/1件、pID=3 は 0/2件
 5行目までに、pID=1 は 3/4件、pID=2 は 1/1件、pID=3 は 1/2件
 6行目までに、pID=1 は 3/4件、pID=2 は 1/1件、pID=3 は 2/2件
 7行目までに、pID=1 は 4/4件、pID=2 は 1/1件、pID=3 は 2/2件
と考えられます。さらに表3で、「n/m件」の部分が n=m を満たしたものを拾っていくと
 【表4】
 1行目までに、全件揃った pID は存在しない。
 2行目までに、全件揃った pID は存在しない。
 3行目までに、全件揃った pID は存在しない。
 4行目までに、pID=2 が 1 件揃った。累積 1 件。
 5行目までに、pID=2 が 1 件揃った。累積 1 件。
 6行目までに、pID=2 が 1件、pID=3 が 2件揃った。累積 3件。
 7行目までに、pID=1 が 4件、pID=2 が 1件、pID=3 が 2件揃った。累積 7件。
となります。

今回の No75324, No75326 の質問内容は、表4 に対して「WHRE 累積件数 <= @Max」で絞り込んで
 @Max が 0 以下の場合、"pID:無し"
 @Max が 1〜2 の時、"pID:2"
 @Max が 3〜6 の時、"pID:3
 @Max が 7 以上の時、"pID:1,2,3
と求めたいのだと推察しましたが、この認識で正しいでしょうか?
No75319 の返答を見る限り、全件走査は不要っぽいので、多分違うのでしょうけれど…。


なお No75327 で回答したものは、表1に対する累積件数を @Max と比較したもののため、
 @Max が 3 以下の場合、"pID:無し"
 @Max が 4 の場合、"pID:1"
 @Max が 5〜6 の場合、"pID:1,2"
 @Max が 7 以上の場合、"pID:1,2,3"
と抽出される仕様です。
引用返信 編集キー/
■75331 / inTopicNo.14)  Re[8]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ モー (5回)-(2015/03/16(Mon) 13:56:11)
2015/03/19(Thu) 16:23:48 編集(投稿者)

No75328 (魔界の仮面弁士 さん) に返信

複数案を丁寧な形でご提示ありがとうございます。
1つずつ内容把握、確認していきたい思います。

> おっと。cID=7、pID=1 が来たパターンを考慮忘れていました。
> この場合、@Max と比較する『pID単位レコード数』をどう考えるのでしょうか?
>
この場合、cIDが不連続ですが、cIDの並びとは無関係に同一pIDでまとめたいので、pID=1の
『pID単位レコード数』は"4件"として扱いたいです。


> 今回の No75324, No75326 の質問内容は、表4 に対して「WHRE 累積件数 <= @Max」で絞り込んで
>  @Max が 0 以下の場合、"pID:無し"
>  @Max が 1〜2 の時、"pID:2"
>  @Max が 3〜6 の時、"pID:3
>  @Max が 7 以上の時、"pID:1,2,3
> と求めたいのだと推察しましたが、この認識で正しいでしょうか?
> No75319 の返答を見る限り、全件走査は不要っぽいので、多分違うのでしょうけれど…。
>
はい、全件走査は不要です。Maxを超えた時点で走査は終了します。
↑の提示頂いた内容でも良いのですが、理想はpIDの昇順にMax比較したいです。

この期待値は↓パターンで満たされていますね。


>
> なお No75327 で回答したものは、表1に対する累積件数を @Max と比較したもののため、
>  @Max が 3 以下の場合、"pID:無し"
>  @Max が 4 の場合、"pID:1"
>  @Max が 5〜6 の場合、"pID:1,2"
>  @Max が 7 以上の場合、"pID:1,2,3"
> と抽出される仕様です。



解決済み
引用返信 編集キー/
■75333 / inTopicNo.15)  Re[9]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ shu (720回)-(2015/03/16(Mon) 14:38:03)
No75331 (モー さん) に返信

まず

Select pID, Min(cID) cIDMin, Sum(Number) SumNum
From 子テーブル
Group By pID

をSelectすると良いと思います。

引用返信 編集キー/
■75388 / inTopicNo.16)  Re[10]: レコード値の合計が一定値を超えるまで抽出
□投稿者/ モー (6回)-(2015/03/19(Thu) 16:24:38)
 
解決済み
引用返信 編集キー/


トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

管理者用

- Child Tree -