需要QUOT&;&合并QUOT;在查询记录块成一体，这取决于其他查询列 [英] Need to "merge" blocks of records in query into one, depending on other query columns
本文介绍了需要QUOT&;&合并QUOT;在查询记录块成一体，这取决于其他查询列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下的DB计划。
CREATE TABLE Twix.dbo.Sensors
（ID INT PRIMARY KEY NOT NULL IDENTITY（1,1），
位置为nvarchar（260）NOT NULL）;
CREATE TABLE TWIX .dbo.Visitors
（ID INT PRIMARY KEY NOT NULL IDENTITY（1,1），
名称为nvarchar（260）NOT NULL）;
CREATE TABLE Twix.dbo.Visits（ID INT PRIMARY KEY NOT NULL IDENTITY（1,1），
SensorId INT约束FK__Visits__Sensor__239E外键引用传感器（ID），
VisitorId INT约束FK__Visits__Visitors__4DCF外键引用游客（ID），
银泰日期时间NOT NULL，
OutTime日期时间NOT NULL）;
在访问表银泰-OutTime期间可以相交。即真正的访问部分。例如，
选择VisitorId，银泰，OutTime FROM访问ORDER BY VisitorId，银泰
1 2011-02-09 15：26：59.173 2011-02-09 15：29：22.097
1 2011-02-09 15点28分： 40.530 2011-02-09 15：29：52.737
1分配2011-02-09 15：30：10.577 2011-02-09 16：17：58.967
1分配2011-02-09 19时09： 23.057 2011-02-09 19：10：57.493
1 2011-02-09 19：12：16.087 2011-02-09 19：13：27.493
1 2011-02-14 15:08： 46.333 2011-02-14 15：26：42.433
2 2011-02-09 17：55：42.390 2011-02-09 18：52：03.780
2 2011-02-09 18:10： 56.727 2011-02-09 18：11：57.493
2 2011-02-09 21：47：15.650 2011-02-09 21：48：38.783
2 2011-02-09 21:50： 18.337 2011-02-09 21：55：26.777
3 2011-02-09 17：12：58.103 2011-02-09 19：51：59.697
3 2011-02-09 22:27： 52.073 2011-02-09 23：03：24.753
3 2011-02-09 23：02：51.177 2011-02-10 09：51：14.890
3 2011-02-14 15点27分： 42.270 2011-02-14 15：42：31.107
3 2011-02-14 15：43：37.320 2011-02-14 18：45：26.163
4 2011-02-09 21点07分： 51.030 2011-02-09 21：51：02.880
4 2011-02-09 22：42：52.660 2011-02-09 23：21：13.830
4 2011-02-09 23:23： 08.563 2011-02-09 23：35：12.847
4 2011-02-09 23：36：05.120 2011-02-09 23：59：02.813
4 2011-02-10 6点差2分： 44.103 2011-02-10 05：59：55.867
4 2011-02-12 08：29：36.620 2011-02-12 09：51：18.510
4 2011-02-12 13点13分： 42.650 2011-02-12 14：06：01.473
5 2011-02-10 06：48：52.717 2011-02-10 07：37：04.870
5 2011-02-10 06:50： 31.067 2011-02-10 06：52：20.877
5 2011-02-10 06：52：36.273 2011-02-10 06：53：36.523
5 2011-02-10 06:59： 11.790 2011-02-10 07：00：34.867
5 2011-02-10 08：36：39.563 2011-02-10 08：46：14.760
5 2011-02-10 12:47： 05.567 2011-02-10 12：48：05.860
5 2011-02-10 12：49：19.590 2011-02-10 13：09：27.880
5 2011-02-10 12时49分： 25.733 2011-02-10 12：59：59.883
5 2011-02-10 12：55：23.460 2011-02-10 12：56：23.507
我需要得到已完成访问，即合并所有访问混凝土位访客，如果访问是相交在它们之间，或者时间差小于10分钟（即visit2的银泰 - visit1的OutTime< 10分钟）
在C＃中，它看起来像：
私人IEnumerable的合并（IEnumerable的访问，UINT holeInterval）
{
变种VLIST =新的LinkedList<参观>（参观。排序依据（O => o.InTime））;
VAR的结果=新的List<&参观GT;（）;
，而（vlist.Count→1）
{
访问A = vlist.First.Value; vlist.RemoveFirst（）;
访问B = vlist.First.Value; vlist.RemoveFirst（）;
变种R = Visit.Merge（A，B，holeInterval）; //合并两个访问
如果（R！= NULL）{vlist.AddFirst（R）; }
，否则{result.Add（一）; vlist.AddFirst（二）; }
}
result.Add（vlist.First.Value）;
返回结果;
}
公开的IEnumerable<&参观GT; ListCompleteVisits（）
{
VAR的结果=新的List<&参观GT;（）;
变种QueryResult中从this.repository.ListVisits次数（）
组互访visits.Visitor.Id到vgroup
选择合并（vgroup，this.holeInterval）=;
的foreach（在QueryResult中VAR V）
{
result.AddRange（V）;
}
返回结果;
}
我试过了，并得到了以下内容：
DECLARE @holeInterval INT
设置@holeInterval = 10
SELECT t.RowNumber，t.VisitorId，t.InTime，t.OutTime，t.BInMinusAOut，
（SELECT MIN（银泰）FROM访问AS D
，其中VisitorId NOT IN（从访问组由VisitorId有COUNT（*选择VisitorId）= 1）
和D.InTime< = t.InTime和D.VisitorId = t.VisitorId
和t.RowNumber> / *这里应该是最大ROWNUMBER比holeinterval * /
）作为MinInTime
起价
大（选择
ROW_NUMBER（）OVER（ORDER BY VisitorId，银泰ASC）AS ROWNUMBER，
VisitorId，银泰，OutTime，
DATEDIFF（MI，银泰，
（SELECT MIN（银泰）FROM访问为b
，其中B.InTime> A.InTime和A.VisitorId = B. VisitorId
）项）BInMinusAIn，
DATEDIFF（MI，OutTime，
（SELECT MIN（银泰）从人员往来b
，其中B.InTime> A.InTime和A.VisitorId = B. VisitorId
）））AS BInMinusAOut
从人员往来一个
，其中VisitorId NOT IN（由VisitorId有COUNT（从访问组中选择VisitorId *）= 1）
）T
/ * WHERE t.BInMinusAOut> @holeInterval或t.BInMinusAOut IS NULL * /
ORDER BY VisitorId，银泰
1 1 2011-02-09 15：26：59.173 2011-02-09 15：28：22.097 0
2分配1 2011-02-09 15：28：40.530 2011-02-09 15：29：52.737 1
3分配1 2011-02-09 15：30：10.577 2011-02-09 16： 17：58.967 9
4 1 16 2011-02-09：26：44.810 2011-02-09 16：51：46.423 20
5 1 2011-02-09 17：11：57.633 2011-02 -09 17：13：20.680 2
6 1 2011-02-09 17：15：35.727 2011-02-09 17：18：48.493 -2
7 1 2011-02-09 17:16 ：12.230 2011-02-09 17：42：47.867 3
8 1 2011-02-09 17：45：43.793 2011-02-09 17：52：10.860 3
9 1 2011-02- 09 17：55：31.127 2011-02-09 20：13：22.743 -109
10 1 18 2011-02-09：24：00.427 2011-02-09 18：32：12.033 2
11 1 2011-02-09 18：34：15.877 2011-02-09 18：37：19.770 2
12 1 18 2011-02-09：39：46.440 2011-02-09 18：48：16.800 $ 2 b $ b 13分配1 2011-02-09 18：50：59.270 2011-02-09 20：03：47.550 -54
14 1 19 2011-02-09：09：23.057 2011-02-09 19： 10：57.493 2
15 1 19 2011-02-09：12：16.087 2011-02-09 19：13：27.493 48
现在我要合并1,2,3,4成2011-02-09 15： 26：59.173 - 2011-02-09 16：51：46.423
5-15H为2011-02-09 17：11：57.633 - 2011-02-09 20：13：22.743
这意味着，我不得不采取分银泰行，这是当前和过去的，其中MinInTime> @holeInterval，和最大OutTime对于那些过于范围之间。
即结果是：
1 2011-02-09 15：26：59.173 2011-02-09 16：51：46.423
1分配2011-02-09 17：11：57.633 2011-02-09 20：13：22.743
感谢的。
解决方案
; WITH
Visits_tuned AS（
/ *添加了一些辅助列* /
选择
VisitorId，
银泰，
OutTime，
OutTimeDelayed = DATEADD（分钟，10，OutTime），
的rownum = ROW_NUMBER（）OVER（PARTITION BY VisitorId
ORDER BY银泰，OutTime），
VisitId = ROW_NUMBER （）OVER（ORDER BY VisitorId，银泰，OutTime）
FROM访问
）
Visits_starts AS（
/ *察觉的起点'合并'参观* /
SELECT DISTINCT
v1.VisitId，
ISSTART = 1 - CASE WHEN v2.VisitId为null，则0 ELSE 1 END
FROM Visits_tuned V1
LEFT JOIN Visits_tuned V2
关于v1.InTime之间v2.InTime和v2.OutTimeDelayed
和v1.VisitorId = v2.VisitorId和v1.rownum<> v2.rownum
）中，
Visits_rec AS（
/ *基本上，选择所述原始数据，而是用
。通过起始InTimes * / $ B取代银泰值$ b选择
VisitId，
VisitorId，
银泰，
OutTime
FROM Visits_tuned
其中VisitId = 1
UNION ALL
选择
v.VisitId，
v.VisitorId，
IntTime = CASE
当v.VisitorId = r.VisitorId和
小号.IsStart = 0和r.InTime< v.InTime
，则r.InTime
，否则v.InTime
端，
v.OutTime
FROM Visits_tuned v
INNER JOIN Visits_rec R ON v.VisitId = r.VisitId + 1
INNER JOIN Visits_starts S于v.VisitId = s.VisitId
）
/ *主要选择;只是游客和及时分组* /
选择
VisitorId，
银泰，
OutTime = MAX（OutTime）
FROM Visits_rec
GROUP BY VisitorId ，银泰
ORDER BY 1，2
I have the following db scheme.
CREATE TABLE Twix.dbo.Sensors
(Id int PRIMARY KEY NOT NULL IDENTITY(1,1),
Location nvarchar(260) NOT NULL);
CREATE TABLE Twix.dbo.Visitors
(Id int PRIMARY KEY NOT NULL IDENTITY(1,1),
Name nvarchar(260) NOT NULL);
CREATE TABLE Twix.dbo.Visits(Id int PRIMARY KEY NOT NULL IDENTITY(1,1),
SensorId int CONSTRAINT FK__Visits__Sensor__239E FOREIGN KEY REFERENCES Sensors(Id),
VisitorId int CONSTRAINT FK__Visits__Visitors__4DCF FOREIGN KEY REFERENCES Visitors(Id),
InTime datetime NOT NULL,
OutTime datetime NOT NULL);
InTime-OutTime periods in Visits table could be intersected. I.e. parts of real visits. For example,
SELECT VisitorId, InTime, OutTime FROM Visits ORDER BY VisitorId, InTime
1 2011-02-09 15:26:59.173 2011-02-09 15:29:22.097
1 2011-02-09 15:28:40.530 2011-02-09 15:29:52.737
1 2011-02-09 15:30:10.577 2011-02-09 16:17:58.967
1 2011-02-09 19:09:23.057 2011-02-09 19:10:57.493
1 2011-02-09 19:12:16.087 2011-02-09 19:13:27.493
1 2011-02-14 15:08:46.333 2011-02-14 15:26:42.433
2 2011-02-09 17:55:42.390 2011-02-09 18:52:03.780
2 2011-02-09 18:10:56.727 2011-02-09 18:11:57.493
2 2011-02-09 21:47:15.650 2011-02-09 21:48:38.783
2 2011-02-09 21:50:18.337 2011-02-09 21:55:26.777
3 2011-02-09 17:12:58.103 2011-02-09 19:51:59.697
3 2011-02-09 22:27:52.073 2011-02-09 23:03:24.753
3 2011-02-09 23:02:51.177 2011-02-10 09:51:14.890
3 2011-02-14 15:27:42.270 2011-02-14 15:42:31.107
3 2011-02-14 15:43:37.320 2011-02-14 18:45:26.163
4 2011-02-09 21:07:51.030 2011-02-09 21:51:02.880
4 2011-02-09 22:42:52.660 2011-02-09 23:21:13.830
4 2011-02-09 23:23:08.563 2011-02-09 23:35:12.847
4 2011-02-09 23:36:05.120 2011-02-09 23:59:02.813
4 2011-02-10 05:58:44.103 2011-02-10 05:59:55.867
4 2011-02-12 08:29:36.620 2011-02-12 09:51:18.510
4 2011-02-12 13:13:42.650 2011-02-12 14:06:01.473
5 2011-02-10 06:48:52.717 2011-02-10 07:37:04.870
5 2011-02-10 06:50:31.067 2011-02-10 06:52:20.877
5 2011-02-10 06:52:36.273 2011-02-10 06:53:36.523
5 2011-02-10 06:59:11.790 2011-02-10 07:00:34.867
5 2011-02-10 08:36:39.563 2011-02-10 08:46:14.760
5 2011-02-10 12:47:05.567 2011-02-10 12:48:05.860
5 2011-02-10 12:49:19.590 2011-02-10 13:09:27.880
5 2011-02-10 12:49:25.733 2011-02-10 12:59:59.883
5 2011-02-10 12:55:23.460 2011-02-10 12:56:23.507
I need to get "completed visits", i.e. merge all visits for concrete vistor, if visits are intersecting, or time difference between them less than 10 min (i.e. "InTime of visit2" - "OutTime of visit1" < 10 min).
In C# it looks like:
private IEnumerable Merge(IEnumerable visits, uint holeInterval)
{
var vlist = new LinkedList<Visit>(visits.OrderBy(o => o.InTime));
var result = new List<Visit>();
while (vlist.Count > 1)
{
Visit a = vlist.First.Value; vlist.RemoveFirst();
Visit b = vlist.First.Value; vlist.RemoveFirst();
var r = Visit.Merge(a, b, holeInterval); // Merges two visits
if (r != null) { vlist.AddFirst(r); }
else { result.Add(a); vlist.AddFirst(b); }
}
result.Add(vlist.First.Value);
return result;
}
public IEnumerable<Visit> ListCompleteVisits()
{
var result = new List<Visit>();
var queryResult = from visits in this.repository.ListVisits()
group visits by visits.Visitor.Id into vgroup
select Merge(vgroup, this.holeInterval);
foreach (var v in queryResult)
{
result.AddRange(v);
}
return result;
}
I tried, and get the following:
DECLARE @holeInterval int
SET @holeInterval = 10
SELECT t.RowNumber, t.VisitorId, t.InTime, t.OutTime, t.BInMinusAOut,
(SELECT MIN(InTime) FROM Visits AS D
WHERE VisitorId NOT IN (select VisitorId from Visits group by VisitorId having COUNT(*) = 1)
AND D.InTime <= t.InTime and D.VisitorId = t.VisitorId
AND t.RowNumber > /*here should be max rownumber greater than holeinterval*/
) AS MinInTime
FROM
(SELECT
ROW_NUMBER() OVER(ORDER BY VisitorId, InTime ASC) AS RowNumber,
VisitorId, InTime, OutTime,
DATEDIFF(MI,InTime,
(SELECT MIN(InTime) FROM Visits AS B
WHERE B.InTime > A.InTime and A.VisitorId = B. VisitorId
)) AS BInMinusAIn,
DATEDIFF(MI,OutTime,
(SELECT MIN(InTime) FROM Visits AS B
WHERE B.InTime > A.InTime and A.VisitorId = B. VisitorId
))) AS BInMinusAOut
FROM Visits AS A
WHERE VisitorId NOT IN (select VisitorId from Visits group by VisitorId having COUNT(*) = 1)
) t
/*WHERE t.BInMinusAOut > @holeInterval OR t.BInMinusAOut IS NULL*/
ORDER BY VisitorId, InTime
1 1 2011-02-09 15:26:59.173 2011-02-09 15:28:22.097 0
2 1 2011-02-09 15:28:40.530 2011-02-09 15:29:52.737 1
3 1 2011-02-09 15:30:10.577 2011-02-09 16:17:58.967 9
4 1 2011-02-09 16:26:44.810 2011-02-09 16:51:46.423 20
5 1 2011-02-09 17:11:57.633 2011-02-09 17:13:20.680 2
6 1 2011-02-09 17:15:35.727 2011-02-09 17:18:48.493 -2
7 1 2011-02-09 17:16:12.230 2011-02-09 17:42:47.867 3
8 1 2011-02-09 17:45:43.793 2011-02-09 17:52:10.860 3
9 1 2011-02-09 17:55:31.127 2011-02-09 20:13:22.743 -109
10 1 2011-02-09 18:24:00.427 2011-02-09 18:32:12.033 2
11 1 2011-02-09 18:34:15.877 2011-02-09 18:37:19.770 2
12 1 2011-02-09 18:39:46.440 2011-02-09 18:48:16.800 2
13 1 2011-02-09 18:50:59.270 2011-02-09 20:03:47.550 -54
14 1 2011-02-09 19:09:23.057 2011-02-09 19:10:57.493 2
15 1 2011-02-09 19:12:16.087 2011-02-09 19:13:27.493 48
Now I need to "merge" 1st-4th into 2011-02-09 15:26:59.173 - 2011-02-09 16:51:46.423 5th-15h into 2011-02-09 17:11:57.633 - 2011-02-09 20:13:22.743 This means, that I have to take min InTime in rows, which is between current and the last, where MinInTime > @holeInterval, and max OutTime for those range too.
I.e. result:
1 2011-02-09 15:26:59.173 2011-02-09 16:51:46.423
1 2011-02-09 17:11:57.633 2011-02-09 20:13:22.743
Thank's.
解决方案
;WITH
Visits_tuned AS (
/* adding some helper columns */
SELECT
VisitorId,
InTime,
OutTime,
OutTimeDelayed = DATEADD(minute, 10, OutTime),
rownum = ROW_NUMBER() OVER (PARTITION BY VisitorId
ORDER BY InTime, OutTime),
VisitId = ROW_NUMBER() OVER (ORDER BY VisitorId, InTime, OutTime)
FROM Visits
),
Visits_starts AS (
/* spotting the starting points of the 'merged' visits */
SELECT DISTINCT
v1.VisitId,
IsStart = 1 - CASE WHEN v2.VisitId IS NULL THEN 0 ELSE 1 END
FROM Visits_tuned v1
LEFT JOIN Visits_tuned v2
ON v1.InTime BETWEEN v2.InTime AND v2.OutTimeDelayed
AND v1.VisitorId = v2.VisitorId AND v1.rownum <> v2.rownum
),
Visits_rec AS (
/* basically, selecting the original data, but with
InTime values replaced by the starting InTimes */
SELECT
VisitId,
VisitorId,
InTime,
OutTime
FROM Visits_tuned
WHERE VisitId = 1
UNION ALL
SELECT
v.VisitId,
v.VisitorId,
IntTime = CASE
WHEN v.VisitorId = r.VisitorId AND
s.IsStart = 0 AND r.InTime < v.InTime
THEN r.InTime
ELSE v.InTime
END,
v.OutTime
FROM Visits_tuned v
INNER JOIN Visits_rec r ON v.VisitId = r.VisitId + 1
INNER JOIN Visits_starts s ON v.VisitId = s.VisitId
)
/* main select; just grouping by visitor and in-time */
SELECT
VisitorId,
InTime,
OutTime = MAX(OutTime)
FROM Visits_rec
GROUP BY VisitorId, InTime
ORDER BY 1, 2
这篇关于需要QUOT&;&合并QUOT;在查询记录块成一体，这取决于其他查询列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
