From SQL to SPL: Deduplicate between an uncertain number of columns
A certain database table has multiple fields, each storing an email address, which may be null or duplicated.
Mail_1 |
Mail_2 |
Mail_3 |
Mail_4 |
Mail_5 |
a.gmail.com |
a.aol.com |
a1.gmail.com |
a.gmail.com |
a1.gmail.com |
b.gmail.com |
b.aol.com |
b1.gmail.com |
b.yahoo.com |
Now we need to merge multiple fields into one, remove null and duplicate values, and merge them with commas.
a.aol.com,a.gmail.com,a1.gmail.com |
b.aol.com,b.gmail.com,b.yahoo.com,b1.gmail.com |
In a database/computing platform that supports functions such as array, for example, in azure-databricks, SQL can be written as follows:
SELECT concat_ws(',', array_distinct(array(Mail_1, Mail_2, Mail_3, Mail_4, Mail_5)), NULL) AS Mail
FROM my_data
SQL Server and other databases do not have an array function, and generally require indirect implementation using statements such as cross apply:
SELECT
STRING_AGG(DistinctEmails.Mail, ',') AS Mail
FROM my_data
CROSS APPLY (
SELECT DISTINCT Mail
FROM (VALUES (Mail_1), (Mail_2), (Mail_3), (Mail_4), (Mail_5)) AS EmailList(Mail)
WHERE Mail IS NOT NULL
) AS DistinctEmails
GROUP BY my_data.Mail_1, my_data.Mail_2, my_data.Mail_3, my_data.Mail_4, my_data.Mail_5
SQL must write column names, which is inflexible. To deduplicate between an uncertain number of columns, it is necessary to dynamically generate column names using stored procedures and then execute SQL, which will make the architecture more complex. SPL does not need to write column names, and the code is the same for different data sources: https://try.esproc.com/splx?36f
A |
|
1 |
$select * from my_data.txt |
2 |
=A1.new(~.array().id@0().concat@c():Mail) |
A1: Load data.
A2: Take the current record, convert it into a set, take unique values and remove null, and merge it with commas. ~ indicates the current record, function ID is used for deduplication, @0 means null is removed.
Question source:https://stackoverflow.com/questions/78188132/how-to-combine-distinct-records-of-multiple-columns-in-sql
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.esproc.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/2bkGwqTj
Youtube 👉 https://www.youtube.com/@esProc_SPL