按特定时间段划分(如:按最后2至3个月分列数据.4-5个月、6-7个月)
按特定时间段划分(如:按最后2至3个月分列数据.4-5个月、6-7个月)
提问于 2021-11-17 19:04:58
我有一个简单的问题，我不知道我在哪里做错了。我真的很感激你的帮助。提前谢谢你的提示。
我有数据，需要在1个月内，2-3个月，4-5个月，6-7个月，7-9个月，等等。所以，我创建了一个查询；
Select case
when release_date >= current_date - interval '1 month' then 'Within 1 month'
when release_date between (current_date - interval '2 months') and (current_date - interval '3 months') then '2-3 months'
when release_date between (current_date - interval '4 months') and (current_date - interval '5 months') then '4-5 months'
when release_date between (current_date - interval '6 months') and (current_date - interval '7 months') then '6-7 months'
when release_date between (current_date - interval '8 months') and (current_date - interval '9 months') then '8-9 months'
when release_date between (current_date - interval '10 months') and (current_date - interval '12 months') then '9-12 months'
when release_date < current_date - interval '12 months' then '> 12 month'
end as release_date,
country,
....最后，我只能看到在1个月内的，空和> 12个月的。
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-11-17 19:13:49
如果我正确地读取了您的查询，您需要交换“介于”语句中的值：
Select case
when release_date >= current_date - interval '1 month' then 'Within 1 month'
when release_date between (current_date - interval '3 months') and (current_date - interval '2 months') then '2-3 months'
when release_date between (current_date - interval '5 months') and (current_date - interval '4 months') then '4-5 months'
when release_date between (current_date - interval '7 months') and (current_date - interval '6 months') then '6-7 months'
when release_date between (current_date - interval '9 months') and (current_date - interval '8 months') then '8-9 months'
when release_date between (current_date - interval '12 months') and (current_date - interval '9 months') then '9-12 months'
when release_date < current_date - interval '12 months' then '> 12 month'
end as release_date,
country,我没有一个系统来运行这个来确认，但它应该可以工作。
问题是between是如何工作的，BETWEEN (lower_limit) AND (upper_limit)。
Stack Overflow用户
发布于 2021-11-17 19:42:05
我认为你的产出是在计算，而不是“月份”。答案取决于您使用的dbms。这是在postgres中执行的一种方法，但是如果您使用的是另一个dbms，那么您应该能够轻松地更改计算CTE中月份的语法。使用CTE可以使您有一个干净的case语句。
with calculated_months as
(
select release_date,
extract(year from age(current_date, release_date)) * 12
+ extract(month from age(current_date, release_date))
+ 1 as months_from_current
from t1
)
select t.release_date,
case
when c.months_from_current < 2 then 'Within 1 month'
when c.months_from_current between 2 and 3 then '2-3 months'
when c.months_from_current between 4 and 5 then '4-5 months'
when c.months_from_current between 6 and 7 then '6-7 months'
when c.months_from_current between 8 and 9 then '8-9 months'
when c.months_from_current between 10 and 12 then '10-12 months'
when c.months_from_current > 12 then '12+ months'
end months_since_release,
t.country
from t1 t
join calculated_months c
on c.release_date = t.release_datedb-花式这里
编辑:不确定您的数据是如何显示的，但是您可能希望在CTE中添加一个" distinct“(选择distinct发布日期.)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70010168
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号