drop table default.salary;
create table default.salary? (
userid string,
dateid string,
salary? int
);
/*
insert into default.salary values('001','2019-03-01',70);
insert into default.salary values('001','2019-03-02',70);
insert into default.salary values('001','2019-03-03',70);
insert into default.salary values('002','2019-03-01',80);
insert into default.salary values('002','2019-03-02',80);
insert into default.salary values('002','2019-03-03',80);
insert into default.salary values('001','2019-04-01',70);
insert into default.salary values('001','2019-04-02',70);
insert into default.salary values('001','2019-05-03',70);
*/
select * from default.salary;
--寫法一
SELECT userid
? ? ? ,first1
? ? ? ,last1
? ? ? ,datediff(last1, first1)+1 days
? ? ? ,salary * (datediff(last1, first1)+1)? as salary
FROM
? ( SELECT userid
? ? ? ? ? ,num
? ? ? ? ? ,salary
? ? ? ? ? ,min(dateid) as first1
? ? ? ? ? ,max(dateid) as last1
? ? ? FROM
? ? ? ? (? SELECT a.userid
? ? ? ? ? ? ? ? ,a.dateid
? ? ? ? ? ? ? ? ,date_sub(dateid, rn - 1) num
? ? ? ? ? ? ? ? ,salary
? ? ? ? ? ? FROM
? ? ? ? ? ? ? (? SELECT userid
? ? ? ? ? ? ? ? ? ? ,dateid
? ? ? ? ? ? ? ? ? ? ,salary
? ? ? ? ? ? ? ? ? ? ,row_number() over(PARTITION BY userid ORDER BY dateid) rn
? ? ? ? ? ? ? ? ? ? FROM default.salary
? ? ? ? ? ? ? ? ? ? GROUP BY userid,dateid,salary
? ? ? ? ? ? ? ? ) a
? ? ? ? ) b?
? ? ? ? group by userid,num,salary
? ? ) a
--寫法二
SELECT a.userid
? ? ? ,b.starttime
? ? ? ,b.endtime
? ? ? ,datediff(b.endtime,b.starttime) + 1 days
? ? ? ,sum(case when a.dateid>=b.starttime and a.dateid<=b.endtime then a.salary else 0 end) as total_salary
FROM default.salary a
LEFT JOIN
(? SELECT userid
? ? ? ? ? ,days
? ? ? ? ? ,qujian[0]? ? starttime
? ? ? ? ? ,case when days=1 then qujian[0]
? ? ? ? ? ? ? ? when days=2 then qujian[1]
? ? ? ? ? ? ? ? when days=3 then qujian[2]
? ? ? ? ? ? ? ? when days=4 then qujian[3] end as? endtime? ? --此處如有其它時間差，需要進行枚舉
? ? FROM? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --取出連續(xù)時間范圍
? ? ? (? SELECT userid
? ? ? ? ? ? ? ,datediff(max(end_date),min(end_date)) + 1 days
? ? ? ? ? ? ? ,COLLECT_set(end_date) qujian
? ? ? ? ? FROM? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --求出連續(xù)時間之差
? ? ? ? ? ( SELECT userid
? ? ? ? ? ? ? ? ? ,dateid end_date
? ? ? ? ? ? ? ? ? ,salary
? ? ? ? ? ? ? ? ? ,date_sub(dateid,rn-1) as? start_date
? ? ? ? ? ? ? FROM? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --日期與排名之間的差值
? ? ? ? ? ? ? ( SELECT userid
? ? ? ? ? ? ? ? ? ? ? ,dateid
? ? ? ? ? ? ? ? ? ? ? ,salary
? ? ? ? ? ? ? ? ? ? ? ,row_number () over (partition by userid order by dateid) rn
? ? ? ? ? ? ? ? ? FROM default.salary? ? ? ? ? ? ? ? --按name分組后對date進行排序
? ? ? ? ? ? ? ? ) a?
? ? ? ? ? ? ) a
? ? ? ? GROUP BY userid,start_date
? ? ? ) a
? ) b on a.userid=b.userid
GROUP BY a.userid,b.starttime,b.endtime?
