Conditional Change Event

Moderator: NorbertKrupa

Post Reply
shaunmcleodvertica
Newbie
Newbie
Posts: 1
Joined: Mon Aug 12, 2013 5:03 pm

Conditional Change Event

Post by shaunmcleodvertica » Mon Aug 12, 2013 5:35 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Conditional Change Event

Post by JimKnicely » Mon Aug 12, 2013 9:31 pm

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!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Analytics”