可为空的Unicode字符串的Spark SQL排名

spark sql中的排名函数支持 NULLS LAST 这样就可以了:

SELECT 
  CompanyName,
  AddressLine1,
  ROW_NUMBER() OVER ( PARTITION BY CompanyName ORDER BY AddressLine1 ) BestDataForCompany1,
  ROW_NUMBER() OVER ( PARTITION BY CompanyName ORDER BY CASE WHEN AddressLine1 IS NULL THEN 1 ELSE 0 END, AddressLine1 DESC ) BestDataForCompany2,
  ROW_NUMBER() OVER ( PARTITION BY CompanyName ORDER BY AddressLine1 NULLS LAST ) BestDataForCompany3  
FROM CompanyData

我的结果是:

我还没有测试过这个——但是我猜你最好把它们分成另一组,然后首先对它们进行排序。然后应用你想要的真实排名:

SELECT CompanyName, 
ROW_NUMBER() OVER
                    (
                        PARTITION BY O.CompanyName, CASE WHEN AddressLine1 IS NOT NULL THEN 0 ELSE 1 END
                        ORDER BY  
                                 CASE WHEN AddressLine1 IS NOT NULL THEN 0 ELSE 1 END, AddressLine1                                 
                    ) AS BestDataForCompany
FROM CompanyData