[SQL]依項目 + 時間來分群(SQL Islands)

2014-09-26

4019
0
SQL

要依項目 + 時間來分群(SQL Islands)

在網路上看到「生產排程分類問題」，就是要依項目 + 時間來分群，

當下個項目不同時，就要成為一組，如下所示，

測試Script如下，

USE tempdb
GO

--DROP TABLE tblA;
--GO

CREATE TABLE tblA
(
ProcTime TIME
, ItemName VARCHAR(10)
, Qty INT
);
GO

INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('08:10', 'A', 1);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('08:11', 'A', 2);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('08:50', 'B', 3);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('08:51', 'B', 4);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('09:52', 'B', 5);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('10:01', 'C', 6);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('11:10', 'A', 3);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('12:11', 'A', 2);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('13:51', 'B', 3);
INSERT INTO tblA(ProcTime, ItemName, Qty) VALUES('14:52', 'B', 5);
GO
SELECT * FROM tblA;

這種依時間來分群的方式，可以透過2個 ROW_NUMBER() 比較取得差異值，就可以將它們分群了。如下，

WITH cte_temp
AS
(
SELECT  ItemName ,
        ProcTime ,
		Qty,
		ROW_NUMBER() OVER (ORDER BY ProcTime) 
			- ROW_NUMBER() OVER (PARTITION BY ItemName ORDER BY ProcTime) AS Grp
		, ROW_NUMBER() OVER (ORDER BY ProcTime) as seq1
		, ROW_NUMBER() OVER (PARTITION BY ItemName ORDER BY ProcTime) AS seq2
		
FROM    tblA
)
SELECT *
FROM cte_temp;

所以可以透過 ItemName + Grp 來分群，如下，

WITH cte_temp
AS
(
SELECT  ItemName ,
        ProcTime ,
		Qty,
		ROW_NUMBER() OVER (ORDER BY ProcTime) 
			- ROW_NUMBER() OVER (PARTITION BY ItemName ORDER BY ProcTime) AS Grp
FROM    tblA
)
SELECT ItemName, MIN(ProcTime) AS StartTime,  MAX(ProcTime) AS EndTime
, SUM(Qty) AS SumOfQty
FROM cte_temp
GROUP BY ItemName, Grp
ORDER BY StartTime;