快速开始
教程
Dataset Airbnb

数据集-Inside Airbnb

简介

Airbnb Data Collection: Inside Airbnb 提供了各种城市的 Airbnb 公开数据。数据中有关于房源、评论和日历的详细信息。这个数据集 (opens in a new tab)可以模拟房东和房客、房源和评论之间的关系。

许可

This data is licensed under a Creative Commons Attribution 4.0 International License（简称 CC BY 4.0）

  1. 共享 — 复制、发行并通过任何媒介或格式传播本素材
  2. 改编 — 重新混合、变形、建立在本素材基础上

可以将其用于任何目的，包括商业用途，只要遵循许可条款。

归属 — 您必须适当地表示原作者名字、提供指向本许可证的链接，同时指明是否（对原始内容）进行了修改。（可以使用数据，但是需要在合适的位置注明数据的来源，并明确数据的许可证类型。）

分析

Airbnb 的数据集包含了多种数据类型，如列表、日历、评论和地区信息等，它们之间存在多种关联。

  1. listings.csv: 这是详细的住宿列表数据。包含房屋的描述、价格、可用性、设施等。这部分数据非常适合存储为关系型数据。
  2. reviews.csv: 这是关于房源的评论数据。评论通常与特定的房源关联，所以这是一个典型的一对多的关系：一个房源可能有多个评论。同时，评论内容本身可能包含非结构化的或半结构化的数据（例如，文本、表情符号等），这些数据可以存储为 JSONB。
  3. calendar.csv.gz: 房源的可用性和价格。可以作为关系型数据来处理，因为它与房源有直接的关系。
  4. neighbourhoods.csv 和 neighbourhoods.geojson: 这些数据提供了地理信息。GeoJSON 文件尤其适合存储为 JSONB 格式，因为它本身就是一个 JSON 格式。

结合上述数据集，设计以下的数据模型：

  • 一个关系型的表格来存储房源信息。
  • 一个关系型的表格来存储评论，其中评论内容字段可以是 JSONB 格式。
  • 一个关系型的表格来存储日历数据。
  • 一个关系型的表格来存储邻居的信息，并使用 JSONB 字段来存储 GeoJSON 数据。

表结构

listings: 这张表包含了房源 的信息。每个房源都有一个唯一的 id，还有其他的相关属性如名称、价格、设施（以 JSONB 格式存储）等。 calendar: 这张表包含了每个房源的可用性 ，价格变动以及最低和最高住宿天数。每条记录都关联到一个 listing_id。 reviews: 这张表存储了房源的评论 。每条评论都与一个 listing_id 关联。 neighbourhoods: 这张表列出了邻里和邻里组 ，每个房源在 listings 表中都有一个与之相关联的邻里。

  1. listings 和 calendar: 一对多关系。一个房源在 calendar 表中可以有多个条目（为了表示不同的日期或可用性）。
  2. listings 和 reviews: 一对多关系。一个房源可以有多条评论。
