使用CTE变量的操作错误无效

Question

DB-Fiddle

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    customer VARCHAR(255)
);

INSERT INTO customers
(order_date, customer)
VALUES 
('2020-04-10', 'user_01'),
('2020-04-15', 'user_02'),
('2020-05-18', 'user_03'),
('2020-05-26', 'user_04'),
('2020-06-03', 'user_05'),
('2020-06-05', 'user_06'),
('2020-06-24', 'user_07');

我将使用以下查询每月提取用户：

SELECT 
c.customer
FROM customers c
WHERE c.order_date BETWEEN '2020-06-01' AND '2020-06-30'

此查询工作正常，没有任何问题。

现在，我希望将日期用作变量，因此切换到：

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
WHERE c.order_date BETWEEN (SELECT start_date FROM time_variables) AND (SELECT end_date FROM time_variables)

但是，当我运行这个查询时，我会得到错误：

Invalid operation: This type of correlated subquery pattern is not supported due to internal error;

您知道为什么在amazon-redshift中会发生此错误吗？

还有其他方法可以使用日期作为变量吗？

Answer 1

之所以会发生这种情况，是因为您编写的查询使用了一个相关的子查询，即当另一个SELECT的子句中的SELECT (如WHERE子句中的SELECT )能够(可能)为外部SELECT的每一行解析不同的值时。在您的示例中，您的CTE只有一行，因此只能生成相同的值，但是查询结构不需要这样做。Redshift b/c上不支持相关的子查询结构，解决海量数据上的这些相关性变得非常昂贵(因此速度慢)。

若要解析更改查询以使用联接，请执行以下操作。如下所示：

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 v
WHERE c.order_date BETWEEN v.start_date AND v.end_date

这只是将CTE的一行加入到customer中的每一行。现在很清楚，开始日期和结束日期是如何与客户中的数据相结合的。但是，如果您希望在SQL中放置变量，那么有更好的方法不包括创建新的表结构。

Answer 2

它使用join工作吗？

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 JOIN
     time_variables tv
     ON c.order_date BETWEEN tv.start_date AND tv.end_date;

Answer 3

要使用变量，可以使用

DECLARE @StartDate DATE = Datefromparts(2020,06,01)
DECLARE @EndDate DATE= Datefromparts(2020,06,30)

SELECT 
c.customer
FROM customers c
WHERE c.order_date BETWEEN @StartDate AND @EndDate