Терадата ошибается в делении?

У меня есть коллега, который не хочет включать нулевые строки в процентиль. Функция Teradata по умолчанию, кажется, просто обрабатывает нуль как наименьшее число в наборе, поэтому я решил выполнить математику вручную. Я начал использовать приведенный ниже запрос, чтобы проверить свое уравнение

drop table tmp;

create multiset volatile table tmp (
  num byteint
) primary index (num)
  on commit preserve rows
;

insert into tmp
values (1)
;insert into tmp
values (2)
;insert into tmp
values (1)
;insert into tmp
values (4)
;insert into tmp
values (null)
;insert into tmp
values (4)
;insert into tmp
values (null)
;insert into tmp
values (2)
;insert into tmp
values (9)
;insert into tmp
values (null)
;insert into tmp
values (10)
;insert into tmp
values (10)
;insert into tmp
values (11)
;

select
  num,
  case
    when num is null then 0
    else cast(dense_rank() over (partition by case when num is not null then 1 else 2 end order by num) as number)
  end as str_rnk,
  q.nn,
  str_rnk/q.nn as pct_rnk
from tmp
cross join (
    select cast(count(num) as number) as nn from tmp
) q
order by num
;

Итак, что я ожидаю увидеть в наборе результатов, так это:

num   str_rnk  nn  pct_rnk
null        0  10        0
null        0  10        0
null        0  10        0
   1        1  10      0.1
   1        1  10      0.1
   2        2  10      0.2
   2        2  10      0.2
   4        3  10      0.3
   4        3  10      0.3
   9        4  10      0.4
  10        5  10      0.5
  10        5  10      0.5

Но я получаю результат, похожий на обычный rank вместо dense_rank, например:

num   str_rnk  nn  pct_rnk
null        0  10        0
null        0  10        0
null        0  10        0
   1        1  10      0.1
   1        1  10      0.1
   2        2  10      0.3
   2        2  10      0.3
   4        3  10      0.5
   4        3  10      0.5
   9        4  10      0.7
  10        5  10      0.8
  10        5  10      0.8

Я знаю, что могу установить ранг в подзапросе, и он будет вычислять так, как я ожидаю, но почему он не делает это так, как сейчас?


teradata rank dense-rank percentile
person Eric Ed Lohmar    schedule 07.05.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Как отметил JNeville, это ошибка, вам следует открыть инцидент с поддержкой Teradata:

SELECT
   num,
   -- cast to FLOAT or DECIMAL works as expected
   Cast(Dense_Rank() Over (ORDER BY num) AS NUMBER) AS a,
   a AS b
FROM tmp

 num    a    b
----  ---  ---
   ?    1    1
   ?    1    1
   ?    1    1
   1    2    4
   1    2    4
   2    3    6
   2    3    6
   4    4    8
   4    4    8
   9    5   10
  10    6   11
  10    6   11
  11    7   13

Но добавление QUALIFY a<>b возвращает пустой результат :-)

Первоначальный расчет для PERCENT_RANK основан на

Cast(Rank() Over (ORDER BY num) -1 AS DEC(18,6)) / Count(*) Over ()

Если вы хотите исключить NULL, вы можете переключиться на Count(num) и NULLS LAST:

SELECT
   num,
   CASE
      WHEN num IS NOT NULL 
      THEN Cast(Dense_Rank() Over (ORDER BY num NULLS LAST) AS DECIMAL(18,6)) 
      ELSE 0
   END AS str_rnk,
   str_rnk / Count(num) Over ()
FROM tmp

Или используя хитрый трюк num * 0:

SELECT
   num,
   Coalesce(Dense_Rank()
            Over (ORDER BY num NULLS LAST) 
             * (num * 0 +1.000000), 0) AS str_rnk,
   str_rnk / Count(num) Over ()
FROM tmp
person dnoeth    schedule 07.05.2018
comment
Приятно слышать, что вы думаете, что это ошибка. Я пытался обдумать, почему он может это делать, и не мог придумать какой-либо веской причины. - person JNevill; 07.05.2018

arrow_upward
2
arrow_downward

Пока это не отвечает на ваш вопрос. Проблема не в разделении, а в том, что CAST и Dense_Rank дважды запускаются в одном и том же SELECT.

Рассмотреть возможность:

select
  num,
  case
    when num is null then 0
    else cast(dense_rank() over (partition by case when num is not null then 1 else 2 end order by num) as number)
  end as str_rnk,

  case
    when num is null then 0
    else cast(dense_rank() over (partition by case when num is not null then 1 else 2 end order by num) as number)
  end as str_rnk2
from tmp
cross join (
    select cast(count(num) as number) as nn from tmp
) q;


