count(distinct ) over() is supported?

Kaito

I get the following error when I use "count(distinct ) over()". If you have a workaroud, could you share it with me?

==Error message==
 [Vertica][VJDBC](7830) ERROR: Only MIN/MAX and AND/OR are allowed to use DISTINCT
===============****

FYI: https://stackoverflow.com/questions/41369687/using-analytical-countdistinct-on-vertica-is-not-supported

Thanks in advance,____> > > > > ``>

Jim_Knicely

Hi,

Maybe the CONDITIONAL_CHANGE_EVENT analytic function can help?

Example:

dbadmin=> SELECT * FROM test ORDER BY 1;
 c1 | c2
----+----
  1 |  2
  1 |  2
  1 |  3
  2 |  1
  3 |  1
  3 |  1
  3 |  2
  3 |  4
  3 |  5
(9 rows)

dbadmin=> SELECT c1, c2, MAX(c3) OVER (PARTITION BY c1) distinct_c2
dbadmin->   FROM (SELECT c1, c2, CONDITIONAL_CHANGE_EVENT(c2) OVER (PARTITION BY c1 ORDER BY c2) + 1 AS c3
dbadmin(>           FROM test) foo;
 c1 | c2 | distinct_c2
----+----+-------------
  1 |  2 |           2
  1 |  2 |           2
  1 |  3 |           2
  2 |  1 |           1
  3 |  1 |           4
  3 |  1 |           4
  3 |  2 |           4
  3 |  4 |           4
  3 |  5 |           4
(9 rows)

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/CONDITIONAL_CHANGE_EVENTAnalytic.htm

Kaito

Thank you, Jim. I am going to test this.

avoronin

Window DISTINCT_COUNT can be implemented as a two step calculation. 1) you do window Dense_rank() 2) You take window MAX()
Try this example:
WITH DATA AS
(
SELECT 1 AS ID, 1 AS Val UNION ALL
SELECT 1 AS ID, 1 AS Val UNION ALL
SELECT 1 AS ID, 2 AS Val UNION ALL
SELECT 1 AS ID, 2 AS Val UNION ALL
SELECT 1 AS ID, 3 AS Val UNION ALL
SELECT 2 AS ID, 4 AS Val UNION ALL
SELECT 2 AS ID, 5 AS Val UNION ALL
SELECT 2 AS ID, 5 AS Val UNION ALL
SELECT 3 AS ID, 6 AS Val UNION ALL
SELECT 3 AS ID, 7 AS Val UNION ALL
SELECT 3 AS ID, 8 AS Val UNION ALL
SELECT 3 AS ID, 9 AS Val
)
SELECT ID, Val, MAX(DR) OVER (PARTITION BY ID) AS PARTITION_DISTINCT_COUNT FROM
(
SELECT ID, Val, DENSE_RANK() OVER (PARTITION BY ID ORDER BY VAL) AS DR FROM DATA
) a