問題描述
拿兩個...
實際數據:
division ID date flag
ABC123 ZZZ123 1/17/2013 Y
ABC123 ZZZ123 1/25/2013 N
ABC123 ZZZ123 2/22/2013 Y
ABC123 ZZZ123 2/26/2013 N
ABC123 YYY222 3/20/2013 Y
ABC123 YYY222 5/17/2013 N
XYZ456 ZZZ999 1/15/2012 N
XYZ456 ZZZ999 1/30/2012 N
XYZ456 ZZZ123 2/09/2012 N
XYZ456 ZZZ123 4/13/2012 Y
XYZ456 ZZZ123 6/23/2012 N
XYZ456 ZZZ123 10/5/2012 Y
XYZ456 ZZZ123 11/18/2012 N
我需要構建一個新列 ORDER_group，它將根據以下規則進行填充:
I need to build a new column, ORDER_group, that will populate based on the following rules:
- 每個部門和 ID 組合都被視為一個組"，按日期排序，并應為其分配一個 ORDER_group(從 1 開始).
- 每次組"遇到標志Y"時，它應該將 ORDER_group 增加 1.
- 如果組"以標志 =N"開始(具有最早日期的第一條記錄)，它仍應以 ORDER_group = 1 開始.
- 如果組"以標志 =Y"開始(具有最早日期的第一條記錄)，它仍應以 ORDER_group = 1 開始.
- 每個后續記錄都應該是相同的 ORDER_group 編號，除非遇到新的組"(部門/ID)，此時，它應該重置回 1，或者遇到下一個標志 =Y".
預期結果:
division ID date flag ORDER_group
ABC123 ZZZ123 1/17/2013 Y 1
ABC123 ZZZ123 1/25/2013 N 1
ABC123 ZZZ123 2/22/2013 Y 2
ABC123 ZZZ123 2/26/2013 N 2
ABC123 YYY222 3/20/2013 Y 1
ABC123 YYY222 5/17/2013 N 1
XYZ456 ZZZ999 1/15/2012 N 1
XYZ456 ZZZ999 1/30/2012 N 1
XYZ456 ZZZ123 2/09/2012 N 1
XYZ456 ZZZ123 4/13/2012 Y 2
XYZ456 ZZZ123 6/23/2012 N 2
XYZ456 ZZZ123 10/5/2012 Y 3
XYZ456 ZZZ123 11/18/2012 N 3
理想情況下，這應該在沒有循環/游標的情況下完成，除非有 CTE/臨時表的性能原因.填充此新列的最佳方法是什么?
Ideally this should be accomplished without a loop/cursor, unless there are performance reasons with CTE/temp tables. What is the best way to populate this new column?
任何幫助將不勝感激.
實際數據的 SQL Fiddler:http://sqlfiddle.com/#!3/5cca0/2
SQL Fiddler for Actual data: http://sqlfiddle.com/#!3/5cca0/2
推薦答案
所以這里有一個方法.它基于 How do I在不使用游標的情況下在 SQL 中計算運行總數? 這確實有一些缺陷.我在建議中使用索引，它可以使排序解決盡管更新順序無法保證的事實.
So here a way to do it. It based on How do I calculate a running total in SQL without using a cursor? which does have some flaws. I'm using an index on the advice that it makes the ordering work out DESPITE the fact that order on the update is not guaranteed.
另外值得指出的是計算運行總額/運行余額 用于 Aaron Bertrand 治療.
And it also worth pointing you to Calculate running total / running balance for Aaron Bertrand treatment.
這里可能很聰明的一點是將 Y/N 轉換為 1/0 以用于計算.
The possibly clever bit here is the conversion of Y/N to 1/0 for use in calculating.
CREATE TABLE Orders (division CHAR(6),ID CHAR(6),dat DATETIME, flag CHAR(1))
INSERT INTO Orders VALUES
('ABC123','ZZZ123','01/17/2013','Y')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','02/22/2013','Y')
,('ABC123','ZZZ123','02/26/2013','N')
,('ABC123','YYY222','03/20/2013','Y')
,('ABC123','YYY222','05/17/2013','N')
,('XYZ456','ZZZ999','01/15/2012','N')
,('XYZ456','ZZZ999','01/30/2012','N')
,('XYZ456','ZZZ123','02/09/2012','N')
,('XYZ456','ZZZ123','04/13/2012','Y')
,('XYZ456','ZZZ123','06/23/2012','N')
,('XYZ456','ZZZ123','010/5/2012','Y')
,('XYZ456','ZZZ123','11/18/2012','N')
CREATE TABLE #Orders (division CHAR(6), ID CHAR(6), dat DATETIME, flag CHAR(1),flag_int INTEGER, rn BIGINT, OrderGroup INT)
CREATE CLUSTERED INDEX IDX_C_Temp_Order ON #Orders(division, id,rn)
INSERT INTO #Orders (division, id,dat,flag,flag_int,rn,OrderGroup)
SELECT division
,ID
,dat
,flag
,CASE flag WHEN 'y' THEN 1 ELSE 0 END flag_int
,ROW_NUMBER() OVER (PARTITION BY division, id ORDER BY dat) rn
,0 OrderGroup
FROM Orders
DECLARE @OrderGroup INT = 0
UPDATE #Orders
SET @OrderGroup = OrderGroup = CASE WHEN rn = 1 THEN 1 ELSE @OrderGroup + flag_int END
FROM #Orders
SELECT *
FROM #Orders
ORDER BY division
,ID
,rn
DROP TABLE #Orders
這篇關于根據標志對計數遞增的記錄進行分組的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！