SQL查询每日玩具总量遇ROW_NUMBER()函数位置错误问题求解

• 核心报错原因：SQL执行逻辑限制，同一SELECT子句中定义的别名（包括窗口函数生成的row_number、自定义的start_date_time）不能在同层SELECT的其他字段表达式中直接引用。你在计算start_date_time时直接调用了刚定义的row_number，计算end_date_time时又直接调用了刚定义的start_date_time，不符合SQL解析规则，因此触发了窗口函数位置错误的提示。
• 潜在兼容问题：UDFcount_toys的入参定义为VARCHAR(25)，但调用时传入的是timestamp类型的end_date_time，存在隐式转换风险，可能导致日期匹配异常。

方案1：调整查询结构+优化UDF参数 #

首先修改UDF入参类型，避免隐式转换问题：

create or replace function count_toys
    (end_date_time timestamp) 
returns number(30,0)  
as 
     $$    
     select sum(count) 
     from 
         (select distinct 
              name, 
              last_value(number_toys) over (partition by name 
                                            order by created_at asc) as count 
          from toys 
          where created_at <= end_date_time) 
$$

再调整查询逻辑，把日期序列生成放到子查询中预先计算完成，外层再调用UDF即可：

select 
    row_number,
    start_date_time,
    end_date_time,
    count_toys(end_date_time) as total_toys
from (
    select
        row_number() over (order by null) as row_number,
        dateadd(day, row_number - 1,  '2021-11-11T00:00:00.000Z') start_date_time,
        dateadd(day, 1, start_date_time) as end_date_time
    from table (generator(rowcount => 3))
) t
order by start_date_time

方案2：无需UDF的更高效实现 #

逐行调用UDF性能较差，可直接通过日期关联+窗口函数批量计算，执行效率更高：

with date_list as (
    select 
        dateadd(day, row_number() over (order by null) -1, '2021-11-11T00:00:00.000Z') as end_date
    from table(generator(rowcount =>3))
),
toy_daily_last as (
    select 
        d.end_date,
        t.name,
        last_value(t.number_toys) over(partition by d.end_date, t.name order by t.created_at) as day_last_num
    from date_list d
    left join toys t on t.created_at <= d.end_date
)
select 
    end_date as start_date_time,
    dateadd(day,1,end_date) as end_date_time,
    sum(distinct day_last_num) as total_toys
from toy_daily_last
group by end_date
order by end_date

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