sql1:
select t.user_name user_name,
t.project_name as project_name,
t.create_ly_cc_time as ly_cc_time,
t.CC_TIME as CC_TIME,
lead(t.cc_time) over(order by t.cc_time desc) as CC_TIME,
status
from ly_cc t
order by t.cc_time desc
sql2:
select t.user_name user_name,
t.project_name as project_name,
t.create_ly_cc_time as ly_cc_time,
t.CC_TIME as CC_TIME,
lead(t.cc_time) over(partition by t.cc_time order by t.cc_time desc ) as next_cc_TIME,
status
from ly_cc t
order by t.cc_time desc
sql3 :
select t.user_name user_name,
t.project_name as project_name,
t.create_ly_cc_time as ly_cc_time,
t.CC_TIME as CC_TIME,
lead(t.cc_time) over( order by t.cc_time ) as next_cc_TIME,
status
from ly_cc t 3个sql都没有语法错误,但只有sql3 的 CC_TIME,next_cc_TIME有时间,其它sql这两值 为空(null),这是什么分析 函数效率高吗
select t.user_name user_name,
t.project_name as project_name,
t.create_ly_cc_time as ly_cc_time,
t.CC_TIME as CC_TIME,
lead(t.cc_time) over(order by t.cc_time desc) as CC_TIME,
status
from ly_cc t
order by t.cc_time desc
sql2:
select t.user_name user_name,
t.project_name as project_name,
t.create_ly_cc_time as ly_cc_time,
t.CC_TIME as CC_TIME,
lead(t.cc_time) over(partition by t.cc_time order by t.cc_time desc ) as next_cc_TIME,
status
from ly_cc t
order by t.cc_time desc
sql3 :
select t.user_name user_name,
t.project_name as project_name,
t.create_ly_cc_time as ly_cc_time,
t.CC_TIME as CC_TIME,
lead(t.cc_time) over( order by t.cc_time ) as next_cc_TIME,
status
from ly_cc t 3个sql都没有语法错误,但只有sql3 的 CC_TIME,next_cc_TIME有时间,其它sql这两值 为空(null),这是什么分析 函数效率高吗
具体问题具体分析,要看你在哪种查询中使用分析函数。比如你上面查询效率不错,但分页查询用分析函数效率是极低的。
我认为它是先排序外部,再排序内部,然后内部的排序没有起作用
order by t.cc_time desc
应该为
order by CC_TIME desc