在参数内计算结果的逻辑有问题

Question

我创建了一个包含日期和位置信息的大型数据库，其中包含我的所有价格和我的竞争对手的所有价格。

我想缩小我的数据库，只在位置和价格基础上的“真正的”竞争对手，因为我们在不同的位置收取不同的价格。例如，我只想知道比我低或高出1美元的竞争者的数量。

我当前的代码停滞不前，无法产生结果。我想这是因为我实现了JOIN ON。

为了进行调试，我将它分离出来，并获得了前两个表的结果。这正是我想要的。对于第三个表"TrueComps"，就没有这样的运气了。

这是连接3个表的结果，因此很复杂。我刚接触SQL，因此对学习新的解决方案很感兴趣。我相信有一个比这个更好的解决方案：

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 = '2015-07-06' 
    AND is_house <> 1  
    AND archived_apartments.high_price <> 0 
  GROUP BY monthlyzip, archived_apartments.week, archived_apartments.clean_zip
),
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 = '2015-07-06' 
    AND is_house <> 1  
  GROUP BY monthlyzip, archived_apartments.week, archived_apartments.clean_zip
),
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, RentDotComOnly
   WHERE
    week = '2015-07-06' 
    AND is_house <> 1 
    AND archived_apartments.high_price <> 0 
    AND low_price > 10000
    GROUP BY monthlyzip, archived_apartments.week, archived_apartments.clean_zip
)

SELECT 
  distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
  TrueComps.true_comps AS "TrueComps"
FROM
  archived_apartments, TrueComps

GROUP BY monthlyzip, archived_apartments.week, archived_apartments.clean_zip, truecomps.true_comps
ORDER BY monthlyzip

原始代码：

AND (low_price > RentDotComOnly.rent_lower_bound and low_price < RentDotComOnly.rent_upper_bound) or (high_price < RentDotComOnly.rent_upper_bound and high_price > RentDotComOnly.rent_lower_bound)

我的完整代码：

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 = '2015-07-06' 
    AND is_house <> 1  
    AND archived_apartments.high_price <> 0 
  GROUP BY monthlyzip, archived_apartments.week, archived_apartments.clean_zip
),
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, archived_apartments.week, archived_apartments.clean_zip
),
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, RentDotComOnly
   WHERE
    week between '2015-07-06' and '2015-10-12'
    AND is_house <> 1 
    AND archived_apartments.high_price <> 0 
    AND (low_price > RentDotComOnly.rent_lower_bound and low_price < RentDotComOnly.rent_upper_bound) or (high_price < RentDotComOnly.rent_upper_bound and high_price > RentDotComOnly.rent_lower_bound)    
  GROUP BY monthlyzip, archived_apartments.week, archived_apartments.clean_zip
)

SELECT 
  distinct concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
  RentDotComOnly.rent_count_clean_zip AS "RentOnly",
  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, archived_apartments.week, archived_apartments.clean_zip, rentdotcomonly.rent_count_clean_zip, allrjdata.all_count_clean_zip, truecomps.true_comps
ORDER BY AllRJData.monthlyzip

Answer 1

尝试在TrueComps中添加联接条件

FROM
    archived_apartments INNER JOIN RentDotComOnly
        ON concat(DATE_PART(mm,archived_apartments.week),archived_apartments.clean_zip) =
           RentDotComOnly.monthlyzip

Answer 2

我想您可能把WHERE子句最后一部分的括号弄错了。我不知道你在尝试实现什么逻辑，但我猜修复它的方法是：

AND (
            low_price  > RentDotComOnly.rent_lower_bound
        and low_price  < RentDotComOnly.rent_upper_bound
    or      high_price < RentDotComOnly.rent_upper_bound
        and high_price > RentDotComOnly.rent_lower_bound
)

正如您已经写好的，or'd条件没有与其他条件结合在一起，而是独立存在的，这也可能导致您看到的速度减慢。

另一种猜测是，你正在寻找价格范围的重叠。有没有可能你真的想要这个？

AND (
            low_price  <= RentDotComOnly.rent_upper_bound
        and high_price >= RentDotComOnly.rent_lower_bound
)