表结构：
<strong>calendar</strong>:
CREATE TABLE calendar (
    listing_id INTEGER REFERENCES listings(id),
    date DATE,
    available CHAR(1) CHECK (available IN ('t', 'f')),
    price NUMERIC(10,2),
    adjusted_price NUMERIC(10,2),
    minimum_nights INTEGER,
    maximum_nights INTEGER
);
<strong>listings</strong><strong>:</strong>
CREATE TABLE listings (
    id INTEGER PRIMARY KEY,
    listing_url TEXT,
    scrape_id BIGINT,
    last_scraped DATE,
    source TEXT,
    name TEXT,
    description TEXT,
    neighborhood_overview TEXT,
    picture_url TEXT,
    host_id INTEGER,
    host_url TEXT,
    host_name TEXT,
    host_since DATE,
    host_location TEXT,
    host_about TEXT,
    host_response_time TEXT,
    host_response_rate TEXT,
    host_acceptance_rate TEXT,
    host_is_superhost CHAR(1) CHECK (host_is_superhost IN ('t', 'f')),
    host_thumbnail_url TEXT,
    host_picture_url TEXT,
    host_neighbourhood TEXT,
    host_listings_count INTEGER,
    host_total_listings_count INTEGER,
    <strong>host_verifications JSONB,   //['phone', 'work_email']</strong>
    host_has_profile_pic CHAR(1) CHECK (host_has_profile_pic IN ('t', 'f')),
    host_identity_verified CHAR(1) CHECK (host_identity_verified IN ('t', 'f')),
    neighbourhood TEXT,
    neighbourhood_cleansed TEXT,
    neighbourhood_group_cleansed TEXT,
    latitude NUMERIC(9,6),
    longitude NUMERIC(9,6),
    property_type TEXT,
    room_type TEXT,
    accommodates INTEGER,
    bathrooms_text TEXT,
    bedrooms INTEGER,
    beds INTEGER,
    <strong>amenities JSONB, //["TV", "AC - split type ductless system", "Fire pit", "Coffee maker", "Refrigerator", "Elevator", "Outdoor furniture", "Kitchen", "Pets allowed", "Washer", "Wifi", "Dedicated workspace"]</strong>
    price NUMERIC(10,2),
    minimum_nights INTEGER,
    maximum_nights INTEGER,
    availability_30 INTEGER,
    availability_60 INTEGER,
    availability_90 INTEGER,
    availability_365 INTEGER,
    number_of_reviews INTEGER,
    first_review DATE,
    last_review DATE,
    review_scores_rating NUMERIC(5,2),
    review_scores_accuracy NUMERIC(5,2),
    review_scores_cleanliness NUMERIC(5,2),
    review_scores_checkin NUMERIC(5,2),
    review_scores_communication NUMERIC(5,2),
    review_scores_location NUMERIC(5,2),
    review_scores_value NUMERIC(5,2),
    license TEXT,
    instant_bookable CHAR(1) CHECK (instant_bookable IN ('t', 'f')),
    calculated_host_listings_count INTEGER,
    reviews_per_month NUMERIC(5,2)
);
<strong>reviews </strong>(与listings一对多的关系):
CREATE TABLE reviews (
    listing_id INTEGER REFERENCES listings(id),
    id BIGINT PRIMARY KEY,
    date DATE,
    reviewer_id BIGINT,
    reviewer_name TEXT,
    comments TEXT
);
<strong>neighbourhoods</strong><del>:</del>
<del>CREATE TABLE neighbourhoods (</del>
<del>    neighbourhood_group TEXT,//数据集里面为空，看介绍应该是下面的上一级概念</del>
<del>    neighbourhood TEXT PRIMARY KEY //这个看数据，是行政区的概念，例如香港数据集里面，包含了18个行政区</del>
<del>);</del>

使用 JSONB 查询：

  • 查询提供了 Wifi 和电视的房源：
SELECT id, name
FROM     listings
WHERE amenities @> '["Wifi", "TV"]';
  • 查询没有提供洗衣机的房源：
SELECT id, name
FROM listings
WHERE NOT amenities ? 'Washer';
  • 查找提供“厨房”但不提供“电视”的房源，并列出其描述和价格：
SELECT id, name, description, price
FROM listings
WHERE amenities @> '["Kitchen"]' AND NOT amenities ? 'TV';

关系型查询：

  • 查询某个特定邻居的所有房源：
SELECT l.id, l.name
FROM listings l
JOIN neighbourhoods n ON l.neighbourhood = n.neighbourhood
WHERE n.neighbourhood = 'Central & Western';
  • 查询在未来一个月内都可用的房源：
SELECT l.id, l.name
FROM listings l
WHERE NOT EXISTS (
    SELECT 1
    FROM calendar c
    WHERE c.listing_id = l.id AND c.available = 'f' AND c.date BETWEEN '2023-09-17' AND '2023-10-17'
);

结合关系型和 JSONB 查询：

  • 查询提供 Wifi 设施且最近一个月内有新评论的房源：
SELECT DISTINCT l.id, l.name
FROM listings l
JOIN reviews r ON l.id = r.listing_id
WHERE l.amenities @> '["Wifi"]' AND r.date BETWEEN '2023-08-17' AND '2023-09-17';
  • 查询在某个特定邻居并且提供了工作空间的房源：
SELECT l.id, l.name
FROM listings l
JOIN neighbourhoods n ON l.neighbourhood = n.neighbourhood
WHERE n.neighbourhood = 'Central & Western' AND l

业务场景

房源可用性查询

a. 依据特定便利设施筛选的即时可预订房源

场景描述：某租客想要在接下来的 30 天内，预订一个提供免费 WiFi 和允许带宠物的房源，并希望它是即时可预订的。

SELECT id, name, description
FROM listings
WHERE amenities @> '["Wifi", "Pets allowed"]' AND availability_30 > 0 AND instant_bookable = 't';

b. 查找经过多种验证方法的主人的房源

场景描述：租客希望预订那些经过手机和工作邮箱验证的主人的房源。

SELECT id, name, description
FROM listings
WHERE host_verifications @> '["phone", "work_email"]';

