iT邦幫忙

2

輪班分配員工在負責維修的站點

我又來出題了@@...這個有解完才放出來XD

這題應該有點難,我是用TSQL處理出來的..

假設有4位員工 AA BB CC DD
站點有01 02 03
01需要2位員工
02需要3位員工
03需要2位員工

因為是採輪班方式,故前天的員工未排到的~則當天會從第1位開始排~

圖片結果:
https://ithelp.ithome.com.tw/upload/images/20180727/20061369ya9fACzFAP.png

歡迎各位高手試試~

之後我再把我的寫法放出來參考~@@"


先貼出我的基本設定方式~

declare @Emp table(
	Emp_ID int
	,Emp_Name nvarchar(50)
)
insert into @Emp
values(1,'AA')
,(2,'BB')
,(3,'CC')
,(4,'DD')

declare @Site table(
	Site_ID int
	,Site_Name nvarchar(50)
	,Site_Num int
)
insert into @Site
values(1,'01',2)
,(2,'02',3)
,(3,'03',2)

declare @Users table(
	Users_Date date
	,Users_Site nvarchar(50)
	,Users_EmpID int
	,Users_Emp nvarchar(50)
)
declare @StartDate date,@EndDate date,@SetDate date,@i int,@Count int
declare @j int,@Sub_Count int,@SetSite nvarchar(50),@SetNum int
set @StartDate = '2018/7/27'
set @EndDate = '2018/7/28'
set @Count = DateDiff(day,@StartDate,@EndDate)
set @i = 0

SQL參考~

while(@i<=@Count)
begin
	set @SetDate = dateadd(day,@i,@StartDate)

	set @j = 1

	select @Sub_Count = count(*)
	from @Site

	while(@j<=@Sub_Count)
	begin

		select @SetSite = Site_Name
		,@SetNum = Site_Num
		from @Site
		where Site_ID = @j

		insert into @Users
		select top (@SetNum)
		@SetDate
		,@SetSite
		,Emp_ID
		,Emp_Name
		from (
			select *
			,isNull((
				select count(*) 
				from @Users
				where Users_EmpID = Emp_ID
			),0) as Emp_Num
			from @Emp
		) as k
		order by Emp_Num

		set @j = @j + 1
	end


	set @i = @i + 1
end

select Users_Date 
,Users_Site
,Users_Emp
from @Users

db<>fiddle 線上測試DEMO

/images/emoticon/emoticon42.gifrogeryao比較厲害~這次SQL直接算出循環排班~哈

看更多先前的討論...收起先前的討論...
浩瀚星空 iT邦研究生 2 級 ‧ 2018-07-27 13:29:25 檢舉
其實,我看不懂你的問題。

因為,總共4個人,然後要輪7個點???
這......我想會需要火影忍者的鳴人才辦的到。
哈~這只是個題目咩~
加上每個站點維修時間就更清楚了~
01 站點是08:00~12:00
02 站點是13:00~17:00
03 站點是18:00~22:00
基本上一天的站點時間不衝突~

另外補充~站點才3個點@@..只是需要不同員工去維護~
也應該沒超過8小時的工作時間吧XD
小魚 iT邦高手 1 級 ‧ 2018-07-27 20:00:33 檢舉
鳴人應該一個人就夠了...
newkevin iT邦高手 1 級 ‧ 2018-07-28 12:45:11 檢舉
粉正常啊 老闆思考
3-4個地方 離粉遠
然後
排時間的
排滿8小時 交通吃飯不算在內
說起來~應該加倍請人才對XD
或者站點縮到2小時就可以換地方了~
newkevin iT邦高手 1 級 ‧ 2018-07-29 14:25:23 檢舉
各種維修保證2小時修好
客戶要付多少錢啊
最好可以修到一半 可以走
那種通常是檢查及維護吧...又沒有一堆壞掉的等你修0.0a
super747 iT邦研究生 5 級 ‧ 2018-08-03 08:09:57 檢舉
看起來沒錯,但為什麼都沒考慮到稼動率,出題的人沒有實務經驗
沒有唷~畢竟只是出題玩玩~實務上修正~又是另一回事了~
要考慮簡單構想而已@@..不能加太多額外的因素~
1
rogeryao
iT邦研究生 5 級 ‧ 2018-07-27 19:02:07
最佳解答

http://sqlfiddle.com/#!18/f8707c/2

CREATE TABLE Emp ([Num] varchar(2)) ;
INSERT INTO Emp ([Num]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

CREATE TABLE Center ([ID] varchar(2), [EmpQty] int) ;
INSERT INTO Center ([ID], [EmpQty]) VALUES ('01', 2), ('02', 3), ('03', 2) ;

CREATE TABLE WorkDate ([SchDate] varchar(10)) ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-27') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-28') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-29') ;

