私たちのワークフローは、グループに割り当て、その後（通常は）グループが個人に割り当てるというもので、私はそれに関するレポートを作成しています。
その結果、次のようなものになっています。
理想的には、それらの NULL ユーザー値を '' に COALESCE できるはずです。ただし、それは文字列であり、選択されたのは整数（user_id）なので、エラーが発生します。それを 0 に COALESCE すると、見た目は同じように悪くなりますが、別の方法でそうなります。
誰かこれをマスターした人はいますか？
これはかなりトリッキーなクエリですね。データエクスプローラーには、おそらく「ニンジャ」HTML機能が組み込まれていると思われます。そのため、次のようなものを使用することになります。
case when group_id IS NOT NULL
then 'http://abc/groups/` || group_id::text
else 'http://abc/user/` || user_id::text
end
そして、そこからリンクを構築します。グループ/ユーザーの名前をハイパーリンクなしで1つの列に表示したいだけであれば、もう少し簡単です。
現在のSQLは何ですか？
ユーザー（利用可能な場合）とグループの両方を表示したいです。また、IDから名前をきれいにフォーマットしてリンクしてくれるninja HTMLが非常に気に入っています。
そのため、手動で構築するのは、特に機能させるために追加のテーブルを結合する必要があることを考えると、私にとっては価値がありません。
しかし、問題のレポートは次のとおりです。
-- 目標：古い割り当てを表示する
-- 古いとは、割り当てから7日経過したものを指す
-- 関連項目：グループの古い割り当て
WITH
-- 会社で働くユーザーを見つける
SonarSourcers AS (
SELECT u.id AS user_id
FROM groups g
INNER JOIN group_users gu ON g.id=gu.group_id
INNER JOIN users u ON u.id = gu.user_id
WHERE g.name='sonarsourcers'
),
-- 関心のあるグループを見つける
teams AS (
SELECT id as group_id
FROM groups g
WHERE g.id not in (10, 11, 12, 13, 14 -- 信頼レベルグループ
, 1, 2, 3 -- ビルトイングループ
, 41 -- SonarSourcers
, 47 -- SonarCloud - スカッドを使用したい
, 53 -- .NET Scanner Guild
, 0 -- 「全員」...？
, 65, 66, 67 -- CFamサブグループ
)
),
-- 各SonarSourcerのプライマリチームを見つける
user_team AS (
SELECT distinct on (user_id) -- 一部のユーザーは2つのグループを持っています。1つに絞ります（任意）
ss.user_id, t.group_id
FROM SonarSourcers ss
JOIN group_users gu on gu.user_id=ss.user_id
JOIN teams t on t.group_id = gu.group_id
),
-- 割り当てられたトピックを見つける
-- 閉じられても解決もされていないもの
-- 7日以上前に割り当てられたもの
assigned_topics AS (
SELECT a.topic_id
, a.updated_at
, CASE WHEN assigned_to_type = 'User' THEN assigned_to_id END AS user_id
, CASE WHEN assigned_to_type = 'Group' THEN assigned_to_id ELSE user_team.group_id END AS group_id
FROM assignments a
JOIN topics t ON a.topic_id = t.id
LEFT JOIN user_team ON user_team.user_id=assigned_to_id
-- 閉じられたトピックを削除 - パート1
JOIN posts p on p.topic_id = t.id
LEFT JOIN post_custom_fields pcf ON pcf.post_id=p.id AND pcf.name='is_accepted_answer'
WHERE active = true
AND a.updated_at < current_date - INTERVAL '7 days'
AND t.closed = false -- 閉じられたトピックを削除 - パート2
AND pcf.id IS NULL -- 解決されたトピックを削除
GROUP BY a.topic_id, a.updated_at, assigned_to_type, assigned_to_id, user_team.group_id, t.updated_at
ORDER BY t.updated_at asc
),
-- 各割り当てられたトピックの最後の公開投稿を見つける
-- グループメンバーが最後に投稿したトピックを除外するために使用します
last_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
WHERE post_type = 1 -- 通常
GROUP BY p.topic_id
),
-- 各割り当てられたグループの最後の公開投稿を見つける
-- 実際に返信されているものを除外するために使用します
last_group_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
, max(created_at) as last_public_post
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
JOIN user_team on user_team.user_id=p.user_id
WHERE post_type = 1 -- 通常
GROUP BY p.topic_id
),
stale AS (
SELECT COUNT(lp.topic_id) AS stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- SonarSourcers以外
OR user_team.group_id != at.group_id)
AND lgp.last_public_post <= current_date - INTERVAL '7 days' -- 最後の投稿が今日から7日以上前
AND lgp.last_public_post > current_date - INTERVAL '14 days' -- 最後の投稿が14日前より最近
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
super_stale AS (
SELECT COUNT(lp.topic_id) as super_stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- SonarSourcers以外
OR user_team.group_id != at.group_id)
AND (lgp.last_public_post <= current_date - INTERVAL '14 days'
OR lgp.last_public_post IS NULL)
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
aggregated AS (
SELECT COALESCE(s.user_id,ss.user_id) AS user_id
, COALESCE(s.group_id,ss.group_id) AS group_id
, COALESCE(stale,0) AS stale
, COALESCE(super_stale,0) AS super_stale
FROM stale s
FULL JOIN super_stale ss using (user_id)
)
-- この時点で、一部のチームにはまだ2つの行があります（user_idがnullの行）。
-- グループ化と集計を使用して行を折りたたみ、nullを削除します。
-- また、「欠落している」チームを再追加します。
SELECT user_id
, group_id
, COALESCE(MAX(stale),0) AS "Stale (7d)"
, COALESCE(MAX(super_stale),0) AS "Super stale (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super stale (14d)" desc, "Stale (7d)" desc