【SQL问题】 JOIN时LAG的变量被0值填充

由bqv93dy2创建，最终由bqv93dy2更新于2025-11-27 03:11 被浏览 1 用户

这是一个很严重的问题。

这段代码采用lag方式偏移获得的变量被0填充了一部分，如果是lag(1),则只有1个有效值。

%%sql
SELECT 
        a.date,
        a.instrument,
        a.name AS stock_name,
        a.close / m_lag(a.close, 2, ob := a.date, pb := a.instrument) - 1 AS stock_return2d,
        a.change_ratio as stock_ret1d,
    FROM cn_stock_prefactors a
    JOIN cn_stock_index_concept_component b 
        ON a.date = b.date AND a.instrument = b.member_code
    WHERE a.date > '2025-11-20' and a.name='江河集团'
    QUALIFY stock_return2d is NOT NULL
    ORDER BY a.date DESC,stock_return2d ASC
    LIMIT 5

下面这段代码一切正常

%%sql
with stock_data as (
    SELECT 
        a.date,
        a.instrument,
        a.name AS stock_name,
        a.close / m_lag(a.close, 2, ob := a.date, pb := a.instrument) - 1 AS stock_return2d,
        a.change_ratio as stock_ret1d,
    FROM cn_stock_prefactors a
    WHERE a.date > '2025-11-20' and a.name='江河集团'
    QUALIFY stock_return2d is NOT NULL
)
select * 
from stock_data a
JOIN cn_stock_index_concept_component b 
        ON a.date = b.date AND a.instrument = b.member_code
ORDER BY a.date DESC,stock_return2d ASC