计数连续的行 - SQL Server 2008
我需要根据其他列中的值计算连续的行。计数连续的行 - SQL Server 2008
当谈到SQL时,我完全是新手 - 所以问一下如果有什么不清楚的地方。
我有SQL Server 2008,因此LEAD()
和LAG()
不起作用,与基于分区的窗口大小的OVER()相同。
实施例的数据集:
SLOT Blocked
-------------------------------
2015-02-09 07:00:00.000 0
2015-02-09 07:15:00.000 0
2015-02-09 07:30:00.000 0
2015-02-09 07:45:00.000 1
2015-02-09 08:00:00.000 1
2015-02-09 08:15:00.000 1
2015-02-09 08:30:00.000 0
2015-02-09 08:45:00.000 0
2015-02-09 09:00:00.000 0
2015-02-09 09:15:00.000 0
2015-02-09 09:30:00.000 1
2015-02-09 09:45:00.000 1
2015-02-09 10:00:00.000 1
2015-02-09 10:15:00.000 1
2015-02-09 10:30:00.000 0
2015-02-09 10:45:00.000 0
2015-02-09 11:00:00.000 0
2015-02-09 11:15:00.000 0
2015-02-09 11:30:00.000 0
2015-02-09 11:45:00.000 0
2015-02-09 12:00:00.000 0
的数据由SLOT
排序。我想对分区where Blocked = 0
上的行进行计数。
的结果集我想获得:
SLOT Blocked RowNum
-------------------------------------------
2015-02-09 07:00:00.000 0 1
2015-02-09 07:15:00.000 0 2
2015-02-09 07:30:00.000 0 3
2015-02-09 07:45:00.000 1 0
2015-02-09 08:00:00.000 1 0
2015-02-09 08:15:00.000 1 0
2015-02-09 08:30:00.000 0 1
2015-02-09 08:45:00.000 0 2
2015-02-09 09:00:00.000 0 3
2015-02-09 09:15:00.000 0 4
2015-02-09 09:30:00.000 1 0
2015-02-09 09:45:00.000 1 0
2015-02-09 10:00:00.000 1 0
2015-02-09 10:15:00.000 1 0
2015-02-09 10:30:00.000 0 1
2015-02-09 10:45:00.000 0 2
2015-02-09 11:00:00.000 0 3
2015-02-09 11:15:00.000 0 4
2015-02-09 11:30:00.000 0 5
2015-02-09 11:45:00.000 0 6
2015-02-09 12:00:00.000 0 7
提前感谢!
对于SQL Server 2008,你可以解决它像这样
SELECT Slot
,Blocked
,CASE WHEN Blocked = 0
THEN ROW_NUMBER() OVER
(PARTITION BY rt.RunningTotal ORDER BY Slot)
ELSE 0 END RowNum
FROM Table1 t
CROSS APPLY (SELECT ISNULL(SUM(Blocked), 0) RunningTotal
FROM Table1
WHERE Slot < t.Slot
) rt
ORDER BY Slot
SQL小提琴(与你的表名称替换表1):http://sqlfiddle.com/#!3/659a0/28
似乎你的排名在前3排中排名不合格。在你的小提琴中相同 – 2015-02-11 11:27:34
由于我的编辑建议被拒绝(???)我把它写成注释:你必须使用'SELECT ISNULL(SUM(Blocked),0)RunningTotal'来避免由第一行引起的自己的Partition缺少前面的行,因此RunningTotal为null。 – flo 2015-02-11 11:28:20
@flo,似乎有人拒绝了你所做的编辑建议。我现在编辑了,这解决了这个问题。 – 2015-02-11 11:39:20
由于您使用SQLSERVER 2008年,你需要使用row_number()结束。 计算有点复杂,但这个脚本会从2005年的sqlserver
;WITH CTE AS
(
SELECT SLOT, row_number() over (order by SLOT)-
row_number() over (partition by blocked order by SLOT) x, blocked
FROM
-- replace these lines with your real table
--start test tabel
(values
(cast('2015-02-09 07:00:00.000' as datetime), 0)
,('2015-02-09 07:15:00.000', 0),('2015-02-09 07:30:00.000', 0)
,('2015-02-09 07:45:00.000', 1),('2015-02-09 08:00:00.000', 1)
,('2015-02-09 08:15:00.000', 1),('2015-02-09 08:30:00.000', 0)
,('2015-02-09 08:45:00.000', 0),('2015-02-09 09:00:00.000', 0)
,('2015-02-09 09:15:00.000', 0),('2015-02-09 09:30:00.000', 1)
,('2015-02-09 09:45:00.000', 1),('2015-02-09 10:00:00.000', 1)
,('2015-02-09 10:15:00.000', 1),('2015-02-09 10:30:00.000', 0)
,('2015-02-09 10:45:00.000', 0),('2015-02-09 11:00:00.000', 0)
,('2015-02-09 11:15:00.000', 0),('2015-02-09 11:30:00.000', 0)
,('2015-02-09 11:45:00.000', 0),('2015-02-09 12:00:00.000', 0)) x(SLOT,Blocked)
-- end test tabel
)
SELECT
SLOT,
BLOCKED,
CASE WHEN blocked = 0
THEN
row_number() over (partition by x, blocked order by slot)
ELSE 0 end ROWNUMBER
FROM cte
ORDER BY slot
工作
有趣的问题.. !!
我能够使用这个代码如下实现:
CREATE TABLE #TestTable(SLOT DATETIME,BLOCKED INT);
INSERT #TestTable(SLOT,BLOCKED)
VALUES
('2015-02-09 07:00:00.000' ,0),
('2015-02-09 07:15:00.000' ,0),
('2015-02-09 07:30:00.000' ,0),
('2015-02-09 07:45:00.000' ,1),
('2015-02-09 08:00:00.000' ,1),
('2015-02-09 08:15:00.000' ,1),
('2015-02-09 08:30:00.000' ,0),
('2015-02-09 08:45:00.000' ,0),
('2015-02-09 09:00:00.000' ,0),
('2015-02-09 09:15:00.000' ,0),
('2015-02-09 09:30:00.000' ,1),
('2015-02-09 09:45:00.000' ,1),
('2015-02-09 10:00:00.000' ,1),
('2015-02-09 10:15:00.000' ,1),
('2015-02-09 10:30:00.000' ,0),
('2015-02-09 10:45:00.000' ,0),
('2015-02-09 11:00:00.000' ,0),
('2015-02-09 11:15:00.000' ,0),
('2015-02-09 11:30:00.000' ,0),
('2015-02-09 11:45:00.000' ,0),
('2015-02-09 12:00:00.000' ,0)
SELECT *
FROM #TestTable
ORDER BY SLOT
;WITH TestTableVw AS
(SELECT SLOT,BLOCKED,ROW_NUMBER() OVER(ORDER BY SLOT) RNum
FROM #TestTable
)
SELECT T.SLOT,T.BLOCKED,CASE WHEN T.BLOCKED=0 THEN V1.RNum-ISNULL((SELECT MAX(V2.RNum) FROM TestTableVw V2 WHERE V2.RNum<V1.RNum AND V2.BLOCKED=1),0) ELSE 0 END
FROM #TestTable T
JOIN TestTableVw V1 ON T.SLOT=V1.SLOT
DROP TABLE #TestTable;
我希望这将有助于.. !!
谢谢,
Swapnil
@TotallyNewb:检查我提供的代码是否适合您。我已使用您提供的示例数据对此进行了测试。该代码生成问题中给出的预期输出。 – Swapnil 2015-02-11 12:03:53
只是像这种情况下的情况下,继续进行条件遮光时= 0那么ROW_NUMBER()OVER(PARTITION BY阻止ORDER BY*)ELSE 0 END – mohan111 2015-02-11 09:17:19
可能是我没有澄清足够 - 在“阻塞”列中找到1后,计数器必须重新启动,如预期结果示例中所示。也许我的terribad英语是问题。 – TotallyNewb 2015-02-11 09:42:07
数据中是否会有空白? – 2015-02-11 09:47:01