我有这个 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 |
并且我正在尝试实现这种细分模型，在该模型中，我将一个组分配给每个客户的相应期间
| 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 |
我尝试了案例功能，但它没有用
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我得到了这个输出：
| 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 |
有人可以指导我实现预期吗？谢谢你的建议
回答1
您可以使用诸如 MAX() OVER () 之类的窗口函数和 Period 列的 values 条件，例如
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 namewhere 分组是通过使用 PARTITION BY 子句计算出来的
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d48f71b9ba4cdd2ddd8b1d02655beb72
回答2
一种方法可以通过内联子查询。
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;小提琴https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1d517968a1a1747b6178d205d693ba31
回答3
您可以使用客户标识符上的 SELF INNER JOIN 结合 IF 语句来执行此操作，该语句将检查相应期间（根据字段为 1、2 或 3）。然后 MAX 聚合函数将删除 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