评价信息查询

a. 查找具有特定便利设施的高评价房源

场景描述：某租客只想预订那些设有 AC 并且总评分在 4.5 以上的房源。

SELECT l.id, l.name, l.description, l.review_scores_rating
FROM listings l
WHERE l.amenities @> '["AC - split type ductless system"]' AND l.review_scores_rating > 4.5;

b. 获取在特定日期之后评论的与 Host(房东)验证方式相关的评价

场景描述：Airbnb 管理层可能想要知道在某一日期后，已通过工作电子邮件进行验证的 Host(房东)收到的评价。

SELECT r.id, r.date, r.comments
FROM reviews r
JOIN listings l ON r.listing_id = l.id
WHERE l.host_verifications @> '["work_email"]'AND r.date > '2023-01-01';

业务增长和趋势分析

a. 分析近一个月内新上架并提供特定便利设施的房源数量

场景描述：管理层希望知道最近一个月内新上架并提供"Free Wifi"和"Elevator"的房源数量，以分析业务增长趋势。

SELECT COUNT(id) as newly_listed_properties
FROM listings
WHERE amenities @> '["Wifi", "Elevator"]' AND last_scraped BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE;

b. 统计房东的房源数量与其验证方式之间的关系

场景描述：为了了解哪种验证方式的房东最活跃，我们希望统计每种验证方式的房东拥有的房源数量。

WITH VerificationCounts AS (
   SELECT host_id,
          JSONB_ARRAY_ELEMENTS_TEXT(host_verifications) AS verification_method
   FROM listings
)
SELECT verification_method, COUNT(DISTINCT host_id) AS host_count
FROM VerificationCounts
GROUP BY verification_method
ORDER BY host_count DESC;

Host(房东)画像分析

a. 筛选经常响应并且已验证身份的房东

场景描述：为了保证用户体验，Airbnb 可能想要筛选出那些响应率高且已经验证身份的房东。

SELECT id, host_name, host_response_rate, host_about
FROM listings
WHERE host_response_rate > '90%'
AND host_identity_verified = 't';

b. 基于特定验证方式的房东房源平均评分

场景描述：为了了解哪种验证方式的房东得到了较高的评价，我们可以按验证方式统计房源的平均评分。

WITH HostAverageRating AS (
    SELECT host_id,
           AVG(review_scores_rating) AS avg_rating
    FROM listings
    GROUP BY host_id
)
SELECT JSONB_ARRAY_ELEMENTS_TEXT(l.host_verifications) AS verification_method,
       AVG(h.avg_rating) as average_rating
FROM listings l
JOIN HostAverageRating h ON l.host_id = h.host_id
GROUP BY verification_method
ORDER BY average_rating DESC;

房源特性和偏好分析

a. 统计最受欢迎的便利设施

场景描述：Airbnb 可能想要了解哪些便利设施最受租客欢迎，以优化房源推荐。

SELECT JSONB_ARRAY_ELEMENTS_TEXT(amenities) AS amenity,
       COUNT(id) AS listings_count
FROM listings
GROUP BY amenity
ORDER BY listings_count DESC;

b. 筛选出特定地理位置并提供某些便利设施的房源

场景描述：某租客想要在某一特定行政区内预订提供免费 Wifi 和允许带宠物的房源。

SELECT id, name, neighbourhood
FROM listings
WHERE amenities @> '["Wifi", "Pets allowed"]'AND neighbourhood = '某一行政区';

评论和反馈分析

a. 查找某特定行政区内评分最高的房源

场景描述：租客希望在某一特定行政区预订评分最高的房源。

SELECT id, name, review_scores_rating
FROM listings
WHERE neighbourhood = '某一行政区' ORDER BY review_scores_rating DESC
LIMIT 5;

b. 分析评论中提到的特定便利设施

场景描述：Airbnb 可能想要了解租客在评论中是否提及某些便利设施，如“Wifi”。

SELECT r.listing_id, r.comments
FROM reviews r
JOIN listings l ON r.listing_id = l.id
WHERE l.amenities @> '["Wifi"]' AND r.comments ILIKE '%wifi%';

listings: 这张表包含了房源 的信息。每个房源都有一个唯一的 id，还有其他的相关属性如名称、价格、设施（以 JSONB 格式存储）等。 calendar: 这张表包含了每个房源的可用性 ，价格变动以及最低和最高住宿天数。每条记录都关联到一个 listing_id。 reviews: 这张表存储了房源的评论 。每条评论都与一个 listing_id 关联。

示例-数据分析服务支持