Nosso fluxo de trabalho é atribuir a grupos e, em seguida (geralmente), os grupos atribuem a indivíduos, e estou trabalhando em relatórios sobre isso.
Acabo com coisas como esta:
Idealmente, eu seria capaz de usar COALESCE nesses valores de user NULL para ''. Exceto que isso é uma string, e o que foi selecionado foi um inteiro (user_id), então recebo um erro. Se eu usar COALESCE para 0, fica igualmente ruim, apenas de uma maneira diferente.
Alguém dominou isso?
Esta é uma consulta bastante complicada, acho que pode haver algum recurso ninja de HTML dentro do explorador de dados, então você basicamente usaria algo como:
case when group_id IS NOT NULL
then 'http://abc/groups/` || group_id::text
else 'http://abc/user/` || user_id::text
end
E então construiria um link a partir disso. Se você quiser apenas mostrar o nome do grupo / usuário em uma coluna sem que seja um hiperlink, é um pouco mais fácil.
Qual é o SQL que você tem agora?
Gostaria de mostrar tanto o usuário (quando disponível) quanto o grupo. E eu realmente gosto e aprecio o HMTL ninja que me dá um nome bem formatado e com link a partir de um ID.
Portanto, construir isso manualmente simplesmente não vale a pena para mim, especialmente porque eu teria que juntar tabelas extras para fazê-lo funcionar.
Mas aqui está o relatório em questão:
-- objetivo: mostrar atribuições obsoletas,
-- onde obsoleto é 7 dias desde a atribuição
-- VEJA TAMBÉM: Atribuições obsoletas para um grupo
WITH
-- encontrar os usuários que trabalham para a empresa
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'
),
-- encontrar grupos de interesse
teams AS (
SELECT id as group_id
FROM groups g
WHERE g.id not in (10, 11, 12, 13, 14 -- grupos de nível de confiança
, 1, 2, 3 -- grupos embutidos
, 41 -- SonarSourcers
, 47 -- SonarCloud - queremos os esquadrões em vez disso
, 53 -- .NET Scanner Guild
, 0 -- "todos"...?
, 65, 66, 67 -- subgrupos CFam
)
),
-- encontrar a equipe principal de cada SonarSourcer
user_team AS (
SELECT distinct on (user_id) -- alguns usuários têm 2 grupos. estreitar (arbitrariamente) para 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
),
-- encontrar tópicos atribuídos
-- que não estão nem fechados nem resolvidos
-- que foram atribuídos há mais de 7 dias
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
-- eliminar tópicos fechados - parte 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'
AND t.closed = false -- eliminar tópicos fechados - parte 2
AND pcf.id IS NULL -- eliminar tópicos resolvidos
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
),
-- encontrar a última postagem pública em cada tópico atribuído
-- usaremos isso para filtrar tópicos onde um membro do grupo foi o último a postar
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 -- regular
GROUP BY p.topic_id
),
-- encontrar a última postagem pública em cada tópico do grupo atribuído
-- usaremos isso para filtrar aqueles que estão realmente sendo respondidos
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 -- regular
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 -- não-SonarSourcers
OR user_team.group_id != at.group_id)
AND lgp.last_public_post <= current_date - INTERVAL '7' -- última postagem 7+ dias antes de hoje
AND lgp.last_public_post > current_date - INTERVAL '14' -- última postagem <14 dias antes de hoje
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 -- não-SonarSourcers
OR user_team.group_id != at.group_id)
AND (lgp.last_public_post <= current_date - INTERVAL '14'
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)
)
-- Neste ponto, ainda existem duas linhas para algumas equipes (linhas com user_id nulo).
-- Use agrupamento e agregação para colapsar linhas e eliminar nulos
-- Além disso, adicione de volta as equipes 'faltando'
SELECT user_id
, group_id
, COALESCE(MAX(stale),0) AS "Obsoleto (7d)"
, COALESCE(MAX(super_stale),0) AS "Super obsoleto (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "Super obsoleto (14d)" desc, "Obsoleto (7d)" desc