redshift——sql常用

Darin2017

已于 2024-12-24 10:56:13 修改

阅读量3k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： redshift 文章标签： linq c#

于 2022-05-12 15:04:28 首次发布

本文链接：https://blog.youkuaiyun.com/Darin2017/article/details/124729530

redshift 专栏收录该内容

1 篇文章

订阅专栏

这篇博客介绍了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')	TIMESTAMP`、`TIME`或`TIMETZ
DATEDIFF ( datepart, {date\|time\|timetz\|timestamp}, {date\|time\|time\|timestamp} )	返回两个日期或时间之间的差值。	select datediff(day, '2022-04-30', '2022-04-30');	TIMESTAMP`、`TIME`或`TIMETZ
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 ) 最后一课时间

未完待续。。。