Author: Saniya Paraveez Introduction SQL is an essential part of data science and data storage. SQL, despite the volume of data generated every day by the various industries, is the best tool for helping to analyze, filter and aggregate the data in order to gain a full understanding. SQL has often been overlooked as an essential skill in data science. It is a way to extract data from a database and feed pandas. Many people think SQL only includes SELECT, ORDER BY, JOIN and JOIN. It is much more than SELECT and JOIN. SQL tricks for everyday use COALESCE function This function handles NULL values. This function replaces null values with a desired value. Let’s look at an Employee table. Figure 1: City row with null values. In the table above, there are many rows that have no values. Let us query the Null value of the city that has a default value. SELECT name and salary from city. COALESCE (city, New Delhi) FROM Employee ORDER BY Name; Figure 2 shows the default value for a city with a NULL value. The ROW_NUMBER function returns a unique integer incrementing value to every row. As it is not found in the data table, this column of values can be called a pseudo-column. The ORDER BY clause determines the order in which the results will be returned. Let’s make a rownumber for the Employee table. SELECT rownumber. Name, salary and cityFROM (SELECT ROW_NUMBER) OVER (PARTITION PER CITY ORDER BY DESC). AS rownumber. Tab. FROM Employee tab (datORDER BY rownumber, name; Figure 3: Row number calculated from the partitioned municipality WITH Statement This is used for defining “statement scoped view” They are not saved in the database schema. This is sometimes called Common Table Expressions (CTE), and subquery-factoring. Take the OrderDetail tableau as shown in Figure 4. Now query the OrderDetailTable to get the average order quantity per ProductId. WITH cte_quantityAS(SELECT SUM(Quantity) as TotalFROM OrderDetailsGROUP BY ProductID) SELECT AVG(Total) average_product_quantityFROM cte_quantity; Output: Figure 5: Average quantity ordered per productId GROUP_CONCAT function It is used to concatenate data from multiple rows into one field. This returns a string containing a non-NULL concatenated value from a group. When there is no other non-NULL value, it returns NULL. Let’s look at the example for “Book master”, as shown in Figure 6. Book Master Table query returns a list with comma separated ‘cate_ids’ for each group. SELECT pub_id,GROUP_CONCAT(cate_id)FROM book_mastGROUP BY pub_id; Figure 7: Comma Separated value 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) Conclusion Data scientists or analysts should understand SQL. All professionals who work with data or analytics need to know SQL. SQL can still be used to analyze, filter and aggregate data to give you a complete understanding. SQL for Data Scientists/Analysts originally appeared in on Medium. People are responding and highlighting this story. Published via
