hive hsql 漏斗模型_HIVE中的总和模型和漏斗模型sql

--1

insert into t_salary values ('a','2018-01',10);

insert into t_salary values ('b','2018-01',20);

insert into t_salary values ('a','2018-02',15);

insert into t_salary values ('b','2018-02',30);

insert into t_salary values ('c','2018-03',10);

insert into t_salary values ('c','2018-03',50);

insert into t_salary values ('a','2018-03',20);

select a."name",a."time",max(a.salary),sum(b.salary) from

(

select t."name",t."time",sum(t.salary) as salary from t_salary t group by t."name",t."time"

) a

inner join

(

select t."name",t."time",sum(t.salary) as salary from t_salary t group by t."name",t."time"

) b

on a."name" = b."name"

where b."time" <= a."time"

group by a."name",a."time"

order by a."name",a."time";

--2 漏斗模型

insert into t_test_1 values (1,10000);

insert into t_test_1 values (2,4000);

insert into t_test_1 values (3,800);

insert into t_test_1 values (4,100);

insert into t_test_1 values (5,50);

insert into t_test_1 values (6,20);

select * from t_test_1 a

left join t_test_1 b on a."id"-1 = b."id";

select a."id",max(a."number") step_now,

round(cast(max(a."number") as numeric )/cast (max(b."number") as numeric),2) step_one,

round(cast(max(a."number") as numeric )/cast (min(b."number") as numeric),2) step_last

from t_test_1 a

left join t_test_1 b on a."id" > b."id"

group by a."id" order by a."id";