来自过度分区函数中的两个时间戳的 Concat 值

问题描述

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 之间切换.
为此，我使用以下查询:

---

In the above result I check if the plan_week of a product has switched between two time_stamps.
For this I use the following query:

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 的列.
在本专栏中，我想描述周是如何切换的.
基本上是这样的:

Now, I want to add a column called plan_week_switch to the results.
In this column I want to describe how the weeks have switched.
Basically, something like this:

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

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

How do I need to modify the query to get this column in the expected result?

推荐答案

我相信你已经对你的问题有了大部分的答案.

I beieve you already had the most of the answer in your question.

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;

这是演示:

演示

这篇关于来自过度分区函数中的两个时间戳的 Concat 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！