AWS RedShift实战应用SQL大全及经验分享[持续更新]_redshift 查询 数据库 表 字段

---

前言 - 关于RedShift

RedShift是AWS上面使用非常广泛的离线数据仓库之一，本文就结合一些实际的经验，把一些常用的SQL查询做一些列举。在正文之前，也对这个数据仓库的一些特性做一下说明，如果大家有在选择一些数据仓库产品，不妨考虑一下合适不合适。

· SQL使用查询非常方便，增删改查都做了一些性能兼顾，提供全套 PostgreSQL 语法。
· 运行SQL前需要对其编译，提倡批处理执行，尤其是从文件拷贝数据进数据库的效率更高。
· 支持比较高的并发，性能不错且稳定。
· AWS后台对其监控与存储解决方案完善。
· 服务价格适中。
· 更多可以参考https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/c-first-time-user.html。

---

一、数据维护篇

1.1 表结构操作

1.1.1 演示一个创建学生成绩的一个数据，让大家知道怎么表示整形、字符串和浮点数。

CREATE TABLE "public"."student_score" (
  "student_id" "int4",
  "subject" "varchar",
  "score" "int4",
  "grade_point" "numeric"
)
WITH OIDS;
1
2
3
4
5
6
7

1.1.2 复制表结构与数据到另一个新表

-- 仅仅复制结构
create table student_copy as select * from student limit 0
-- 复制结构和数据
create table student_copy as select * from student
1
2
3
4

1.1.3 重命名表名

alter table student rename to student_tmp;
1

1.1.4 演示增加一个表字段，为学生成绩表增加一个创建日期

alter table student_score
add column create_time varchar(20);
1
2

1.1.5 删除表

drop table student_score;
1

1.2 数据添加与查询

1.2.1 演示insert 语句，这里要注意，RedShift中只有主键类似的定义，但是不会真正执行主键约束，这是底层文件结构决定的

