1父泳、lag() over()

lag(pay_succ_time, 1, '1990-01-01 00:00:00') over(partition by user_pin order by pay_succ_time)
注：取用戶(hù)的上一筆交易時(shí)間莫矗，若無(wú)上一筆交易（即本單為用戶(hù)首單），則令上一筆交易時(shí)間=1990-01-01 00:00:00（第三個(gè)參數(shù)不寫(xiě)時(shí)捷凄，默認(rèn)為 null）

拓展：年新用戶(hù)判定

select user_pin, ---用戶(hù)PIN
    pay_succ_time, --完成時(shí)間
    to_date(pay_succ_time) as dt
from
(
    select
    pay_succ_time, --完成時(shí)間
    user_pin, ---用戶(hù)PIN
    datediff(pay_succ_time,lag(pay_succ_time, 1, '1990-01-01 00:00:00') over(partition by user_pin order by pay_succ_time)) as dis_date
    from
    (
        select user_pin,pay_succ_time, --完成時(shí)間
            row_number() over(partition by trade_no order by pay_succ_time asc) as rn
        from db.table_nm
        where dt between date_sub('$TX_DATE', 730) and '$TX_DATE'
        and to_date(pay_succ_time) between date_sub('$TX_DATE', 730) and '$TX_DATE'
        and trim(nvl(user_pin,''))<>''
    )x
    where rn = 1
)t
where dis_date > 365

2凿叠、sum() over()俯逾、count() over()

-- 匯總每個(gè)用戶(hù)的交易額
sum(tx_amt) over(partition by user_pin))

-- 匯總每個(gè)用戶(hù)的訂單量
count(distinct ordr_num) over(partition by user_pin)

3、rank() over,dense_rank() over,row_number() over

rank() over：1 2 2 4
查出指定條件后的進(jìn)行排名匣吊。特點(diǎn)是儒拂，加入是對(duì)學(xué)生排名，使用這個(gè)函數(shù)色鸳，成績(jī)相同的兩名是并列社痛，下一位同學(xué)空出所占的名次。

select 
    name,subject,score
    ,rank() over(partition by subject order by score desc) rank
from student_score;
# output：1 2 2 4

dense_rank() over：1 2 2 3
與ran() over的區(qū)別是命雀，兩名學(xué)生的成績(jī)并列以后蒜哀，下一位同學(xué)并不空出所占的名次。

select 
    name,subject,score
    ,dense_rank() over(partition by subject order by score desc) rank
from student_score;
# output：1 2 2 3

row_number() over:1 2 3 4
這個(gè)函數(shù)不需要考慮是否并列吏砂，哪怕根據(jù)條件查詢(xún)出來(lái)的數(shù)值相同也會(huì)進(jìn)行連續(xù)排名

select 
    name,subject,score
    ,row_number() over(partition by subject order by score desc) rank
from student_score;
# output：1 2 3 4

使用rank() over的時(shí)候撵儿，空值是最大的，如果排序字段為null,可能造成null字段排在最前面狐血，影響排序結(jié)果淀歇。
可以這樣：rank() over(partition by course order by score desc nulls last)
來(lái)規(guī)避這個(gè)問(wèn)題。

select 
    name,subject,score
    ,rank() over(partition by subject order by score desc nulls last) rank
from student_score;

拓展：求連續(xù)最大天數(shù)

-- step1
use dev;
drop table dev.fin_user_fig_continue_hold;
create table dev.fin_user_fig_continue_hold as
select user_pin,count(flag) as continue_hold_cnt
from
(
    select
        user_pin
        ,(row_number() over(partition by user_pin order by dt)) - datediff(dt,start_date) as flag
    from dev.fin_user_fig_03
)t1
group by user_pin

-- step2
select
    count(case when max_continue_hold>=30 then user_pin end) as one_mth_pin_cnt
    ,count(case when max_continue_hold>=90 then user_pin end) as three_mth_pin_cnt
    ,count(case when max_continue_hold>=180 then user_pin end) as nine_mth_pin_cnt
    ,count(case when max_continue_hold>=360 then user_pin end) as one_year_pin_cnt
    ,(case when max_continue_hold>=30 then user_pin end)/count(user_pin) as one_mth_pin_rate
    ,count(case when max_continue_hold>=90 then user_pin end)/count(user_pin) as three_mth_pin_rate
    ,count(case when max_continue_hold>=180 then user_pin end)/count(user_pin) as nine_mth_pin_rate
    ,count(case when max_continue_hold>=360 then user_pin end)/count(user_pin) as one_year_pin_rate
from
(
    select user_pin,max(continue_hold_cnt) as max_continue_hold
    from dev.fin_user_fig_continue_hold
    group by user_pin
)t

拓展：求用戶(hù)首單便捷方法——利用named_struct

select
    pin as jd_pin
    ,struct1.orderid as jd_order_id
from
(
   select
        pin
        ,min(named_struct('consumerdate',consumerdate,'orderid',orderid)) as struct1
    from db.table_nm
    where dt='{TX_DATE}'
    group by pin
) m
group by pin,struct1.orderid