count(distinct ) over() is supported?

KaitoKaito Employee
edited December 2018 in General Discussion

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,____> > > > > ``>

Comments

  • Jim_KnicelyJim_Knicely Administrator
    edited December 2018

    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

  • KaitoKaito Employee

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

  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file