SQL Server 2012 PERCENT_RANK() 排除 NULL [英] SQL Server 2012 PERCENT_RANK() Exclude NULLS

问题描述

SELECT [Cole_Crops], 
       PERCENT_RANK() OVER (PARTITION BY HUC02 
                            ORDER BY [Cole_Crops]) AS Cole_Crops_PCT_RANK], 
       [Row_Crops], 
       PERCENT_RANK() OVER (PARTITION BY HUC02 
                            ORDER BY [Row_Crops]) AS Row_Crops_PCT_RANK]            
FROM V012_CDLMAX_09

<小时>

如何在具有多个要排名的列的查询中排除 NULL 值影响 PERCENT_RANK?

---

How do you exclude NULL values from affecting the PERCENT_RANK in a query with multiple columns to be ranked?

SQL Server 2012 PERCENT_RANK() 将 NULLS 视为分布中的实际值.如果有一列要排序，您可以简单地添加一个 where 子句(即，where Cole_Crops IS NOT NULL).

SQL Server 2012 PERCENT_RANK() treats NULLS as a real value in the distribution. If there were one column to be ranked, you can simply add a where clause (i.e., where Cole_Crops IS NOT NULL).

推荐答案

这是一个简单的解决方法，在您的分区子句中使用 case 语句.

Here's an easy fix, use a case statement in your partition clause.

CASE 
    WHEN COLUMN_1 IS NULL THEN NULL 
    ELSE PERCENT_RANK() OVER 
        (PARTITION BY 
            CASE 
                WHEN COLUMN_1 IS NULL THEN 0 
                ELSE 1 
            END 
         ORDER BY COLUMN_1) 
END AS RANK_COLUMN_1

这将分别对空值和非空值进行排名，但包装 case 语句会将空值显示为空值.

This will rank nulls and non-nulls individually, but the wrapping case statement will display null values as nulls.

问题解决了！

这篇关于SQL Server 2012 PERCENT_RANK() 排除 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！