酒店业务（半结构化数据支持）

背景

在繁忙的纽约市，餐厅的数量众多，从街头小摊到高档餐厅，种类繁多。对于一个数据分析师来说，纽约是一个充满机会和挑战的数据宝藏。通过对餐厅的数据进行深入挖掘，分析师可以为食客提供更具价值的推荐，例如：哪家餐厅的食品安全评分更高、哪家餐厅在特定时间段内顾客评价更好等。

为了进行这样的深入分析，数据分析师需要一个可以同时存储结构化和非结构化数据的数据库，而且必须具有高效的查询性能。传统的关系型数据库在处理结构化数据方面很出色，但在处理如顾客评价、餐厅介绍等非结构化数据时，可能会遇到一些挑战。同样，虽然文档型数据库在处理非结构化数据方面很灵活，但在执行某些复杂查询时可能不如关系型数据库高效。

这时，ProtonBase 数据库显得尤为出色。它不仅完全兼容 PostgreSQL 的语法生态，而且能够完美地结合关系型和文档型数据库的优势。这为数据分析师提供了一个高效、灵活的工具，以帮助他们从海量的餐厅数据中提炼出有价值的信息。

本教程将指导您如何在 ProtonBase 中创建和查询一个纽约餐厅的数据集，并展示其在数据查询和管理上的强大功能。

数据结构与准备的样本数据

您不需要从零开始处理原始的 JSON 数据。为了使您的体验更为流畅，我们已经将数据处理为与表结构相匹配的 CSV 格式。这种格式可以方便地导入 ProtonBase，充分利用其强大的存储和查询功能。

CSV 样本数据：

