【数据库】HIVE|【数据库】HIVE SQL--相邻时间问题处理
【【数据库】HIVE|【数据库】HIVE SQL--相邻时间问题处理】求每个员工的连续工作时间段及工资和
文章图片
select name, first1, last1,salary * (datediff(last1, first1)+1)as salary
from (select name, num, salary, min(date) as first1, max(date) as last1
from (SELECT a.name, a.date, date_sub(date, rn - 1) num, salary
FROM (SELECT name,
date,
salary,
row_number() over(PARTITION BY name ORDER BY date) rn
FROM dw_htlbizdb.tmp_qh_liu_interview_data
GROUP BY name, date, salary
) a
) b
group by name, num, salary
) a结果：
文章图片
对于连续时间问题的处理还有一种方法：
select name,
diff,
qujian [ 0 ] start,
case when diff = 0 then qujian [ 0 ]
when diff = 1 then qujian [ 1 ]
when diff = 2 then qujian [ 2 ]
when diff = 3 then qujian [ 3 ]
end as endtime
from (SELECT name,
datediff(max(date), min(date)) diff,
COLLECT_set(date) qujian
from (select name, date, salary, date_sub(date, rank) as date2
from (select name,
date,
salary,
row_number() over(partition by name order by date) rank
from dw_htlbizdb.tmp_qh_liu_interview_data
) a
) a
group by name, date2
) a
文章图片
推荐阅读
- 宽容谁
- 我要做大厨
- Docker应用:容器间通信与Mariadb数据库主从复制
- 增长黑客的海盗法则
- 画画吗（）
- 2019-02-13——今天谈梦想（）
- 远去的风筝
- 三十年后的广场舞大爷
- 叙述作文
- 20190302|20190302 复盘翻盘