null id를 깔끔하게 처리하는 방법

커뮤니티 구축 데이터 및 보고
1

저희 워크플로우는 먼저 그룹에 할당하고, 그 다음(보통) 그룹에서 개인에게 할당하는 방식입니다. 그리고 저는 이 부분에 대한 보고서를 작성하고 있습니다.

결국 이런 결과를 얻게 되네요:

Selection_822
Selection_822633×474 13.8 KB

이상적으로는 NULL인 사용자 값을 ''로 COALESCE 처리할 수 있으면 좋겠습니다. 하지만 ''는 문자열이고, 선택된 값은 정수(user_id)라서 오류가 발생합니다. 0으로 COALESCE 처리하면 외형적으로는 비슷해 보이지만, 다른 방식으로 나빠집니다.

이 문제를 해결하신 분이 계신가요?

2

이 쿼리는 꽤 까다로운 것 같습니다. 데이터 탐색기 안에 어떤 닌자 HTML 기능이 있는 것 같아서, 기본적으로 다음과 같은 것을 사용하게 될 것입니다:

case when group_id IS NOT NULL
   then 'http://abc/groups/` || group_id::text
   else 'http://abc/user/` || user_id::text
end

그리고 나서 그 값으로 링크를 구성하면 됩니다. 하이퍼링크 없이 그룹/사용자 이름만 한 열에 표시하려는 경우라면 조금 더 쉽습니다.

현재 가지고 있는 SQL은 무엇인가요?

3

사용자(가용할 경우)와 그룹을 모두 표시하고 싶습니다. 그리고 ID에서 잘 포맷되고 링크된 이름을 제공하는 그 닌자 같은 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 - INTEGER '7'
        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 - 7 -- 오늘보다 7일 이상 지난 마지막 게시글
        AND lgp.last_public_post > current_date - 14 -- 오늘보다 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 - 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)
)


-- 이 시점에서, 일부 팀에 대해 여전히 두 줄이 있습니다 (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