我有这样的桌子
CREATE TABLE proc_code (
id INTEGER,
c_date DATE,
code VARCHAR(255)
);
INSERT INTO proc_code (id, c_date, code)
VALUES
(101, '2017-12-21', '3027f'),
(101, '2017-12-24', '3027f'),
(102, '2017-10-09', '3027f'),
(102, '2017-10-16', '3025f'),
(102, '2017-10-23', '3027f'),
(103, '2017-09-21', '3025f'),
(104, '2017-07-23', '3027f'),
(104, '2017-07-28', '3027f'),
(104, '2017-07-31', '3025f'),
(105, '2017-03-06', '3025f'),
(105, '2017-03-16', '3027f'),
(105, '2017-03-22', '3027f');
我正在尝试Status
根据以下条件创建一个名为“ ”的新列
查看date
列的最大值和最小值,
Status
=“不改变”,如果在值code
列是相同的为MAX( date
)和分钟(date
)Status
=“已增加”,如果该code
列中的值对于min()是' 3025f ',date
对于max(date
)是' 3027f 'Status
=“减少”,如果该code
列中的值对于min()是' 3027f ',date
对于max(date
)是' 3025f '我想要的输出是
id c_date code status
101 2017-12-21 3027f Constant
101 2017-12-24 3027f Constant
102 2017-10-09 3027f Constant
102 2017-10-16 3025f Constant
102 2017-10-23 3027f Constant
103 2017-09-21 3025f Constant
104 2017-07-23 3027f Decreased
104 2017-07-28 3027f Decreased
104 2017-07-31 3025f Decreased
105 2017-03-06 3025f Increased
105 2017-03-16 3027f Increased
105 2017-03-22 3027f Increased
我正在尝试以这种方式进行,但没有正确完成
select id, c_date, code,
CASE WHEN ((max(c_date) > min(c_date) && (code == '3025f'))) THEN "Constant" ELSE "Increased" END AS Status
FROM proc_code
GROUP BY id, c_date, code
有没有一种有效的方法可以让我获得所需的输出?有人可以指出我正确的方向吗?
使用first_value()
。我将使用子查询使逻辑更容易理解:
select pc.*,
(case when first_code = last_code then 'no change'
when first_code = '3025f' and last_code = '3027f' then 'increasing'
when first_code = '3027f' and last_code = '3025f' then 'decreasing'
else 'something else'
end) as status
from (select pc.*,
first_value(code) over (partition by id order by c_date) as first_code,
first_value(code) over (partition by id order by c_date desc) as last_code,
from proc_code pc
) pc
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句