Redshift中基于两张表按日期分组统计活跃员工数的问题

你遇到的Invalid operation: subquery in FROM may not refer to other relations of same query level错误，本质是FROM子句中的子查询无法引用同层级其他表的字段——你在dp子查询里用了dd.date_dt做过滤，这违反了SQL的层级引用规则。

不用UNION的话，有几种高效的替代方案，以下是最常用的两种：

方案1：利用状态生效区间关联（通用SQL） #

先给每个员工的状态记录标记出下一次状态变更的日期，这样每条记录对应的生效时间段就是[effective_date, next_effective_date)，关联日期表时只需判断日期落在该区间内即可：

SELECT 
    dd.date_dt AS the_date,
    COUNT(DISTINCT CASE WHEN et.is_active = 1 THEN et.login_id END) AS amount_of_employees
FROM 
    Date_Table dd
LEFT JOIN (
    SELECT 
        login_id,
        effective_date,
        is_active,
        -- 标记下一次状态变更的日期，无后续变更则用极大值（比如'9999-12-31'）
        LEAD(effective_date, 1, '9999-12-31') OVER (PARTITION BY login_id ORDER BY effective_date) AS next_effective_date
    FROM 
        Employee_table
) et ON 
    et.effective_date <= dd.date_dt
    AND dd.date_dt < et.next_effective_date
GROUP BY 
    dd.date_dt
ORDER BY 
    dd.date_dt ASC;

逻辑说明 #

1. 子查询里用LEAD窗口函数，给每个员工的每条状态记录找到下一次变更的日期，最后一条记录用9999-12-31表示永久生效；
2. 关联日期表时，判断当前日期是否在该状态的生效区间内；
3. 最后统计每个日期下is_active=1的员工数量。

方案2：使用LATERAL JOIN（支持的SQL引擎：PostgreSQL、Redshift等） #

如果你的SQL引擎支持LATERAL JOIN，可以直接对每个日期查询对应员工的最新状态，写法更直观：

SELECT 
    dd.date_dt AS the_date,
    COUNT(DISTINCT et.login_id) AS amount_of_employees
FROM 
    Date_Table dd
LEFT JOIN LATERAL (
    -- 对每个日期，查询员工在该日期前的最新状态
    SELECT login_id, is_active
    FROM Employee_table
    WHERE effective_date <= dd.date_dt
    ORDER BY effective_date DESC
    LIMIT 1
) et ON et.is_active = 1
GROUP BY 
    dd.date_dt
ORDER BY 
    dd.date_dt ASC;

逻辑说明 #

LATERAL JOIN允许子查询引用主查询的dd.date_dt字段，对每个日期单独查询每个员工的最新状态，然后筛选活跃员工统计数量，完全贴合你单日期查询的逻辑，但实现了批量统计。

两种方案都能返回你期望的结果：

内容的提问来源于stack exchange，提问作者Phil C.