sql - 具有多个内部联接的 SQL 查询返回错误的值计数

Question

我是 sql 新手，我正在使用 oracle 中创建的数据库练习有关阿姆斯特丹和柏林的 airbnb 列表。我正在尝试加入表 HOSTS（host_id，host_name）、LISTINGS（包括两个城市中的所有列表，属性为 Listings_id、listings_name、Price、host_id...）、Neighborhoods（Neighbourhood_Group、City 和 Neighbourhood）和评论（以 review_id、listings_id 作为外键、reviewer_id、reviewer_name 和评论）。

现在我想写一个查询，它返回平均价格、最低价格、最高价格、城市（柏林或阿姆斯特丹）、社区（Centrum、Alexanderplatz...）、房源数量和评论数量，所有按不同的社区和 WHERE 语句分组，这限制了那些列表的回报，这些列表由总共少于 3 个列表的主机托管。

现在，如果我在没有评论表的情况下运行查询并且仅按 amount_listings 排序，我会为“amount_listings”列获得每个社区的正确列表数量。

SELECT avg(l.price) as Mean_Price, 
n.city, 
n.neighbourhood, 
count (l.listings_id) as amount_listings,
min(l.price),
max(l.price)
    FROM listings l
    INNER JOIN neighborhood n
    ON l.neighbourhood = n.neighbourhood

    INNER JOIN hosts h
    ON l.host_id = h.host_id

WHERE h.host_id IN (
    SELECT host_id
    FROM listings
    GROUP BY host_id
    HAVING count(host_id) < 3
)
GROUP BY n.neighbourhood, n.city
ORDER BY amount_listings DESC;

但是，如果我在查询中包含评论数量，则结果不正确。评论数量和列表数量列显示错误数量（太多）。

SELECT avg(l.price) as Mean_Price, count(l.listings_id) as amount_listings, 
       min(l.price), max(l.price), n.city, n.neighbourhood, count(r.review_id) as amount_reviews
FROM listings l
INNER JOIN neighborhood n
ON l.neighbourhood = n.neighbourhood

INNER JOIN hosts h
ON l.host_id = h.host_id

INNER JOIN reviews r
ON l.listings_id= r.listings_id
WHERE h.host_id IN (
    SELECT host_id
    FROM listings
    GROUP BY host_id
    HAVING count(host_id) < 3
)

GROUP BY n.neighbourhood, n.city
ORDER BY amount_listings DESC, amount_reviews DESC;

我不知道为什么 amount_listings 和 amount_reviews 会返回这样错误的结果。

标签： sqloraclejoin

Answer 1

加入前聚合。您希望将评论计数加入每个列表，然后将汇总的列表信息加入社区。

select
  lr.mean_price,
  n.city,
  n.neighbourhood,
  lr.amount_listings,
  lr.min_price,
  lr.max_price,
  lr.amount_reviews
from neighborhood n
join
(
  select
    l.neighbourhood,
    min(l.price) as min_price,
    max(l.price) as max_price,
    avg(l.price) as mean_price,
    count(*) as amount_listings,
    coalesce(sum(r.reviews_for_listing), 0) as amount_reviews
  from listings l
  left join
  (
    select
      listings_id,
      count(*) as reviews_for_listing
    from reviews
    group by listings_id
  ) r on r.listings_id = l.listings_id
  where l.host_id in
  (
    select host_id
    from listings
    group by host_id
    having count(*) < 3
  )
  group by l.neighbourhood
) lr on lr.neighbourhood = n.neighbourhood
order by n.city, n.neighbourhood;