redshift——sql常用

这篇博客介绍了SQL中处理日期时间的各种函数,如TRUNC、DATE_TRUNC、EXTRACT、DATE_PART等,以及字符串操作,如CONCAT、CHAR_LENGTH、SUBSTRING等。还提到了日期增减、星期判断、年周计算和时间戳转换的方法,并提供了不同数据库系统的特定函数示例,如GROUP_CONCAT的替代方法和自定义变量的使用技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、日期截取

where trunc( lesson_start_time) = '2022-05-10',后方日期必须加引号,不然无法识别;

select date_trunc('month',getdate()),返回当月第一天;

select salesid, extract(week from saletime) as weeknum 返回第几周;可以返回日、月或年、小时、分钟、秒、毫秒或微秒等;

select date_trunc('week', saletime)  DATE_TRUNC 函数使用“周”日期部分返回每周星期一的日期

select date_part(mon,getdate()) 从表达式中提取日期部分值;

select DATE_PART_YEAR('2022-05-12') 返回日期中的年份

TO_DATE 会将以字符串形式表示的日期转换为 DATE 数据类型。

select sysdate,
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as seconds

日期格式转化:从左到右

12/05/22 07:53

2022-05-12 07:53:24

日期格式参考

2、日期缩写

3、字符串连接:concat可以使用,也可以替换为||

示例:select tutor_user_id||student_user_id||'hello' from t1;

4、CHAR_LENGTH 函数等同于len函数,length函数

5、select charindex('fish', 'dogfish'); 返回指定字符串在字符串中的位置;

6、select position('dog' in 'fish');返回指定子字符串在字符串中的位置,如果在字符串中未找到子字符串,POSITION 将返回 0:

7、SUBSTRING 函数=substr函数,select substring('caterpillar',6,4);

8、TRANSLATE 函数:对于给定表达式,将指定字符的所有匹配项替换为指定替代项

select translate('mint tea', 'inea', 'osin'); translate ----------- most tin

9、自定义变量的替代方法:创建临时表

with tmp_date as (select '20220511' as date1)

SELECT *
FROM dws_cls_student_trail_lesson_ss_daily
WHERE ds=(select date1 from tmp_date)
  AND trunc(lesson_start_time) = CURRENT_DATE
LIMIT 5

10、手动输入变量方法:双大括号

SELECT *
FROM dws_cls_student_trail_lesson_ss_daily
WHERE ds='{{datet}}'
  AND trunc(lesson_start_time) = CURRENT_DATE
LIMIT 5

11、日期增减:

select add_months(current_date,-2)
select dateadd(MONTH,-2,current_date)

结果一样;

12、返回星期几

select date_part(weekday,'2022-06-05'),返回对应的星期,其中星期日对应0,星期一到六对应1-6;

星期一至星期日对应1-7的写法:

date_part(weekday,dateadd(day,-1,'2022-06-06'))+1

13、mysql中group_concat函数替代:

mysql:select a, group_concat(b separator ',') from table group by a;

Presto:select a, array_join(array_agg(b), ',') from table group by a

redshift:select a, listagg(b, ',') from table group by a

LISTAGG( [DISTINCT] expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ] OVER ( [PARTITION BY partition_expression] )

示例1:

listagg(distinct t3.edition_name,',') within group(order by t3.edition_name asc) over (partition by tut.tutor_user_id) banben

示例2:

listagg(
DISTINCT class_type_desc,','
) within group (order by class_type_id ) over(partition by tutor_user_id,student_user_id) 季度班型,

示例3:

listagg(DISTINCT datediff('minute',lesson_start_time,lesson_end_time),',') within group (order by datediff('minute',lesson_start_time,lesson_end_time) ) over(partition by tutor_user_id,student_user_id) 课时长,

14、年周判断:

SELECT 
to_char(dateadd(day,-3,next_day('2022-01-03','Monday')),'YYYY') yyr,
extract(week from timestamp ' 2022-01-03') wk

若本身为时间格式,则不需要timestamp

SELECT 
to_char(dateadd(day,-3,next_day(lesson_start_time,'Monday')),'YYYY')*100+
extract(week from  lesson_start_time)

返回某年某周的6位数字

15、时间戳转化

函数

解释

语法

返回值

TIMEZONE('timezone'{timestamp|timestamptz})

返回指定时区和时间戳值的一个时间戳。

TIMEZONE('UTC+8', (timestamp 'epoch' + 1650347023 * interval '1 second'))

timestamp

CONVERT_TIMEZONE (['timezone',] 'timezone', timestamp)

将一个时区的时间戳转换为另一个时区的时间戳。

convert_timezone('GMT+8','UTC','2022-04-19 05:43:43') |convert_timezone('UTC-8','2022-04-19 05:43:43')

timestamp

DATEADD (datepart, interval,{date|time|timetz|timestamp})

按指定的时间间隔递增日期或时间。

dateadd(day,30,'2022-04-30')

TIMESTAMPTIMETIMETZ

DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} )

返回两个日期或时间之间的差值。

select datediff(day, '2022-04-30', '2022-04-30');

TIMESTAMPTIMETIMETZ

TO_TIMESTAMP ('timestamp', 'format')

将 TIMESTAMP 字符串转换为 TIMESTAMPTZ

to_timestamp('2022-04-19 05:43:43','YYYY-MM-DD HH24:MI:SS');

TIMESTAMPTZ

json函数嵌套处理13位时间戳为日期格式:

JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList') 调前virtual_class_id,
JSON_EXTRACT_PATH_TEXT(info,'repairStartTime') 调后时间,
JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustVirtualclassId') tiaoqianvir,
JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime') tiaoqiantime,
dateadd(s , CAST(JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime') / 1000 AS INTEGER), '1970-01-01 00:00:00') datetime1 ,
date(dateadd(s , CAST(JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime') / 1000 AS INTEGER), '1970-01-01 00:00:00')) date1,
TIMESTAMP 'epoch' + JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime')/1000*interval'1second' time2

以上提供两种方法转化

16、最后一个值last_value函数

示例:

last_value(class_type_desc) over(partition by tutor_user_id,student_user_id order by  lesson_start_time ASC rows between unbounded preceding and unbounded following ) 最后一课班型,
last_value(datediff('minute',lesson_start_time,lesson_end_time)) over(partition by tutor_user_id,student_user_id order by  lesson_start_time ASC rows between unbounded preceding and unbounded following ) 最后一课时长,
last_value(lesson_start_time) over(partition by tutor_user_id,student_user_id order by  lesson_start_time ASC rows between unbounded preceding and unbounded following ) 最后一课时间

未完待续。。。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值