'SQL Segmentation

I have this table

Customer Amount Date Period Group
77766 50 2022-02-28 1 2
77766 20 2022-03-31 2 2
77766 30 2022-04-30 3 3
12345 50 2022-02-28 1 1
12345 20 2022-03-31 2 2
12345 30 2022-04-30 3 3

and I'm trying to achieve this segmentation model where I assign a Group to its corresponding Period per Customer

Customer Amount Date Period Group Group_Period1 Group_Period2 Group_Period3
77766 50 2022-02-28 1 2 2 2 3
77766 20 2022-03-31 2 2 2 2 3
77766 30 2022-04-30 3 3 2 2 3
12345 50 2022-02-28 1 1 1 2 3
12345 20 2022-03-31 2 2 1 2 3
12345 30 2022-04-30 3 3 1 2 3

I tried case function but it didn't work

select a.*, 
case when a.Period=1 then Group end Grupo1 , 
case when a.Period=2 then Group2 end Grupo2 , 
case when a.Period=3 then Group3
end Grupo3 
from a

I got this output:

Customer Amount Date Period Group Group_Period1 Group_Period2 Group_Period3
77766 50 2022-02-28 1 2 2 null null
77766 20 2022-03-31 2 2 null 2 null
77766 30 2022-04-30 3 3 null null 3
12345 50 2022-02-28 1 1 1 null null
12345 20 2022-03-31 2 2 null 2 null
12345 30 2022-04-30 3 3 null null 3

Can anybody guide me to achieve the expected? Thank you in advice

sqlsql-server


Solution 1:[1]

You can use window function such as MAX() OVER () with conditionals for values of Period column such as

SELECT *, 
       MAX(CASE WHEN Period=1 THEN `Group` END) 
           OVER (PARTITION BY Customer) AS Group_Period1,
       MAX(CASE WHEN Period=2 THEN `Group` END) 
           OVER (PARTITION BY Customer) AS Group_Period2,
       MAX(CASE WHEN Period=3 THEN `Group` END) 
           OVER (PARTITION BY Customer) AS Group_Period3
  FROM t -- replace with your table's name

where grouping is figured out through use of PARTITION BY clause

Demo

Solution 2:[2]

One approach can be via inline sub-query.

select *,
(select group_1 from cust a where a.customer = b.customer and period=1) Group_Period1,
(select group_1 from cust a where a.customer = b.customer and period=2) Group_Period2,
(select group_1 from cust a where a.customer = b.customer and period=3) Group_Period3 
from cust b;

Fiddle here

Solution 3:[3]

You can do it using a SELF INNER JOIN on the Customer identifier in combination with an IF statement, that will check for the corresponding period (1, 2 or 3 according to the field). Then the MAX aggregation function will remove NULL values.

SELECT t1.Customer,
       t1.Amount,
       t1.Date,
       t1.Period,
       t1.Group,
       MAX(IF(t2.Period = 1, t2.Group, NULL)) AS PeriodGroup1,
       MAX(IF(t2.Period = 2, t2.Group, NULL)) AS PeriodGroup2,
       MAX(IF(t2.Period = 3, t2.Group, NULL)) AS PeriodGroup3
FROM       tab t1
INNER JOIN tab t2
        ON t1.Customer = t2.Customer
GROUP BY t1.Customer,
         t1.Amount,
         t1.Date,
         t1.Period,
         t1.Group

Try it here.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Barbaros Özhan
Solution 2 Pankaj
Solution 3

Related Questions