Conditional Change Event
Posted: 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.
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.