Скачать или смотреть Solving the NULL Value Dilemma in SQL by Populating Columns with Values from Other Columns

Learn how to effectively fill `NULL` values in SQL columns by using joins and window functions, ensuring accurate and complete data for analysis.
---
This video is based on the question https://stackoverflow.com/q/67771959/ asked by the user 'Ahmed' ( https://stackoverflow.com/u/11180690/ ) and on the answer https://stackoverflow.com/a/67772626/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL Query : Add values to columns based on other columns

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving the NULL Value Dilemma in SQL

When working with datasets in databases like MariaDB or MySQL, it is not uncommon to encounter NULL values that may hinder data analysis and reporting. This post tackles a specific problem encountered by data professionals: how to populate NULL values in a column (Runs) based on corresponding values in another column (tot_rand) for the same date, ensuring that your dataset is accurate and meaningful for any analysis you wish to perform later.

Understanding the Dataset

Consider the following example dataset that represents some parts, their run counts, their durations, and dates:

PartRunsDurationDaterandom_1NULL202020-01-01random_2NULL12020-01-01random_3NULL42020-01-01tot_rand40NULL2020-01-01random_1NULL602020-01-02random_2NULL122020-01-02random_3NULL32020-01-02tot_rand100NULL2020-01-02random_1NULL92020-01-10random_2NULL42020-01-10tot_rand30NULL2020-01-10As you can see, the Runs column has several NULL entries. The objective is to fill these NULL values with the corresponding tot_rand value from the same date.

Proposed Solution

Using Window Functions

For databases that support window functions, you can use the following SQL query to effectively impute the Runs values:

[[See Video to Reveal this Text or Code Snippet]]

This query works by checking for NULL values in the runs column and replacing them with the maximum value of runs for the same date. The COALESCE function returns the first non-null value in the list.

Imputing Based on Specific Value (tot_rand)

If you specifically want to fill the Runs column with the value from tot_rand, you can modify the query slightly:

[[See Video to Reveal this Text or Code Snippet]]

In this version, a CASE statement is used to check if the Part is tot_rand, thus ensuring that only this value is considered for imputation.

Updating the Original Table

If your goal is to permanently update the Runs column in your table, you can do this with a join operation. Here’s how:

[[See Video to Reveal this Text or Code Snippet]]

In this query:

A subquery finds the maximum tot_rand values for each date.

The main table is joined with this subquery based on the date.

The Runs column is updated wherever the original Runs column is NULL.

Final Output Preparation

Once the Runs column is properly populated, you may want to create an additional column called All which represents the formula (Runs - Duration) / Runs. Below is the SQL to calculate this if you want a final result visualization:

[[See Video to Reveal this Text or Code Snippet]]

In this final output, we are using NULLIF to prevent division by zero errors in case Runs has been imputed to zero.

Conclusion

By utilizing the above SQL strategies, you can efficiently fill NULL values based on other columns in your datasets. Not only does this improve data quality, but it also prepares your dataset for complex analyses moving forward, such as calculating new metrics like All. If you have other suggestions or methods, feel free to share them!