将价值分散到一周内，加权到一周开始
将价值分散到一周内，加权到一周开始
提问于 2019-10-03 16:06:51
我每周有几批货。我不希望他们都安排在星期五。我想在一周内平分
如果我下周有9批货要运，我想在day1上运2件，用day2送2件，然后在剩下的一周里每天运1件。我尝试了累积和，并有一个非常类似的问题，与其他数据，我解决了嵌套的for循环，但它的权重，直到本周开始，使我与这一次挣扎。我试过row_number，除以被分裂的日子，但不是循环的动作。
-- Creates table 1
(SELECT 'SHIP1' SHIP_ID, '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP2', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP3', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP4', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP5', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP6', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP7', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP8', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP9', '202014' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP10', '202015' SHIP_WK FROM DUAL UNION ALL
SELECT 'SHIP11', '202015' SHIP_WK FROM DUAL) SHIPS
-- Creates table 2
(SELECT '202014' WEEK, '7' NO_DAYS FROM DUAL UNION ALL
SELECT '202015' WEEK, '5' NO_DAYS FROM DUAL) WEEK我需要说SHIP1和SHIP2在202014周的第1天，SHIP2 3/4在第2天，然后一天一天。SHIP10 10/11在202015的第1天和第2天，然后3天与空的运输信息。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-10-03 16:31:51
NTILE()解析函数正是您所需要的。
with
ships (ship_id, ship_wk) as (
select 'SHIP1' , '202014' from dual union all
select 'SHIP2' , '202014' from dual union all
select 'SHIP3' , '202014' from dual union all
select 'SHIP4' , '202014' from dual union all
select 'SHIP5' , '202014' from dual union all
select 'SHIP6' , '202014' from dual union all
select 'SHIP7' , '202014' from dual union all
select 'SHIP8' , '202014' from dual union all
select 'SHIP9' , '202014' from dual union all
select 'SHIP10', '202015' from dual union all
select 'SHIP11', '202015' from dual)
, weeks (week, no_days) as (
select '202014', 7 from dual union all
select '202015', 5 from dual
)
select s.ship_id, s.ship_wk,
ntile(w.no_days) over (partition by s.ship_wk, w.no_days
order by s.ship_id) as day_no
from ships s left outer join weeks w on s.ship_wk = w.week
;输出
SHIP_ID SHIP_W DAY_NO
--------- ------ ------
SHIP1 202014 1
SHIP2 202014 1
SHIP3 202014 2
SHIP4 202014 2
SHIP5 202014 3
SHIP6 202014 4
SHIP7 202014 5
SHIP8 202014 6
SHIP9 202014 7
SHIP10 202015 1
SHIP11 202015 2这是从“船”的结束和分配日的一周。在您最初的文章中，您还讨论了一周中没有使用的几天内的NULL；可以添加这个值，但是现在还不清楚您是从哪一边查看输出的。你需要知道哪些船在每周的每一天，还是你需要知道每艘船，哪一周和哪一周需要装运？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58223006
复制相关文章
相似问题
腾讯云开发者
