SQL for Data Scientists/Analysts
Last Updated on August 6, 2021 by Editorial Team
Author(s): Saniya Parveez
SQL is an important part of data and data science for the storage and retrieval of data. With extensive data being gathered and churned out every day in the industries, as long as the data remains in a SQL-compliant database, SQL is still the ablest tool to help to investigate, filter, and aggregate data to get a thorough understanding of data.
I have seen many times that SQL is an underrated skill for data science because it has been taken for granted as a necessary yet uncool way of obtaining data out from the database to feed into pandas. People think that SQL is just SELECT, JOIN, and ORDER BY. But, it is way more than just SELECT, JOIN, and ORDER By statements.
SQL Tricks for Every Day
This function is used to handle NULL values. It replaces NULL values with another desired value.
Let's take an Employee table:
In the above table, the city row has many null values. Let’s query the Null values of the city with a default value.
SELECT name, salary, city,
COALESCE(city, 'New Delhi') AS city_null_value
ORDER BY name;
This function creates a unique incrementing integer value to each row of the result. This column of values is supposed pseudo-column as it does not naturally exist in our data table. Because of this, the result is returned in the order determined by the analysts in the ORDER BY clause.
Let’s create a rownumber in the Employee table.
ROW_NUMBER() OVER (PARTITION BY city ORDER BY name DESC) AS rownumber,
FROM Employee tab
ORDER BY name, rownumber;
It is used to define “statement scoped views”. These are not stored in the database schema. It is also called Common Table Expression (CTE) and subquery factoring.
Let’s take the OrderDetail table as below:
Now, query to return the average quantity ordered per ProductId.
SUM(Quantity) as Total
GROUP BY ProductID)
It is used to concatenate data from multiple rows into one field. It returns a string with a concatenated non-NULL value from a group. It returns NULL when there are no non-NULL values.
Let’s take an example of the “Book master” table as below:
Query to return a list of comma(,) separated ‘cate_id’s for each group of ‘pub_id’.
GROUP BY pub_id;
Integrate SQL Query with Python
Panda is a wonderful library that gives one line code to query from SQL. Below code to query from Pandas:
query = "SELECT * FROM CURRENT_TABLE"
sql_data = pandas.read_sql(query, connection)
Data scientists or analysts should understand SQL. In fact, all professionals working with data and analytics should know SQL. SQL is still the most powerful tool to help you investigate, filter, and aggregate to get a thorough understanding of your data.
Published via Towards AI