みんなに優しく、解りやすくをモットーに開設しています。 以下のルールを守りみんなで助け合いましょう。
1.ファイルメーカーで解らない事があればここで質問して下さい。 何方でも、ご質問・ご回答お願いします。 （優しく回答しましょう）
You are not logged in.
FileMaker 初心者で、初めて質問します。
【背景】
別のソフトウェアを用いたデータモデリングのアプリ化のために FileMaker を使おうとしています。
そのモデルには、集計フィールドで計算できる合計値に相当する、全案件の合計値の計算が複数含まれ、
一つの合計値を用いてさらに別の計算をし、その合計値を計算してさらに別の計算をし、といった
入れ子型の合計値を計算したり、複数の合計値を含むような計算式を評価したりしなければなりません。
ここで単純に一つのテーブルに集計フィールドを含めると、計算回数がとんでもない数になり、事実上計算できません。
そこで、集計フィールド計算専用のテーブルを作り、集計フィールドの計算はすべてそちらに切り出し、
スクリプトトリガで逐次的に、各集計フィールド独立に計算することによって、計算量を現実的な規模に抑えています。
例えば、sum2 = SumN( SumN( data1 * data2 ) * data3 ) は、以下のような 8 ステップの形で計算しています。
テーブル Data
data1（数字）
data2（数字）
data3（数字）
テーブル Calc
1. eq1 = data1 * data2（計算）
4. sum1（数字）<-- SumN/sumN1 のフィールド値を全置換
5. eq2 = sum1 * data3（計算）--> SumN/eq2 のフィールド値を全置換
8. sum2（数字）<-- SumN/sumN2 のフィールド値を全置換
テーブル SumN
2. eq1（数字）<-- Calc/eq1 のフィールド値を全置換
3. sumN1 = eq1合計（集計）
6. eq2（数字）<-- Calc/eq2 のフィールド値を全置換
7. sumN2 = eq2合計（集計）
ここで、表記 SumN は、Sum() に相当するフィールドの合計ではなく、集計フィールドの合計に相当するレコードに関する合計を表しています。
【質問】
(1)
フィールド値を全置換するスクリプトの前には、対象フィールドにレイアウト切り替えをしておかないと、対象フィールドに値が入りません。
このレイアウト切り替えは、FileMaker では、必須で、唯一の対処方法でしょうか？
直感的には、レイアウトはあくまでも画面表示のためだけのものであって、データベースのフィールドはそれとは無関係に操作できると思いたいのですが。
(2)
レイアウト切り替えをしなければならない場合、UI として使用するレイアウトをユーザーに表示し続けておきたいのに、
計算中に切り替え先のレイアウトが表示されてしまいます。
それを避けるためには、UI レイアウトの複製を作成し、その参照テーブルをスクリプトトリガで対象にして、見た目に変化がないようにしてしのいでいます。
こんな方法以外のやり方はないのでしょうか？
この方法だと、開発で UI レイアウトに変更を加えるたびに、複製したレイアウトにも見た目に寸分違わない変更を加えていくか、
UI レイアウトを新たに複製し、そのことにより切れたスクリプト内リンクをすべて張り直すかしないといけません。
基本的なことだとは思うのですが、このコミュニティの過去の類似例を見てもまだよく分からないので、教えて頂ける方がおられましたら幸いです。
（訂正）
ステップ5
元： eq2 = sum1 * data3（計算）--> SumN/eq2 のフィールド値を全置換
修正： 5. eq2 = sum1 * data3（計算）
そんな面倒な構造にしないでも、dataテーブル内設置のSQL計算フィールド一発で実装できます。
（他の無駄な追加要素は一切無用です。この計算フィールド単独で解決します。）
求める最終集計結果「sumN2」は全レコード共通だから、「dataテーブル::sumN2」はグローバル・計算フィールドとして、
その式は、（計算結果：数値タイプ、グローバル格納）
ExecuteSQL(
"
SELECT SUM( (SELECT SUM(""data1"" * ""data2"") FROM ""dataテーブル"") * ""data3"" )
FROM ""dataテーブル""
"
; ""
; ""
)
& Left(data1&data2&data3; 0) // ← ※ 再計算誘発用：無害のダミー式 の追記
Offline
Hiro さま
代替案のご提案、ありがとうございます。
数式を SQL で書いてやれば計算できるとのこと。
今、集計フィールドで計算した合計値を全置換で参照する、数多くある数字フィールドの一つ目だけを
計算フィールドにして、計算式として教えて頂いた ExecuteSQL() を書いてみました。
例示したテーブル・フィールド構造で言うと、次のようなものに対応しています。
フィールド Calc::eq1
ExecuteSQL(
"
SELECT SUM(data1 * data2)
FROM Calc
" ;
"" ;
""
)
この 1 つのフィールド以外は元のままです。
この場合、計算式を入力して Ok を押すと、今までの FileMaker の標準的な機能だけを使った場合は待ち時間はゼロだったところ、
「データベース更新」のダイアログが現れ、「処理対象残りレコード数」のメーターが表示されるようになり、
操作できる状態になるまで20秒くらい待たなければならなくなりました。
さらに、今までは 1 秒足らずで済んでいた、全データをスキャンする初期化スクリプトの実行が、非現実的にそれ以上かかるようになりました。
試したことはこの１点だけですが、このことから、もしも FileMaker の数式を SQL で置き換えてうまくいくのなら、
最終の評価フィールドに、数多くある SUM を含んだすべての数式を書き切らないと非現実的な時間がかかって無理だろうと思っています。
実際は、実データを見ながら、このアプリ上でデータの解析から再モデリングをしたく、
そのためには数式を構成するすべての項を確認しながら使えないと困るので、
項の値をフィールドとして保持し、開発用のレイアウトに表示する必要があります。
そのように、最終の評価フィールドの値だけが得られればよい状況にはないので、
SQL だと一つのフィールドで数式を表現できてしまうというのがメリットではなく、それがデメリットになってしまうと見ています。
（このすべての項の値を保持したいという点は、質問時に気付いていたわけではなく、今認識できたことです。）
すべての項の値をフィールドで保持するとすれば、結局、SUM の計算の部分だけを他の計算式とは異なった処理方法を取る必要が生じ、
そこを、ExecuteSQL() で置き換えるか、私が採っている元の FileMaker 標準の機能の組合せで置き換えるか、どちらが良いかということに帰着します。
一方、ExecuteSQL() について Web 上で少しだけ調べた中に、SQL だと時間がかかるという記述がどこかの Web サイトにあった気がします。（忘れました。）
元々、計算速度を気にしているところ、これを書いている今は、試行錯誤する時間を十分に取れず、ExecuteSQL の実行速度がどうなのか知らないので、
ExecuteSQL() で置き換えるという、思いもしなかったこのアプローチは、ちょっと保留にしておきたいと思います。
データベースを専門に扱ってきた者ではないので、SQL を使うという発想自体、ありませんでした。
そういう方法もあるのかと勉強になりました。ありがとうございます。
解答の理解が間違っています。
そんな面倒な構造にしないでも、dataテーブル内設置のSQL計算フィールド一発で実装できます。
（他の無駄な追加要素は一切無用です。この計算フィールド単独で解決します。）
必要なのはデータがある「dataテーブル」と そのテーブル内に新設のグローバル計算フィールド「sumN2」だけです。
テストはこの環境でお試しください。非常にシンプルですからテスト環境整備はすぐできるはずです。
例えば、（5レコードの集計例）
dataテーブル
フィールド(data1、data2、data3 → sumN2)
・ 1、2、3 → 1750
・ 2、3、4 → 1750
・ 3、4、5 → 1750
・ 4、5、6 → 1750
・ 5、6、7 → 1750
Offline
設問は「2重入れ子の計算値の集計」が必須だから、解決方法が何であれ、処理時間が掛かるのは致し方ない事案でしょう。
確かに、入れ子のSQL集計（サブクエリ 副問合せ）処理は取分け重たいと云えます。
そのため、問題のサブクエリを外部引数（「？」ダイナミック・パラメータ）に差し替えた1段SQL式も試してみては如何でしょう？
この腹案の具体式は、
ExecuteSQL(
"
SELECT SUM( ? * ""data3"" )
FROM ""dataテーブル""
"
; ""
; ""
; ExecuteSQL( "SELECT SUM(""data1"" * ""data2"") FROM ""dataテーブル"""; ""; "" )
)
& Left(data1&data2&data3; 0)
更に、計算フィールドは更新再計算のタイミングが自動で制御できないので、
必要な時だけの手動が良いなら、スクリプト「フィールド設定」にすると余計なストレスがないでしょう。
Last edited by Hiro (2020-04-17 15:47:03)
Offline
結論から言って、SQL集計は思ったよりかなり早く、十分実用的です
環境： 対象データレコード数 = 10,000件、 = 100,000件
集計： SQL集計計算方式（計算値の２重集計）
動作： 単スクリプト・ボタン「フィールド設定［グローバルフィールド；※#6 SQL式］」
速度： 2.542 秒 ／ 1万件（の計算値２重集計）
速度： 23.280 秒 ／ 10万件（の計算値２重集計）
※なお、遅いと予測した#3のサブクエリ方式も並列比較テストしてみましたが、全く同等の速度結果を得ました。
Last edited by Hiro (2020-04-20 13:39:33)
Offline
Hiro さま
引き続き何度も情報を作っていただき、ありがとうございます。
本件が関係する目先のことを仕上げることを優先させるために、
SQL ではない当初の方法の延長でその他諸々を試行錯誤して検討していました。
FileMaker 特有の挙動がいくつか分かったりしながら勉強になるプロセスになりました。
4月16日から19日まで 3 つに分けて書いていただいた情報は、まだ手を付けられていません。
そのうち時間ができたら追っていきたいと思います。
以下は、確認できていない現時点でのコメントだけです。今日のところは粗い書き方ですみません。
4月16日 #5 について。
> 必要なのはデータがある「dataテーブル」と そのテーブル内に新設のグローバル計算フィールド「sumN2」だけです。
一回試した方法では、この注意書きに対応する状況で確認したはずだったと思っていますが、
勘違いかもしれないので、まずはここから確認していきます。
4/17日 #6 について。
> 必要な時だけの手動が良いなら、スクリプト「フィールド設定」にすると余計なストレスがないでしょう。
この質問の動機がこれです。
そのために、集計フィールドを別のテーブルに移し、スクリプトのフィールド全置換の時点で個別に集計するようにしました。
この他に良い方法がないものなのかというのがこの質問の動機です。
SQL による方法はまだ確認する時間を取れていないこれを書いている現在、まだ分からない状況です。
4月19日 #7 について。
いろいろ直接確認、ありがとうございます。
目下のケースでは、このサイトでサンプルで示したほど単純ではなく、
案件数は1000件ほどですが、集計が多く、元の SQL ではない方法では 100 秒くらいかかったりしています。（Core i7, 6 cores, 32 GB, SSD）
> 必要なのはデータがある「dataテーブル」と そのテーブル内に新設のグローバル計算フィールド「sumN2」だけです。
ExecuteSQLの計算フィールドを、データテーブル内に作るのは（すでにお気づきのようですが）、レコード数分の計算が発生するのでよくないです。
>このサイトでサンプルで示したほど単純ではなく、
計算（集計）結果を保存する場所を考慮する必要があるでしょう。レコード内の計算なら、同じテーブルの計算フィールドでいいですが、複数のレコードからの計算であれば、別テーブルにしないと同じ計算を無駄に繰り返すことになります。
最初の質問１）
スクリプトの操作対象のレコードは常に現在選択されているレイアウト上のもの（検索結果・ソート状態が反映される）なので、レイアウトを切り替えない場合、他のテーブルの操作は「関連テーブル」として処理されます。関連がなければエラーです。
現在のレイアウト上を1レコードにして、Xリレーションで操作すれば、他テーブルの全レコードの全置換がレイアウト切り替えなしでできると思うけど、やったことはないですね。
質問２）
スクリプトで「ウィンドウの固定」してから一連の操作をすれば、ユーザ操作が必要（可能）な状態（一時停止など）を作らない限りユーザの見た目を変えずに別のレイアウトで処理ができます。
>#1 たっくゲストユーザー＜ 最適な複数集計フィールドの逐次計算 ＞『FileMaker 初心者で、・・・・』
そもそも論で
「>#1たっくさん発想の解法案」を異常に複雑難解なものと強いる根本要因は、
集計処理の基本手段に「集計フィールド」を唯一利用する発想から生じています。
「集計フィールド」は簡易パッケージ機能で初心者向きですが、その分自由度はほぼありません。
特に、集計対象は現物の具象的リアルなレコードセットのみ唯一に限定され、同時多元・多重・複合など抽象的集計には全く不向きです。
是非、他の集計手法も調べて、事案に最適な手法への見直しを検討ください。
FMの集計手法を思いつくまま列挙すると、
・集計レイアウト＆集計フィールド法
・リレーション集計法
・ループスクリプト集計法
・変数値「全置換」集計法
・SQL計算集計法
など、かな。
その中で、>#1事例説明の限りに於いて最適最楽と思う集計法に「SQL計算集計法」案をお勧めした次第です。
もし、>#8『このサイトでサンプルで示したほど単純ではなく、』なら、正確な具体説明を示してください。
具体解法案はそれにより再考できますから！
Offline
> もし、>#8『このサイトでサンプルで示したほど単純ではなく、』なら、正確な具体説明を示してください。
はい、ちょうど、次はそうするつもりでした。
また一週間くらい空けるかもしれませんが、エッセンスを残したサンプルで示したいと思います。
> 是非、他の集計手法も調べて、事案に最適な手法への見直しを検討ください。
> FMの集計手法を思いつくまま列挙すると、
> ・集計レイアウト＆集計フィールド法
> ・リレーション集計法
> ・ループスクリプト集計法
> ・変数値「全置換」集計法
> ・SQL計算集計法
またいろいろありがとうございます。
集計フィールドしか知らなかったもので。
ちょっと込み入ったことをし始めるのならそもそも集計手法には他の方法を使うのが一般的だとしたら、
それを知りたく思います。
可読性を悪くしないでメンテナンスでき、かつ高速であること、この二つの点で最良の方法が良いかと思っています。
挙げて頂いた方法は、FMでは知られているものなのでしょうか。
もしも「ここ見ろ、読め」といった適切なサイトなり書籍なりがあるのでしたら、教えて頂けると、
いったんは自力で理解することを試みられるように思うので、
もし手数少なく思いつきで挙げられるところがありましたらご教示頂けるとありがたいです。
Hiro さま
いったん、#5 の続きとして、目下の課題に対応する計算式の構造を保ったサンプルを作成し、系統的なテストをしてみました。
サンプルの Zip ファイル
https://www.dropbox.com/s/qe554v22esx43ml
この中の Test.pdf に、結果を表形式でまとめています。
調べた限りでは、やはり、ExecuteSQL だと、とんでもない時間、おそらく何万年、何億年といった天文学的時間がかかり、事実上計算不能です。
また、eq1 のみにした場合に、そもそも結果が得られていません。
何か基本的なことが間違っている気がするのですが、よく分かりません。
なお、元の方法の場合、案件数 1200 で、このサンプルの数式だと 75 秒かかっています。
これを劇的に短くする方法が何かあるのかどうかに関心があります。
ちなみに、テストに含めているとおり、集計フィールドを別のテーブルに切り出さない場合でも
計算時間の点では実質的には変わらないように見えますが、
そうしてしまうと、UI で、複数の値のセットを入力してから計算させたいのに、一つの値を変えるごとに 1 分以上計算が回ってしまうので、
別のテーブルに分けてスクリプトで計算を始めるようにしないといけないだろうと、今までのところは考えてきました。
himadanee さま
#9 2) 「ウィンドウの固定」について、ありがとうございます。
そういうのがあるのですね。
スクリプトのメニューには全部目を通していたのですが、名称からそのように動作させると解釈できず、関係ないと思っていたので、試していませんでした。
やってみます。
himadanee さま
> ExecuteSQLの計算フィールドを、データテーブル内に作るのは（すでにお気づきのようですが）、レコード数分の計算が発生するのでよくないです。
ああ、やっぱりそうでしたか。
ちょっとやってみて、それからさっきもう一度やってみて、挙動から、やはり、すべてのレコードの組合せをスキャンしているのだろうと見ていました。
ということは、現在の最低限の構成でも、集計したいフィールド（SQL の SUM）が 17 はあって、レコード数が 1200 ですから、
計算回数は 1200 の 17 乗 ＝ 2.2E52 ですね。
1 秒で 200 レコード計算できるとして、10 の 50 乗秒 ＝ 10 の 43 乗年くらい。
宇宙誕生からでもたったの 145 億年 ＝ 10 の 10 乗年ですから、計算時間は天文学的数字を超えて、神の世界ですね。
そりゃ、だめです。
じゃあ、FileMaker でやるなら、おそらく別のテーブルで集計するのは絶対条件だろうとして、高速化する方法があるかどうか、というところが目下の関心です。
レコード数分の計算だと、天文学的数字ではなくて、1200 × 17 程度かもしれません。
そうだとしても、目下の最低限の構成でさえ数時間かかることになるので、完全に非現実的だということには変わりないですね。
#7でHIROさんがやってるような、
動作： 単スクリプト・ボタン「フィールド設定［グローバルフィールド；※#6 SQL式］」
でやれば、
＞集計したいフィールド（SQL の SUM）が 17 はあって、レコード数が 1200 ですから、
これは処理数17回です。
フィールド定義で、1200レコードのテーブルに計算フィールドを１つ作ると、同じ計算を1200回することになります。「レコードの組合せをスキャン」ではなくて、単純に計算する場所の問題です。
テーブル全体に関する集計だけ（GROUP BYがない）で、別のテーブルを作りたくなければ、計算でないグローバルフィールドを使えばいいです。
（GROUPがあってもできるが、結果が複数行になるので表示に工夫が必要）
「レコードの組合せをスキャン」が発生するのは、SQL内でFROMのテーブルを複数指定する場合です。
>#7のSQL集計法の実装サンプルです。
文書説明で解って貰えない様なので、代わりに内容確認ください。
●サンプル「SQL集計.fmp12」→ https://1drv.ms/u/s!AlaCGhTKTWEOp3CFsEa … b?e=ImFxN0
Last edited by Hiro (2020-05-12 15:16:08)
Offline
>#14『集計したいフィールド（SQL の SUM）が 17 はあって、レコード数が 1200 です』
これはデータ数18個でSQL集計処理数17回ですネ。
データを繰り返し[18回]フィールドに一括保存し、ループSQL集計スクリプトで統合処理する作例サンプルです。
●サンプル「多重連装SQL集計.fmp12」→ https://1drv.ms/u/s!AlaCGhTKTWEOp24MBN6 … -?e=d3EcVv
（※所要処理時間 → わずか 2.609 秒 / 1200レコード；18個データ配列；17回SQL集計計算 ）
Last edited by Hiro (2020-05-12 15:10:36)
Offline
Hiro さま
#17, #18 ありがとうございます。
フィールドで計算するのではなく、スクリプトのフィールド設定の計算式を使うという方法なのだと、理解できました。
たしかに、その組合せには気付きませんでした。
この方法だと、テーブルを増やすようなことは不要で、任意の時にスクリプトを実行する形で計算できる良い方法だと理解しました。
何度も丁寧にお付き合い、本当にありがとうございます。
---
以下は、計算時間についてです。
ExecuteSQL の計算時間は、テーブルを分けて集計フィールドで計算させる場合の計算時間と実際上同じだと判断しました。
ただし、以下に記す修正した計算式構造では、SQL の SUM に ?* を付けていると計算されず、どうするのがよいのか分からなかったため、?* を単純に除去した場合でのことです。
以下の記述に関係するファイルを 4 つここに置いておきます。
https://www.dropbox.com/s/pryflqq3qzpz0p4
#16, #17 のファイルを見て、どう解釈されたか分かりました。
私がこの質問の冒頭 #1 の「入れ子型の合計値」で表現したかったのは、#17 のループで表せてしまえるような集計ではありません。
一つの合計値を用いてさらに別の計算をし、その合計値を計算してさらに別の計算をし、といった入れ子型の合計値を計算
これを数式で書きますと、入れ子 1 つの場合、次のような計算を表そうとしていました。
Σj{ Σi( d1 ) × d2 } × d3
まず d1 の合計値を計算（i について）し、その合計値に d2 を掛けた値の合計値を計算（j について）しています。
j についての合計値は i についての合計値に依存し、これを入れ子型と呼びました。
一方、Hiro さまの解釈の場合、次のような別の形の計算に相当しているかと思います。
Σi( d1 ) × Σj( d2 ) × d3
これは、i と j についての合計値を互いに独立に計算できるケースです。
しかし、この単純なケースは私が扱う数式の形ではありません。
目下、入れ子型で、入れ子は 5 階層になっています。
入れ子4階層まで作ってみた計算時間は、手元の計算環境では、ms オーダーではなく、11 秒くらいになっており、オーダーが私のサンプルと同じである点で、それと整合した結果になっています。
（実際の計算式では、Sqrt が入ったり If 文が入ったりすることによってさらに計算時間がかかることになっていると思います。）
#16, #17 のファイルを見て、どう解釈されたか分かりました。
私がこの質問の冒頭 #1 の「入れ子型の合計値」で表現したかったのは、#17 のループで表せてしまえるような集計ではありません。
いいえ、最初から「入れ子型の合計値」と承知して回答してますし、#17はループSQL集計で十分「入れ子」対応できています。
一つの合計値を用いてさらに別の計算をし、その合計値を計算してさらに別の計算をし、といった入れ子型の合計値を計算
これを数式で書きますと、入れ子 1 つの場合、次のような計算を表そうとしていました。
Σj{ Σi( d1 ) × d2 } × d3
まず d1 の合計値を計算（i について）し、その合計値に d2 を掛けた値の合計値を計算（j について）しています。
j についての合計値は i についての合計値に依存し、これを入れ子型と呼びました。
ずっと求めていた計算内容の具体的説明がようやく貰えましたネ。
そこで、１つ確認します。
３データの場合の式は、
・３回集計 の Σk{ Σj{ Σi( d1 ) × d2 } × d3 }
じゃなくて
・２回集計 の Σj{ Σi( d1 ) × d2 } × d3
で良いのですね？
一方、Hiro さまの解釈の場合、次のような別の形の計算に相当しているかと思います。
Σi( d1 ) × Σj( d2 ) × d3
これは、i と j についての合計値を互いに独立に計算できるケースです。
しかし、この単純なケースは私が扱う数式の形ではありません。
いいえ、当初#1の抽象的コメントから私の解釈した式は、（多分、そうとしか読めない！）
・Σj{ Σi( d1 × d2 ) × d3 }
Last edited by Hiro (2020-05-01 11:47:15)
Offline
Hiro さま
#20、それも理解しました。
その通りです。
ありがとうございます。
たぶん、全部理解できたと思います。
もしも横におられたら数分のやり取りで済むようなことだったように思い、その点では難しいなあと感じています。
#17 の数式は、#1 冒頭に書いた記述に対応させて示すのが誤解がないだろうと考えて書いています。
数式は、そこで説明したかった 2 種類の計算式の形の違いが分かるように形式化したもので、d1, d2, d3 は必ずしも数値ではありません。
そのままの d1, d2, d3 という 3 つの数値と解釈してもかまいませんが、実際は、どちらかというと計算式です。
その計算式には、単純に数値の掛け算もありますが、四則演算、べき乗や平方根に加え、
割り算でゼロ割を避けたり UI でユーザー入力する数値に従って計算式を切り替えたりするための If 文があったりします。
ですから、実際にはその面が多くなるにつれて計算に時間がかかっていくことになっているかと思っています。
---
複数のことが同時発生していて、混乱するところが複数あって、分かりにくい流れにしてしまいました。
現在気付くところで、得た理解を以下にまとめておきたいと思います。
## 当初の表面的な問題に対して： 異なるテーブルで計算させるときのレイアウト関係
提示いただいた ExecuteSQL が解決。
## 本件をきっかけに位置付け直してみる FileMaker の使い方
FileMaker は、フィールドやボタンを配置したりなどして、アプリとして UI を構築する部分のやりやすさに優れている。そこを買う。
一方、集計を多用するような本件の場合には、FileMaker が備えている計算フィールドや集計フィールドは使用せず、
すべて数値フィールドにして、そこに結果を入れる計算は、
提案いただいた、スクリプト「フィールド設定」での ExecuteSQL にしてしまうことを基本に据えておくくらいでよいかもしれない。
（SQL 以外にも方法はあるのでしょうけど、目下、そこに留めておく範囲では。）
## 集計計算の計算時間に関して
現在の計算時間は、たぶん、計算式から最低限必要となる集計計算時間だとして受け入れなければならない。
FileMaker 上でその部分をこれ以上高速化することは期待できないように思う。
・ExecuteSQL と集計フィールドとで大差ない。
・#19 で集計計算を増やした場合に計算時間が長くなったのは、集計計算自体の方法やアルゴリズムが関係しているのではなく、
単純に、計算式を増やした分だけの計算量が増えたから。
## 「計算フィールド」に関係する経緯
初期のご回答 #3〜#6 からは、計算フィールドに ExecuteSQL を入れるものだと解釈していました。
その後、#6 で、スクリプトでフィールド設定を使うという後日案を挙げていただきました。
今、#17 で作っていただいたサンプルにおいて、スクリプトのフィールド設定での計算式に入れてある ExecuteSQL（#6 の後日案に対応）を、
結果を入れる対象の数値フィールドを計算フィールドに変更し、そこで定義できる計算式に入れてみると（#3 の「計算フィールド」の解釈）、
やはり、とんでもなく計算時間がかかることを確認しました。（ここでやったことは、今記述した字義通りのことだけです。）
これが、#4 で私がちょっと確認していた状況でした。
そのことがおそらく頭に残っていて、さらにそれまで、数値フィールドに結果を入れる方法を含めて、
当初の SQL ではないやり方のほうで似たことを複数試して簡単な方法を見つけられずに本件を質問していたことも重なり、
フィールドにではなく、スクリプトのフィールド設定の計算式に SQL を書くということになかなか気づけませんでした。
（あと、くだらない混乱ですが、FileMaker に慣れてないもので、「フィールド設定」が、「フィールドの設定」ではなく、スクリプトの名称だということに、
必ずしも明瞭に意識が向くほど FileMaker のインターフェイスとの関係で見えていなかったということも、今思い返せばあるような気がします。
そういう機能を使い始めたのもこの一連の検討で初めてなので。）
ちなみに、計算フィールドに SQL を書いた場合、とんでもなく計算時間がかかる挙動からすると、
レコードごとに SQL が実行されてしまうのではないかと想像しています。
このことから、この場合には、SUM を実行する複数の計算フィールド間でレコードの組合せの数だけ計算が生じてしまっているのだろうと想像しました。
メカニズムに関するその真偽はともかく、少なくとも、ExecuteSQL を計算フィールドで実行させるのはだめだという理解に現在至っています。
そうやってしまうと数式から期待する計算回数とはまったく異なる挙動を示すため、入れ子構造特有の何かがあるのだろうと考えていました。
その点は今は、スクリプトのフィールド設定で計算させれば数式通りの計算回数になると理解しています。
横からですが、計算が遅くなっている原因を、完全に誤解していらっしゃいます。
最大の原因は、未保存、未索引の計算フィールドの多用です。ざっと計算して、1集計ごとに数万回のフィールド計算を行っており、それを10回ほど繰り返していますので、数十万回の計算が発生しています。しかも、その7割くらいは同じ計算を繰り返しています。
計算の元値を計算するテーブル側にインポートし、可能な限り索引ありに変更するだけで数倍早くなりましたし、計算フィールドを全て内部変数化したオンメモリーの計算に置き換えると、さらに数倍早くなりました。
実際にはさらに面倒な式だと思いますが、此の改善だけで高速化はかなりできそうです。考えてみられては。
追加です、定数として持たせているグローバルフィールドも、計算対象のテーブルにフィールドとして値を持たせておきます。
Last edited by Shin (2020-05-04 02:35:22)
Offline
仕様変更に伴い、最終結果はレコードごと固有値となり、各数字フィールドに個別書出へ変更しています。
#16: Σj{Σi(d1×d2)×d3} → Σj{Σi(d1)×d2}×d3
・修正サンプル「SQL集計v2.fmp12」→ https://1drv.ms/u/s!AlaCGhTKTWEOp23UWX8 … i?e=CotBTS
#17: Σn{・・・Σj{Σi(d1×d2)×d3}・・・}xd18} → Σm{・・・Σj{Σi(d1)×d2}・・・}x d18
・修正サンプル「多重連装SQL集計v2.fmp12」→ https://1drv.ms/u/s!AlaCGhTKTWEOp2rlfX_ … 5?e=dCTnPF
Last edited by Hiro (2020-05-12 15:08:23)
Offline
>ちなみに、計算フィールドに SQL を書いた場合、とんでもなく計算時間がかかる挙動からすると、
>レコードごとに SQL が実行されてしまうのではないかと想像しています。
それが最初に私が書いた点で、SQL関数を使うかどうかにかかわらず、計算フィールドは当然全レコードで計算されます。
（ExecuteSQLが導入された当初に私自身がやった失敗です。フィールド定義は確定して計算が始まってからはキャンセルできないので、強制終了させる羽目に...）
レコードごとに必要な計算に使う場合は問題ない（というか仕方ない）ですが、集計ではなくルックアップのようなケースでしょう。
数式は、そこで説明したかった 2 種類の計算式の形の違いが分かるように形式化したもので、d1, d2, d3 は必ずしも数値ではありません。
そのままの d1, d2, d3 という 3 つの数値と解釈してもかまいませんが、実際は、どちらかというと計算式です。
その計算式には、単純に数値の掛け算もありますが、四則演算、べき乗や平方根に加え、
割り算でゼロ割を避けたり UI でユーザー入力する数値に従って計算式を切り替えたりするための If 文があったりします。
ですから、実際にはその面が多くなるにつれて計算に時間がかかっていくことになっているかと思っています。
集計速度は、データ値が、入力値か、計算値か、ルックアップ値か、集計値か、などは指して問題ありません。
真の速度貢献は、フィールド索引の有無の点です。（何故かは考えてみて下さい！！）
非索引データは、「索引データへの持替え」など工夫前処理が必須でしょう。
※この「索引データへの持替え」創意工夫の事例は、前記サンプルファイルの初期設定スクリプトでも見て取れます。
作例では、ランダム計算式で得た一過性の乱数値を、各データフィールドに代入索引保存し、初期値セットアップしています。
Last edited by Hiro (2020-05-02 13:01:56)
Offline
[ Generated in 0.007 seconds, 9 queries executed - Memory usage: 662.65 KiB (Peak: 715.55 KiB) ]