在 Amazon Redshift 上写 SQL和之前在 MySQL、PostgreSQL 上写 SQL 完全是两回事。我做了将近十年数据平台相关的工作这两年把大量离线数仓任务迁到 Redshift 上踩过的坑比写过的报表还多。今天这篇不打算讲那种“从入门到精通”的百科式内容专门聊 Redshift SQL 编写里最要紧的语法细节、去重空值窗口函数这类高频操作、慢 SQL 优化的实际套路以及我自己的避坑记录。打算迁到 Redshift、或者刚接手 Redshift 集群的朋友直接可以参考这里面的思路去落地。Redshift 本质上是 MPP 分布式数据仓库列式存储、按列压缩、数据分散在多个计算节点上。这些底层特性决定了你不能照搬 MySQL、Oracle 甚至 SQL Server 的写法。很多在行式数据库里很高效的模式搬到 Redshift 里反而会拖慢整个查询。反过来Redshift 一些很擅长的分析型写法传统数据库反而不一定支持。把这块先想清楚后面写 SQL 才有正确的方向。1. 先搞明白 Redshift SQL 的定位再动手写1.1 Redshift 不是“普通的数据库”SQL 写法要跟着架构走先看架构Redshift 集群有 leader node 和多个 compute node每个 compute node 又被切分成若干 slice查询被切分到所有 slice 上并行执行。数据是按分布键DISTKEY和排序键SORTKEY存储的。列式存储意味着每个列的数据是独立存储的查询只需要读取涉及的列所以“select 少用星号”不仅是为了节省网络传输更是为了减少扫描的磁盘块。这个架构对 SQL 编写直接提出了几个隐性问题。第一JOIN 时如果关联字段和分布键不一致数据就要在节点之间重新分发shuffle这是 Redshift 上最常见的慢查询来源。第二过滤条件尽量落到表的排序键上这样 Redshift 可以通过 block 级别的元数据直接跳过大量数据块根本不用读如果你对列做了函数变换这个剪枝机制可能失效。第三数据倾斜会导致某个 slice 变成瓶颈明明集群有一堆节点却只有一个节点在拼命算其他节点在空转。所以你在 Redshift 上写 SQL脑子里要时刻带一张“数据分布图”哪张表以哪个字段分布、哪些字段适合做过滤条件、JOIN 的字段能不能对上分布键。方案选型上我一般建议先做核心查询的性能测试再反推表结构设计而不是先建一堆表再慢慢优化 SQL。1.2 Redshift SQL 与 MySQL、SQL Server 的核心差异Redshift 的 SQL 方言大量参考了 PostgreSQL 8.x但并不是完全一致。实际使用中有几个高频差异点从其他数据库迁移过来时最容易踩约束只做元数据参考不强制。PRIMARY KEY、UNIQUE、FOREIGN KEY 在 Redshift 里更多是给优化器提供信息不保证唯一性和完整性。也就是说你在业务上要求的“主键唯一”得靠 ETL 过程自己保证不能在入库时指望约束帮你挡脏数据。没有 AUTO_INCREMENT 这种自增语法而是用 IDENTITY(seed, step) 来生成自增列。字符串拼接用||这一点和 PostgreSQL、SQL Server 的都不太一样。UPDATE 和 DELETE 性能比较差而且会产生死行dead rows需要 VACUUM。所以大表更新通常不直接 UPDATE而是用 CTAS 重建表再换名字。窗口函数非常完善大部分分析场景都优先用窗口函数解决而不是写一堆子查询。一次多行 INSERT 的语法兼容性不如 COPY 命令数据入库优先走 COPY 从 S3 加载。这些差异直接影响着你写的每条 SQL。我的习惯是团队里新增同学从 MySQL 转过来时先让 ta 把官方文档的 SQL reference 翻一遍尤其是函数列表和系统表部分。不然写出来的代码经常在本地看着没问题一跑就报Function does not exist之类的东西。1.3 写 SQL 之前先养成看系统表和日志的习惯Redshift 和 MySQL 这类数据库不太一样它的排查诊断基本依赖系统表和日志视图。有几个视图我几乎每天都会用STV_TBL_PERM看表的真实字节数、行数、分布情况尤其适合判断数据有没有倾斜。STL_QUERY记录所有执行过的 SQL包含开始时间、结束时间、扫描行数等。SVL_QLOG相当于 STL_QUERY 的轻量版适合快速看最近跑过的查询。STL_LOAD_ERRORSCOPY 导入出错时的详细日志定位脏数据非常有用。我一般排查慢查询第一步不是去看 SQL 本身而是先查STL_QUERY拿到执行时长、返回行数、扫描行数。如果返回行数很少但扫描行数是天量说明过滤下推没做好如果 SQL 本身的执行计划里有大量广播操作那大概率是 JOIN 条件和分布键没对齐。养成先看日志再改 SQL 的习惯能少走不少弯路。2. 高频语法细节与案例去重、空值、窗口函数与日期处理2.1 去重DISTINCT、GROUP BY、ROW_NUMBER 怎么选去重是数据清洗里最常见的需求。很多新手一上来就SELECT DISTINCT *但这种写法只适合整行完全重复的情况而且如果表很宽、数据量很大DISTINCT 需要把所有去重字段都放到内存做排序代价不低。实际工作中我更常用三种方案整行重复直接用 DISTINCT没什么好说的。对某几个业务键去重并且要保留最新的一条记录用ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)。做统计口径的去重比如算活跃用户数、付费用户数用COUNT(DISTINCT user_id)但别对大表频繁使用否则会很慢。一个保留最新记录的经典写法WITH ranked AS ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders ) SELECT user_id, order_id, amount FROM ranked WHERE rn 1;这里的PARTITION BY user_id会对每个用户编号重新开始排序ORDER BY created_at DESC让最新创建的订单排在前面最后WHERE rn 1取每个用户的第一条。这个模式我几乎每周都要用比起关联子查询窗口函数不仅写法清晰在 Redshift 上执行效率也通常更高。需要注意窗口函数在 Redshift 里虽然好使但如果分区字段选择不当会造成大量数据重分布。比如一张几十亿行的订单表按 user_id 分区一般没问题因为用户维度基数很高但如果你按一个只有几个枚举值的字段分区比如 status那么每个分区里的数据量巨大窗口排序的成本会直线上升。2.2 空值与空字符串该清理的时候千万别手软Redshift 里 NULL 和空字符串不是一回事但在实际数据里它们经常混在一起。这个问题在数据清洗阶段不解决后面写统计 SQL 时会出一堆莫名其妙的结果。常见的坑有两个第一COUNT(字段)只会统计非 NULL 值但空字符串会被当作有效值计入第二JOIN 的时候如果字段在某张表里是 NULL在另一张表里是空字符串两边永远关联不上。所以清洗阶段的标准化很重要。我常用的标准写法SELECT id, COALESCE(NULLIF(TRIM(name), ), 未知) AS name FROM raw_table;这里NULLIF(TRIM(name), )的作用是先把 name 字段两边的空格去掉如果结果是空字符串就转成 NULL再用COALESCE把 NULL 变成默认值“未知”。这一套组合拳在处理 Excel 导入、CSV 导入的数据时特别有效能把“空串”“空格串”“NULL”统一成一个口径。还有统计的时候别忽略空串。比如统计用户填没填昵称如果你写COUNT(nickname)空字符串的用户会被算进去这时候要么先做清洗要么在统计时统一用COUNT(NULLIF(TRIM(nickname), ))。2.3 窗口函数报表和分析场景的核心武器Redshift 对窗口函数的支持比较完善这也是我特别喜欢拿它做分析的原因。常用的主要有几类排名类ROW_NUMBER、RANK、DENSE_RANK。聚合类SUM、AVG、COUNT 配合 OVER 子句做累计、移动平均。偏移类LAG、LEAD拿上一行或下一行的值适合算环比、同比。分组取首尾FIRST_VALUE、LAST_VALUE。一个很典型的业务场景是算月度营收和环比SELECT DATE_TRUNC(month, order_date) AS month, SUM(amount) AS revenue, LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC(month, order_date)) AS prev_revenue FROM orders WHERE order_date 2023-01-01 GROUP BY DATE_TRUNC(month, order_date) ORDER BY month;这里的关键点在于窗口函数是在 GROUP BY 之后执行的所以可以在窗口函数里直接引用聚合结果SUM(amount)不需要再套一层子查询。很多人第一次看到这种写法会愣一下但理解了执行顺序就很好掌握WHERE 先过滤GROUP BY 再分组然后才是窗口计算。还有一个能体现窗口函数威力的场景连续登录天数计算。这也是数据分析师面试题里出现频率很高的问题。思路是先用 DISTINCT 去掉一天内多次登录的记录然后用登录日期减去按用户分组的行号得到一个分组日期。同一个用户的连续登录记录会分到同一个组里最后再对分组做统计WITH daily AS ( SELECT DISTINCT user_id, DATE(login_ts) AS login_date FROM login_log WHERE login_ts 2024-01-01 ), grp AS ( SELECT user_id, login_date, DATEADD(day, - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), login_date) AS grp_date FROM daily ) SELECT user_id, COUNT(DISTINCT login_date) AS consecutive_days FROM grp GROUP BY user_id, grp_date HAVING COUNT(DISTINCT login_date) 3;这个写法初看有点绕但拆开看也不复杂ROW_NUMBER()按登录日期排序后连续日期的行号也是连续的日期减去行号得到的日期保持不变而中间断了一天日期减行号就会往前跳一天。然后按这个差值分组就能把连续日期聚到一起。Redshift 上跑这种查询效率不错比多层自 JOIN 好得多。2.4 WITH 子句让层次结构更清晰WITH 子句也叫公共表表达式CTE在 Redshift 里支持得很好适合把复杂查询拆成一段一段的步骤每段都像一张临时视图后面可以直接引用。这样写有几个好处逻辑清晰调试方便后面维护的人不用从头到尾读一遍几十行的嵌套子查询。我经常拿 CTE 做多步清洗比如先过滤、再标准化、最后聚合WITH filtered AS ( SELECT user_id, order_id, amount, created_at FROM orders WHERE created_at 2024-01-01 ), user_last_order AS ( SELECT user_id, MAX(created_at) AS last_order_time FROM filtered GROUP BY user_id ) SELECT f.user_id, f.order_id, f.amount FROM filtered f JOIN user_last_order u ON f.user_id u.user_id AND f.created_at u.last_order_time;但 CTE 也不是越多越好。Redshift 对 CTE 有内联优化不过如果你在后面的查询里多次引用同一个 CTE各引用有可能会被分别计算重复执行的代价不小。我的建议是简单拆分、只被引用一次的放心用 CTE。一个大 CTE 会被下游重复引用两三次以上的先把它落成临时表或者直接 CTAS 再查性能更稳。CTE 嵌套不要太深超过四五层以后阅读和维护都很痛苦。2.5 日期、时间与 BETWEEN 的边界别把最后一天算丢了日期处理是 SQL 编写里最容易出隐性 bug 的地方尤其是BETWEEN。BETWEEN 是闭区间BETWEEN 2024-01-01 AND 2024-01-31等价于 2024-01-01 AND 2024-01-31。如果 created_at 是 timestamp 类型那么 1 月 31 日当天 19:30 的订单其实会被包含在 2024-01-31里。听起来没问题但反过来如果传的是字符串日期而列是date类型也可能出现边界差异。更要命的是下个月的边界很多报表要统计一整个月的数据新手常用BETWEEN 2024-01-01 AND 2024-01-31。但这种写法容易漏掉 1 月 31 日当天的数据因为如果日期列是 timestamp2024-01-31 会被当成 2024-01-31 00:00:00那 1 月 31 日白天产生的数据就全被丢掉了。这是我在生产环境里真实踩过的坑。正确做法是采用“左闭右开”区间WHERE created_at 2024-01-01 AND created_at 2024-02-01这样无论 created_at 是 date 还是 timestamp都能完整覆盖整个 1 月也不会把 2 月 1 日 00:00:00 的数据算进来。Redshift 的日期函数有几个特别好用DATEADD()、DATEDIFF()、DATE_TRUNC()、GETDATE()、TO_CHAR()。比如算上周一到今天的数据WHERE created_at DATE_TRUNC(week, GETDATE()) AND created_at GETDATE()用DATE_TRUNC(week, ...)把当前时间截断到周一零点比手写DATEADD(day, -7, GETDATE())更清晰而且能自动适配跨月跨年。3. 慢 SQL 优化的完整思路3.1 先从执行计划里找硬伤Redshift 的EXPLAIN和EXPLAIN VERBOSE是慢查询排查第一站。我遇到一个慢 SQL一般先看执行计划里有没有DS_DIST_INNER、DS_BCAST_INNER、DS_DIST_ALL_INNER这些关键字。简单解释一下DS_BCAST_INNER把其中一张小表广播到所有节点然后跟大表做 JOIN。如果小表真的很小这种方式很高效但如果“小表”其实有上亿行那广播的数据量就是天文数字查询必然慢。DS_DIST_INNERJOIN 两边的数据按关联键重新分布到节点之间。关联键和其中一张表的分布键不一致时就会出现跨节点传输数据非常耗时。DS_DIST_ALL_INNER把整张表复制到所有 slice常见于维度表分布策略不当时。看到这些标识优化方向基本就清楚了要么改表结构把分布键设置成和高频 JOIN 字段一致要么改 SQL先在小结果集里做聚合再 JOIN减少重分布的数据量。比如SELECT a.user_id, b.total_amount FROM users a LEFT JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ) b ON a.user_id b.user_id;这里先把订单表按 user_id 聚合缩小数据量再和用户表关联虽然底层还是按 user_id 分发但传输的数据量会小很多。3.2 表设计对 SQL 性能的影响很多时候 SQL 慢不是 SQL 的锅而是表结构没设计好。Redshift 表设计最核心的就是 DISTKEY 和 SORTKEY。DISTKEY 的选择有个原则高频 JOIN 的字段、高基数字段优先。比如订单表经常按 user_id 和用户表关联那把 user_id 设为 DISTKEY 就能让 JOIN 尽可能在本地完成避免重分布。但也要注意数据倾斜建议选月份、地区这类低基数高基数不高基数字段更适合避免倾斜。低基数字段容易让数据分布不均比如按支付状态作为 DISTKEY那“已支付”“未支付”两组可能数量相差悬殊出现严重的数据热点。SORTKEY 的选择则要考虑过滤条件。如果查询经常按order_date做时间范围过滤那把order_date设为 SORTKEYRedshift 可以通过块级别的 min/max 统计直接跳过不相关的数据块。多个字段的复合 SORTKEY 也有讲究最常用的过滤字段放最前面。如果建表时这些没考虑好后面 SQL 怎么调都别扭。我自己遇到过一张十几亿行的日志表没有 SORTKEY按时间过滤时每次都把全部数据扫描一遍。后来重建表把时间字段设为 SORTKEY同样的 SQL 快了 4 倍多这就是表设计对 SQL 执行效果的直接体现。3.3 让查询利用并行别把并行做没了Redshift 是大规模并行架构但有些 SQL 写法会让并行优势发挥不出来。一个典型问题是在 WHERE 子句里对列做函数运算比如WHERE DATE(created_at) 2024-01-01这种写法虽然返回结果没错但 Redshift 无法利用 SORTKEY 对created_at做范围剪枝因为每一行都得先算一遍 DATE 函数再去跟常量比较。改成WHERE created_at 2024-01-01 AND created_at 2024-01-02就能充分利用排序键跳过大量无关数据块。另一个问题是过大的IN列表。如果你在 IN 后面写了几万个 IDRedshift 处理起来很吃力而且网络传输也不划算。这种情况更合理的做法是把 ID 列表先加载到一张临时表再通过 JOIN 关联。还有一个常见问题UNION 和 UNION ALL 的误用。如果两个 SELECT 结果集本身没有重复或者重复不影响最终结果就尽量用 UNION ALL避免多余的排序去重操作。UNION 自带去重代价是额外的排序和内存开销。3.4 大数据量场景下的 SQL 编写习惯在 Redshift 上处理百亿级数据时日常的“好习惯”会变得更重要。我总结了几条比较实用的经验尽量避免 SELECT *只列出实际需要的字段。列式存储虽然按列读取但多选一列就多一份 IO。能用常量的地方别用动态计算比如日期范围直接传参便于优化器做裁剪。JOIN 之前先聚合、先过滤把中间结果尽量缩小。多表 JOIN 时保证关联字段类型一致。类型不一致可能导致隐式转换从而让优化器无法有效利用分布和排序信息。使用临时表保存中间结果时向 CKAS 建表后加上 DISTKEY 和 SORTKEY别把临时表建得跟日志一样无序。4. 常见问题排查与技术细节补充4.1 处理单引号别被转义规则坑到Redshift 里的字符串常量用单引号包裹如果字符串本身包含单引号需要写成两个单引号转义。写法和 PostgreSQL 一样SELECT Its OK;这里的两个单引号前一个是转义符后一个是内容里的真实单引号。很多人刚开始会不习惯写成Its OK执行直接报语法错误。使用动态 SQL 拼接时尤其要注意拼接的字符串内容本身可能包含单引号要提前做替换把单个单引号替换成两个单引号。我见过不止一次因为某个用户昵称里带了个单引号导致整个报表写入失败。另外安全问题必须提一句写 SQL 的时候永远不要直接把用户输入拼到 SQL 字符串里否则容易引入注入风险。正确做法是用参数化查询、PreparedStatement或者让用户输入经过服务层校验和转义。我之前遇到过同学为了图方便把前端传来的筛选值直接拼 SQL结果一个特殊字符就把全表数据带偏了这种教训一次就够了。4.2 DBeaver 等客户端执行 SQL 文件的注意事项很多人习惯用 DBeaver 连接 Redshift 写临时查询但执行整个 .sql 文件时容易踩坑。DBeaver 里按 CtrlEnter 只执行当前光标所在的一条语句如果文件里有几百条 SQL只按一次显然不行。要用执行脚本功能通常是 AltX或右键菜单的“执行脚本”DBeaver 才会按分隔符把整个文件拆成多条语句顺序执行。还有一个隐藏问题如果 .sql 文件是用 MySQL Workbench 或 SQL Server Management Studio 导出的里面可能带有DELIMITER $$、SET ...、GO之类的语法这些在 Redshift 里根本不存在直接执行必然报错。导出的文件最好先确认里面没有这些数据库特定的语法再拿去执行。4.3 把 SQL Server 写法改成 Redshift 的清单这几年很多企业把原本跑在 SQL Server 上的报表迁到 Redshift语法迁移的工作量不小。我整理过一张速查清单场景SQL Server 写法Redshift 写法取前 N 条SELECT TOP 10 col FROM tSELECT col FROM t LIMIT 10空值处理ISNULL(col, 0)NVL(col, 0)或COALESCE(col, 0)字符串拼接a ba || b多条插入INSERT INTO t VALUES (...), (...)语法兼容但大并发建议用 COPY日期转字符串CONVERT(VARCHAR(10), col, 120)TO_CHAR(col, YYYY-MM-DD)表信息sys.tablespg_tables或svv_tables时间差DATEDIFF(day, a, b)同样支持DATEDIFF(day, a, b)递归查询WITH ... AS (... UNION ALL ...)Redshift 对递归 CTE 支持有限建议用多次 CTAS 或展开的维度表替代这些看起来都是小差异但踩中一个就能让迁移排期多走一周。4.4 面试里常见的连续登录、Top N 问题说句题外话最近面试数据分析师我经常拿 Redshift SQL 出题主要是看候选人有没有理解分组去重和窗口函数。几个常问的方向是连续登录天数、按部门取薪资 Top N、重复订单清洗。这些需求在生产环境里确实高发写法的核心思路我在 2.3 已经给过这里再补充一个 Top N 的常见解法SELECT department_id, user_id, amount, RANK() OVER (PARTITION BY department_id ORDER BY amount DESC) AS rk FROM sales;如果想取每个部门前 3 名外面套一层SELECT * FROM ( SELECT department_id, user_id, amount, RANK() OVER (PARTITION BY department_id ORDER BY amount DESC) AS rk FROM sales ) ranked WHERE rk 3;RANK 在处理并列排名时比较符合业务认知并列第一都算第一名后面会跳号。如果要求严格连续排名就改用 DENSE_RANK。4.5 大表清洗用 CTAS 重建表代替 UPDATERedshift 上对超大表做 UPDATE 或 DELETE 很伤性能而且会产生死行。数据量大的清洗场景我习惯用 CTAS 重建表的方式。思路很简单把需要保留的数据和清洗后的数据写入一张新表然后通过事务切换表名。CREATE TABLE orders_clean AS SELECT DISTINCT user_id, order_id, COALESCE(NULLIF(TRIM(remark), ), 暂无) AS remark FROM orders WHERE order_id IS NOT NULL;跑完确认数据没问题后再做表切换BEGIN; ALTER TABLE orders RENAME TO orders_old; ALTER TABLE orders_clean RENAME TO orders; DROP TABLE orders_old; COMMIT;这个办法比在原表上 UPDATE 干净得多也方便回滚。记得清洗后检查 DISTKEY 和 SORTKEY 是否被保留如果原来的结构很关键最好在 CTAS 语句里显式带上DISTSTYLE和SORTKEY选项别让新表丢失原来精心设计的物理结构。写在最后这几年的实践下来我最大的感受是在 Redshift 上写 SQL不能只看 SQL 本身还得把集群架构、表分布方式、数据倾斜情况都当成 SQL 运行环境的一部分。慢查询大多不是语法错而是物理设计不合理。我现在拿到一个新的查询需求习惯先在草稿纸上画一遍数据流想想这表是怎么分布的、JOIN 会不会重分布、过滤能不能命中排序键然后才落到编辑器里写代码。这个习惯帮我少踩了很多坑。另外给个小建议Redshift 的查询日志真的很有价值。慢查询跑完别急着改 SQL先翻翻STL_QUERY和EXPLAIN把问题定位清楚再动手。很多时候问题不在 SQL 写法而在表结构折腾 SQL 只是白费功夫。希望这篇实战指南能让你在 Amazon Redshift 上写 SQL 的时候心里更有底。