使用CTE:重写PostgreSQL查询

Question

我有以下代码从PostgreSQL中的日期范围中提取记录，它按预期工作。"；结束日期"；由"；日期"；从最后一个记录开始的列；开始日期"；是通过从"0"减去7天间隔来计算的；结束日期"；。

SELECT date 
FROM files
WHERE daterange((
(SELECT date FROM files ORDER BY date DESC LIMIT 1) - interval '7 day')::date, -- "start date"
(SELECT date FROM files ORDER BY date DESC LIMIT 1)::date, -- "end date"
'(]') @> date::date 
ORDER BY date ASC

我正在尝试使用CTE重写这个查询，这样我就可以用end_date和start_date等值来替换这些子查询。使用这种方法可能吗？还是应该寻找其他替代方案，如变量？我还在学习SQL。

WITH end_date AS 
(
SELECT date FROM files ORDER BY date DESC LIMIT 1
), 
start_date AS 
(
SELECT date FROM end_date - INTERVAL '7 day'
) 
SELECT date 
FROM files
WHERE daterange(
start_date::date, 
end_date::date, 
'(]') @> date::date 
ORDER BY date ASC

现在我得到以下错误：

ERROR:  syntax error at or near "-"
LINE 7:     SELECT date FROM end_date - INTERVAL '7 day'

Answer 1

您不需要两个CTE，只需要一个就可以了，它可以连接起来过滤数据。

WITH RECURSIVE files AS (
SELECT CURRENT_DATE date, 1 some_value
UNION ALL
SELECT (date + interval '1 day')::date, some_value + 1 FROM files
WHERE date < (CURRENT_DATE + interval '1 month')::date
),
dates AS (
SELECT 
(MAX(date) - interval '7 day')::date from_date,
MAX(date) to_date 
FROM files    
)
SELECT f.* FROM files f
JOIN dates d ON daterange(d.from_date, d.to_date, '(]') @> f.date

你甚至可以在CTE中最初将其设置为日期范围，然后像这个一样使用它

WITH dates AS (
SELECT 
daterange((MAX(date) - interval '7 day')::date, MAX(date), '(]') range
FROM files    
)
SELECT f.* FROM files f
JOIN dates d ON d.range @> f.date

这里，第一个CTE仅用于生成一些数据。

它将获取上周日期的所有file行，不包括from_date和to_date。

日期2022-09-26252022-09-27年26年2022-09-28年27年2022-09-29年28年2022-09-30292022-10-01302022-10-0231

Answer 2

I认为这就是您想要的：

WITH end_date AS 
(
SELECT date FROM files ORDER BY date DESC LIMIT 1
), 
start_date AS 
(
SELECT date  - INTERVAL '7 day' as date
FROM end_date
) 
SELECT F.date, S.date startDate, E.date endDate
FROM files F
JOIN start_date S on F.date >= S.date
JOIN end_date E on F.date <= E.date
ORDER BY date ASC;

Answer 3

我希望我没有重复任何内容，但如果我正确理解你的问题，我认为这会奏效：

with cte as (
select max (date)::date as max_date from files
)
select date
from files
cross join cte
where date >= max_date - 7

甚至：

select date
from files
where date >= (select max (date)::date - 7 from files)

由于您已经确定CTE具有最大日期，因此确实没有必要进一步将其与介于两者之间的<=或范围。你可以在那一天减去7天后简单地说任何话。

上面代码中的错误是因为你想要这个：

SELECT date - INTERVAL '7 day' as date FROM end_date 

而不是这个：

SELECT date FROM end_date - INTERVAL '7 day'

你在从表中减去，这没有道理。