@icaria36 様の Discourse グループをサポートシステムとして活用する際の改善に取り組む過程で、いくつかのクエリを開発しました（今後も追加予定です）。問題点や改善点が見つかりましたら、お気軽にお知らせください 
サポートグループへの初回返信までの時間
- 一定期間の平均日数（例：2019-04-04 から 2019-04-06）
- 営業日のみカウント（土曜・日曜は除く）
- 指定された祝日はカウントしない（例：2019-04-11）
- 例でのグループ名は ‘support’
SELECT AVG(t.days)::float AS "初回返信までの平均営業日数"
FROM (
SELECT t.id, t.title, t.created_at, MIN(p.created_at) as "初回返信日時", (
SELECT count(*) FILTER (
WHERE d not in ('2019-04-11')
AND extract('ISODOW' FROM d) < 6
)
FROM generate_series(t.created_at::timestamp::date
, MIN(p.created_at)::timestamp::date
, interval '1 day') as s(d)
) as "日数"
FROM topics t
INNER JOIN posts p ON p.topic_id = t.id AND (
CASE WHEN p.post_number = 1
THEN p.via_email IS TRUE
ELSE true
END
)
WHERE t.archetype = 'private_message'
AND t.id IN (
SELECT topic_id FROM topic_allowed_groups
WHERE group_id IN (SELECT id FROM groups WHERE name ilike 'support')
)
AND t.deleted_at IS NULL
AND t.created_at::timestamp::date >= '2019-04-04'
AND t.created_at::timestamp::date <= '2019-04-06'
AND p.deleted_at IS NULL
AND p.post_number > 1
GROUP BY t.id
) t
クエリが正しく機能しているか確認するには、SELECT 句を平均値から以下のように変更してみてください。
t.days as "営業日数", t.title, t.created_at, t.first_reply_created_at
例えば、私の Sandbox でこれを実行すると以下のようになります。
注釈：
- 同じ日の返信を 0 日としてカウントしますか、それとも 1 日としてカウントしますか？（現在は 1 日としてカウントしています）
- 返信のないメッセージはどのように扱いますか？（つまり、除外すべきか、何日としてカウントすべきか）。現在は返信のないメッセージはカウントから除外されています。
特定のグループで受信したメッセージ数
- 例でのグループ名は ‘support’
- 一定期間のカウント（例：2019-04-04 から 2019-04-06）
SELECT count(t) as "メッセージ数"
FROM topics t
WHERE t.archetype = 'private_message'
AND t.id IN (
SELECT topic_id FROM topic_allowed_groups
WHERE group_id IN (SELECT id FROM groups WHERE name ilike 'support')
)
AND (
SELECT via_email FROM posts
WHERE topic_id = t.id AND post_number = 1
)
AND t.deleted_at IS NULL
AND t.created_at::timestamp >= '2019-04-04'::timestamp
AND t.created_at::timestamp <= '2019-04-06'::timestamp