mysql查询之 用户行程的取消率,人流量高峰时段

1.用户行程的取消率

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

+----+-----------+-----------+---------+--------------------+----------+

| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|

+----+-----------+-----------+---------+--------------------+----------+

| 1 | 1 | 10 | 1 | completed |2013-10-01|

| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|

| 3 | 3 | 12 | 6 | completed |2013-10-01|

| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|

| 5 | 1 | 10 | 1 | completed |2013-10-02|

| 6 | 2 | 11 | 6 | completed |2013-10-02|

| 7 | 3 | 12 | 6 | completed |2013-10-02|

| 8 | 2 | 12 | 12 | completed |2013-10-03|

| 9 | 3 | 10 | 12 | completed |2013-10-03|

| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|

+----+-----------+-----------+---------+--------------------+----------+

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

+----------+--------+--------+

| Users_Id | Banned | Role |

+----------+--------+--------+

| 1 | No | client |

| 2 | Yes | client |

| 3 | No | client |

| 4 | No | client |

| 10 | No | driver |

| 11 | No | driver |

| 12 | No | driver |

| 13 | No | driver |

+----------+--------+--------+

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

+------------+-------------------+

| Day | Cancellation Rate |

+------------+-------------------+

| 2013-10-01 | 0.33 |

| 2013-10-02 | 0.00 |

| 2013-10-03 | 0.50 |

+------------+-------------------+

select t.Request_at Day,(round(count(if(status!="completed",status,null))/count(status),2) ) `Cancellation Rate`

from Users u inner join Trips t

on u.Users_id = t.Client_Id and u.banned != 'Yes'

where t.Request_at >= '2013-10-01' and t.Request_at <= '2013-10-03'

group by t.Request_at

说明：

　　IF(expr1,expr2,expr3)

　　如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。

　　IF() 的返回值为数字值或字符串值

2.人流量高峰时段

每日人流量信息被记录在：序号 (id)、日期 (date)、 人流量 (people)

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 stadium：

+------+------------+-----------+

| id | date | people |

+------+------------+-----------+

| 1 | 2017-01-01 | 10 |

| 2 | 2017-01-02 | 109 |

| 3 | 2017-01-03 | 150 |

| 4 | 2017-01-04 | 99 |

| 5 | 2017-01-05 | 145 |

| 6 | 2017-01-06 | 1455 |

| 7 | 2017-01-07 | 199 |

| 8 | 2017-01-08 | 188 |

+------+------------+-----------+

对于上面的示例数据，输出为：

+------+------------+-----------+

| id | date | people |

+------+------------+-----------+

| 5 | 2017-01-05 | 145 |

| 6 | 2017-01-06 | 1455 |

| 7 | 2017-01-07 | 199 |

| 8 | 2017-01-08 | 188 |

+------+------------+-----------+

Note:每天只有一行记录，日期随着 id 的增加而增加。

select distinct s1.* from stadium s1,stadium s2,stadium s3

where

s1.people>=100 and s2.people>=100 and s3.people>=100

and (

(s1.id = s2.id-1 and s2.id=s3.id-1) or

(s1.id = s2.id-1 and s1.id=s3.id+1) or

(s1.id = s2.id+1 and s2.id=s3.id+1)

) order by s1.id

相关文章