sql - 解码当天并与列进行比较
问题描述
我有专栏:
1.: delivery_date
column.. 它返回星期几,如下所示:
1 --monday
2 --tuesday
3 --wednesday
4 --thursday
5 --friday
2.:date
列.. 它返回取件日期 - 这是一个正常的日期列 - 示例:25.03.2019 (format DD.MM.YYYY)
但是,我们的系统会自动比较并设置date
最接近的 delivery_date
.
例子:
伴侣delivery_date
每周星期四和星期二都有.. [所以 4 和 2]。他想在星期五取货。此时我们的系统更改date
为星期四。但我想要下一个date
,也就是星期二 -我想把它解码到今天(格式 DD.MM.YYYY)
所以输出如下:
--if pick up date: 22.03.2019 (friday)
--partner has delivery_date: 4,2(thursday, tuesday)
I want to get: 26.03.2019 --as output
我可以比较列..这没问题..但是我如何解码“实际提取”日期?
我怎样才能做到这一点 ?
解决方案
为了解决这个问题,构建一个“日历”表可能是一个优势 - 让它足够大(!),随后可以用来加密你的数据(参见下面代码中的注释)。为了测试,让我们使用下表(Oracle 12c、18c):
表 CAL_
create table cal_
as
select sysdate + level date_
, to_char( sysdate + level, 'Day' ) dname_
, to_char( sysdate + level, 'D' ) dnumber_
from dual
connect by level <= 31 ; -- number should be big enough to cover the lifetime of your system
SQL> select * from cal_ ;
DATE_ DNAME_ DNUMBER_
17-MAR-19 Sunday 7
18-MAR-19 Monday 1
19-MAR-19 Tuesday 2
20-MAR-19 Wednesday 3
21-MAR-19 Thursday 4
...
12-APR-19 Friday 5
13-APR-19 Saturday 6
14-APR-19 Sunday 7
15-APR-19 Monday 1
16-APR-19 Tuesday 2
表 DELIVERYDAYS
create table deliverydays ( partner, deliveryday, dayofweek )
as
select
mod( level, 3 ) + 1
, mod( level, 5 ) + 1
, to_char( sysdate + ( mod( level, 5 ) + 2 ), 'Day' )
from dual
connect by level <= 10 ;
-- partner 4 delivers on Tue and Thu (same days as in the question)
insert into deliverydays ( partner, deliveryday, dayofweek )
values ( 4, 2, 'Tuesday' ) ;
insert into deliverydays ( partner, deliveryday, dayofweek )
values ( 4, 4, 'Thursday' ) ;
SQL> select * from deliverydays order by partner, deliveryday ;
PARTNER DELIVERYDAY DAYOFWEEK
1 2 Tuesday
1 4 Thursday
1 5 Friday
2 1 Monday
2 2 Tuesday
2 3 Wednesday
2 5 Friday
3 1 Monday
3 3 Wednesday
3 4 Thursday
4 2 Tuesday
4 4 Thursday
12 rows selected.
您可以像这样加密您的数据。请注意,交货日期之间的间隔变得可见。(带有下划线的列名:来自 CAL_ 表)。
select
partner, deliveryday , dayofweek
, date_, dname_, dnumber_
from deliverydays D partition by ( partner )
right join cal_ C on D.deliveryday = C.dnumber_
where partner = 4
order by date_ ;
PARTNER DELIVERYDAY DAYOFWEEK DATE_ DNAME_ DNUMBER_
4 NULL NULL 17-MAR-19 Sunday 7
4 NULL NULL 18-MAR-19 Monday 1
4 2 Tuesday 19-MAR-19 Tuesday 2
4 NULL NULL 20-MAR-19 Wednesday 3
4 4 Thursday 21-MAR-19 Thursday 4
4 NULL NULL 22-MAR-19 Friday 5
4 NULL NULL 23-MAR-19 Saturday 6
4 NULL NULL 24-MAR-19 Sunday 7
4 NULL NULL 25-MAR-19 Monday 1
4 2 Tuesday 26-MAR-19 Tuesday 2
4 NULL NULL 27-MAR-19 Wednesday 3
4 4 Thursday 28-MAR-19 Thursday 4
...
-- 31 rows selected
下一步,您可以使用 LEAD() 函数来查找“下一个”交货日期,然后计算“取货”和交货之间的天数,并根据需要格式化日期。(请参阅此处查询的“注释”版本。)
select pid, dday, next_dday, date_, dname_, dnumber_
, case
when dnumber_ > next_dday then ( 7 - dnumber_ + next_dday ) -- next week
else next_dday - dnumber_
end ddiff_
, case
when dnumber_ > next_dday then
to_char( date_ + ( 7 - dnumber_ + next_dday ), 'DD.MM.YYYY' )
else
to_char( date_ + ( next_dday - dnumber_ ), 'DD.MM.YYYY' )
end delivered_on
from (
select
partner as pid, deliveryday as dday
, lead( deliveryday ) ignore nulls over ( order by date_ ) as next_dday
, dayofweek
, date_, dname_, dnumber_
from deliverydays D partition by ( partner )
right join cal_ C on D.deliveryday = C.dnumber_
where partner = 4 -- partner id
)
where to_char( date_, 'DD.MM.YYYY' ) = '22.03.2019' -- "pick up" date
order by date_ ;
结果
PID DDAY NEXT_DDAY DATE_ DNAME_ DNUMBER_ DDIFF_ DELIVERED_ON
4 NULL 2 22-MAR-19 Friday 5 4 26.03.2019
不使用外部 WHERE 子句,我们得到 ..
PID DDAY NEXT_DDAY DATE_ DNAME_ DNUMBER_ DDIFF_ DELIVERED_ON
4 NULL 2 17-MAR-19 Sunday 7 2 19.03.2019
4 NULL 2 18-MAR-19 Monday 1 1 19.03.2019
4 2 4 19-MAR-19 Tuesday 2 2 21.03.2019
4 NULL 4 20-MAR-19 Wednesday 3 1 21.03.2019
4 4 2 21-MAR-19 Thursday 4 5 26.03.2019
4 NULL 2 22-MAR-19 Friday 5 4 26.03.2019
4 NULL 2 23-MAR-19 Saturday 6 3 26.03.2019
4 NULL 2 24-MAR-19 Sunday 7 2 26.03.2019
4 NULL 2 25-MAR-19 Monday 1 1 26.03.2019
4 2 4 26-MAR-19 Tuesday 2 2 28.03.2019
4 NULL 4 27-MAR-19 Wednesday 3 1 28.03.2019
4 4 2 28-MAR-19 Thursday 4 5 02.04.2019
...
-- 31 rows selected
推荐阅读
- python - 基于条件的函数计算
- php - 尝试使用隐藏的 mySQL ID 获取隐藏的输入值
- javascript - 将活动类添加到 React Accessible Accordion 中的按钮
- javascript - 获取执行phantomjs的php文件的url
- sql - MariaDB 查找当月的第一天并转换为 UTC
- codeigniter - 如何修复错误:count():参数必须是数组或实现 Countable 的对象
- java - 可选的
为空时从 Optional.ifPresentOrElse() 返回一个字符串? - swiftui - JSON 数据未复制到结构
- puma - 您是否尝试打开与非 SSL Puma 的 SSL 连接?
- python - Pycharm 不会运行程序