本文介绍了来自过度分区函数中的两个时间戳的 Concat 值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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 值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!