1   Morris Park Bake Shop  Bronx  Bakery {"coord": [-73.856077, 40.848447], "street": "Morris Park Ave", "zipcode": "10462", "building": "1007"}    [{"date": {"$date": 1393804800000}, "grade": "A", "score": 2}, {"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, {"date": {"$date": 1358985600000}, "grade": "A", "score": 10}, {"date": {"$date": 1322006400000}, "grade": "A", "score": 9}, {"date": {"$date": 1299715200000}, "grade": "B", "score": 14}]   30075445
2  Wendy'S    Brooklyn   Hamburgers {"coord": [-73.961704, 40.662942], "street": "Flatbush Avenue", "zipcode": "11225", "building": "469"} [{"date": {"$date": 1419897600000}, "grade": "A", "score": 8}, {"date": {"$date": 1404172800000}, "grade": "B", "score": 23}, {"date": {"$date": 1367280000000}, "grade": "A", "score": 12}, {"date": {"$date": 1336435200000}, "grade": "A", "score": 12}]    30112340
3  Dj Reynolds Pub And Restaurant Manhattan  Irish  {"coord": [-73.98513559999999, 40.7676919], "street": "West   57 Street", "zipcode": "10019", "building": "351"}   [{"date": {"$date": 1409961600000}, "grade": "A", "score": 2}, {"date": {"$date": 1374451200000}, "grade": "A", "score": 11}, {"date": {"$date": 1343692800000}, "grade": "A", "score": 12}, {"date": {"$date": 1325116800000}, "grade": "A", "score": 12}]    30191841

在开始导入数据之前，请确保您已下载了这份 CSV 文件，并知道其存放路径(例如 /tmp/restaurants.csv)。

预备知识与环境设置

基础的 SQL 语言知识。
已经安装并配置好 ProtonBase 的环境。

主要步骤

1. 创建数据库与数据表

CREATE DATABASE restaurantdb;
 
\c restaurantdb
 
CREATE TABLE restaurants (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   borough text,
   cuisine text,
   address JSONB NOT NULL,
   grades JSONB NOT NULL,
   restaurant_id text
);

2. 导入数据

根据前面的处理，我们已将 JSON 数据转化为与表结构相匹配的格式，现在我们可以使用 COPY 命令将其导入。

\copy restaurants from '/tmp/restaurants.csv';

3. 数据查询

简单查询

SELECT name, borough, cuisine FROM restaurants WHERE borough = 'Brooklyn';

查询含有特定评级的餐厅

SELECT name, grades FROM restaurants WHERE grades @> '[{"grade": "A"}]';

查询特定地点的餐厅

SELECT name FROM restaurants WHERE address->>'zipcode' = '11225';

进阶查询教程

查询某一类型的餐厅，并按名称排序:

SELECT name, borough FROM restaurants WHERE cuisine = 'Hamburgers' ORDER BY name;

查询所有存在评级数据的餐厅:

SELECT name FROM restaurants WHERE jsonb_array_length(grades) > 0;

查询所有评级为“A”的餐厅:

SELECT borough, name FROM restaurants WHERE grades @> '[{"grade": "A"}]'::jsonb order by borough;

查询某一邮编下的餐厅数量:

SELECT address->>'zipcode' AS zipcode, COUNT(*) FROM restaurants GROUP BY address->>'zipcode';

查询某一评分日期范围内的餐厅:

SELECT name FROM restaurants WHERE grades @> '[{"date": {"$date": 1419897600000}}]'::jsonb;

查询具有多个评级的餐厅:

SELECT name, grades FROM restaurants WHERE jsonb_array_length(grades) > 1;

查询某一街道上评分最高的餐厅:

SELECT name, MAX((grades->0->>'score')::int) AS max_score FROM restaurants WHERE address->>'street' = 'Flatbush Avenue' GROUP BY name;

深度探索 ProtonBase：高级查询与数据分析

在前面的章节中，我们介绍了如何使用 ProtonBase 设置数据结构并导入数据。但数据的真正价值来自于我们如何查询和分析它。ProtonBase 的魅力不仅仅是它可以轻松地存储关系型和文档型数据，还在于它可以高效地执行复杂查询，特别是涉及 JSON 数据的查询。

在本章节中，我们将深入探索一些高级查询技巧。这些技巧将展示如何充分利用 ProtonBase 的能力，对 restaurants 表中的数据进行深入的数据挖掘和分析。这些查询将帮助数据分析师们更好地理解餐厅的分布、菜系的受欢迎程度、评分趋势等多种维度的信息。让我们开始吧！

聚合查询: 找出每种菜系下的餐厅数量。

SELECT cuisine, COUNT(*) as restaurant_count
FROM restaurants
GROUP BY cuisine
ORDER BY restaurant_count DESC;

JSON 操作: 找出所有位于指定坐标范围内的餐厅。

SELECT name, address->>'street' as street
FROM restaurants
WHERE (address->'coord'->>0)::float8 BETWEEN -73.97 AND -73.95
AND (address->'coord'->>1)::float8 BETWEEN 40.66 AND 40.67;

深度 JSON 查询：找出所有评分超过 90 的餐厅。

SELECT name
FROM restaurants
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(grades) grade
  WHERE (grade->>'score')::int > 90
);

使用 JSON 聚合函数：找出平均评分最高的 5 家餐厅。

SELECT
    name,
    AVG((grade->>'score')::float) as average_score
FROM
    restaurants, jsonb_array_elements(grades) as grade
WHERE
    grade->>'score' IS NOT NULL
GROUP BY
    name
HAVING
    AVG((grade->>'score')::float) IS NOT NULL
ORDER BY
    average_score DESC
LIMIT 5;

日期与时间操作: 分析某个时间段内，得分最高的 5 家餐厅。

SELECT
    name,
    borough,
    cuisine,
    grades->0->>'score' AS score
FROM
    restaurants
WHERE
    (grades->0->>'score') IS NOT NULL
AND
    (grades->0->'date'->>'$date')::bigint / 1000 BETWEEN EXTRACT(EPOCH FROM '2015-01-01'::timestamp) AND EXTRACT(EPOCH FROM '2015-02-28'::timestamp)
AND
    grades->0->'date'->>'$date' IS NOT NULL
ORDER BY
    (grades->0->>'score')::int DESC
LIMIT 5;

深入探索 ProtonBase：JSONB 索引与优化

随着文档型数据在现代应用中的广泛应用，能够有效地查询和索引 JSON 结构变得越来越重要。特别是在 ProtonBase 这样的高性能数据库中，利用 jsonb 数据类型及其索引能力，可以为我们提供前所未有的查询优势。在本章中，我们将集中探讨如何最大化地利用 jsonb 的索引能力，以及如何针对它进行查询优化，确保您的查询始终处于最佳状态。

在我们的场景中，一个数据分析师正在研究众多餐厅的信息，这些信息储存于 restaurants 表中，其中 address 和 grades 两个字段使用了 jsonb 数据类型。为了快速地查询和分析这些文档型数据，我们需要对其进行合理的索引。

JSONB 索引类型

ProtonBase 提供了几种不同的 jsonb 索引类型，其中最常用的是 GIN（Generalized Inverted Index）索引。它特别适用于包含多个键值对、数组和嵌套结构的 jsonb 列。

为了提高查询效率，我们可以为 address 和 grades 字段创建 GIN 索引：

CREATE INDEX idx_address_gin ON restaurants USING GIN(address);
CREATE INDEX idx_grades_gin ON restaurants USING GIN(grades);

使用索引进行查询

有了 GIN 索引后，我们可以更高效地执行包含 jsonb 字段的查询。例如，要找到所有在 Brooklyn 地区并且得分为"A"的餐厅，我们可以这样查询：

SELECT name, borough
FROM restaurants
WHERE borough = 'Brooklyn' AND grades @> '[{"grade": "A"}]';

这里的 @> 是 jsonb 的包含操作符，用于检查左侧的 jsonb 值是否包含右侧的 jsonb 值。

查询优化建议

当查询 jsonb 字段时，使用 ProtonBase 提供的提供的 jsonb 操作符和函数，这些都已经为 GIN 索引优化。
避免使用转换函数，例如 ::text，因为这些函数会避免使用索引并导致全表扫描。
定期使用 EXPLAIN 命令检查查询的执行计划，以确保它们正在有效地使用索引

在 ProtonBase 这种高性能数据库中，通过合理地使用 jsonb 的索引，可以确保文档型数据的查询效率与传统关系型数据相媲美，甚至在某些情况下更优。结合 ProtonBase 的特性，数据分析师可以在不牺牲查询性能的情况下，灵活地分析和探索文档型数据。

深入探索 ProtonBase：使用 JSONPath 查询餐厅信息

使用 jsonpath，我们可以更加灵活和直观地查询 jsonb 数据。进行复杂查询时，关键是理解其内部工作原理以及如何与 JSON 结构进行交互。以下是一些复杂查询示例及其详细解释：

1. 查找至少有一次评级为"A"且分数超过 15 的餐厅

SELECT name
FROM restaurants
WHERE grades @? '$[*] ? (@.grade == "A" && @.score > 15)';

此查询使用了 [*] 通配符来遍历 grades 数组中的每一个元素。? 操作符后的条件用于筛选符合条件的元素。@ 符号代表当前遍历到的元素。

2. 查找所有没有评级为 "B" 的餐厅

SELECT name
FROM restaurants
WHERE NOT grades @? '$[*] ? (@.grade == "B")';

这里使用了 NOT 关键字与存在操作符 @? 结合，确保没有任何评级为"B"的元素。

3. 查找街道名称中包含 "Avenue" 但不是 "Flatbush Avenue" 的餐厅

SELECT name
FROM restaurants
WHERE address @? '$ ? (@.street like_regex "Avenue" flag "i" && !(@.street == "Flatbush Avenue"))';

like_regex 操作符用于正则表达式匹配，flag "i" 表示匹配是不区分大小写的。! 符号表示逻辑“非”。

4. 查询指定日期范围内评级为"A"的餐厅

SELECT name
FROM restaurants
WHERE grades @? '$[*] ? (@.grade == "A" && @.date."$date" >= 1419897600000 && @.date."$date" <= 1422748800000)';

此查询旨在筛选评级为"A"且日期在指定范围内的评级。首先，我们使用 [*] 通配符来遍历每一个 grades 数组中的元素。然后，我们使用 ? 操作符后的条件进行筛选，其中 @ 符号代表当前遍历到的元素。我们检查每一个评级的 grade 属性是否为"A"，以及其 date.$date 属性是否在我们的目标日期范围内。

5. 查找评分中有超过 90 分的餐厅及其对应分数

SELECT
    name,
    (jsonb_path_query_first(grades, '$[*] ? (@.score > 90)')->>'score')::int AS score_above_90
FROM
    restaurants
WHERE
    grades @? '$[*] ? (@.score > 90)'
ORDER BY
    score_above_90 DESC;

整个 JSONPath 表达式 $[*] ? (@.score > 90) 的作用是：遍历 grades 数组中的每个评分对象，并选取其中 score 属性大于 90 的对象。

jsonpath 为数据分析师提供了一个强大的工具，使其能够灵活地查询嵌套和结构化的 JSON 数据，无需使用复杂的 JSON 函数和操作符。结合 ProtonBase 的高性能特性，jsonpath 可以帮助数据分析师快速地获取他们需要的信息，从而提高他们的生产力。

总结与进一步学习

通过这篇教程，我们深入了解了 ProtonBase 如何完美结合关系型和文档型的数据存储和查询能力。有了这些知识，您可以更加灵活地处理各种复杂数据，为您的数据分析工作提供强大的支持。对于进一步学习，您可以参考 ProtonBase 的官方文档，了解其更多高级功能和特性。