Data science tools

Data retrieval with SQL

Star

sql query hive presto mysql postgresql

By Afshine Amidi and Shervine Amidi

General concepts

Structured Query Language Structured Query Language, abbreviated as SQL, is a language that is largely used in the industry to query data from databases.

Query structure Queries are usually structured as follows:

-- Select columns                    mandatory
SELECT
    col_1,
    col_2,
     ... ,
    col_n

-- Source of data                    mandatory
FROM table t

-- Gather info from other sources    optional
JOIN other_table ot
  ON (t.key = ot.key)

-- Conditions                        optional
WHERE some_condition(s)

-- Aggregating                       optional
GROUP BY col_group_list

-- Restricting aggregated values     optional
HAVING some_condition(s)

-- Sorting values                    optional
ORDER BY col_order_list

-- Limiting number of rows           optional
LIMIT some_value

Remark: the SELECT DISTINCT command can be used to ensure not having duplicate rows.

Condition A condition is of the following format:

some_col some_operator some_col_or_value

where some_operator can be among the following common operations:

Joins Two tables table_1 and table_2 can be joined in the following way:

...
FROM table_1 t1
type_of_join table_2 t2
  ON (t2.key = t1.key)
...

Type of join	Illustration
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

Remark: joining every row of table 1 with every row of table 2 can be done with the CROSS JOIN command, and is commonly known as the cartesian product.

Aggregations

Grouping data Aggregate metrics are computed on grouped data in the following way:

The SQL command is as follows:

SELECT
    col_1,
    agg_function(col_2)
FROM table
GROUP BY col_1

Grouping sets The GROUPING SETS command is useful when there is a need to compute aggregations across different sets of dimensions at a time. Below is an example of how all aggregations across two dimensions are computed:

SELECT
    col_1,
    col_2,
    agg_function(col_3)
FROM table
GROUP BY col_1, col_2
GROUPING SETS (
    (col_1),
    (col_2),
    (col_1, col_2)
)

Aggregation functions The table below summarizes the main aggregate functions that can be used in an aggregation query:

Remark: the median can be computed using the PERCENTILE_APPROX function with p equal to 0.5.

Filtering The table below highlights the differences between the WHERE and HAVING commands:

Remark: if WHERE and HAVING are both in the same query, WHERE will be executed first.

Window functions

Definition A window function computes a metric over groups and has the following structure:

The SQL command is as follows:

some_window_function() OVER(PARTITION BY some_col ORDER BY another_col)

Remark: window functions are only allowed in the SELECT clause.

Row numbering The table below summarizes the main commands that rank each row across specified groups, ordered by a specific column:

Values The following window functions allow to keep track of specific types of values with respect to the partition:

Advanced functions

SQL tips In order to keep the query in a clear and concise format, the following tricks are often done:

Sorting values The query results can be sorted along a given set of columns using the following command:

... [query] ...
ORDER BY col_list

Remark: by default, the command sorts in ascending order. If we want to sort it in descending order, the DESC command needs to be used after the column.

Column types In order to ensure that a column or value is of one specific data type, the following command is used:

CAST(some_col_or_value AS data_type)

Remark: if the column contains data of different types, the TRY_CAST() command will convert unknown types to NULL instead of throwing an error.

Column manipulation The main functions used to manipulate columns are described in the table below:

Conditional column A column can take different values with respect to a particular set of conditions with the CASE WHEN command as follows:

CASE WHEN some_condition THEN some_value
               ...
     WHEN some_other_condition THEN some_other_value
     ELSE some_other_value_n END

Combining results The table below summarizes the main ways to combine results in queries:

Common table expression A common way of handling complex queries is to have temporary result sets coming from intermediary queries, which are called common table expressions (abbreviated CTE), that increase the readability of the overall query. It is done thanks to the WITH ... AS ... command as follows:

WITH cte_1 AS (
SELECT ...
),

...

cte_n AS (
SELECT ...
)

SELECT ...
FROM ...

Table manipulation

Table creation The creation of a table is done as follows:

CREATE [table_type] TABLE [creation_type] table_name(
  col_1 data_type_1,
         ...       ,
  col_n data_type_n
)
[options];

Data insertion New data can either append or overwrite already existing data in a given table as follows:

WITH ...                           -- optional
INSERT [insert_type] table_name    -- mandatory
SELECT ...;                        -- mandatory

Dropping table Tables are dropped in the following way:

DROP TABLE table_name;

View Instead of using a complicated query, the latter can be saved as a view which can then be used to get the data. A view is created with the following command:

CREATE VIEW view_name AS complicated_query;

Remark: a view does not create any physical table and is instead seen as a shortcut.