+--------+---------+----------+
|  num   | str_rnk | str_rnk2 |
+--------+---------+----------+
| 1      |       1 |        1 |
| 1      |       1 |        1 |
| 2      |       2 |        3 |
| 2      |       2 |        3 |
| 4      |       3 |        5 |
| 4      |       3 |        5 |
| 9      |       4 |        7 |
| 10     |       5 |        8 |
| 10     |       5 |        8 |
| 11     |       6 |       10 |
| <null> |       0 |        0 |
| <null> |       0 |        0 |
| <null> |       0 |        0 |
+--------+---------+----------+

Поскольку CAST здесь не нужен:

select
  num,

  case
    when num is null then 0
    else dense_rank() over (partition by case when num is not null then 1 else 2 END order by num) 
  end as str_rnk,

  case
    when num is null then 0
    else dense_rank() over (partition by case when num is not null then 1 else 2 END order by num) 
  end as str_rnk2
from tmp
cross join (
    select cast(count(num) as number) as nn from tmp
) q;

+--------+---------+----------+
|  num   | str_rnk | str_rnk2 |
+--------+---------+----------+
| 1      |       1 |        1 |
| 1      |       1 |        1 |
| 2      |       2 |        2 |
| 2      |       2 |        2 |
| 4      |       3 |        3 |
| 4      |       3 |        3 |
| 9      |       4 |        4 |
| 10     |       5 |        5 |
| 10     |       5 |        5 |
| 11     |       6 |        6 |
| <null> |       0 |        0 |
| <null> |       0 |        0 |
| <null> |       0 |        0 |
+--------+---------+----------+

Ваш запрос с быстрым переписыванием:

select
  num,
  case
    when num is null then 0
    else dense_rank() over (partition by num * 0 order by num) 
    end as str_rnk, 
  str_rnk * 1.0/COUNT(*) OVER (PARTITION BY num * 0) as pct_rnk
from tmp
order by num
;

+--------+---------+---------+
|  num   | str_rnk | pct_rnk |
+--------+---------+---------+
| <null> |       0 |     0.0 |
| <null> |       0 |     0.0 |
| <null> |       0 |     0.0 |
| 1      |       1 |     0.1 |
| 1      |       1 |     0.1 |
| 2      |       2 |     0.2 |
| 2      |       2 |     0.2 |
| 4      |       3 |     0.3 |
| 4      |       3 |     0.3 |
| 9      |       4 |     0.4 |
| 10     |       5 |     0.5 |
| 10     |       5 |     0.5 |
| 11     |       6 |     0.6 |
+--------+---------+---------+

Или, если вы хотите полностью убрать оператор CASE:

select
  num,
  dense_rank() over (partition by num * 0 order by num) * (num * 0 + 1.0) as str_rnk,  
  str_rnk/COUNT(*) OVER (PARTITION BY num * 0) as pct_rnk
from tmp
order by num;
person JNevill    schedule 07.05.2018
comment
Хороший улов на двойном броске, удаление одного из них делает работу, но в игре есть некоторые хитрости TD. count(*) включает в себя нули, а это не то, к чему я стремлюсь. Либо str_rnk, либо nn должны быть каким-то десятичным типом, либо результат - все 0 в pct_rnk. Я слышал, что number делает расчеты наиболее точно? *(num*0+1) возвращает нули, и мне нужны 0 для целей отображения, но я мог бы использовать функцию zeroifnull. Наконец, я поместил ocunt в подзапрос, чтобы убедиться, что он выполняется только один раз. Я уверен, что СУБД оптимизирована для подобных вещей, но мне хотелось бы быть в этом уверенным. - person Eric Ed Lohmar; 07.05.2018
comment
num*0 возвращает либо 0, либо NULL для вашей группировки. Это быстрее, чем оператор CASE. * (num*0+1) умножает время результата на 1, если оно не равно нулю, тогда он делает весь результат нулевым, устраняя необходимость во внешнем операторе CASE. Запустите последний, и я думаю, вы увидите, что вам нужно. The 1.0 заставляет расчет учитывать один десятичный знак, поэтому приведение не требуется. - person JNevill; 07.05.2018
comment
Я определенно собираюсь использовать умножение во внутреннем регистре, но * (num*0+1.0) возвращает ноль, где мне нужно, чтобы он был 0 в конце. Я могу использовать функцию zeroifnull, но я предполагаю, что это всего лишь оболочка для оператора case. - person Eric Ed Lohmar; 07.05.2018
comment
Да, вы можете просто обернуть все это в zeroifnull() или вернуться к более длинному оператору case. Я думаю, что использование ЦП будет ничьей между ними. - person JNevill; 07.05.2018
comment
Трюк num * 0 хорош :-) Но я не думаю, что будет большая разница в процессорах... - person dnoeth; 07.05.2018