MySQL 基础查询（四）

**第一题：**下面表格是用户访问表users，记录了用户id（usr_id）和访问日期（log_date）
查询出每个用户最近一次的登录记录和每个用户登录总次数（同一天多次登录认为是一次）。

实例结果：

解题思路：
根据示例结果，第二列current_day，是每个用户访问日期的最大值，直接用MAX聚合函数就可以。
第三列sum_day是每个用户登录总次数，因为同一天多次登录认为是一次，所以通过将原表log_date列格式化去掉时间，得出用户登录的日期，比如2021-05-03 01:00:08 格式化成 2021-05-03。我们就可以将日期去重，得到每个用户的登录总次数。
第一步：先将原表log_date列，通过DATE_FORMAT函数，进行日期的格式化。

SELECT
  usr_id,
  log_date,
  DATE_FORMAT(log_date,'%Y-%m-%d') AS days
FROM Users

第二步：再根据第一步得到的结果，将数据通过usr_id分组，并通过MAX聚合函数得到每组log_date列的最大值（也就是最近一次登录日期），通过COUNT聚合函数，得到用户登录总次数。

SELECT usr_id,
  MAX(log_date) AS current_day,
  COUNT(DISTINCT days) AS sum_day
FROM(
  SELECT
    usr_id,
    log_date,
    DATE_FORMAT(log_date,'%Y-%m-%d') AS days
  FROM Users
) AS a
GROUP BY usr_id;

**第二题：**我们现在有一个数据表DateTable，如下：

问题一：对数据表DateTable的dates列添加索引。
问题二：通过SQL语句，输入下方结果

问题一：
这个就是考察索引语法的，很简单。
给DateTable表的dates列添加一个名为index_date的索引

CREATE INDEX index_date ON DateTable(dates);

查看索引

SHOW INDEX FROM DateTable;

问题二：
解题思路：
我们查看输出结果，第一列是年，第二列是月，这两列直接用日期函数就可以得到。
第四列，是将cost列按年汇总，第五列是将cost列全部汇总，因为每一行都有汇总记录，所以我们不应该单纯用聚合函数SUM来计算，而是用窗口函数+聚合函数SUM。
第一步：我们先将前三列查询展示出来

SELECT dates,
  YEAR(dates) AS years,
  MONTH(dates) AS months,
  SUM(cost) AS cost
FROM DateTable
GROUP BY dates

第二步：再根据第一步得到的结果，通过聚合函数SUM，结合窗口函数得到四五列。

SELECT years,months,cost,
SUM(cost) OVER(PARTITION BY years ORDER BY months) ysum,
SUM(cost) OVER(ORDER BY dates) sum
FROM(
  SELECT dates,
    YEAR(dates) AS years,
    MONTH(dates) AS months,
    SUM(cost) AS cost
  FROM DateTable
  GROUP BY dates
) AS a

注意：此处在窗口函数中使用了ORDER BY months，对月份进行了排序，那么求和的时候，会区分月份
可以使用下方语句快速建表测试：

create table DateTable (
dates datetime,
cost int
);
insert into DateTable values
("2020/07/12",10),
("2020/11/30",3),
("2019/2/9",20),
("2019/3/31",23),
("2022/2/8",99),
("2021/7/31",10);

第三题：查询支付日期在2022.3.20之后的购买商品超过一次的用户，按照最新购买时间倒叙排列

解题思路：
先找出2022.3.20之后的数据记录，再将这些记录根据user_id进行分组，通过COUNT聚合函数查出每组条数大于1的记录，最后还要按照最新购买时间倒叙排列

SELECT user_id
FROM Products
WHERE purchase_date>"2022-03-20"
GROUP BY user_id
HAVING COUNT(*)>1销售
ORDER BY max(purchase_date) desc

第四题：现在有一个订单表orders，包括订单id（Oid）、商品id（product_id）、销售渠道（channel）、订单量（amount）。我们根据这个表，选取订单量前4的商品，并且选出每个商品订单量排名前2的销售渠道

示例结果：

解题思路：
根据题目以及示例结果，我们最终要得到订单量最高的4个产品，以及每个产品对应排名前2的销售渠道。
我们可以先得到订单量排名前4的结果集1，再得到每个商品对应销售渠道的订单量排名结果集2，通过join将结果集1和结果集2连接得到最终结果。
第一步：首先获得订单量前4的商品，GROUP BY将产品分组之后，我们通过聚合函数SUM来获得每个产品的总订单量，命名为amt，再将amt倒序排序，通过limit 4得到订单量前4的商品。

SELECT product_id,
  SUM(amount) AS amt
FROM Orders
GROUP BY product_id
ORDER BY amt desc
LIMIT 4

第二步：我们再得到每个商品对应销售渠道的订单量排名

SELECT product_id,
  channel,
  ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY amount DESC) AS rk
FROM Orders

第三步：将第一步和第二步的结果通过product_id列进行join链接，并限定条件第二步得到的rk排名列<=2。最终结果如下：

SELECT b.product_id,b.channel
FROM(
  SELECT product_id,
    SUM(amount) AS amt
  FROM Orders
  GROUP BY product_id
  ORDER BY amt desc
  LIMIT 4
) AS a
JOIN (
  SELECT product_id,
    channel,
    ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY amount DESC) AS rk
  FROM Orders
) AS b
ON a.product_id = b.product_id
WHERE b.rk <=2

**第五题：**现在有主播表a：包括主播id（upid）、直播间id（rid）、开播时间（stime）

观众表b：包括观众id（uid）、进入的直播间id（rid）、进入时间（intime）

找出开播三分钟内无人进入的直播房间号。
解题思路：
我们先通过左连接，得到主播对应的3分钟进入直播间的观众，如果没有对应的观众，就是我们要找的记录。
第一步：将主播表a和观众表b进行左连接，条件是直播间id（rid）相同，并且主播开播时间（stime）大于（观众进入时间减去3分钟）。符合这个条件的主播，说明开播3分钟内有观众进入直播间。
比如主播是12:10分开播，观众在12:12分进入，这符合开播3分钟内进入的要求。
用数据表示就是：观众时间减去3分钟，是12:09分，12:10分>12:09分，说明用户在开播3分钟内进入直播间。

SELECT a.upid,a.rid,b.uid
FROM a
LEFT JOIN b
ON a.rid = b.rid
AND a.stime > date_add(b.intime,INTERVAL -3 minute)

第二步：第一步得到的结果集，第三列b.uid如果为null，说明这个直播间开播3分钟内没有观众进入。所以我们再添加一个where条件就可以了

SELECT a.upid,a.rid
FROM a
LEFT JOIN b
ON a.rid = b.rid
AND a.stime > date_add(b.intime,INTERVAL -3 minute)
WHERE b.uid IS NULL;