想要精通算法和SQL的成长之路 - 行程和用户（SQL）

前言

想要精通算法和SQL的成长之路 - 系列导航

一. 行程和用户

原题链接

有这么个表结构和数据：Trips表：

相关SQL：

-- ----------------------------
-- Table structure for Trips
-- ----------------------------
DROP TABLE IF EXISTS `Trips`;
CREATE TABLE `Trips` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `client_id` int(10) DEFAULT NULL,
  `driver_id` int(10) DEFAULT NULL,
  `city_id` int(10) DEFAULT NULL,
  `status` enum('completed','cancelled_by_driver','cancelled_by_client') DEFAULT NULL,
  `request_at` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cc` (`client_id`),
  KEY `dd` (`driver_id`),
  CONSTRAINT `cc` FOREIGN KEY (`client_id`) REFERENCES `Users` (`users_id`),
  CONSTRAINT `dd` FOREIGN KEY (`driver_id`) REFERENCES `Users` (`users_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of Trips
-- ----------------------------
BEGIN;
INSERT INTO `Trips` VALUES (1, 1, 10, 1, 'completed', '2013-10-01');
INSERT INTO `Trips` VALUES (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01');
INSERT INTO `Trips` VALUES (3, 3, 12, 6, 'completed', '2013-10-01');
INSERT INTO `Trips` VALUES (4, 4, 13, 6, 'cancelled_by_client', '2013-10-01');
INSERT INTO `Trips` VALUES (5, 1, 10, 1, 'completed', '2013-10-02');
INSERT INTO `Trips` VALUES (6, 2, 11, 6, 'completed', '2013-10-02');
INSERT INTO `Trips` VALUES (7, 3, 12, 6, 'completed', '2013-10-02');
INSERT INTO `Trips` VALUES (8, 2, 12, 12, 'completed', '2013-10-03');
INSERT INTO `Trips` VALUES (9, 3, 10, 12, 'completed', '2013-10-03');
INSERT INTO `Trips` VALUES (10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Users表（先建立Users表）

相关SQL：

-- ----------------------------
-- Table structure for Users
-- ----------------------------
DROP TABLE IF EXISTS `Users`;
CREATE TABLE `Users` (
  `users_id` int(10) NOT NULL,
  `banned` enum('Yes','No') DEFAULT NULL,
  `role` enum('client','driver','partner') DEFAULT NULL,
  PRIMARY KEY (`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of Users
-- ----------------------------
BEGIN;
INSERT INTO `Users` VALUES (1, 'No', 'client');
INSERT INTO `Users` VALUES (2, 'Yes', 'client');
INSERT INTO `Users` VALUES (3, 'No', 'client');
INSERT INTO `Users` VALUES (4, 'No', 'client');
INSERT INTO `Users` VALUES (10, 'No', 'driver');
INSERT INTO `Users` VALUES (11, 'No', 'driver');
INSERT INTO `Users` VALUES (12, 'No', 'driver');
INSERT INTO `Users` VALUES (13, 'No', 'driver');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。

• 非禁止用户即 banned 为 No 的用户。
• 禁止用户即 banned 为 Yes 的用户。

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四五入保留两位小数 。

首先，我们先审题，看看写SQL我们应该注意到哪些点：

• “2013-10-01” 至 “2013-10-03” 期间，那么这里就对应一个where子句的范围查询。
• 乘客和司机都必须未被禁止，即client_id和driver_id对应在Users表中的banned字段都是No。
• 按照结果输出，可以看出，结果是根据日期来进行分组的，因此我们代码里最后要跟上一个group by 。

1.1 确定非禁止用户

首先，我们确定乘客是非禁止的：client_id对应的banned字段都是No。

SELECT
	* 
FROM
	Trips t
	JOIN Users u1 ON ( t.client_id = u1.users_id AND u1.banned = 'No' )

其次，我们在其基础上，在确保司机也是非禁止的：driver_id对应的banned字段都是No。同时整合上时间的范围查询：

SELECT
	t.id AS '主键Id',
	t.client_id AS '乘客Id',
	u1.role AS '乘客角色',
	t.driver_id AS '司机Id',
	u2.role AS '司机角色' ,
	t.request_at,
	t.`status`
FROM
	Trips t
	JOIN Users u1 ON ( t.client_id = u1.users_id AND u1.banned = 'No' )
	JOIN Users u2 ON ( t.driver_id = u2.users_id AND u2.banned = 'No' )
	WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'

结果如下：

1.2 取消率的计算

那么取消率怎么算？我们先根据案例输出结果来分析：

• 2013-10-01这一天，有3个单子（满足前置条件的），最终完成的单子有两个（completed）。那么取消率就是 1/3 = 0.33。
• 2013-10-02这一天，有2个单子（满足前置条件的），最终完成的单子有两个（completed）。那么取消率就是 0/2 = 0.00。
• 2013-10-03这一天，有2个单子（满足前置条件的），最终完成的单子有一个（completed）。那么取消率就是 1/2 = 0.50

那么这个取消率涉及到几个函数：

• Sum：计数非完成的单子个数。可以结合if来统计：if(t.status = 'completed',0,1)，即如果状态是完成的，代表0，否则为1。
• Count：计数总数。
• Round：用于四舍五入的，可以控制保留2位小数。

if语法：IF(expr1,expr2,expr3)，expr1为true，则返回expr2，否则expr3。

那么取消率这部分的计算就是：

ROUND( 
	SUM( 
		IF ( t.STATUS = 'completed', 0, 1 )
	)
	/ 
	COUNT( t.STATUS )
, 2) as `Cancellation Rate`

最终代码就出来了，此时我们只需要将结果再根据日期来进行分组即可，加一个group by：

SELECT
	t.request_at AS 'Day',
	ROUND( 
		# 统计状态非完成的单子个数
		SUM(    IF ( t.STATUS = 'completed', 0, 1 )     )
		# 除法
		/ 
		# 统计当前分组下的总个数
		COUNT( t.STATUS )
	, 2) as `Cancellation Rate`
FROM
	Trips t
	JOIN Users u1 ON ( t.client_id = u1.users_id AND u1.banned = 'No' )
	JOIN Users u2 ON ( t.driver_id = u2.users_id AND u2.banned = 'No' ) 
WHERE
	t.request_at BETWEEN '2013-10-01' 
	AND '2013-10-03'
	GROUP BY t.request_at