时间戳

首页 > 解决方案 > 过度分区函数中两个时间戳的连接值

mysql - 过度分区函数中两个时间戳的连接值

问题描述

DB-小提琴：

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

在上面的结果中，我检查plan_weeka 的product是否在两个 之间切换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)

我如何需要修改查询以在预期结果中获取此列？

标签： mysqlsql

解决方案

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

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;

这是演示：

演示

推荐阅读