PostgreSQL查询指定时间段内及之前已安装未卸载的软件记录

要满足你的需求，我们需要结合窗口函数和子查询来筛选符合条件的记录，核心是区分3月内的安装记录和3月前已安装且未在3月前卸载、同时在3月期间仍有安装状态的记录。以下是可行的查询语句：

WITH latest_pre_march_status AS (
    -- 获取每个软件在2024年3月1日之前的最后一条状态记录
    SELECT
        id,
        time,
        is_installed,
        software_name
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY software_name ORDER BY time DESC) AS rn
        FROM my_table
        WHERE time < '2024-03-01'
    ) sub_query
    WHERE rn = 1 AND is_installed = true
),
eligible_softwares AS (
    -- 筛选符合条件的软件：3月内有安装记录，或3月前已安装且3月内未被卸载
    SELECT DISTINCT software_name
    FROM my_table
    WHERE time >= '2024-03-01' AND time < '2024-04-01' AND is_installed = true
    UNION
    SELECT software_name
    FROM latest_pre_march_status
    WHERE NOT EXISTS (
        SELECT 1
        FROM my_table t
        WHERE t.software_name = latest_pre_march_status.software_name
          AND t.time >= '2024-03-01' AND t.time < '2024-04-01'
          AND t.is_installed = false
    )
)
-- 合并两部分结果：符合条件的3月前安装记录 + 3月内安装记录
SELECT * FROM latest_pre_march_status WHERE software_name IN (SELECT software_name FROM eligible_softwares)
UNION ALL
SELECT * FROM my_table WHERE time >= '2024-03-01' AND time < '2024-04-01' AND is_installed = true
ORDER BY id;

语句解释： #

1. latest_pre_march_status CTE：

   • 用ROW_NUMBER()窗口函数按软件分组，取每个软件在3月1日之前的最后一条记录，只保留安装状态（is_installed = true）的记录，这就是“3月前已安装且未在3月前卸载”的候选记录。

2. eligible_softwares CTE：

   • 第一部分直接筛选3月内有安装记录的软件；
   • 第二部分筛选“3月前已安装且3月内未被卸载”的软件（即3月前最后状态是安装，且3月内没有卸载记录）；
   • 用UNION去重得到所有符合条件的软件名单。

3. 最终查询：

   • 从候选的3月前安装记录中筛选出属于符合条件软件的记录；
   • 合并3月内的安装记录，按id排序得到结果。

结果验证： #

执行后会得到你需要的记录：id=1（3月前安装且3月期间有后续安装）、id=4（3月内安装）、id=5（3月内安装），同时排除了id=6（3月前安装但3月内被卸载且无后续安装）等不符合条件的记录。

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