# One Stop Guide to SQL for AI and Machine Learning

- Canonical: https://33rdsquare.com/one-stop-guide-to-your-sql/
- Published: 2024-09-03
- Author: Jordan Brown
- Categories: [Artificial Intelligence & Machine Learning & ChatGPT](https://33rdsquare.com/category/tech/ai/)

---

SQL (Structured Query Language) is often thought of as a tool for business analysts and database administrators, but it‘s equally essential for data scientists and machine learning engineers. In fact, SQL is perhaps the most important skill for anyone working with data to have in their toolkit.

Consider this: in a recent survey of data scientists, 54% said they use SQL on a daily basis – more than Python (44%) or R (10%) [(source)](https://www.burtchworks.com/wp-content/uploads/2019/06/Burtch-Works-Study_DS-PAP-2019.pdf). As AI and ML initiatives become more widespread, the ability to efficiently retrieve, filter and aggregate data using SQL is becoming a prerequisite for data science roles.

In this guide, we‘ll cover all the key SQL concepts through an AI/ML lens, with a focus on the most important operation of all: counting the number of rows in a table. By the end, you‘ll be equipped to use SQL to wrangle data and build powerful features for your ML models. Let‘s dive in!

## SQL Basics for Data Science

Before we get into the machine learning specifics, let‘s review the fundamental SQL concepts you‘ll need.

At its core, SQL is used to interact with relational databases, which consist of tables linked together by primary and foreign key relationships. The basic syntax for retrieving data from a table is:

```
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```

The `WHERE` clause allows filtering rows based on specified criteria, while `ORDER BY` and `GROUP BY` allow you to sort and aggregate results. `HAVING` is used in conjunction with `GROUP BY` to filter the aggregated values.

For example, let‘s say we have an `orders` table with the following columns:

| order_id | customer_id | order_date | total_amount |
| --- | --- | --- | --- |
| 1 | 101 | 2022-01-15 | 149.99 |
| 2 | 102 | 2022-02-03 | 49.50 |
| 3 | 101 | 2022-02-19 | 250.00 |

To get the total sales per customer:

```
SELECT customer_id, SUM(total_amount) AS total_sales
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 100;
```

This would return:

| customer_id | total_sales |
| --- | --- |
| 101 | 399.99 |

Now that we‘ve reviewed the basics, let‘s look at how SQL fits into the AI/ML workflow.

## SQL for AI/ML Data Pipelines

It‘s estimated that data scientists spend 60-80% of their time on data preparation tasks like cleaning, reshaping, and aggregating data [(source)](https://www.forbes.com/sites/gilpress/2016/03/23/data-preparation-most-time-consuming-least-enjoyable-data-science-task-survey-says/). This is where SQL shines as a powerful tool for building the input datasets needed to train ML models.

The typical data science SQL workflow involves:

1. Extracting relevant data from source systems like web logs, sensor streams or application databases into a central data warehouse or data lake
2. Transforming and combining data into a denormalized table or view optimized for analysis
3. Exploring and visualizing the prepared data to understand distributions and relationships
4. Creating features (i.e. input variables) and labels for ML models
5. Loading sampled datasets into Python/R/Spark for model training and evaluation

While Python and R libraries like pandas and dplyr are great for data manipulation, SQL is often faster and more scalable for processing large, complex datasets. It‘s not uncommon for ML datasets to reach billions of rows – at which point even simple operations like filtering and aggregating can become painful in procedural languages.

Consider this comparison of filtering a 100M row table in SQL vs pandas:

| Operation | SQL | Pandas |
| --- | --- | --- |
| Filter rows where col1 > 10 | SELECT * FROM table WHERE col1 > 10 | df[df.col1 > 10] |
| Time to filter 100M rows and return 1M results | ~2 seconds | ~1 minute |

[(source)](https://medium.com/carwow-product-engineering/sql-vs-pandas-how-to-balance-tasks-between-server-and-client-side-9e2f6c95677)

SQL‘s speed advantage comes from its declarative, set-based operations that can be optimized and parallelized by the database. So as an ML engineer, it‘s advantageous to push as much of the heavy lifting as possible into SQL before pulling results into your ML environment.

## Determining Rows for ML Dataset Sizing

One of the most important applications of SQL in machine learning is in determining the size and composition of training datasets. Many algorithms are sensitive to the number of examples they‘re trained on, as well as the class balance and distribution. Too little data and your model will underfit; too much and training becomes unnecessarily slow.

Before training a model, you‘ll often want to use SQL to get counts like:

- Total number of examples (rows) for the input features
- Number of examples for each class label (for classification problems)
- Size of key categorical variables (number of distinct user_ids, product_ids, etc.)

You can extract these counts using variations of `SELECT COUNT(*)`. To get the total number of rows:

```
SELECT COUNT(*) AS total_examples FROM input_data;
```

To check for class imbalance, group by the label column:

```
SELECT label, COUNT(*) AS count
FROM input_data
GROUP BY label;
```

For example, in a binary classification problem you might see:

| label | count |
| --- | --- |
| Positive | 8762 |
| Negative | 1319 |

Indicating that the classes are indeed imbalanced (87% positive vs 13% negative). In this case you may want to downsample the majority class, upsample the minority class, or apply class weights during training [(source)](https://www.tensorflow.org/tutorials/structured_data/imbalanced_data).

To build a balanced dataset using SQL, you can use window functions like `ROW_NUMBER()`:

```
WITH pos_examples AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS row_num
  FROM input_data
  WHERE label = 1
),
neg_examples AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS row_num
  FROM input_data
  WHERE label = 0
)
SELECT * FROM pos_examples WHERE row_num <= 3000
UNION ALL
SELECT * FROM neg_examples WHERE row_num <= 3000
```

This will give you a random sample of 3000 examples from each class. You can adjust the threshold to control the overall dataset size.

## Advanced SQL for Data Science

Beyond the basics, SQL provides several powerful features for reshaping and transforming data into ML-friendly formats. Some advanced SQL concepts worth knowing include:

- Pivoting rows into columns (and vice versa) to create wide feature matrices from long tables [(example)](https://mode.com/sql-tutorial/sql-pivot-table/)
- Using window functions like `LAG()` and `LEAD()` to create features based on previous/next values in a sequence [(example)](https://www.postgresql.org/docs/9.1/tutorial-window.html)
- Extracting and manipulating text data with string functions like `SUBSTRING()`, `SPLIT()`, `REGEXP_EXTRACT()` [(example)](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions)
- Handling date/time data with functions like `DATE_TRUNC()`, `DATE_ADD()`, `EXTRACT()` [(example)](https://prestodb.io/docs/current/functions/datetime.html)
- Scaling SQL to big data using extensions like Hive, Presto, and Spark SQL [(comparison)](https://www.xplenty.com/blog/hive-vs-spark/)

As a data scientist, you don‘t need to become an SQL master – but being able to write queries that go beyond basic SELECT statements will make you much more effective at sourcing and shaping data for your AI/ML projects.

## Conclusion

We‘ve seen how SQL is an indispensable part of the AI/ML workflow, from data preparation to feature engineering to dataset construction. Far from being a relic of the pre-big-data era, SQL has evolved to meet the needs of petabyte-scale machine learning.

Counting the number of rows in a table – whether it‘s the total number of examples, the class distribution, or the categorical variable cardinality – is a small but crucial operation that you‘ll perform again and again as a data scientist. And SQL is almost always the fastest and most scalable way to get those counts.

If you‘re just starting out with SQL, focus on mastering the fundamentals of SELECT queries, JOINs, and aggregations. But don‘t stop there – keep learning about windowing, pivoting, arrays, and other advanced features that will take your data wrangling skills to the next level.

The best way to learn is by doing. Whenever you‘re faced with a data manipulation task, challenge yourself to do it in SQL first before reaching for procedural Python or R. With practice, you‘ll start to think in terms of sets and relations versus loops and conditionals.

Here are some of the best resources for learning and practicing SQL for data science:

- [Kaggle SQL courses](https://www.kaggle.com/learn/certification/ryanorsinger/intro-to-sql)
- [DataCamp SQL fundamentals track](https://www.datacamp.com/tracks/sql-fundamentals)
- [LeetCode SQL problems](https://leetcode.com/problemset/database/)
- [HackerRank SQL challenges](https://www.hackerrank.com/domains/sql)
- [Mode SQL tutorial](https://mode.com/sql-tutorial/)

No matter what type of data you‘re working with or what ML models you‘re building, leveling up your SQL skills will pay dividends throughout your career as an AI/ML professional. So keep querying!

---

Source: [One Stop Guide to SQL for AI and Machine Learning](https://33rdsquare.com/one-stop-guide-to-your-sql/)