postgres数据库中获取当前日期、周几，还有列转行和hive中获取当前日期、string转成map、列转行的代码

最新推荐文章于 2024-01-17 19:28:38 发布

风是外衣衣衣

最新推荐文章于 2024-01-17 19:28:38 发布

阅读量388

点赞数

分类专栏： hive postgres

本文链接：https://blog.csdn.net/weixin_41804049/article/details/105136079

版权

--postgresSQL写法 需求都是同一roadid下，求每15分钟的均速km/h

 create table if not exists avg_speed_week_day0 as (
 		select c.roadid,c.week_day,
	
		max(case when cast(btrim(c.times,'h')as int)  = 000  then c.avg_speed else 0  end ) as "h0000", -- 00
		max(case when cast(btrim(c.times,'h')as int)  = 015  then c.avg_speed else 0  end ) as "h0015",
		max(case when cast(btrim(c.times,'h')as int)  = 030  then c.avg_speed else 0  end ) as "h0030",
		max(case when cast(btrim(c.times,'h')as int)  = 045  then c.avg_speed else 0  end ) as "h0045"
        	from 
				(select b.roadid,b.week_day,b.times, 
						round((sum(b.speed)/count(*)) * 3.6) as avg_speed
				from

						(select a.roadid,a.speed,a.week_day,a.utc,
								case when minute=0 
									then concat('h',hour,'00')
									else concat('h',hour,minute*15)
								end times
						from
								(select roadid,speed,utc,