数据集-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)
- 共享 — 复制、发行并通过任何媒介或格式传播本素材
- 改编 — 重新混合、变形、建立在本素材基础上
可以将其用于任何目的,包括商业用途,只要遵循许可条款。
归属 — 您必须适当地表示原作者名字、提供指向本许可证的链接,同时指明是否(对原始内容)进行了修改。(可以使用数据,但是需要在合适的位置注明数据的来源,并明确数据的许可证类型。)
分析
Airbnb 的数据集包含了多种数据类型,如列表、日历、评论和地区信息等,它们之间存在多种关联。
- listings.csv: 这是详细的住宿列表数据。包含房屋的描述、价格、可用性、设施等。这部分数据非常适合存储为关系型数据。
- reviews.csv: 这是关于房源的评论数据。评论通常与特定的房源关联,所以这是一个典型的一对多的关系:一个房源可能有多个评论。同时,评论内容本身可能包含非结构化的或半结构化的数据(例如,文本、表情符号等),这些数据可以存储为 JSONB。
- calendar.csv.gz: 房源的可用性和价格。可以作为关系型数据来处理,因为它与房源有直接的关系。
- neighbourhoods.csv 和 neighbourhoods.geojson: 这些数据提供了地理信息。GeoJSON 文件尤其适合存储为 JSONB 格式,因为它本身就是一个 JSON 格式。
结合上述数据集,设计以下的数据模型:
- 一个关系型的表格来存储房源信息。
- 一个关系型的表格来存储评论,其中评论内容字段可以是 JSONB 格式。
- 一个关系型的表格来存储日历数据。
- 一个关系型的表格来存储邻居的信息,并使用 JSONB 字段来存储 GeoJSON 数据。
表结构
listings: 这张表包含了房源 的信息。每个房源都有一个唯一的 id,还有其他的相关属性如名称、价格、设施(以 JSONB 格式存储)等。 calendar: 这张表包含了每个房源的可用性 ,价格变动以及最低和最高住宿天数。每条记录都关联到一个 listing_id。 reviews: 这张表存储了房源的评论 。每条评论都与一个 listing_id 关联。 neighbourhoods: 这张表列出了邻里和邻里组 ,每个房源在 listings 表中都有一个与之相关联的邻里。
- listings 和 calendar: 一对多关系。一个房源在
calendar
表中可以有多个条目(为了表示不同的日期或可用性)。 - 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 关联。