sql-server - 从 SQL Server 中的四分位数转义空值

Question

我创建了一个表并插入了一些记录。

CREATE TABLE #t 
   (
   ID INT IDENTITY(1,1) PRIMARY KEY,
   val INT NULL
   );

--   INSERT 10 values: 3 NULL, 7 integers
   INSERT INTO #t SELECT NULL;
   INSERT INTO #t SELECT NULL;
   INSERT INTO #t SELECT NULL;
   INSERT INTO #t SELECT 5;
   INSERT INTO #t SELECT 7;
   INSERT INTO #t SELECT 8;
   INSERT INTO #t SELECT 9;
   INSERT INTO #t SELECT 9;
   INSERT INTO #t SELECT 11;
   INSERT INTO #t SELECT 12;

现在当我执行脚本来寻找四分位数时

SELECT *,
  NTILE(4) OVER (ORDER BY val) As Q
FROM #t;

我得到的值是

ID val Q
---------
1 NULL 1
2 NULL 1
3 NULL 1
4 5 2
5 7 2
6 8 2
7 9 3
8 9 3
9 11 4
10 12 4

我不想使用“Null”记录，也不想使用“Where”子句。我想要这样的结果

ID val Q
---------
4 5 1
5 7 1
6 8 2
7 9 2
8 9 3
9 11 3
10 12 4

Answer 1

令我印象深刻的是，您可能希望 NTILE() 值为 NULL 并忽略计算值。你可以这样做:

SELECT t.*,
       (CASE WHEN val IS NOT NULL
             THEN NTILE(4) OVER (PARTITION BY (CASE WHEN val IS NOT NULL THEN 'NOTNULL' ELSE 'NULL' END)
                                 ORDER BY val
                                )
        END) as Q
FROM #t t;

或者，也许更简单:

SELECT id, val, NTILE(4) OVER (ORDER BY val) As Q
FROM #t t
WHERE val IS NOT NULL
UNION ALL
SELECT id, val, NULL as q
FROM #t t
WHERE val IS NULL;