hive sql复杂场景-2

日期对应ID记录生成与查询：Python实现时段表操作

最新推荐文章于 2024-12-08 19:29:22 发布

原创最新推荐文章于 2024-12-08 19:29:22 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

本文介绍如何使用Python将给定的时段数据转换为id与日期一一对应的表，以便快速查询每个日期上的id列表，同时解释了如何处理enddate不计入存在日期。通过explode和posexplode函数实现数据拆分和遍历。

输入表：描述了每个id存在的时段

id	startdate	enddate
4172752725	2017-07-31	2017-08-02
4172778959	2017-08-01	2017-08-02
4172779968	2017-07-31	2017-08-01
4172781986	2017-08-09	2017-08-10
4172945444	2017-08-03	2017-08-04
4173014056	2017-08-04	2017-08-06

需求描述：要id和date一一对应的表，这样可以方便的求出每个日期上有哪些id存在。注意，对于每个id，enddate上不算这个id存在。

with testdata as (
	select 4172752725 as id, '2017-07-31' as startdate, '2017-08-02' as enddate
	union all
	select 4172778959 as id, '2017-08-01' as startdate, '2017-08-02' as enddate
	union all
	select 4172779968 as id, '2017-07-31' as startdate, '2017-08-01' as enddate
	union all
	select 4172781986 as id, '2017-08-09' as startdate, '2017-08-10' as enddate
	union all
	select 4172945444 as id, '2017-08-03' as startdate, '2017-08-04' as enddate
	union all
	select 4173014056 as id, '2017-08-04' as startdate, '2017-08-06' as enddate
)


select z.id
,date_add(z.startdate,z.pos) as checkindate
from (
  select id 
  ,startdate
  ,posexplode(split(repeat('d',datediff(enddate,startdate) - 1),'d'))
  from testdata
) z 
order by z.id asc
;

输出表：

id	checkindate
4172752725	2017-07-31
4172752725	2017-08-01
4172778959	2017-08-01
4172779968	2017-07-31
4172781986	2017-08-09
4172945444	2017-08-03
4173014056	2017-08-04
4173014056	2017-08-05

输出一些中间结果以帮助理解：

select id 
  ,startdate
  ,enddate
  ,datediff(enddate,startdate)
  ,datediff(enddate,startdate)-1 
  ,repeat('d',datediff(enddate,startdate) - 1)
  ,split(repeat('d',datediff(enddate,startdate) - 1),'d')
  ,posexplode(split(repeat('d',datediff(enddate,startdate) - 1),'d'))
from testdata
order by id asc

id	startdate	enddate	datediff	datediff-1	repeat	split	pos	col
4172752725	2017-07-31	2017-08-02	2	1	d	["",""]	0
4172752725	2017-07-31	2017-08-02	2	1	d	["",""]	1
4172778959	2017-08-01	2017-08-02	1	0		[""]	0
4172779968	2017-07-31	2017-08-01	1	0		[""]	0
4172781986	2017-08-09	2017-08-10	1	0		[""]	0
4172945444	2017-08-03	2017-08-04	1	0		[""]	0
4173014056	2017-08-04	2017-08-06	2	1	d	["",""]	0
4173014056	2017-08-04	2017-08-06	2	1	d	["",""]	1