将两个时间戳中的值合并到过分区函数中

Question

DB Fiddle:

CREATE TABLE operations (
id int auto_increment primary key,
time_stamp DATE,
product VARCHAR(255),
plan_week VARCHAR(255),
quantity INT
);
INSERT INTO operations
(time_stamp, product, plan_week, quantity
)
VALUES 
("2020-01-01", "Product_A", "CW01", "125"),
("2020-01-01", "Product_B", "CW01", "300"),
("2020-01-01", "Product_C", "CW01", "700"),
("2020-01-01", "Product_D", "CW01", "900"),
("2020-01-01", "Product_G", "CW01", "600"),
("2020-03-15", "Product_A", "CW01", "570"),
("2020-03-15", "Product_C", "CW02", "150"),
("2020-03-15", "Product_E", "CW02", "325"),
("2020-03-15", "Product_G", "CW05", "482");

预期结果：

time_stamp     product    plan_week     quantity    week_switched    plan_week     plan_week_switch
2020-01-01    Product_A     CW01         125            no             CW01            no
2020-03-15    Product_A     CW01         570            no             CW01            no
2020-01-01    Product_B     CW01         300            no             CW01            no
2020-01-01    Product_C     CW01         700            yes            CW01         CW01-to-CW02
2020-03-15    Product_C     CW02         150            yes            CW02         CW01-to-CW02
2020-01-01    Product_D     CW01         900            no             CW01            no
2020-03-15    Product_E     CW02         325            no             CW02            no 
2020-01-01    Product_G     CW01         600            yes            CW01         CW01-to-CW05
2020-03-15    Product_G     CW05         482            yes            CW05         CW01-to-CW05

在上述结果中，我检查product的plan_week是否已在两个time_stamps之间切换
为此，我使用以下查询：

SELECT 
time_stamp,
product,
plan_week,
quantity,
(CASE WHEN MIN(plan_week) over (partition by product) = MAX(plan_week) over (partition by product)
THEN 'no' else 'yes' END) as week_switched,
plan_week
FROM operations
GROUP BY 1,2
ORDER BY 2,1;

所有这些都非常有效。

---

现在，我想在结果中添加一个名为plan_week_switch的列
在本专栏中，我想描述一下这几周是如何变化的
基本上是这样的：

CONCAT(plan_week in first time_stamp, "-to-" , plan_week in second time_stamp)

我需要如何修改查询才能获得预期结果中的此列？

Answer 1

我相信你的问题已经得到了大部分答案。

SELECT time_stamp
, product
, plan_week
, quantity
, (CASE WHEN MIN(plan_week) over (partition by product) = MAX(plan_week) over (partition by product) THEN 
'no' 
ELSE 'yes' 
END) as week_switched
,(CASE WHEN MIN(plan_week) over (partition by product) = MAX(plan_week) over (partition by product) THEN 
'no' 
ELSE 
concat(cast(MIN(plan_week) over (partition by product) as char), '-to-', MAX(plan_week) over (partition by product)) 
END) as plan_week_switch
, plan_week
FROM operations
GROUP BY 1,2
ORDER BY 2,1;

这是演示：

演示