如何妥善处理 null ID

数据与报告
1

我们的工作流程是将任务分配给组，然后（通常）由组分配给个人，我正在围绕这一点进行报告。

我最终得到了类似这样的结果：

Selection_822
Selection_822633×474 13.8 KB

理想情况下，我希望能够将那些 NULL 用户值 COALESCE 为 ''。但那是一个字符串，而选择的是一个整数（user_id），所以我得到了一个错误。如果我将其 COALESCE 为 0，看起来同样糟糕，只是方式不同。

有人掌握了这个技巧吗？

2 个赞
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

我想同时显示用户（如果可用）和组。我真的很喜欢并赞赏 ninja HMTL，它能从 ID 为我提供格式良好且链接的用户名称。

因此，手动构建它对我来说不值得，特别是由于我必须加入额外的表才能使其正常工作。

但这是有问题的报告：

-- 目标：显示过时的分配，
--    其中过时是指分配后 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 - 我们想要的是 squad
                    , 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 "过时 (7d)"
    , COALESCE(MAX(super_stale),0) AS "非常过时 (14d)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "非常过时 (14d)" desc, "过时 (7d)" desc