select d.Row1,d.SchDate,d.ID,e.Num
from (
select ROW_NUMBER() OVER(ORDER BY f.SchDate,c.ID) AS Row1,f.SchDate,c.ID,c.EmpQty
from WorkDate as f ,(
select b.ID,b.EmpQty
from Center as a
Cross Apply Center as b
where 1=1
and a.EmpQty<=b.EmpQty
) as c
) as d
left join
(select (ROW_NUMBER() OVER(ORDER BY Num asc) %4 ) AS Row2,Num from Emp) as e
on d.Row1 % 4 = e.Row2

看更多先前的回應...收起先前的回應...

哈~~這厲害~直接算出循環排班!!/images/emoticon/emoticon12.gif

暐翰 iT邦大師 4 級 ‧ 2018-07-27 23:41:52 檢舉

真的厲害! /images/emoticon/emoticon12.gif
今天再來看發現圖片點錯... Sorry

研究了一下Cross apply
發現在新增站點就會有問題呢@@a

declare @Site table(
	Site_Name nvarchar(2)
	,Site_Num int
)
insert into @Site
values('01',2)
,('02',3)
,('03',2)
,('04',2)


select b.Site_Name
,b.Site_Num
from @Site as a
Cross apply @Site as b
where a.Site_Num <= b.Site_Num

https://ithelp.ithome.com.tw/upload/images/20180728/20061369VKCq6SXSEp.png

暐翰rogeryao
喔喔!我瞭解Cross apply其實就是相乘~
所以當資料只有1筆的時候~只會1 * 1
但資料有2筆時就是 2 * 2 依此類推~
所以修正這個Bug問題~
就是還是先列出全清單或少資料就直接建

直接建:

站點 人數
01 2
01 2

如果4筆站點加總有大於員工數量就可以Cross apply

例如4 * 4 = 16 >= 2 + 3 +2 +2 就可以用全清單方式@@a

declare @Site table(
	Site_Name nvarchar(2)
	,Site_Num int
)
insert into @Site
values('01',2)
,('02',3)
,('03',2)
,('04',2)


select Site_Name
,Site_Num
from (
	select Row_Number()Over(Partition by b.Site_Name order by b.Site_Name) as Sort
	,b.Site_Name
	,b.Site_Num
	from @Site as a
	Cross apply @Site as b
) as k
where Sort <= Site_Num

--Cross apply 相同道理
select Site_Name
,Site_Num
from (
	select Row_Number()Over(Partition by b.Site_Name order by b.Site_Name) as Sort
	,b.Site_Name
	,b.Site_Num
	from @Site as a
	, @Site as b
) as k
where Sort <= Site_Num

https://ithelp.ithome.com.tw/upload/images/20180728/20061369GC0AZ1cFge.png

rogeryao iT邦研究生 5 級 ‧ 2018-07-28 13:01:40 檢舉

CREATE TABLE Emp ([Num] varchar(2)) ;
INSERT INTO Emp ([Num]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

CREATE TABLE Center ([ID] varchar(2), [EmpQty] int) ;
修改 :
INSERT INTO Center ([ID], [EmpQty]) VALUES ('01', 2), ('02', 3), ('03', 2), ('04', 2), ('05', 4) ;

CREATE TABLE WorkDate ([SchDate] varchar(10)) ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-27') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-28') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-29') ;

select d.Row1,d.SchDate,d.ID,e.Num
from (
select ROW_NUMBER() OVER(ORDER BY f.SchDate,c.ID) AS Row1,f.SchDate,c.ID,c.EmpQty
from WorkDate as f ,(
select a.ID,a.EmpQty
from Center as a
left join (
select ROW_NUMBER() OVER(ORDER BY number asc) as RowNum
from (
select number
from master..spt_values
) as BaseData) as g on a.EmpQty>=g.RowNum
) as c
) as d
left join
(select (ROW_NUMBER() OVER(ORDER BY Num asc) %4 ) AS Row2,Num from Emp) as e
on d.Row1 % 4 = e.Row2

單站最大員工數 2048

http://sqlfiddle.com/#!18/c806f/2

rogeryao iT邦研究生 5 級 ‧ 2018-07-28 14:52:09 檢舉

CREATE TABLE Emp ([Num] varchar(2)) ;
INSERT INTO Emp ([Num]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

CREATE TABLE Center ([ID] varchar(2), [EmpQty] int) ;
INSERT INTO Center ([ID], [EmpQty]) VALUES ('01', 2), ('02', 3), ('03', 2), ('04', 2), ('05', 4) ;

CREATE TABLE WorkDate ([SchDate] varchar(10)) ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-27') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-28') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-29') ;

