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 dual

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.