寻找“TOTIME”通过聚集取而代之的是加入
我想和大家分享一个真正的野生查询只需要1个逻辑读取1个扫描表。 相比之下，页面，西蒙金士顿的查询上最好的对方的回答，需要2次扫描。
在一个非常大的数据集（17,408输入行，产生8,193结果行），它需要CPU 574和时间2645，而西蒙金士顿的查询需要CPU 63820和时间37108。
这有可能是使用索引的其他查询页面上可以执行许多倍的，而只是通过重写查询，以达到111X CPU改进和14X速度的提高是对我有意思。
（请注意：我的意思是不尊重可言西蒙金士顿或其他任何人，我只是高兴我的想法对这个查询平移这么好了他的问题比我为它的性能足够好，它实际上是理解和维护。不像我的。）
这里是不可能的查询。 这是很难理解的。 这是很难写。 但它是真棒。 :)
WITH Ranks AS (
SELECT
T = Dense_Rank() OVER (ORDER BY Time, Num),
N = Dense_Rank() OVER (PARTITION BY Name ORDER BY Time, Num),
*
FROM
#Data D
CROSS JOIN (
VALUES (1), (2)
) X (Num)
), Items AS (
SELECT
FromTime = Min(Time),
ToTime = Max(Time),
Name = IsNull(Min(CASE WHEN Num = 2 THEN Name END), Min(Name)),
I = IsNull(Min(CASE WHEN Num = 2 THEN T - N END), Min(T - N)),
MinNum = Min(Num)
FROM
Ranks
GROUP BY
T / 2
)
SELECT
FromTime = Min(FromTime),
ToTime = CASE WHEN MinNum = 2 THEN NULL ELSE Max(ToTime) END,
Name
FROM Items
GROUP BY
I, Name, MinNum
ORDER BY
FromTime
注意：这需要SQL 2008或向上。 为了在2005年SQL工作，改变VALUES子句SELECT 1 UNION ALL SELECT 2 。
更新查询
想着这一点后，我意识到，我是在同一时间完成两个不同的逻辑任务，这使查询不必要地复杂化：1）修剪指出，对最终的解决方案没有影响中间行（行不开始一个新的任务）和2）从下一行拉“TOTIME”值。 通过＃2 之前执行＃1，查询更简单，与大约一半的CPU执行！
因此，这里是简化查询第一，修剪出我们不关心行， 然后得到使用集合而非JOIN的TOTIME值。 是的，它有3层窗口的功能，而不是2，但由于较少的行的最终（修剪后，那些我们不关心），它具有较少的工作要做：
WITH Ranks AS (
SELECT
Grp =
Row_Number() OVER (ORDER BY Time)
- Row_Number() OVER (PARTITION BY Name ORDER BY Time),
[Time], Name
FROM #Data D
), Ranges AS (
SELECT
Result = Row_Number() OVER (ORDER BY Min(R.[Time]), X.Num) / 2,
[Time] = Min(R.[Time]),
R.Name, X.Num
FROM
Ranks R
CROSS JOIN (VALUES (1), (2)) X (Num)
GROUP BY
R.Name, R.Grp, X.Num
)
SELECT
FromTime = Min([Time]),
ToTime = CASE WHEN Count(*) = 1 THEN NULL ELSE Max([Time]) END,
Name = IsNull(Min(CASE WHEN Num = 2 THEN Name ELSE NULL END), Min(Name))
FROM Ranges R
WHERE Result > 0
GROUP BY Result
ORDER BY FromTime;
此更新的查询具有所有的相同的问题我在解释提出的，但是，他们更容易解决，因为我不处理额外的不必要的行。 我还看到， Row_Number() / 2的值为0，我不得不排除，我不知道为什么我没有从先前查询中排除，但在任何情况下，这个完美的作品，是惊人的快！
外应用收拾东西最多
最后，这里是一个版本基本相同，西蒙金士顿的查询，我认为是比较容易理解的语法。
SELECT
FromTime = Min(D.Time),
X.ToTime,
D.Name
FROM
#Data D
OUTER APPLY (
SELECT TOP 1 ToTime = D2.[Time]
FROM #Data D2
WHERE
D.[Time] < D2.[Time]
AND D.[Name] <> D2.[Name]
ORDER BY D2.[Time]
) X
GROUP BY
X.ToTime,
D.Name
ORDER BY
FromTime;
下面是安装脚本，如果你想要做一个更大的数据集性能对比：
CREATE TABLE #Data (
RecordId int,
[Time] int,
Name varchar(10)
);
INSERT #Data VALUES
(1, 10, 'Running'),
(2, 18, 'Running'),
(3, 21, 'Running'),
(4, 29, 'Walking'),
(5, 33, 'Walking'),
(6, 57, 'Running'),
(7, 66, 'Running'),
(8, 77, 'Running'),
(9, 81, 'Walking'),
(10, 89, 'Running'),
(11, 93, 'Walking'),
(12, 99, 'Running'),
(13, 107, 'Running'),
(14, 113, 'Walking'),
(15, 124, 'Walking'),
(16, 155, 'Walking'),
(17, 178, 'Running');
GO
insert #data select recordid + (select max(recordid) from #data), time + (select max(time) +25 from #data), name from #data
GO 10
说明
这里是我的查询背后的基本理念。
表示开关的时间必须出现在两个相邻的行，一个结束现有活性，和一个开始下一个活动。 自然的解决方案，这是一个加盟，使输出的行可以从自己的行（为开始时间）和下一更改的行（为结束时间）拉。
然而，我的查询完成需要通过重复连续两次做出结束时间出现在两个不同行，与CROSS JOIN (VALUES (1), (2)) 现在，我们有我们的所有行复制。 我们的想法是，与其使用JOIN做跨列计算，我们将使用某种形式的聚集崩塌每个需要对行成一个。
接下来的任务就是使每一个重复的行拆分正确，这样一个实例出现与现有对和一个与下一对。 这与T形柱，完成ROW_NUMBER()通过有序的Time ，然后除以2（虽然我改变了它做对称性的DENSE_RANK（）在这种情况返回相同的值ROW_NUMBER）。 为了提高效率我在下一步骤中进行的分割，以使行数可在其它计算（保持读出）进行再利用。 由于行号从1开始，并除以2隐式转换为int，这具有产生该序列的效果0 1 1 2 2 3 3 4 4 ...具有所期望的结果：由该计算值进行分组，由于我们还下令Num中的行数，我们现在已经完成了第一个毕竟集从“前”行由民= 2，而从“下一个”排民= 1。
下一个艰巨的任务是找出一种方法来消除我们不关心行，不知何故折叠块的起始时间到同一行作为一个块的结束时间。 我们要的是一个办法让每个离散集合跑步或步行的给予自己的号码，以便我们可以通过它组。 DENSE_RANK()是一个自然的解决方案，但问题是，它注重每个值在ORDER BY子句中-我们没有语法做DENSE_RANK() OVER (PREORDER BY Time ORDER BY Name)从而使Time不会引起RANK除了在每个变化计算改变Name 。 经过一番思考，我意识到我可能婴儿床从背后的逻辑有点伊茨克奔甘的分组岛屿的解决方案 ，我想通了，通过命令行的排名Time ，由分割行的排名减去Name ，并下令由Time ，将产生，这是在同一组中的每个行相同，但来自其他组的不同的值。 通用分组岛屿技术是创建两个在锁步与行如提升两个计算的值4 5 6和1 2 3 ，当减去将产生相同的值（在本例情况下， 3 3 3作为结果4 - 1 ， 5 - 2 ，和6 - 3 ）。 注：我最初开始与ROW_NUMBER()我N计算，但它不工作。 正确答案是DENSE_RANK()虽然我很抱歉地说，我不记得我为什么结束了本的时候，我将不得不再次潜水弄明白。 但无论如何，这就是TN计算：可分为上隔离每个之一的地位（无论是跑步或步行）“岛”的数字。
但是，这不是结束，因为有一些皱纹。 首先，“下一个”行各组中包含了不正确的值Name ， N和T 。 我们解决这个问题的选择，从每一组中，从价值Num = 2时，它存在的行（但如果没有，那么我们使用剩余价值）。 这产生了这样的表达式CASE WHEN NUM = 2 THEN x END ：这将正确地淘汰不正确的“下一个”行值。
一些实验后，我意识到，这是不够的，按T - N本身，因为无论是散步组和运行组可以有相同的计算值（以提供高达17我的样本数据的情况下，有2 T - N的6个值）。 但是，简单地通过分组Name以及解决了这个问题。 没有组或者“运行”或“行走”的将具有相同数目的从相反类型居间值。 也就是说，由于第一组的“运行”启动时，并且存在两个“行走”的行的下一个“运行”组之前介入，则N的值将超过该值2小于T在该下一“运行”组。 我刚刚意识到思考的方式之一就是T - N计算计算行数之前的当前行那些不属于相同的值“运行”或“走”。 一些人认为会证明这是正确的：如果我们进入到第三个“运行”组，只有通过具有一个“走”组将它们分开的第三组，因此具有不同数量在未来介入的行在它之前，并且由于它起始于一个较高的位置，这是足够高，使得该值不能被复制。
最后，由于我们的最后一组只由一排（没有终点的时刻，我们需要显示NULL代替），我在这可以用来确定我们是否有一个结束时间或不计算扔。 这是通过完成Min(Num)表达，然后最终检测时的最小值（NUM）为2（意味着我们没有“下一个”行），则显示一个NULL的而不是Max(ToTime)值。
我希望这个解释是有用处的人。 我不知道如果我的“行倍增”技术将是通常有用，适用于因难以生产环境中的大多数SQL查询的作家了解它，并维护它肯定会呈现给下一个人参观的难度代码（反应可能是“地球上它正在做什么！？！”随后又迅速“时间重写！”）。
如果你做了这么远那么我感谢你的时间和我沉迷在我的小游到令人难以置信的乐趣-SQL益智土地。
亲身感受一下
阿卡模拟“PREORDER经”：
最后一个音符。 要了解如何T - N做这项工作-并指出，用我的方法，这部分可能不是一般适用于SQL社区-运行针对第17行中的样本数据的以下查询：
WITH Ranks AS (
SELECT
T = Dense_Rank() OVER (ORDER BY Time),
N = Dense_Rank() OVER (PARTITION BY Name ORDER BY Time),
*
FROM
#Data D
)
SELECT
*,
T - N
FROM Ranks
ORDER BY
[Time];
这产生了：
RecordId Time Name T N T - N
----------- ---- ---------- ---- ---- -----
1 10 Running 1 1 0
2 18 Running 2 2 0
3 21 Running 3 3 0
4 29 Walking 4 1 3
5 33 Walking 5 2 3
6 57 Running 6 4 2
7 66 Running 7 5 2
8 77 Running 8 6 2
9 81 Walking 9 3 6
10 89 Running 10 7 3
11 93 Walking 11 4 7
12 99 Running 12 8 4
13 107 Running 13 9 4
14 113 Walking 14 5 9
15 124 Walking 15 6 9
16 155 Walking 16 7 9
17 178 Running 17 10 7
最重要的部分是，每个基团“行走”或“运行”的具有相同值的T - N是从具有相同名称的任何其它基团不同。
性能
我不想痛打我的查询比别人的快一点。 然而，考虑如何惊人的差异（当没有索引）我想在一个表格式来显示数字。 这是需要这种行到行相关的高性能，当一个很好的技术。
每个查询运行之前，我使用DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; 。 我设置MAXDOP 1为每个查询删除并行的时间崩溃的影响。 我所选择的每个结果集到的变量，而不是将其返回到客户端，以便测量仅性能而不是客户端的数据传输。 所有查询都给予相同的ORDER BY子句。 所有测试都使用17,408输入行产生8,193结果行。
未显示结果为下面的人/原因：
RichardTheKiwi *Could not test--query needs updating*
ypercube *No SQL 2012 environment yet :)*
Tim S *Did not complete tests within 5 minutes*
由于没有指标：
CPU Duration Reads Writes
----------- ----------- ----------- -----------
ErikE 344 344 99 0
Simon Kingston 68672 69582 549203 49
随着指数CREATE UNIQUE CLUSTERED INDEX CI_#Data ON #Data (Time); ：
CPU Duration Reads Writes
----------- ----------- ----------- -----------
ErikE 328 336 99 0
Simon Kingston 70391 71291 549203 49 * basically not worse
随着指数CREATE UNIQUE CLUSTERED INDEX CI_#Data ON #Data (Time, Name); ：
CPU Duration Reads Writes
----------- ----------- ----------- -----------
ErikE 375 414 359 0 * IO WINNER
Simon Kingston 172 189 38273 0 * CPU WINNER
所以这个故事的寓意是：
适当指标均优于查询巫术更重要
有了适当的指数，西蒙金士顿的版本整体胜出，尤其是包括查询的复杂性/可维护性时。
注意这个很好的教训！ 38K读是不是真的那么多，和西蒙金士顿的版本中一半的时间作为矿山跑。 我查询的速度增加完全是由于那里是在表上没有索引，这给了任何查询需要联接伴随灾难性的成本（其中矿没有）到：全表扫描哈希匹配杀害它的性能。 与索引，查询自己能够做一个嵌套循环与使事情真快一个聚集索引查找（又名书签查找）。
有趣的是，在一个时间聚集索引单单是不够的。 虽然时代是独一无二的，这意味着每一次只发生一个名称，它仍然需要名字，以便正确地使用它索引的一部分。
添加聚集索引表时，完整的数据拿了不到1秒钟！ 不要忽视你的索引。
这不会在SQL Server 2008中的工作，只有在SQL Server 2012版本有LAG()和LEAD()解析函数 ，但我会离开这里与较新版本的人：
SELECT Time AS FromTime
, LEAD(Time) OVER (ORDER BY Time) AS ToTime
, Name
FROM
( SELECT Time
, LAG(Name) OVER (ORDER BY Time) AS PreviousName
, Name
FROM Data
) AS tmp
WHERE PreviousName <> Name
OR PreviousName IS NULL ;
经测试，在SQL-小提琴
随着指数(Time, Name)这将需要一个索引扫描。
编辑：
如果NULL是一个有效的值Name ，需要被视为一个有效的条目，请使用以下WHERE子句：
WHERE PreviousName <> Name
OR (PreviousName IS NULL AND Name IS NOT NULL)
OR (PreviousName IS NOT NULL AND Name IS NULL) ;
我认为你是在实质上有意在“名称”的变化从一个记录到下一个（在“时间”的顺序）。 如果你能找出哪里发生这种情况，你可以生成你想要的输出。
既然你提到的CTE我会假设你的SQL Server 2005+上，因此可以使用ROW_NUMBER()函数。 您可以使用ROW_NUMBER()作为一种方便的方法来识别连续配对记录，然后找到那些“名称”的变化。
这个怎么样：
WITH OrderedTable AS
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY Time) AS Ordinal
FROM
[YourTable]
),
NameChange AS
(
SELECT
after.Time AS Time,
after.Name AS Name,
ROW_NUMBER() OVER (ORDER BY after.Time) AS Ordinal
FROM
OrderedTable before
RIGHT JOIN OrderedTable after ON after.Ordinal = before.Ordinal + 1
WHERE
ISNULL(before.Name, '') <> after.Name
)
SELECT
before.Time AS FromTime,
after.Time AS ToTime,
before.Name
FROM
NameChange before
LEFT JOIN NameChange after ON after.Ordinal = before.Ordinal + 1
我假设RecordIDs并不总是连续的，因此CTE来创建一个非打破顺序号。
SQLFiddle
;with SequentiallyNumbered as (
select *, N = row_number() over (order by RecordId)
from Data)
, Tmp as (
select A.*, RN=row_number() over (order by A.Time)
from SequentiallyNumbered A
left join SequentiallyNumbered B on B.N = A.N-1 and A.name = B.name
where B.name is null)
select A.Time FromTime, B.Time ToTime, A.Name
from Tmp A
left join Tmp B on B.RN = A.RN + 1;
该数据集我用来测试
create table Data (
RecordId int,
Time int,
Name varchar(10));
insert Data values
(1 ,10 ,'Running'),
(2 ,18 ,'Running'),
(3 ,21 ,'Running'),
(4 ,29 ,'Walking'),
(5 ,33 ,'Walking'),
(6 ,57 ,'Running'),
(7 ,66 ,'Running');
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 走好不送 的文章《如何检测和行值之间的绑定变化在SQL表？(How Can I Detect and Bound Ch》','https://www.manongdao.com/article-987592.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}