Other special values besides NULL?

Answers

• Options

  mosheg Vertica Employee Administrator

  November 2022

  NaN (Not a Number) does not equal anything, not even another NaN.
  You can query for them using the predicate … WHERE column != column
  See: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values

  create table NaN_Test (c1 float);
  insert into NaN_Test select 'NaN';
  select * from NaN_Test;
  c1
  -----
  NaN
  (1 row)
  
  select * from NaN_Test where c1 = 'NaN';
  c1
  ----
  (0 rows)
  
  select * from NaN_Test where c1 != c1;
  c1
  -----
  NaN
  (1 row)
  

  1
• Options

  Pieter_Sheth-Vo ✭✭

  November 2022 edited November 2022

  @mosheg

  Circling back with new words from exploration, my actual goal was to calculate COUNT/GROUP BY with rollups and differentiate counts of actual NULL values from rollups across values. For this the Vertica ROLLUP(x1, x2, ...) feature is _almost_ perfect.

  But there are three complications in the toy example below:

  • the BY variables are of unknown possibly mixed types
  • I'd like to differentiate true NULL values from rollups but they both return as NULL
  • I'd like to merge the results back but you can't merge on NULL values

  Where I'm netting out is that it's practical if not pretty to:

  • cast all by columns to VARCHAR to avoid type conflicts
  • coalesce all NULL values to a special string to differentiate rollups from true nulls
  • use the vSQL syntax GROUP BY ROLLUP(1,2,3...)
  • on return, interpret NULL as a rollup value and the special string as a NULL

  WITH 
      base AS (
          SELECT 1 as numeral, 'one' AS name UNION ALL
          SELECT 2 as numeral, 'two' AS name UNION ALL
          SELECT NULL as numeral, NULL AS name
      ),
      summed AS (
          SELECT numeral, name, GROUPING_ID(),count(*) 
          FROM base
          GROUP BY ROLLUP(1,2)
      )
  SELECT * FROM summed
  ORDER BY grouping_id, numeral, name
  ;
  

  numeral	name	grouping_id	count
  NULL	NULL	0	1
  1	one	0	1
  2	two	0	1
  NULL	NULL	1	1
  1	NULL	1	1
  2	NULL	1	1
  NULL	NULL	3	3

  0
• Options

  VValdar Vertica Employee Employee

  November 2022

  Hi Pieter_Sheth-Vo,

  You're only lacking the knowledge of the grouping function that let you know if it's a base line row or a rolled up one.

  with cte_base (numeral, name) as
  (
  select 1   , 'one' union all
  select 2   , 'two' union all
  select null, null
  )
    select case grouping(numeral) when 1 then 999        else numeral end as numeral
         , case grouping(name)    when 1 then 'All name' else name    end as name
         , count(*)
      from cte_base
  group by rollup(numeral, name)
  order by grouping_id(), 1, 2;
  
  numeral  name     count
  -------- -------- -----
   [NULL]  [NULL]       1
        1  one          1
        2  two          1
   [NULL]  All name     1
        1  All name     1
        2  All name     1
      999  All name     3
  

  1
• Options

  mosheg Vertica Employee Administrator

  November 2022

  ..and a VSQL count scale example:

  \set SCALE_MAX_CHARS 40
  with list1 (numeral, name) as
  (
  select 1   , 'one' union all
  select 2   , 'two' union all
  select 2   , 'two' union all
  select 3   , 'three' union all
  select 3   , 'three' union all
  select 3   , 'three' union all
  select null, null
  ),
  list2 as (select case grouping(numeral) when 1 then 'TOTAL'    else ISNULL(TO_CHAR(numeral),'[NULL]') end as numeral
         , case grouping(name)    when 1 then '' else ISNULL(name,'[NULL]') end as name
         , grouping_id() as gid
         , count(*) as count
      from list1
  group by rollup(numeral,name)
  order by grouping_id(), 1, 2),
  norm as (select max(count / :SCALE_MAX_CHARS)::INT as norm_factor from list2)
  select numeral, name,
         case when gid > 0 then count end as count,
         case gid when 1 then REPEAT('*', ((count / (norm_factor +1))::INT)) else '' end as Count_Scale
  from list2, norm
  order by gid ,1,2;
  
   numeral |  name  | count | Count_Scale
  ---------+--------+-------+-------------
   1       | one    |       |
   2       | two    |       |
   3       | three  |       |
   [NULL]  | [NULL] |       |
   1       |        |     1 | *
   2       |        |     2 | **
   3       |        |     3 | ***
   [NULL]  |        |     1 | *
   TOTAL   |        |     7 |
  (9 rows)
  

  0
• Options

  HKhan1 Community Edition User

  November 2022

  See this: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values/

  0