Page 1 of 1

Conditional Change Event

Posted: Mon Aug 12, 2013 5:35 pm
by shaunmcleodvertica
I have an orders table which shows customer ids' alongside product ids' ( the customer id is entered in the CustomerID column alongside each ProductID corresponding to the products a particular customer orders ).
Clearly there is a one to many relationship in both directions.

I wish to count the distinct number of products each customer has ordered, without using a with statement or GROUP BY clause ( as this column will sit inside a larger table which I do not wish to Group in any way ).

This is an extract from the query:

SELECT

CAST( TO_TIMESTAMP( orders.OrderDate ) AS DATE ) AS OrderDate,
orders.CustomerID,
orders.ProductID,
COUNT( orders.ProductID ) OVER ( PARTITION BY orders.CustomerID ORDER BY orders.ProductID DESC ROWS BETWEEN UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING ) AS OrderCount,
1+ CONDITIONAL_CHANGE_EVENT( orders.ProductID ) OVER ( PARTITION BY orders.CustomerID ORDER BY orders.ProductID DESC ROWS BETWEEN UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING ) AS DistinctCount

FROM
orders

The problem is the last column changes in value ( as it increments by 1 each time Vertica observes a new productid ). I want the number of distinct products ordered to be a constant value for each customerID and thus be the same at each instance of a particular customer in the table. The same as it is in the penultimate column.

If anyone can help that would be much appreciated.

Re: Conditional Change Event

Posted: Mon Aug 12, 2013 9:31 pm
by JimKnicely
Hi,

Here's a quick example that shows you may be able to out he MAX analytic function on top of your query...

Sample data:

Code: Select all

dbadmin=> SELECT * FROM test ORDER BY c1, c2;
 c1 | c2
----+----
  1 |  1
  1 |  2
  2 |  1
  2 |  1
  2 |  2
  2 | 10
(6 rows)

Code: Select all

dbadmin=> SELECT c1,
dbadmin->        c2,
dbadmin->        OrderCount,
dbadmin->        MAX(DistinctCount) OVER (PARTITION BY c1) DistinctCount
dbadmin->   FROM (SELECT c1,
dbadmin(>                c2,
dbadmin(>                COUNT(c2) OVER (PARTITION BY c1) OrderCount,
dbadmin(>                1 + CONDITIONAL_CHANGE_EVENT(c2) OVER (PARTITION BY c1 ORDER BY c2) DistinctCount
dbadmin(>           FROM test) foo
dbadmin->  ORDER
dbadmin->     BY c1, c2;
 c1 | c2 | OrderCount | DistinctCount
----+----+------------+---------------
  1 |  1 |          2 |             2
  1 |  2 |          2 |             2
  2 |  1 |          4 |             3
  2 |  1 |          4 |             3
  2 |  2 |          4 |             3
  2 | 10 |          4 |             3
(6 rows)
I think this is what you intended!