スポンサーサイト 


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

再帰処理にはまる(その6) 


テーブル名「T9」
フィールド:顧客番号(テキスト)/ 売上金額(通貨) / 売上月(テキスト)
an顧客番号売上金額売上月
1001¥100 1月
2002¥50 1月
3003¥200 1月
4004¥150 1月

テーブル名「T9A」
フィールド:顧客番号(テキスト)/ 集約先番号(テキスト)
an顧客番号集約先番号
1001002
2002003

上記2つのテーブルから以下の結果を導きたい
集約番号売上金額計売上月
003¥350 1月
004¥150 1月

標準モジュールに以下を記述しておきます。
Public Function Bango(sSrc As String) As String
  Dim rs As New ADODB.Recordset
  Dim sR As String

  sR = sSrc
  rs.Source = "SELECT * FROM T9A WHERE 顧客番号='" & sSrc & "';"
  rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  If (Not rs.EOF) Then sR = Bango(rs("集約先番号"))
  rs.Close
  Bango = sR
End Function

クエリの SQL ビューで以下を記述します。
SELECT Bango(顧客番号) AS 集約番号, Sum(売上金額) AS 売上金額計, 売上月
FROM T9
GROUP BY Bango(顧客番号), 売上月;

というのを回答したわけですが・・・・
 
や~~、回答内容で動いて良かったですね・・・

というのは、もし、テーブル「T9A」が
an顧客番号集約先番号
1001002
2002003
3003001

のように、循環していたら・・・・無限ループになっちゃいますね・・・
ある程度動いたら、スタック領域がない・・・とかのエラーになりますが・・・
強制終了して・・・・最悪、mdb/accdb が壊れ・・・・ってな状況になったりするのでしょうか・・・・

循環する様なデータを許可している・・・・これが根本的な問題になるのですが・・・・
何を想定して回答すれば良いんですかね・・・・
基本的には、提示されたサンプルで、そこそこ動けば OK だと思っています。

この話は、これ位にして・・・

前述した VBA 記述と、以下の記述ではどちらが良いのでしょう。
Public Function Bango(sSrc As String) As String
  Dim rs As New ADODB.Recordset
  Dim sR As String

  sR = sSrc
  rs.Source = "SELECT 集約先番号 FROM T9A WHERE 顧客番号='" & sSrc & "';"
  rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  If (Not rs.EOF) Then sR = Bango(rs(0))
  rs.Close
  Bango = sR
End Function

得られる結果は同じなんですけどね・・・・
前者は、SELECT でフィールド全部を得ておいて、後参照では必要なフィールドを指定
後者は、SELECT で必要なフィールドだけを求めておいて、後参照では何個目・・・・

雰囲気的には、後者の方がリソースを節約できるのかなぁ~~・・・・わかりません。

関数の再帰呼出し・・・ではなくて、ループ処理でも記述できますね。
Public Function Bango(sSrc As String) As String
  Dim v As Variant
  Dim sR As String

  v = sSrc
  While (Not IsNull(v))
    sR = v
    v = DLookup("集約先番号", "T9A", "顧客番号='" & v & "'")
  Wend
  Bango = sR
End Function

Until って書き方もあると思いますが、私は While を使います。
途中抜ける事がなければ While ~ Wend で・・・・
抜ける事があれば、Do While ~ Loop で・・・・
××の間、処理する・・・・・っていうのが考えやすいので・・・(ある意味 For と同じですね)

いろいろなサイトを見ると、D 系の関数は遅いとか・・・云々言われてますね。
実際にどうなのか・・・私にはわかりません。
ただ、汎用のものを専用にすると速いかな・・・・と思いますけど・・・・

循環があった時には、この書き方ではスタック領域が・・・云々のエラーは起きないと思うので、
無限ループに陥ると思います。

循環チェックの例は、過去記事「クエリで再起処理?その2」で記述していたかと・・・・
参考になるかどうか・・・・ダメもとで見てみる・・・・それはありかと


【追記】6/2

テーブル「T9A」のデータの持ち方が変わったようで
an顧客番号集約先番号
1001002
2002003
3003 
4004 

の様に、顧客番号は全て登録・・・で、集約先番号は、Null だったり、従来通りだったり
また、顧客番号 / 集約先番号 でネストするのは最大でも5段とか

最悪、循環があっても動くように再帰・・・ではなく、クエリ(SQL)で記述してみた。
SELECT Switch(
Not IsNull(Q5.顧客番号), Q5.顧客番号,
Not IsNull(Q4.顧客番号), Q4.顧客番号,
Not IsNull(Q3.顧客番号), Q3.顧客番号,
Not IsNull(Q2.顧客番号), Q2.顧客番号,
True, Q1.顧客番号) AS 集約番号, Sum(Q0.売上金額) AS 売上金額計, Q0.売上月
FROM (((
(T9 AS Q0 INNER JOIN T9A AS Q1 ON Q0.顧客番号=Q1.顧客番号)
LEFT JOIN T9A AS Q2 ON Q1.集約先番号=Q2.顧客番号)
LEFT JOIN T9A AS Q3 ON Q2.集約先番号=Q3.顧客番号)
LEFT JOIN T9A AS Q4 ON Q3.集約先番号=Q4.顧客番号)
LEFT JOIN T9A AS Q5 ON Q4.集約先番号=Q5.顧客番号
GROUP BY Switch(
Not IsNull(Q5.顧客番号), Q5.顧客番号,
Not IsNull(Q4.顧客番号), Q4.顧客番号,
Not IsNull(Q3.顧客番号), Q3.顧客番号,
Not IsNull(Q2.顧客番号), Q2.顧客番号,
True, Q1.顧客番号), Q0.売上月;

テーブル「T9」「T9A」を内部結合しておいてから、
段数分、テーブル「T9A」を外部結合・・・・・
Switch を使って、どの顧客番号を集約番号として求めるか・・・・
これと、売上月でグループ化して・・・・売上金額を Sum すれば・・・

表示結果は同じになりましたが・・・・処理性能はわかりません


実際には、こう考えた方が良いよ・・・・等、教えてください。
関連記事

2013/05/21

Category: 解説か

TB: --  /  CM: 0

top △

この記事に対するコメント

top △

コメントの投稿

Secret

top △


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。