--- highlight: a11y-dark ---

> 携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第30天，[点击查看活动详情](https://juejin.cn/post/7123120819437322247) > - 💡 作者：[韩信子](https://github.com/HanXinzi-AI)@[ShowMeAI](https://www.showmeai.tech/) > - 📘 [数据分析实战系列](https://www.showmeai.tech/tutorials/40)：[https://www.showmeai.tech/tutorials/40](https://www.showmeai.tech/tutorials/40) > - 📘 [AI 面试题库系列](https://www.showmeai.tech/tutorials/48)：[https://www.showmeai.tech/tutorials/48](https://www.showmeai.tech/tutorials/48) > - 📘 [本文地址](https://www.showmeai.tech/article-detail/318)：[https://www.showmeai.tech/article-detail/318](https://www.showmeai.tech/article-detail/318) > - 📢 声明：版权所有，转载请联系平台与作者并注明出处 > - 📢 收藏[ShowMeAI](https://www.showmeai.tech/)查看更多精彩内容

本篇内容基于场景面试题完成，在给定场景和数据表的前提下，有一系列的分析挖掘问题，大家可以基于SQL来完成。场景：Danny非常喜欢日本料理，因此在 2021 年初，他决定冒险冒险，开了一家可爱的小餐厅，出售他最喜欢的 3 种食物：寿司、咖喱和拉面。这家餐厅从其几个月的运营中获取了一些非常基本的数据，但不知道如何使用他们的数据来帮助他们经营业务。 Danny 想基于收集到的数据来更深入地了解他的客户，例如他们的就餐模式、点餐花费以及他们最喜欢哪些菜等。下面你就来帮助他完成核心问题的分析吧，这里的分析基于SQL完成。 > 对于SQL更详尽的内容，欢迎大家查阅[ShowMeAI](https://www.showmeai.tech/)制作的速查手册，快学快用： > > - [**编程语言速查表 | SQL 速查表**](https://www.showmeai.tech/article-detail/99) # 💡 数据说明本次的场景涉及到3个核心数据集，都已存入数据库表中： - `sales` - `menu` - `members` 这3张表对应的实体关系图如下所示：

## 📌 表1：Sales 销售额表对应的建表与数据插入SQL语句如下： ```sql CREATE TABLE sales ( "customer_id" VARCHAR(1), "order_date" DATE, "product_id" INTEGER ); INSERT INTO sales ("customer_id", "order_date", "product_id") VALUES ('A', '2021-01-01', '1'), ('A', '2021-01-01', '2'), ('A', '2021-01-07', '2'), ('A', '2021-01-10', '3'), ('A', '2021-01-11', '3'), ('A', '2021-01-11', '3'), ('B', '2021-01-01', '2'), ('B', '2021-01-02', '2'), ('B', '2021-01-04', '1'), ('B', '2021-01-11', '1'), ('B', '2021-01-16', '3'), ('B', '2021-02-01', '3'), ('C', '2021-01-01', '3'), ('C', '2021-01-01', '3'), ('C', '2021-01-07', '3'); ```

## 📌 表2：menu 菜单表对应的建表与数据插入SQL语句如下： ```sql CREATE TABLE menu ( "product_id" INTEGER, "product_name" VARCHAR(5), "price" INTEGER ); INSERT INTO menu ("product_id", "product_name", "price") VALUES ('1', 'sushi', '10'), ('2', 'curry', '15'), ('3', 'ramen', '12'); ```

## 📌 表3：members 会员表对应的建表与数据插入SQL语句如下： ```sql CREATE TABLE members ( "customer_id" VARCHAR(1), "join_date" DATE ); INSERT INTO members ("customer_id", "join_date") VALUES ('A', '2021-01-07'), ('B', '2021-01-09'); ```

# 💡 数据分析挖掘问题 ## 📌 1.每位顾客在餐厅消费的总金额是多少？这里的信息显然来源于sales和menu两张表，我们先对它们进行关联，而问题中的『每位顾客』意味着我们会基于 customer_id 进行分组统计。最后的SQL如下所示： ```sql SELECT customer_id, Sum(price) AS total_sales FROM sales JOIN menu ON sales.product_id = menu.product_id GROUP BY sales.customer_id ``` 查询结果如下：

## 📌 2.每位顾客光顾了餐厅多少天？我们知道，每位顾客每次光顾，都会生成 sales 中的相关记录，我们可以基`customer_id`统计客户访问餐厅的不同日期。 ```sql SELECT customer_id, Count(DISTINCT( order_date )) as no_of_days_customer_visited FROM sales GROUP BY customer_id ``` 查询结果如下：

## 📌 3.每位顾客购买的菜单中的第一道菜是什么？这个问题同样会涉及到 sales 和 menu 表，我们会用到`customer_id`、`product_name`、`order_date`字段，按照要求，我们希望查询每个客户从菜单中购买的第 1 件商品，因此使用 rank 函数进行订单日期排序。对应的SQL如下所示： ```sql WITH view_tab AS (SELECT customer_id, product_name, order_date, Rank() OVER( partition BY customer_id ORDER BY order_date ) AS Ranking FROM sales JOIN menu ON sales.product_id = menu.product_id) SELECT customer_id, product_name FROM view_tab WHERE ranking = 1 GROUP BY customer_id, product_name ``` 我们这里启用了临时表`view_tab`，选择 ranking 位1的数据对应的`customer_id`和`product_name`。查询结果如下：

## 📌 4.菜单上购买最多的菜是什么，所有顾客购买了多少次？这里很显然是以『菜』为核心，因此我们会基于`product_id`进行分组，同时我们需要统计的是购买了多少次，因此需要根据`count(product_id)`的结果进行排序，对应的SQL如下所示： ```sql SELECT product_name, Count(sales.product_id) AS most_purchsed FROM sales JOIN menu ON sales.product_id = menu.product_id GROUP BY sales.product_id ORDER BY most_purchsed DESC LIMIT 1 ``` 查询结果如下：

第2小问是问所有顾客在这个最热门的菜上下单的次数，我们在上述SQL的基础上加上`customer_id`进行统计。 ```sql SELECT customer_id, product_name, Count(customer_id) AS purchase_count FROM sales JOIN menu ON sales.product_id = menu.product_id WHERE sales.product_id = 3 GROUP BY customer_id ORDER BY purchase_count DESC ``` 查询结果如下：

## 📌 5.每位顾客最喜欢的菜品分别是什么？在这个问题中，我们要对客户购买每种产品的次数进行排名，因此使用窗口函数 rank，按`customer_id`划分，按客户购买产品的次数（计数）排序。对应的SQL如下： ```sql WITH view_tab AS (SELECT customer_id, product_name, Count(product_name) AS count_item, Rank() OVER( partition BY customer_id ORDER BY Count(product_name) DESC) AS most_popular FROM sales JOIN menu ON sales.product_id = menu.product_id GROUP BY customer_id, product_name) SELECT customer_id, product_name, count_item FROM view_tab WHERE most_popular = 1 ``` 查询结果如下：

## 📌 6.客户成为会员后最先购买的商品是什么？这个问题中涉及到会员信息，我们会需要所有 3 个表，我们要把它们关联起来。我们要查询客户成为会员后购买的第一件商品，因此要选出订单日期需要大于加入日期的订单。使用窗口函数通过对`customer_id`进行划分并按`order_date` 对其进行排序，可以实现对第一个购买日期进行排序。这里依旧会需要借助临时表`view_tab`。最终的SQL如下： ```sql WITH view_tab AS (SELECT sales.customer_id, product_name, order_date, Rank() OVER( partition BY sales.customer_id ORDER BY order_date) AS first_order FROM sales JOIN menu ON sales.product_id = menu.product_id JOIN members ON sales.customer_id = members.customer_id WHERE join_date <= order_date) SELECT customer_id, product_name, order_date FROM view_tab WHERE first_order = 1 ``` 查询结果如下：

## 📌 7.在客户成为会员之前最后购买的是哪件菜品？同上一个问题，我们需要用到所有 3 个表。要查询客户在成为会员之前购买的商品，订单日期需要小于加入日期。使用窗口函数通过对`customer_id`进行划分并按`order_date`对其进行排序，对第一个购买日期进行降序排列。最终的SQL如下： ```sql WITH rank AS (SELECT S.customer_id, M.product_name, Dense_rank() OVER ( partition BY S.customer_id ORDER BY S.order_date) AS Rank FROM sales S JOIN menu M ON m.product_id = s.product_id JOIN members Mem ON Mem.customer_id = S.customer_id WHERE S.order_date < Mem.join_date) SELECT customer_id, product_name FROM rank WHERE rank = 1 ``` 查询结果如下：

## 📌 8.每位会员入会前的总消费项目和消费金额是多少？要查询客户在成为会员之前购买的总商品和花费的金额，订单日期需要小于入会日期。将`customer_id` 的计数命名为`total_items`，将消费金额price的总和命名为`total_sales`，最终的SQL如下： ```sql SELECT sales.customer_id, Count(sales.product_id) AS total_items, Sum(price) AS total_sales FROM sales JOIN menu ON sales.product_id = menu.product_id JOIN members ON sales.customer_id = members.customer_id WHERE join_date > order_date GROUP BY sales.customer_id ORDER BY sales.customer_id ``` 查询结果如下：

## 📌 9.如果每消费 1 美元累计10积分，寿司消费有 2 倍积分——每位顾客会有多少积分？这个问题用到sales和menu两张表。我们使用case语句将积分分配给客户购买的商品，并对积分进行统计求和得到每位顾客的积分数。对应的SQL如下： ```sql WITH view_tab AS (SELECT customer_id, CASE WHEN product_name = 'sushi' THEN price * 20 ELSE price * 10 END AS points FROM sales JOIN menu ON sales.product_id = menu.product_id) SELECT customer_id, Sum(points) AS total_points FROM view_tab GROUP BY customer_id ``` 查询结果如下：

## 📌 10.在客户加入计划后的第一周（包含入会日期），寿司和其他所有商品都是2倍积分，这种情况下1月份结束后客户有多少积分？ ```sql WITH dates AS (SELECT *, Dateadd(day, 6, join_date) AS valid_date, Eomonth('2021-01-31') AS last_date FROM members) SELECT S.customer_id, Sum(CASE WHEN m.product_id = 1 THEN m.price * 20 WHEN S.order_date BETWEEN D.join_date AND D.valid_date THEN m.price * 20 ELSE m.price * 10 END) AS Points FROM dates D JOIN sales S ON D.customer_id = S.customer_id JOIN menu M ON M.product_id = S.product_id WHERE S.order_date < d.last_date GROUP BY S.customer_id ``` 查询结果如下：

## 📌 11.构建新的宽表，包含这些字段信息：customer_id, order_date, product_name, price, member [Y/N] ```sql SELECT s.customer_id, s.order_date, m.product_name, m.price, CASE WHEN mb.join_date > s.order_date THEN 'N' WHEN mb.join_date <= s.order_date THEN 'Y' ELSE 'N' END AS is_member FROM sales s LEFT JOIN menu m ON s.product_id = m.product_id LEFT JOIN members mb ON mb.customer_id = s.customer_id ORDER BY s.customer_id; ``` 查询结果如下：

## 📌 12.对客户点菜菜品按时间先后编码，区分会员与非会员状态，非会员的菜品不计入顺序编码，记为NULL。 ```sql WITH joined_table AS (SELECT s.customer_id, s.order_date, m.product_name, m.price, CASE WHEN mb.join_date > s.order_date THEN 'N' WHEN mb.join_date <= s.order_date THEN '‘Y' ELSE 'N' END AS is_member FROM sales s LEFT JOIN menu m ON s.product_id = m.product_id LEFT JOIN members mb ON mb.customer_id = s.customer_id ORDER BY s.customer_id) SELECT *, CASE WHEN is_member = 'N' THEN NULL ELSE Rank() OVER( partition BY customer_id, is_member ORDER BY order_date) END AS ranks FROM joined_table; ``` 查询结果如下：

# 参考资料 - 📘 **编程语言速查表 | SQL 速查表**：[https://www.showmeai.tech/article-detail/99](https://www.showmeai.tech/article-detail/99)