I have a table with columns for Customer, Order, Fruit, and Quantity, sorted by Customer and Order.
I need to assign the example Desired Group Number values sequentially, starting at 1, assigning the same value until the Fruit changes and then increment 1 for the next Fruit, resetting back to one when Customer changes.
I've racked my brain to think of a solution using LAG, RANK or ROW_NUMBER window functions but remain stumped. Suggestions?!?!
Customer Order Fruit Qty DESIRED GROUP NUMBER
-------- ----- ----- --- --------------------
A 1 Apple. 5 1
A 2 Apple. 4 1
A 3 Apple. 6 1
A 4 Banana. 4 2
A 5 Orange. 1 3
A 6 Orange. 9 3
A 7 Apple. 9 4
A 8 Apple. 3 4
B 1 Banana. 6 1
B 2 Banana. 5 1
B 3 Apple. 4 2
It can be achieved with windowed SUM and LAG:
WITH cte_lag AS
(
SELECT t.*, LAG(t.Fruit) OVER(PARTITION BY Customer ORDER BY "order") AS fruit_prev
FROM t
)
SELECT *,SUM( CASE WHEN fruit = COALESCE(fruit_prev,fruit) THEN 0 ELSE 1 END)
OVER(PARTITION BY Customer ORDER BY "order") + 1 AS grp
FROM cte_lag
ORDER BY customer, "order";