在RedShift中声明一个变量

Question

SQL Server 具有声明变量的能力，然后可以在查询中调用该变量，如下所示：

DECLARE @StartDate date;
SET @StartDate = '2015-01-01';

SELECT *
FROM Orders
WHERE OrderDate >= @StartDate;

这个功能在亚马逊的RedShift中可以工作吗？从文档来看，DECLARE仅用于游标。看起来我需要使用SET函数，但是当我尝试使用时，出现了错误。

set session StartDate = '2015-01-01';
 [Error Code: 500310, SQL State: 42704]  [Amazon](500310) Invalid operation: unrecognized configuration parameter "startdate";

在 RedShift 中是否有可能做到这一点？

- mikebmassey

Answer 1

Slavik Meltser的回答非常好。作为这个主题的一个变体，你也可以使用WITH结构：

WITH tmp_variables AS (
SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id
)

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);

- Johan Lammens

---

在我的使用情况下，这更好：一个定制的ETLM系统，在创建临时表之后不会“解释”第二个语句。 - Merlin

你是正确的，这也可以工作。但是，这只是一种美学方法，因为在查询运行期间，Redshift中的WITH语句会转换为TEMP TABLE。 - Slavik Meltser

我想知道这是否会对性能产生任何影响，或者RedShift是否足够聪明以理解这些是常量。例如，它会在每一行上评估（选择StartDate..）还是只运行并存储结果一次。 我对RedShift的优化能力做出了假设，但我发现使用“where”子句时实际上非常慢。 - Leo Ufimtsev

好奇心驱使我尝试查询了超过5000万行数据并进行了聚合/去重操作。无论是硬编码日期还是在“WITH”子句中使用函数（DATE/TO_TIMESTAMP）以及日期，都不会对性能产生影响。（大约18秒）。WITH vars as (SELECT TO_TIMESTAMP('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')::TIMESTAMP as from_date - Leo Ufimtsev

Answer 2

实际上，您可以使用临时表模拟变量，创建一个表并设置数据，然后就可以使用了。

像这样：

CREATE TEMP TABLE tmp_variables AS SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id;

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);

事务执行后，临时表将被删除。
临时表绑定在每个会话（连接）上，因此无法在会话之间共享。

- Slavik Meltser

Answer 3

不，Amazon Redshift没有变量的概念。Redshift自我呈现为PostgreSQL，但是高度修改。

在2014年的AWS re:Invent大会上提到了用户定义函数，这可能满足您的某些需求。

2016年的更新: 标量用户定义函数可以执行计算，但不能作为存储变量。

- John Rotenstein

Answer 4

请注意，如果您使用psql客户端进行查询，则在Redshift中仍然可以像往常一样使用psql变量。

$ psql --host=my_cluster_name.clusterid.us-east-1.redshift.amazonaws.com \
     --dbname=your_db   --port=5432 --username=your_login -v dt_format=DD-MM-YYYY

# select current_date;     
    date    
------------
 2015-06-15
(1 row)

# select to_char(current_date,:'dt_format');
  to_char   
------------
 15-06-2015
(1 row)

# \set
AUTOCOMMIT = 'on'
...
dt_format = 'DD-MM-YYYY'
...
# \set dt_format 'MM/DD/YYYY'
# select to_char(current_date,:'dt_format');
  to_char   
------------
 06/15/2015
(1 row)

- DogBoneBlues

Answer 5

现在您可以使用用户定义的函数（UDF）来实现您想要的功能：

CREATE FUNCTION my_const()
    RETURNS CSTRING IMMUTABLE AS 
    $$ return 'my_string_constant' $$ language plpythonu;

不幸的是，这确实需要您的Redshift数据库拥有特定的访问权限。

- nbubis

Answer 6

Redshift是建立在旧版的PostgreSQL上。 在运行的会话中：

set param.variable = 'xxx'
select current_setting('param.variable')
Output`xxx`

select * 
  from your_table
 where filter_column = current_setting('param.variable')

- Fiodar Rezkou

Answer 7

虽然不是确切的答案，但在DBeaver中，您可以设置变量以在IDE中的本地查询中使用。我们的团队发现这对于在生产之前进行测试非常有帮助。

来自此答案：https://dev59.com/llMI5IYBdhLWcg3wS5gv#58308439

然后您应该能够执行：

@set date = '2019-10-09'

SELECT ${date}::DATE, ${date}::TIMESTAMP WITHOUT TIME ZONE

它会产生：

| date       | timestamp           |
|------------|---------------------|
| 2019-10-09 | 2019-10-09 00:00:00 |

再次注意：这仅适用于DBeaver IDE。当集成到存储过程中或从其他工具调用时，此SQL将无法工作。

- Gabe