insert into student_score(student_id, subject, score, grade_point) 
values (1, '语文', '80', '2.31');
-- 批量插入也是类似，注意RedShift中是不识别[`]的，这个和MySQL不一样。
1
2
3

1.2.2 演示COPY命令，此命令使用非常广泛，一般来说从s3同步数据文件到RedShift，因为这样执行的效率更高，一般来说数据仓库是分析作用，会把业务数据库的数据同步上来

copy student_score from 's3://xxxx/student_score/'
access_key_id '' 
secret_access_key '' 
ACCEPTINVCHARS AS ' ' TRUNCATECOLUMNS IGNOREBLANKLINES delimiter '\t' 
gzip region 'us-east-1'
-- 其中注意delimiter要根据实际文件里面的分隔符来确定
1
2
3
4
5
6

1.2.3 COPY命令要是执行有错误，可以通过下面的命令查看错误来源

select * from STL_LOAD_ERRORS  order by starttime desc limit 10
1

1.2.4 查询数据，这里演示一个查询的综合例子，查询2班所有学生里面语文成绩不低于80分的同学，然后按照成绩高低排名取10到30名的数据，并且展示同学的姓名与分数

select sd.name, sc.score from student sd 
inner join student_score sc on sd.id=sc.student.id
where sc.score >= 80 and sd.id in(
select id from student where class_id = 2
) and subject = '语文' order by sc.score desc limit 20 offset 10;
1
2
3
4
5

1.2.5 数据库数据导出到文件，下面演示把学生表导出为文件

unload ('select * from student') to 's3://xxxx/student_score/' 
access_key_id '' secret_access_key ''
1
2

1.3 数据修改与删除

1.3.1 演示更新数组，将student_id为20的学生分数更新为90

update student_score set score=90 where student_id = 20;
1

1.3.2 演示删除数据，删除分数小于60分的学生

delete from student_score where score<60;
1

1.4 事物操作

1.4.1 RedShift同样也是支持事物的，这一点很多时候就比较强大了

begin transaction;
-- sql代码段，成功执行下面提交代码
end transaction;
-- 失败执行事务回滚
rollback transaction;
1
2
3
4
5

二、SQL结构篇

2.1 使用with封装代码

2.2.1 with真的是一个比较好的东西，可以把一些重复用到的查询结构封装起来，既提高了代码的可读性，同时也会一定程度上提高程序执行效率

-- 这里将括号类的查询封装成一个新表，后面可以多次使用
with class2student as (
select * from student where class_id = 2
) select count(*) from class2student
1
2
3
4

2.2 条件判断

2.2.1 case when很多时候用来代替if，下面演示统计学生的id，如果为null或空字符串，用0显示

select 
   case when student_id is null then 0 
        when student_id = '' then 0 
        else student_id end as student_id 
from student_score
1
2
3
4
5

三、常用的函数篇

3.1 类型转化

RedShift里面主要通过cast命令来实现不同数据类型的转换，常见的类型有字符串，数字，整形，时间戳等。注意，当不能发生转化的时候会报错，如不能把字符串ABC转化为整形。

cast(create_time as timestamp) -- 字符串转化为时间戳
cast(student_id as interger) -- 将字符串转化为整形
1
2

3.2 值运算

RedShift里面值得注意的是，如果整数的除法运算，要保留精度，需要运算前把整数转化为浮点型，其他类型同理。

-- 这里演示类型转化的除法运算，以及保留两位小数的用法
select 
  cast(int1 as numeric)/cast(int2 as numeric) as result,
  round(result, 2) as r_result
  from ···
1
2
3
4
5

3.3 字符串

3.3.1 字符串拼接使用concat或者||，一般来说||是比较好用的，尤其是连接多个字符串的时候

select student_id || '-' || subject || '-' || score from student_score
1

3.3.2 字符串截取，一般来说用substring，更多的描述就不说了，开发人员都懂

-- 截取前两个字符
select substring(subject,0,2) from student_score
1
2

3.3.3 字符串正则匹配与替换

-- 将城市名中的市和逝去替换为空字符串
select city,regexp_replace(city, '(市)|(市区)','') as format_city from city
1
2

3.3.4 字符串通过字符分隔取值

-- 取出地址里面的城市，通过市分隔字符串后1表示取第一段
select address,split_part(address, '市', 1) as format_city from city
1
2

3.3.5 字符串大小写转化，redshift是大小写敏感的数据库，下面将用户邮箱转化为小写

select email, lower(email) from users
1

3.3.6 listagg一般配合group by使用，一般用户分组后把组内的其他字符串拼接起来

select province_id,listagg(city_name, ',') as citys from city group by province_id
-- 输出 2,'遂宁市,巴中市,南充市....'
1
2

3.4 日期

3.4.1 获取当前时间

-- 2022-04-01 02:16:44
select getdate();
1
2

3.4.1 时间戳字符串转化为字符串日期

-- 将时间戳1648778850转化为日期
select TIMESTAMP 'epoch' + 1648778850 * INTERVAL '1 second' as datetime;
1
2

3.4.1 时间增减，在指定时间上计算之前或者之后的时间

-- 将当前时间增加8小时
select dateadd(h,8,getdate());
-- 将当前时间减少8小时
select dateadd(h,-8,getdate());
1
2
3
4

3.4.1 字符串转化为时间戳

select cast('2021-01-01 12:00:02' as TIMESTAMP);
1

四、进阶操作

4.1 分组排序

分组排序这个功能实际上用的比较多，例如目前有一个表数据里面含有商品，颜色，对应颜色的销量，让我们拉出每个商品颜色销量的前5名。

with main as(
  select goods_id,color,num, 
  row_number() over (partition by goods_id order by num desc) as row
  from goods_sale_table order by goods_id
) select * from main where row <= 5;
1
2
3
4
5

4.2 实现MySQL Order By Field功能

输出通过指定字段排序的列表，如下命令结果可以实现列表通过123,234,456,678,789,910的顺序排列。

select * from goods 
where goods_id in(123,234,456,678,789,910)
order by position(',' || goods_id || ',' in ',123,234,456,678,789,910,')
1
2
3

另外也可以通过case when来排序

SELECT *
FROM your_table
ORDER BY CASE region
    WHEN 'us' THEN 1
    WHEN 'ca' THEN 2
    WHEN 'uk' THEN 3
    ELSE 4 -- 如果有其他值，放在最后
END;
1
2
3
4
5
6
7
8

五、经验分享篇

redshift实际上是一个已经比较成熟的产品了，许多公司都在用，下面将自己使用过程中的一些经验作为分享。

• 建议业务库的字段名称不要以redshift的保留字命名，这样同步到redshift的时候会有问题。
• 不宜将过长的SQL发送到RedShift执行，这样会增加编译的负担，甚至很多时候会导致查询失败。
• 建议使用批量插入代替多次插入，建议使用文件拷贝进数据库，这样的效率更高，删除和新增同样建议批量。
• 要建立有效的数据监控机制与数据清理机制，避免数据不准确，以及数据库压力过大。

总结

以上就是今天要讲的内容，本文简单介绍了RedShift的SQL使用以及一些日常经验分享，本篇文章将会持续更新，大家使用过程中遇到任何问题，也可以与我私信哟。