Get the App
SLTechnology News&Howtos  ›  Internet Technology  › 

How to use pandas in sql

Shulou Source: shulou.com Published: 2022-06-01 19:05:39 11月19日 Update

This article mainly shows you "how to use pandas in sql", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to use pandas in sql" this article.

In addition to SQL, Python's pandas also provides us with most of the features of SQL. Since engaged in the algorithm rarely write SQL, today in the collation of impression notes took the opportunity to review, but also spent some time in the SQL of the main add, delete, change and search methods with pandas corresponding to achieve again. It can be said to be very practical.

The standard SQL query syntax is as follows:

Select (distinct) [Field] from [Table 1] join [Table 2] on [matching Field] where [filter condition] group by [Field] having [filter condition] order by [Field] desclimit [number] offset [number]

We take the 2018-19 season part of the NBA super-huge data as an example. The data is styled as follows in pandas and MySQL respectively:

The most important thing in the addition, deletion, modification and query of SQL is the query method. Let's start with the query method.

Select: select players, teams and scores per game in three columns:

Distinct: see what kind of court positions these players have:

Count: statistical sample size

Classification value statistics:

Continuous value descriptive statistics:

Where:

Single condition: find players who belong to shooting guards:

Multiple conditions: find players who belong to shooting guards and score more than 27 points:

In/not in lookup:

Order by sort statement:

To rank the scores of players:

Limit/offset statement:

Rank the players in the first three or the second to the fourth after scoring.

Group by statement:

Calculate the average score of the players in each position and sort it in descending order:

Having clause:

Calculate the average score of the players in each position and screen the records that are greater than 26 points:

Multi-table simultaneous query: inner join/outer (left right) join/union

The new table is as follows:

Inner join:

Left join:

Right join:

Union:

After comparing the main query, let's take a look at the addition, deletion and correction methods in SQL and pandas.

The statements to create, modify, insert, and delete tables in SQL are shown in the following table:

The pandas corresponding to the above four methods is written as follows:

The above is all the contents of the article "how to use pandas in sql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Tags: Score player method query field condition statement sort location content article statistics number guard data part study help practical writing Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Shulou Information vpn Shulou Technology Apple macOS