select d.Row1,d.SchDate,d.ID,e.Num
from (
select ROW_NUMBER() OVER(ORDER BY f.SchDate,c.ID) AS Row1,f.SchDate,c.ID,c.EmpQty
from WorkDate as f ,(
select a.ID,a.EmpQty
from Center as a
left join (
select ROW_NUMBER() OVER(ORDER BY Num asc) as RowNum
from (
select Num
from Emp
) as BaseData) as g on a.EmpQty>=g.RowNum
) as c
) as d
left join
(select (ROW_NUMBER() OVER(ORDER BY Num asc) %4 ) AS Row2,Num from Emp) as e
on d.Row1 % 4 = e.Row2

單站最大員工數=Emp 員工數

http://sqlfiddle.com/#!18/b6c3c/37

喔喔!~
1.利用master..spt_values資料呀~第一次看到這麼用@@~
2.利用員工數去配~第2點比較合理~不然同一個站點~員工就配了2次~/images/emoticon/emoticon32.gif

2
暐翰
iT邦大師 4 級 ‧ 2018-07-27 14:53:40

我的解法也是寫一個SP,邏輯寫在註解裡面
有想到優化方式,再來優化 :D

db<>fiddle 線上測試DEMO

TEST DDL:

CREATE TABLE Emp ([ID] varchar(2)) ; INSERT INTO Emp ([ID]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ; 

CREATE TABLE Center ([ID] int, [EmpQty] int) ; INSERT INTO Center ([ID], [EmpQty]) VALUES (01, 2), (02, 3), (03, 2) ; 

CREATE TABLE Sch ([Datetime] datetime, [CenterID] int, [EmpID] varchar(2)) ;

SP:

CREATE PROCEDURE SP_SCH_Center_ByEmp   
    @v_date datetime  
AS  

declare @v_firstEmp nvarchar(2) =  (
	--找出昨天誰排班最少,藉由group + rownumber count,接著篩選出rank = 1的資料
	select [EmpID] 
	from (
		select [EmpID]
			,row_number() over (order by count(1),[EmpID]) rnk 
		from Sch
		where [Datetime] = (@v_date-1)
		group by [EmpID]
	) T where rnk = 1 
);
declare @v_empCount int = (select count(1) from Emp);

--如果@v_firstEmp沒資料,以第一個員工為準
--如果@v_firstEmp有資料,以前天排最少班的第一個員工為準
if @v_firstEmp is null begin
	select @v_firstEmp = (select top 1 [ID] from Emp);
end;

--先把當天Center資料展出來(使用遞迴)
WITH CTE AS (
	SELECT 1 as Number,ID,EmpQty from Center
	UNION ALL
	SELECT Number+1,ID,EmpQty
	FROM CTE 
	WHERE Number < EmpQty 
)
--幫員工做一個排序
,CTE2 as (
	select * , row_number() over (order by [ID] ) rnk from Emp
)
--Center資料做一個排序,特別使用站點排名 + @v_firstEmp員工的排名-2 做%的基準數
,CTE3 as (
	SELECT @v_date [Datetime],ID [CenterID]
		,( row_number() over (order by [ID]) 
			+ (select rnk - 2 from CTE2 where [ID] = @v_firstEmp )
		) rnk
	FROM CTE
)
insert into sch 
	select T1.[Datetime],T1.[CenterID],T2.[ID] 
	from CTE3 T1
	left join CTE2 T2 on ((T1.rnk) % (@v_empCount))  = T2.rnk-1;

測試新增7/27,28的資料

exec SP_SCH_Center_ByEmp '2018-07-27';
exec SP_SCH_Center_ByEmp '2018-07-28';

select * from SCH;

得到結果:

Datetime CenterID EmpID
2018/7/27 1 AA
2018/7/27 1 BB
2018/7/27 2 CC
2018/7/27 2 DD
2018/7/27 2 AA
2018/7/27 3 BB
2018/7/27 3 CC
2018/7/28 1 DD
2018/7/28 1 AA
2018/7/28 2 BB
2018/7/28 2 CC
2018/7/28 2 DD
2018/7/28 3 AA
2018/7/28 3 BB
2018/7/29 1 CC
2018/7/29 1 DD
2018/7/29 2 AA
2018/7/29 2 BB
2018/7/29 2 CC
2018/7/29 3 DD
2018/7/29 3 AA

另外,這間公司這麼血汗,一個人排這麼多點站一天 XDD

看更多先前的回應...收起先前的回應...

哈~還好啦~不要超過法令8小時就沒問題了@@a
7/27
AA BB CC 都是2個站點
DD 是1個站點
也就每個人每天大約會要去兩個站點維護@@"

果然這題輪班~SQL有點難做~@@a

實務上排班要實際建檔,才能確認前一天到底誰最後~
(因為可能請假不在)

若純粹玩SQL 用暫存就可以了XD..

暐翰 iT邦大師 4 級 ‧ 2018-07-27 15:12:21 檢舉

感覺用純SQL會不好維護呢

假如遇到這種需求我還是會用後端程式來跑順序邏輯
這樣遇到鳥客戶的千變萬化需求,也比較好調整 XDD

比如:
中間客戶提出需求,預設是照大大的邏輯,但也可以人工決定隔天的第一順序


最後,感謝大大提供好玩的題目 /images/emoticon/emoticon12.gif

其實輪班狀況很多~例如~請假不在~臨時調班~翹班~之類的狀況@@a
還要看上班地點打卡是否確實~

暐翰
ok~我也把我寫的方式~放出來參考@@"
你再看看~什麼可以改的~

暐翰 iT邦大師 4 級 ‧ 2018-07-27 17:09:11 檢舉

好喔 我先煮完晚餐給家人有時間來想優化方式

1
一級屠豬士
iT邦新手 3 級 ‧ 2018-07-27 22:51:46
create table ithelp180727_emp (
    id serial primary key
,   ename text not null
);

insert into ithelp180727_emp (ename) values
('AA'), ('BB'), ('CC'), ('DD');

create table ithelp180727_stat (
    sid serial primary key
,   matching int not null
);

insert into ithelp180727_stat values
(1, 2), (2, 3), (3, 2);

--
with   st1 as (
select max(matching) mm
  from ithelp180727_stat
), st2 as (
select count(*) as cnt
  from ithelp180727_emp
), st3 as (
select date '2018-07-27' + d as dt
     , sid
  from st1
     , generate_series(1, st1.mm) as i
     , generate_series(1, i) as j
     , generate_series(0, 1) as d
     , ithelp180727_stat s
 where s.matching = i
), st4 as (
select *
     , row_number() over(order by dt, sid) rn
  from st3
), st5 as (
select dt, sid, rn
     , ntile(st2.cnt::integer) over(order by mod(rn::integer - 1, st2.cnt::integer)) as asgnemp
  from st4
     , st2
)
select dt, sid, ename
  from st5
     , ithelp180727_emp e
 where st5.asgnemp = e.id
order by dt, sid, rn
;

     dt     | sid | ename 
------------+-----+-------
 2018-07-27 |   1 | AA
 2018-07-27 |   1 | BB
 2018-07-27 |   2 | CC
 2018-07-27 |   2 | DD
 2018-07-27 |   2 | AA
 2018-07-27 |   3 | BB
 2018-07-27 |   3 | CC
 2018-07-28 |   1 | DD
 2018-07-28 |   1 | AA
 2018-07-28 |   2 | BB
 2018-07-28 |   2 | CC
 2018-07-28 |   2 | DD
 2018-07-28 |   3 | AA
 2018-07-28 |   3 | BB
(14 筆資料列)

----------------------

insert into ithelp180727_stat values (4,1);
-- 指令一樣
with   st1 as (
select max(matching) mm
  from ithelp180727_stat
), st2 as (
select count(*) as cnt
  from ithelp180727_emp
), st3 as (
select date '2018-07-27' + d as dt
     , sid
  from st1
     , generate_series(1, st1.mm) as i
     , generate_series(1, i) as j
     , generate_series(0, 1) as d
     , ithelp180727_stat s
 where s.matching = i
), st4 as (
select *
     , row_number() over(order by dt, sid) rn
  from st3
), st5 as (
select dt, sid, rn
     , ntile(st2.cnt::integer) over(order by mod(rn::integer - 1, st2.cnt::integer)) as asgnemp
  from st4
     , st2
)
select dt, sid, ename
  from st5
     , ithelp180727_emp e
 where st5.asgnemp = e.id
order by dt, sid, rn
;

     dt     | sid | ename 
------------+-----+-------
 2018-07-27 |   1 | AA
 2018-07-27 |   1 | BB
 2018-07-27 |   2 | CC
 2018-07-27 |   2 | DD
 2018-07-27 |   2 | AA
 2018-07-27 |   3 | BB
 2018-07-27 |   3 | CC
 2018-07-27 |   4 | DD
 2018-07-28 |   1 | AA
 2018-07-28 |   1 | BB
 2018-07-28 |   2 | CC
 2018-07-28 |   2 | DD
 2018-07-28 |   2 | AA
 2018-07-28 |   3 | BB
 2018-07-28 |   3 | CC
 2018-07-28 |   4 | DD
(16 筆資料列)

我要發表回答

立即登入回答