应对没完没了的报表开发 – esproc for reporting | 润乾 -pg游戏官网登录入口

sql

select customer, amount, sum_amount
	from (select customer, amount,
		sum(amount) over(order by amount desc) sum_amount
			from (select customer, sum(amount) amount
	   			from orders group by customer))
	   			where 2 * sum_amount < (select sum(amount) total from orders)

spl

sql

with first_login as ( 
    select userid, min(trunc(ts)) as first_login_date  from login_data group by userid),
next_day_login as (
    select distinct(fl.userid), fl.first_login_date, trunc(ld.ts) as next_day_login_date
    from first_login fl left join login_data ld on fl.userid = ld.userid where trunc(ld.ts) = fl.first_login_date   1),
day_new_users as (
    select first_login_date,count(*) as new_user_num from first_login group by first_login_date),
next_new_users as (
    select next_day_login_date, count(*) as next_user_num from next_day_login group by next_day_login_date),
all_date as (
    select distinct(trunc(ts)) as login_date from login_data)
select all_date.login_date 1 as dt,dn. new_user_num,nn. next_user_num,
      (case  when nn. next_day_login_date is null then 0  else nn.next_user_num end)/dn.new_user_num as ret_rate
from all_date join day_new_users dn on all_date.login_date=dn.first_login_date
    left join next_new_users nn on dn.first_login_date 1=nn. next_day_login_date
order by all_date.login_date;

spl