Tip: after the article is written, the directory can be generated automatically. Please refer to the help document on the right for how to generate it
preface
Tip: Here you can add the general contents to be recorded in this article:
For example, with the continuous development of artificial intelligence, machine learning technology is becoming more and more important. Many people have started learning machine learning. This paper introduces the basic content of machine learning.
Tip: the following is the main content of this article. The following cases can be used for reference
1, Title
Table: Trips +-------------+----------+ | Column Name | Type | +-------------+----------+ | Id | int | | Client_Id | int | | Driver_Id | int | | City_Id | int | | Status | enum | | Request_at | date | +-------------+----------+ Id Is the primary key of this table.
This table stores the itinerary information of all taxis. Each stroke has a unique Id, where Client_Id and Driver_Id is the Users in the Users table_ Foreign key for Id.
Status is an enumeration type indicating travel status. The enumeration members are ('completed ',' cancelled_by_driver ',' cancelled_by_client ').
Table: Users +-------------+----------+ | Column Name | Type | +-------------+----------+ | Users_Id | int | | Banned | enum | | Role | enum | +-------------+----------+ Users_Id Is the primary key of this table.
All users are stored in this table, and each user has a unique user_ ID, Role is an enumeration type representing user identity, and the enumeration members are ('client ',' driver ',' partner ').
Banded is an enumeration type indicating whether the user is prohibited. The enumeration members are ('Yes', 'No').
Write an SQL statement to find out the cancellation rate of non prohibited users (passengers and drivers must not be prohibited) from "October 1, 2013" to "October 3, 2013". Non prohibited users are users with band No and prohibited users are users with band Yes.
The cancellation rate is calculated as follows: (number of orders generated by non prohibited users cancelled by drivers or passengers) / (total number of orders generated by non prohibited users).
The data in the return result table can be organized in any order. The Cancellation Rate shall be rounded to two decimal places.
The query result format is as follows:
Trips Table: +----+-----------+-----------+---------+---------------------+------------+ | 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 Table: +----------+--------+--------+ | 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 | +----------+--------+--------+
Result Table: +------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+
2013-10-01:
- There are 4 requests, 2 of which are cancelled.
- However, the request with Id=2 is made by the forbidden user (User_Id=2), so it should be ignored in the calculation.
- Therefore, a total of 3 non prohibited requests participated in the calculation, of which 1 was cancelled.
- The cancellation rate is (1 / 3) = 0.33
2013-10-02: - There are 3 requests in total, of which 0 are cancelled.
- However, the request with Id=6 is issued by the forbidden user, so it should be ignored in the calculation.
- Therefore, a total of 2 non prohibited requests participate in the calculation, of which 0 are cancelled.
- The cancellation rate is (0 / 2) = 0.00
2013-10-03: - There are three requests, one of which is cancelled.
- However, the request with Id=8 is issued by the forbidden user, so it should be ignored in the calculation.
- Therefore, a total of 2 non prohibited requests participate in the calculation, of which 1 is cancelled.
- The cancellation rate is (1 / 2) = 0.50
Source: LeetCode link: https://leetcode-cn.com/problems/trips-and-users
The copyright belongs to Lingkou network. For commercial reprint, please contact the official authorization, and for non-commercial reprint, please indicate the source.
2, Problem solving ideas
1.WHERE+WHEN CASE
(1) Find the total number of orders generated by non prohibited users first
(2) Based on the above, find the unfinished orders (i.e. cancelled orders)
(3) Find cancellation rate
SELECT Request_at Day, round(sum(case Status when'completed' THEN 0 ELSE 1 END)/count(Status),2) 'Cancellation Rate' FROM Trips t,Users u1,Users u2 WHERE t.Client_Id=u1.Users_Id and u1.Banned='No' and t.Driver_Id=u2.Users_Id and u2.Banned='No' and Request_at between '2013-10-01' and '2013-10-03' Group by Request_at ORDER BY Day;
2. JOIN ON + IF()
SELECT t.Request_at as Day, round((sum(if(t.Status='completed',0,1))/count(t.Status)),2) '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') and t.Request_at between '2013-10-01' and '2013-10-03' GROUP BY t.Request_at ORDER BY Day;
The url used here is the data requested by the network.
summary
Tip: here is a summary of the article:
For example, the above is what we want to talk about today. This paper only briefly introduces the use of pandas, which provides a large number of functions and methods that enable us to process data quickly and conveniently.