Amazon Redshift用CTE定义日期变量查询报错原因及替代方案

报错原因 #

Amazon Redshift的查询优化器对非关联标量子查询的支持存在限制，你在BETWEEN运算符两侧分别用子查询从CTE中取日期值的写法，会被Redshift内部误识别为不支持的关联子查询模式，即使你的子查询本身没有引用外部查询的任何字段，也会触发内部限制报错。

可行的参数化实现方案 #

方案1：CTE交叉连接（最通用，兼容大多数SQL语法） #

直接将存储参数的CTE和业务表做交叉连接，因为参数CTE只有1行数据，不会改变业务表的行数，同时可以直接引用CTE的字段做筛选，不会触发子查询限制：

WITH time_variables AS 
(SELECT 
'2020-06-01'::date AS start_date,
'2020-06-30'::date AS end_date)

SELECT 
c.customer
FROM customers c
CROSS JOIN time_variables t
WHERE c.order_date BETWEEN t.start_date AND t.end_date

方案2：使用Redshift会话变量（适合同会话多次调用参数的场景） #

Redshift支持自定义会话级变量，通过SET命令定义变量，查询时用current_setting函数调用即可：

-- 定义会话级日期变量
SET query_start_date = '2020-06-01';
SET query_end_date = '2020-06-30';

-- 业务查询
SELECT 
customer
FROM customers
WHERE order_date BETWEEN current_setting('query_start_date')::date AND current_setting('query_end_date')::date

方案3：动态传参（适合脚本/BI工具调用场景） #

如果你是通过Python脚本、JDBC/ODBC连接或者BI工具执行查询，可以直接使用对应工具的参数占位符（比如%s、?等）传递日期参数，由上层工具完成参数注入，避免SQL硬编码。

内容的提问来源于stack exchange，提问作者Michi