重用查询结果子查询
问题描述:
我有这个丑陋的查询....重用查询结果子查询
sum(CASE
WHEN effective_from_date < '2011-05-24' THEN (rate * (effective_to_date - '2011-05-24' + 1))
WHEN effective_to_date > '2011-05-28' THEN (rate * ('2011-05-28' - effective_from_date + 1))
ELSE (rate * (effective_to_date - effective_from_date + 1))
END
) as price_cal_rate
FROM calendar_event
WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND
((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))
AND
NOT EXISTS (
SELECT days_diff FROM (
SELECT ((effective_from_date - lag(effective_to_date) OVER (PARTITION BY NULL ORDER BY effective_from_date ASC))) AS days_diff, effective_from_date, effective_to_date
FROM calendar_event
WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND
((effective_from_date BETWEEN '2011-05-26' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-26' AND '2011-05-28'))
) AS t WHERE COALESCE(days_diff, 0) > 1
) AND EXISTS (select * from (
select min(effective_from_date) as min_date, max(effective_to_date) as max_date FROM calendar_event
WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND
((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))
) as max_min WHERE min_date <= '2011-05-24' and max_date >= '2011-05-28')
查询被计算在日期范围率....查询是好的......但有一个很多查询重复....我想知道是否有存储该子查询的地方
FROM calendar_event
WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314')
AND
((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))
的结果,并用它在我的查询的好办法....
答
你可以使用临时表作为“亩太短”的建议泰德但如果你只需要在一个单一的“主”的查询结果,你使用PostgreSQL 8.4或更高版本,你也可以使用with queries
+0
非常感谢球员 – Rob 2011-05-08 07:18:08
这可能是什么工作,但它是值得一试。 pgsql非常擅长于在运行时优化看起来像是一次重复的重复。我肯定会比较这两个查询的解释分析输出,以查看当您创建临时表或使用WITH子句时是否更快。查找与,它几乎没有被制作成您所查询的表中的临时表 – 2011-05-08 14:28:20