こんにちは、管理人の@Salesforce.Zです。
今回、集計関数について、使用方法とその制限および対策をメモします^^
★ 集計関数使用時に制限接触の対策
目次
- 集計関数
- リファレンス
- この時、ガバナ制限の対象はGroup Byする前のレコードの総数となります。
- Number of query rows: 53000 out of 50000 ***** CLOSE TO LIMIT エラー
集計関数
AggregateResultは集計関数のオブジェクト名である
使う時に返り値はこのAggregateResultのリストになる
使用方法
Select Name , MAX(CreatedDate) From Account Group By Name
Select Name , MAX(請求金額) From Account Group By Name
Select Name , MAX(支払金額) From Account Group By Name
Select Name , MAX(契約数) From Account Group By Name
SalescforceはCRMが世界1だから、顧客ごとの管理が多いではないか
そこで、お客様ごとの請求金額合計や支払合計金額などを集計する時に使えるのがこの集計関数、簡単にできるですよ
制限
しかし、便利でも、制限があり、それは50,000(5万件)制限を超えると Number of query rows: 53000 out of 50000 ***** CLOSE TO LIMIT エラーが発生するんです。
制限の対策
条件付きに集計する
クエリに条件を付けて、対象データを絞るのが一番いいかもね
エリアごとに集計する・担当者ごとに集計する・国ごとに集計する・規模で絞るなど
注意: クエリにlimit 50000をつけたら、いいやんと思う子がいるかも
実は、効かないというか
1.ガバナ制限はGroup Byする前のレコードの総数を制限している 2.limitとGroup Byは Group Byが先に処理され、group byの前の総数は50000を超えた場合、制限に接触し その後にlimitはまだ実行されないところ、ガバナ制限になってしまうんです。
だから、limit 50000をつけても、エラーになる!!!
AJAX ToolKitで対応する
条件つけて、5万件をこえる場合、AJAX ToolKitを使うしかないですね。
下記は一部抜粋
<apex:page> <script src="../../soap/ajax/46.0/connection.js" type="text/javascript"></script> <script> sforce.connection.sessionId='{!GETSESSIONID()}'; var result = sforce.connection.query("select name, id from account"); var queryMore = true; while (queryMore) { var records = result.getArray("records"); for (var i = 0; i < records.length; i++) { //process records[i] } if (result.getBoolean("done")) { queryMore = false; } else { result = sforce.connection.queryMore(result.queryLocator); } } </script> ... </apex:page>
リファレンス
公式サイトのリンクはごろごろ変わるから、引用でもってきます。
説明
List<AggregateResult> a = new List<AggregateResult>(); a = [Select max(key__c) key, count(ID) cnt From Obj__c Where key__c = 'A001' group by key__c];
上記のようなSOQLを実行した場合に対象データが50001件以上あった際は、 取得可能なレコード数のガバナを超えてしまい、データ取得することができません。
しかし、limit 50000 をSOQLにつけたとしても、group byが先に効いてしまい 取得レコード数が1と認識されているようでリミットに当たらずにエラーになってしまいます。 上記のような場合に、エラーを出さずに処理するにはどのようにしたらよいでしょうか。
解決策
現状、Salesforceでは、 Where句の条件を含むSOQLにより取得されるレコードに対して、Group By を適用する形になります。
この時、ガバナ制限の対象はGroup Byする前のレコードの総数となります。
keyc : A2000 というレコードを10000件 keyc : B5000 というレコードを53000件
上記の様にレコードが格納された項目がある場合
List<AggregateResult> a = new List<AggregateResult>(); a = [select key__c, count(ID) from obj__c where key__c = 'A2000' group by key__c limit 50000];
↑↑↑ 正常に実行可能
List<AggregateResult> a = new List<AggregateResult>(); a = [select key__c, count(ID) from obj__c where key__c = 'B5000' group by key__c limit 50000];
↑↑↑
Number of query rows: 53000 out of 50000 ***** CLOSE TO LIMIT エラー
そのため、SOQLのみを使用しての回避はできません。
回避策としては、下記の2つが考えられます。
- Visualforceページに設置したjavascriptより Ajax Toolkitを利用して、Webservice API経由でデータを取得する。 Webservice API 経由であれば、上記ガバナ制限に抵触することなくデータ件数の取得が可能です。
下記サンプルとなります。 http://www.salesforce.com/us/developer/docs/ajax/Content/sforce_api_ajax_embedding.htm?SearchType=Stem&Highlight=SOQL
- データ作成日時をWhere句に含め、SOQLの取得件数を50000件以下にし SOQLを複数に分け、抽出した結果を変数に格納し、足しあわせて出力する。
データ内容にもよるかと思われますが、50000件のデータの作成日や最終更新日等の差異を利用し Where句条件に日付指定を入れていただき、抽出件数を50000件以下にします。 それらの結果をAPEXクラス上の変数に格納し、APEXクラス上で足し合わせる事で ガバナ制限に抵触することなく、同様の結果を取得いただけます。