postgre sql 语法

_小笼包_

已于 2022-06-30 18:40:53 修改

阅读量291

点赞数

分类专栏：数据库文章标签： postgresql

于 2022-06-17 12:52:45 首次发布

本文链接：https://blog.csdn.net/guanqingqing_/article/details/125331538

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

案例一：postgre 按半小时分组

select split_update_time,count(*) as cn from (
	SELECT update_time
		,to_char( update_time, 'yyyyMMdd hh24' )  
			|| ':' || 
			to_char(to_timestamp(
						'' || FLOOR(to_char( update_time, 'mi' ) :: INTEGER / 30) * 30
			,'mi'),'mi') as split_update_time
	FROM
		ods_umetrip 
	where update_time >= current_date-10
) t1 group by split_update_time order by split_update_time desc

案例二：postgre 今天星期几

SELECT (EXTRACT(DOW FROM CURRENT_DATE))

案例三：postgre 按周分组

select  
	(flight_date::DATE-(extract(dow from flight_date::TIMESTAMP)-1||'day')::interval)::DATE monday
	,sum(flight_total) flight_total
from dws_d_prod_quota 
where substr(flight_date,1,4)='2022' and airport_code='URC'
group by flight_date::DATE-(extract(dow from flight_date::TIMESTAMP)-1||'day')::interval
order by monday desc