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.
Conditional Change Event
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Conditional Change Event
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:
I think this is what you intended!
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.