拡張SQLを利用して集計ページを作成する

はじめに

Pleasanterはデータベースとして非常に便利なのですが、集計機能が少々弱いように感じます。
特にユーザごとに登録されたデータから、集計・平均などを一覧表示するような用途にはほぼ対応できません。
今回は拡張SQLを使って、多少無理やりですが集計ページを作成してみたいと思います。

拡張SQL

拡張SQLは、Pleasanterの機能の中でも特殊なもので、情報もあまり見つけられません。基本的にデータの更新に使用することが想定されているようですが、表示にも利用できます。
多少手間ではありますがサーバスクリプトより高速に稼働することが多く、かつ特殊な条件での表示を実現することが可能です。
なお拡張SQLの設定を間違うと、画面が表示できなくなるなどの問題が発生する可能性があるため、作業は注意深く行ってください。

データについて

今回は、テストの成績がユーザごとに複数登録されている状態で、その合計・平均・順位を表示することを想定します。
具体的には以下の状態です。

• 成績テーブル
  

集計を表示するテーブル

事前に、ユーザ名(名前)・合計・平均・順位を表示できるよう、ユーザごとにレコードを作成します。
具体的には以下の通りです。

JSONファイル

拡張SQLは、1つのJSONファイル内に設定とSQLを併記することもできますが、わかりづらいので今回はSQLと設定を分離(APIを使用する)する方法で作成します。
なお複数のカラムをまとめて指定することはできないので、表示するカラム1つにつき、1セットのJSONファイルを作成する必要があります。今回の場合は3つのカラムを表示する必要があるため、3セットのJSONを作成します。

設定ファイル

• sumGrades.json

{
    "Name": "sumGrades",
    "ColumnList": ["NumA"],
    "SiteIdList": [56119],
    "OnSelectingColumn": true,
    "Api": true
}

• averageGrades.json

{
    "Name": "averageGrades",
    "ColumnList": ["NumB"],
    "SiteIdList": [56119],
    "OnSelectingColumn": true,
    "Api": true
}

• rankGrades.json

{
    "Name": "rankGrades",
    "ColumnList": ["NumC"],
    "SiteIdList": [56119],
    "OnSelectingColumn": true,
    "Api": true
}

SQLファイル

• sumGrades.json.sql

SELECT sumScore
FROM (
    SELECT
	    userCd,
	    ROUND(SUM(score), 1) as sumScore
    FROM (
    SELECT 
	    ClassA as userCd,
	    NumA as score
        FROM [Results] base
        WHERE SiteId = 56118 -- 個人成績テーブルのID
    ) personalScore
    Group By userCd
) totalTable
WHERE totalTable.userCd = [Results].[ClassA]

• averageGrades.json.sql

SELECT averageScore
FROM (
    SELECT
	    userCd,
	    AVG(Convert(decimal, score)) as averageScore
    FROM (
    SELECT 
	    ClassA as userCd,
	    NumA as score
        FROM [Results] base
        WHERE SiteId = 56118 -- 個人成績テーブルのID
    ) personalScore
    Group By userCd
) totalTable
WHERE totalTable.userCd = [Results].[ClassA]

• rankGrades.json.sql

SELECT CONVERT(decimal, ranks)
FROM (
    SELECT
	    userCd,
	    RANK() OVER (ORDER BY score DESC) as ranks
    FROM (
		SELECT 
			ClassA as userCd,
			SUM(NumA) as score
        FROM [Results] base
        WHERE SiteId = 56118 -- 個人成績テーブルのID
		Group By ClassA
    ) personalScore
) totalTable
WHERE totalTable.userCd = [Results].[ClassA]

Numはdecimalに変換するか、Roundで丸めたりしないとエラーが出ます

ファイルの作成・配置が完了したら、サーバーを再起動します。

画面表示

それでは、実際に画面を表示してみましょう。
以下のような表示になると思われます。

ちなみに、この方法で表示するとソートがうまく機能しません。

まとめ

今回は、拡張SQLを利用して集計ページを作ってみました。
Pleasanterの集計はあまり強くないので、このような方法で表示できると便利になるのではないかと思います(表示が重くはなりますが)。
今後、別の方法で集計ページを作る方法も掲載する予定ですので、よろしければ使ってみてください。