数据库每日一题 --- 第 15 天：未消费的顾客

2022 年 6 月 17 日
本文字数：1780 字
阅读完需：约 6 分钟

一、问题描述

表：Visits

+-------------+---------+| Column Name | Type    |+-------------+---------+| visit_id    | int     || customer_id | int     |+-------------+---------+visit_id 是该表的主键。该表包含有关光临过购物中心的顾客的信息。

复制代码

表：Transactions

+----------------+---------+| Column Name    | Type    |+----------------+---------+| transaction_id | int     || visit_id       | int     || amount         | int     |+----------------+---------+transaction_id 是此表的主键。此表包含 visit_id 期间进行的交易的信息。

复制代码

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个 SQL 查询，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

返回以 任何顺序 排序的结果表。

查询结果格式如下例所示。

题目链接：未消费的顾客

二、题目要求

样例

输入:Visits+----------+-------------+| visit_id | customer_id |+----------+-------------+| 1        | 23          || 2        | 9           || 4        | 30          || 5        | 54          || 6        | 96          || 7        | 54          || 8        | 54          |+----------+-------------+Transactions+----------------+----------+--------+| transaction_id | visit_id | amount |+----------------+----------+--------+| 2              | 5        | 310    || 3              | 5        | 300    || 9              | 5        | 200    || 12             | 1        | 910    || 13             | 2        | 970    |+----------------+----------+--------+输出:+-------------+----------------+| customer_id | count_no_trans |+-------------+----------------+| 54          | 2              || 30          | 1              || 96          | 1              |+-------------+----------------+解释:ID = 23 的顾客曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。ID = 9 的顾客曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。ID = 30 的顾客曾经去过购物中心，并且没有进行任何交易。ID = 54 的顾客三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。ID = 96 的顾客曾经去过购物中心，并且没有进行任何交易。如我们所见，ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。

复制代码

考察

1.外连接2.建议用时10~25min

复制代码

三、问题分析

这一题可以使用左外连接，常用的如下：

A inner join B：内连接，取交集
A left join B：左外连接，取 A 全部，B 没有对应的值，则为null
A right join B：右外连接，取 B 全部，A 没有对应的值，则为null
A full outer join B：全外连接，取并集，彼此没有对应的值为null

我们使用左外连接获取的表如下：

{"headers": ["visit_id", "customer_id", "transaction_id", "visit_id", "amount"],   "values": [     [1,        23,           12,              1,             910],                   [2,         9,           13,              2,             970],                   [4,        30,           null,            null,         null],                   [5,        54,           2,               5,             310],                   [5,        54,           3,               5,             300],                   [5,        54,           9,               5,             200],                   [6,        96,          null,             null,         null],                   [7,        54,          null,             null,         null],                   [8,        54,          null,             null,         null]]}

复制代码

这样就很好判断了，左外连接之后对于右侧为空的数据计数，最后聚合分组一下，这一题就解决了。

四、编码实现

select v.customer_id,count(*) as 'count_no_trans'from Visits vleft join Transactions t on v.visit_id=t.visit_idwhere t.visit_id is nullgroup by  v.customer_id

复制代码