如何编写SQL查询筛选近1个月内新增或迁移的资产？

现有assets表，表结构如下：

CREATE TABLE assets
(
   id int,
   location_id int,
   updated_at timestamp
);

该表记录资产的地点变更历史，同一资产同一时间仅处于一个地点。示例数据：

INSERT INTO assets VALUES
(1,1,'2022-08-01'),
(1,2,'2022-09-01'),
(1,3,'2022-10-01'),
(2,1,'2022-01-01'),
(3,2,'2022-10-05'),
(4,1,'2022-01-01'),
(4,2,'2022-02-01');

需要编写SQL查询，返回近一个月内新部署的资产或近一个月内发生迁移的资产。针对示例数据，预期结果为：

• (1)--资产1在近一个月内发生迁移
• (3)--资产3在近一个月内新部署

可以通过窗口函数标记每个资产的首次和最新变更记录，再筛选符合时间条件的资产：

WITH asset_changes AS (
    SELECT
        id,
        updated_at,
        -- 按资产分组，时间升序标记首次部署记录
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at ASC) AS rn_asc,
        -- 按资产分组，时间降序标记最新变更记录
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn_desc
    FROM assets
)
SELECT DISTINCT
    id,
    CASE
        WHEN rn_asc = 1 AND updated_at >= CURRENT_TIMESTAMP - INTERVAL '1 month' THEN '资产' || id || '在近一个月内新部署'
        WHEN rn_desc = 1 AND rn_asc != 1 AND updated_at >= CURRENT_TIMESTAMP - INTERVAL '1 month' THEN '资产' || id || '在近一个月内发生迁移'
    END AS status
FROM asset_changes
WHERE
    (rn_asc = 1 AND updated_at >= CURRENT_TIMESTAMP - INTERVAL '1 month')
    OR (rn_desc = 1 AND rn_asc != 1 AND updated_at >= CURRENT_TIMESTAMP - INTERVAL '1 month');

1. 窗口函数作用：
   • rn_asc：给每个资产的变更记录按时间从早到晚编号，rn_asc=1代表该资产的首次部署记录。
   • rn_desc：给每个资产的变更记录按时间从晚到早编号，rn_desc=1代表该资产的最新变更记录。
2. 筛选逻辑：
   • 新部署：首次部署时间在近一个月内的资产。
   • 迁移：最新变更时间在近一个月内，且存在至少两次地点变更的资产。
3. 数据库兼容性：CURRENT_TIMESTAMP - INTERVAL '1 month'是标准SQL写法，不同数据库可按需调整（比如MySQL用DATE_SUB(NOW(), INTERVAL 1 MONTH)）。

针对示例数据（假设当前时间为2022-10-06），查询会返回符合预期的结果。

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