sql笔记

--分析函数示例
select name,lesson,times,score,
       rank() over (partition by lesson,times order by score desc) as "RANK_L",             --排名（不连续）
       dense_rank() over (partition by lesson,times order by score desc) as "DRANK_L",      --排名（连续）
       row_number() over (partition by lesson,times order by score desc) as "ROWN_L",       --行号，非确定
       rank() over (partition by name,times order by score desc) as "RANK_P",               
       max(score) over (partition by lesson,times) as "Max RANK_L",                         --聚合，最大值
       sum(score) over (partition by NAME,times) as "RANK_SUM",                             --聚合，求和
       TRUNC(100*RATIO_TO_REPORT(score) OVER (partition by NAME,times),2) as "RATIO_S",     --当前值对SUM的百分比
       sum(score) over (partition by lesson,times) as "SUM_L",                              
       TRUNC(100*RATIO_TO_REPORT(score) OVER (partition by lesson,times),2) as "RATIO_L",   
       lag(score) over (partition by lesson,times order by score desc) as "Prev",           --引用前一行，前n行
       score - lag(score) over (partition by lesson,times order by score desc) as "D_Prev", 
       lead(score) over (partition by lesson,times order by score desc) as "Next",          --引用后一行，后n行
       score - lead(score) over (partition by lesson,times order by score desc) as "D_Next",
       PERCENT_RANK() OVER (partition by lesson,times order by SCORE desc) as "PCT_RANK_L",                     --排名百分比
       percentile_cont(0.8) within group (order by score desc) over (partition by lesson,times ) as "MID_PC",   --PERCENT_RANK反函数，在每一个分组中检查百分比排名的值并返回，如果没有精确匹配值，则取前后最近两个平均数
       percentile_disc(0.8) within group (order by score desc) over (partition by lesson,times ) as "MID_PD",   --PERCENT_RANK反函数，在每一个分组中检查百分比排名的值并返回，如果没有精确匹配值，则按排序取后一个值
       PERCENT_RANK() OVER (partition by lesson,times order by SCORE) as "PCT_RANK_L_A",
       percentile_disc(0.8) within group (order by score) over (partition by lesson,times ) as "MID_PD_A",   
       first_value(score) over (partition by lesson,times order by score desc rows between unbounded preceding and unbounded FOLLOWING) as "No1_Score",     --第1个值
       nth_value(score,2) over (partition by lesson,times order by score desc rows between unbounded preceding and unbounded following) as "2nd_Score",     --第N个值
       last_value(score) over (partition by lesson,times order by score desc rows between unbounded preceding and unbounded following) as "Last_Score",     --最后一个值
       ntile(3) over (partition by lesson,times order by score desc) as "RANK_NTILE",                   --分成N片，并返回分片序号
       listagg(name,'|') within group (order by score) over (partition by lesson,times) as LSG,    --行合并成一列（VARCHAR2）
       stddev(score) over (partition by lesson,times) as LSG        --标准差
       from t_analyze
       order by times,lesson ;

--省略窗口取值，默认窗口rows between unbounded preceding and current row     窗口第一行到当前行
--开窗语法
--rows between
--range between
--开窗示例
--sum(score) over (partition by NAME,times rows between unbounded preceding and unbounded following)      --SUM计算窗口范围：分组内所有取值
--sum(score) over (partition by NAME,times rows between unbounded preceding and unbounded following)      --SUM计算窗口范围：分组内第一行到当前行