关系不存在错误。

Question

我非常感谢我的帮助。我认为这是最后的挑战，使我无法完成一个星期的项目。

我的错误:亚马逊无效操作:关系“租金”不存在；

我的代码：

WITH 
RentDotComOnly AS
(
  SELECT 
    distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
    COUNT(distinct apt_unique_id) AS "rent_count_clean_zip", 
    -- AVG((low_price+high_price)/2) AS "rent_avg_price", 
    0.85*min(low_price) AS "rent_lower_bound", 
    1.15*max(high_price) AS "rent_upper_bound"
  FROM 
    archived_apartments 
  WHERE 
    source_type in (29,36,316) 
    AND week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1  
    AND archived_apartments.high_price <> 0 
  GROUP BY monthlyzip
),
AllRJData AS
(
  SELECT
    distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
    COUNT(distinct apt_unique_id) AS "all_count_clean_zip"
    --, AVG((low_price+high_price)/2) AS "all_avg_price"
  FROM 
    archived_apartments 
  WHERE 
    week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1  
  GROUP BY monthlyzip
),
TrueComps AS
(
  SELECT
    distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
    COUNT(distinct apt_unique_id) AS "true_comps"
   FROM
    archived_apartments
   WHERE
    week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1 
    AND archived_apartments.high_price <> 0 
    AND archived_apartments.low_price > RentDotComOnly.rent_lower_bound
    AND archived_apartments.low_price < RentDotComOnly.rent_upper_bound
    OR
    week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1 
    AND archived_apartments.high_price <> 0 
    AND archived_apartments.high_price > RentDotComOnly.rent_lower_bound
    AND archived_apartments.high_price < RentDotComOnly.rent_upper_bound
)

SELECT 
  distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
  RentDotComOnly.rent_count_clean_zip AS "RentComOnly",
  AllRJData.all_count_clean_zip AS "Total",
  TrueComps.true_comps AS "TrueComps"
FROM
  archived_apartments 
JOIN AllRJData 
ON concat(DATE_PART(mm,archived_apartments.week),archived_apartments.clean_zip) = AllRJData.monthlyzip
JOIN RentDotComOnly
ON concat(DATE_PART(mm,archived_apartments.week),archived_apartments.clean_zip) = RentDotComOnly.monthlyzip
JOIN TrueComps
ON concat(DATE_PART(mm,archived_apartments.week),archived_apartments.clean_zip) = TrueComps.monthlyzip

GROUP BY AllRJData.monthlyzip
ORDER BY AllRJData.monthlyzip

Answer 1

突变问题

问题变了。我还不清楚修订后的问题是什么。

原问题

最初的错误是：

 [Amazon](500310) Invalid operation: relation "rentdotcomonly" does not exist;

在TrueComps的最初定义中，您有：

TrueComps AS
(
  SELECT
    distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
    COUNT(distinct apt_unique_id) AS "true_comps"
   FROM
    archived_apartments
   WHERE
    week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1 
    AND archived_apartments.high_price <> 0 
    AND archived_apartments.low_price > RentDotComOnly.rent_lower_bound
    AND archived_apartments.low_price < RentDotComOnly.rent_upper_bound

在FROM子句中没有使用RentDotComOnly。您需要列出它，以便在此CTE中定义它。