SQL查询如何简便排除出现次数小于2的device_item_id对应记录

你可以通过公用表表达式（CTE）+ 窗口函数的方式实现需求，不需要重复编写原有查询逻辑，代码简洁易维护。

完整改写后的SQL如下：

WITH original_result AS (
    -- 直接复用你原有的完整查询逻辑
    SELECT 
           c.device_item_id
         , DATEDIFF(day, (DATE(c.tstmp)), sysdate) AS day
         , (DATE(c.tstmp)) AS date
         , COUNT(c.val) AS count
    FROM (
      SELECT 
          b.device_item_id
        , TO_TIMESTAMPTZ(a.tstamp) AT TIME ZONE 'UTC' AS tstmp
        , MAX(a.im_utilization) AS val
        FROM physical_memstats_rate AS a
        INNER JOIN v_poll_item AS b ON a.item_id = b.item_id
        WHERE b.device_item_id IN (
            SELECT item_id
            FROM v_poll_item
            WHERE device_item_id IN (
                SELECT member_item_id
                FROM etl_group_membership
                WHERE group_item_id = 335640
                )
            )
        -- 注意：你原有逻辑的时间范围写反了，1624233600 > 1623801599会导致查询结果为空，已调整顺序
        AND a.tstamp > 1623801600 AND a.tstamp <= 1624233599
        GROUP BY 1, 2 ORDER BY 1, 2 DESC
        ) AS c
    WHERE c.val > 48
    GROUP BY 1, 2, 3
    HAVING COUNT(c.val) > 12
)
SELECT device_item_id, day, date, count
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY device_item_id) AS total_appear
    FROM original_result
) t
WHERE total_appear >= 2
ORDER BY date, device_item_id DESC;

逻辑说明 #

• 用CTE original_result 存储原有查询的全部结果，不需要修改原有查询的任何逻辑
• 窗口函数COUNT(*) OVER (PARTITION BY device_item_id) 会统计每个device_item_id在全量结果里的总出现次数，不会改变原有结果的行数和列值
• 最后过滤总出现次数≥2的记录即可，完全保留原有输出的四列内容
• 针对你给出的示例结果，该查询会自动过滤掉device_item_id=298711的所有记录，其余记录全部保留。

内容的提问来源于stack exchange，提问作者curious_brain