RaqForum 73 No.
2 Reply  •  700 View  • 
     ...

How can I perform this SQL code in SPL?

 
sql(1) date handling(1) data rearrangement(1) caculate(1) conditional grouping(1) difference operation(1)

Data transformation is the process of converting raw data into a structured format that is more suitable for analysis, reporting, or other downstream tasks. In context, let’s say we want to generate a report showing the top three languages spoken in each country, along with the percentage of speakers for each language.

WITH RankedLanguages AS (
    SELECT 
        cl.CountryCode,
        cl.Language,
        cl.Percentage,
        ROW_NUMBER() OVER (PARTITION BY cl.CountryCode ORDER BY cl.Percentage DESC) AS LanguageRank
    FROM 
        CountryLanguage cl
),
TopThreeLanguages AS (
    SELECT 
        rl.CountryCode,
        rl.Language,
        rl.Percentage
    FROM 
        RankedLanguages rl
    WHERE 
        rl.LanguageRank <= 3
)
SELECT 
    c.Name AS Country,
    ttl.Language AS TopLanguage,
    ttl.Percentage AS PercentageOfSpeakers
FROM 
    TopThreeLanguages ttl
JOIN 
    Country c ON ttl.CountryCode = c.Code;

Result:

imagepng

How can I perform similar script using esProc SPL?

sql(1) date handling(1) data rearrangement(1) caculate(1) conditional grouping(1) difference operation(1)
 
 •  700 View  •