语法格式:row_number() over(partition by 分组列 order by 排序列 desc)
row_number() over()分组排序功能:
在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行
说白了就是给你的字段做下标
例:row_number() over(partition by a.passport_user_id order by a.business_time) rn
以用户id分区,用户交易时间排序,求每个用户的首投金额
其中分组字段和排序字段只要在from表中有就可以,不用显示查出来
用法示例:
select t.passport_user_id,t.mobile_no,t.login_date
from
(
SELECT u.passport_user_id,u.mobile_no,u.login_date,row_number() over(partition by u.passport_user_id order by u.login_date) rn
from biz_bigdata.ods_tyyh_user u
JOIN
(
SELECT DISTINCT passport_user_id
FROM biz_bigdata.ods_tyyh_business
WHERE business_line<>'YILIAN'
) a on u.passport_user_id=a.passport_user_id
join (
SELECT DISTINCT passport_user_id FROM fcmmid.mid_user_invest_behave_detail_info
) b on a.passport_user_id=b.passport_user_id
WHERE u.login_date>='2019-08-10'
)t
where rn=1
;
sum() over()函数使用实例:
SELECT
dd
,f_capital_no
,sum(t.f_payout_amount) over(partition by f_capital_no order by dd) f_payout_amount
---以产品为分区,按日期求累加
FROM
(
select
df.dd
,df.f_capital_no
,if(s.f_payout_amount is null,0,s.f_payout_amount) f_payout_amount
---补数(没有数据的日期补数为0)
from tmp.yzy_df df
left join
(
select
substr(f_pay_time,1,10) dd
,f_capital_no
,sum(f_payout_amount) f_payout_amount
---由于表中日期的值是时分秒的,故先求出每日和才能求累加
from odsdb_gmcf_lmc.t_lmc_payout_single
WHERE f_status='6' and f_capital_no like 'C51%'
group by substr(f_pay_time,1,10),f_capital_no
)s on df.dd=s.dd and df.f_capital_no=s.f_capital_no
)t