基于SQL聚合函数的日期范围过滤问题求助

我有一个大型数据库，行与个体相关联，一行或多行可归为同一事件，且一个个体可能拥有多个事件。我无法正确声明返回的数据项，也无法准确指定筛选的日期条件。我使用嵌入在R脚本中的SQL SELECT语句，但不确定这是否会有影响——我对SQL并不熟悉。

我需要按每个event_id获取以下去重值：

• individual、event_id
• 首个start_date
• 最后end_date
• 首个type
• 首个sample
• 最小age
  需通过sort1、sort2、sort3等排序字段确保提取首个记录的顺序正确。

我要对数据集应用以下过滤条件：

• 首个type在20-22或30-39之间
• 最小age≥65
• max(end_date)在'2019年4月1日'至'2020年3月31日'之间

数据集示例 #

预期结果 #

现有SQL语句 #

SELECT distinct individual, event_id,
            min(start_date) OVER (PARTITION BY individual, event_id) dstart,
            max(end_date) OVER (PARTITION BY individual, event_id) dend,
            min(age) OVER (PARTITION BY individual, event_id) age,
            FIRST_VALUE(sample) OVER (PARTITION BY individual, event_id ORDER BY individual, start_date, end_date, sort1, sort2,sort3) sample,
            FIRST_VALUE(type) OVER (PARTITION BY individual, event_id ORDER BY individual, start_date, end_date, sort1, sort2,sort3) adm_type
   FROM ANALYSIS.TABLE z
   WHERE
    exists (
      select * from ANALYSIS.TABLE where individual=z.individual and event_id=z.event_id
                                    and (type between '20' and '22' or type between '30' and '39'))
      AND AGE >64
     ORDER BY individual, event_id

你的核心问题是窗口函数的结果无法直接在WHERE子句中过滤，且现有逻辑的过滤条件不够精准（比如原WHERE里的AGE>64是针对单条记录的age，不是事件的最小age）。可以用CTE（公共表表达式）先计算出每个事件的所有聚合值和首值，再对CTE结果应用过滤条件，逻辑更清晰且能满足需求。

修改后的SQL如下：

WITH event_summary AS (
    SELECT 
        individual,
        event_id,
        MIN(start_date) OVER (PARTITION BY individual, event_id) AS start_date,
        MAX(end_date) OVER (PARTITION BY individual, event_id) AS end_date,
        MIN(age) OVER (PARTITION BY individual, event_id) AS min_age,
        FIRST_VALUE(type) OVER (
            PARTITION BY individual, event_id 
            ORDER BY sort1, sort2, sort3, start_date, end_date
        ) AS first_type,
        FIRST_VALUE(sample) OVER (
            PARTITION BY individual, event_id 
            ORDER BY sort1, sort2, sort3, start_date, end_date
        ) AS first_sample
    FROM ANALYSIS.TABLE
)
SELECT DISTINCT
    individual,
    event_id,
    start_date,
    end_date,
    first_type AS type,
    first_sample AS sample,
    min_age AS age
FROM event_summary
WHERE
    -- 首个type在指定范围
    (first_type BETWEEN 20 AND 22 OR first_type BETWEEN 30 AND 39)
    -- 最小age≥65
    AND min_age >= 65
    -- max(end_date)在2019-04-01到2020-03-31之间
    AND end_date BETWEEN '2019-04-01' AND '2020-03-31'
ORDER BY individual, event_id;

关键说明： #

1. CTE预计算聚合值：通过窗口函数一次性算出每个事件的首条记录（按sort1/sort2/sort3优先排序）、最小age、最早start_date、最晚end_date，确保首条记录顺序符合要求。
2. 精准过滤：
   • 直接用first_type判断范围，替代原EXISTS子查询（原EXISTS仅判断事件中存在符合type的记录，而非首条type符合）
   • 用min_age >=65替代原AGE>64，确保是事件的最小年龄满足条件
   • 直接用end_date（即事件的max(end_date)）判断日期范围，解决日期过滤问题
3. 去重处理：窗口函数会给每个事件的每条记录返回相同聚合结果，用DISTINCT确保每个事件仅返回一条记录。

日期格式注意： #

如果数据库不识别'2019-04-01'格式，可替换为对应数据库支持的格式（如示例中的'01/04/2019'），但需注意日期顺序（日/月/年或月/日/年），避免解析错误。

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