组连续范围
我有一个有很多行的数据表，我想有条件地组合两列，即开始和结束。 这些列代表相关人员在做某事的某个月。 以下是一些示例数据（可以使用R读入，或者如果不使用R，则可以在下面找到纯表）：
# base:
test <- read.table(
text = "
1 A mnb USA prim 4 12
2 A mnb USA x 13 15
3 A mnb USA un 16 25
4 A mnb USA fdfds 1 2
5 B ghf CAN sdg 3 27
6 B ghf CAN hgh 28 29
7 B ghf CAN y 24 31
8 B ghf CAN ghf 38 42
",header=F)
library(data.table)
setDT(test)
names(test) <- c("row","Person","Name","Country","add info","Begin","End")
out <- read.table(
text = "
1 A mnb USA fdfds 1 2
2 A mnb USA - 4 25
3 B ghf CAN - 3 31
4 B ghf CAN ghf 38 42
",header=F)
setDT(out)
names(out) <- c("row","Person","Name","Country","add info","Begin","End")
应该按如下方式进行分组：如果A人从第4个月到第15个月远足，从第16个月到第24个月，我将从第4个月到第24个月的连续（即不间断）活动分组。如果之后人A做了25个月到28个月的冲浪活动，我还要补充一点，整个小组活动将持续4到28天。现在问题是有重叠期的情况，例如，A人也可能从11岁到31岁，所以整个事情会变成4比31。然而，如果人A做了1到2的事情，那将是一个单独的活动（相比1到3，这也将被添加，因为3连接到4 ）。 我希望这很清楚，如果不是，你可以在上面的代码中找到更多的例子。 我正在使用数据表，因为我的数据集非常大。 到目前为止，我已经开始使用sqldf了，但是如果每个人都有这么多的活动（假设有8个或更多），那么这是有问题的。 这可以在datatable，plyr或sqldf中完成吗？ 请注意：我也在SQL中寻找答案，因为我可以直接在sqldf中使用它，或者尝试将它转换为另一种语言。 sqldf支持（1）SQLite后端数据库（默认），（2）H2 java数据库，（3）PostgreSQL数据库和（4）sqldf 0.4-0以上版本也支持MySQL。
编辑：这里是'纯'表：
在：
Person Name Country add info Begin End
A mnb USA prim 4 12
A mnb USA x 13 15
A mnb USA un 16 25
A mnb USA fdfds 1 2
B ghf CAN sdg 3 27
B ghf CAN hgh 28 29
B ghf CAN y 24 31
B ghf CAN ghf 38 42
日期：
A mnb USA fdfds 1 2
A mnb USA - 4 25
B ghf CAN - 3 31
B ghf CAN ghf 38 42
我做了这个，在我的测试中工作，几乎所有的主要数据库应该通常运行它...我强调了我的专栏......请在测试之前更改名称：
SELECT
r1.person_,
r1.name_,
r1.country_,
CASE
WHEN max(r2.begin_) = max(r1.begin_)
THEN max(r1.info_) ELSE '-'
END info_,
MAX(r2.begin_) begin_,
r1.end_
FROM stack_39626781 r1
INNER JOIN stack_39626781 r2 ON 1=1
AND r2.person_ = r1.person_
AND r2.begin_ <= r1.begin_ -- just optimizing...
LEFT JOIN stack_39626781 r3 ON 1=1
AND r3.person_ = r1.person_
-- matches when another range overlaps this range end
AND r3.end_ >= r1.end_ + 1
AND r3.begin_ <= r1.end_ + 1
LEFT JOIN stack_39626781 r4 ON 1=1
AND r4.person_ = r2.person_
-- matches when another range overlaps this range begin
AND r4.end_ >= r2.begin_ - 1
AND r4.begin_ <= r2.begin_ - 1
WHERE 1=1
-- get rows
-- with no overlaps on end range and
-- with no overlaps on begin range
AND r3.person_ IS NULL
AND r4.person_ IS NULL
GROUP BY
r1.person_,
r1.name_,
r1.country_,
r1.end_
此查询基于以下事实：输出中的任何范围都没有连接/重叠。 可以说，对于五个范围的输出，存在五个begin和五个end ，没有连接/重叠。 找到并关联它们应该比生成所有连接/重叠更容易。 那么，这个查询所做的是：
end值没有重叠/连接; begin值没有重叠/连接; person ，并end ，正确的begin对可中最大的一个，其值等于或大于该较小的end ......人们很容易验证此规则...首先，你不能有一个begin比更大的end 。 ..如果你有两个或更多可能的begin小于end ，例如，begin1 = end - 2和begin2 = end - 5，选择较小的一个（begin2）使得这个范围的重叠（begin1） 。 希望能帮助到你。
如果您正在使用SQL Server 2012或更高版本，则可以使用LAG和LEAD函数来构建逻辑以获得最终所需的数据集。 我相信，自Oracle 8i以来，这些功能在Oracle中也可用。
下面是我在SQL Server 2012中创建的一个解决方案，它可以帮助您。 您提供的示例值将加载到临时表中，以表示您称为第一个“纯表”的内容。 使用这两个函数以及OVER子句，我使用下面的T-SQL代码得到了最终的数据集。 我在代码中留下了一些注释掉的行，以显示我是如何逐个构建整体解决方案的，这些解决方案说明了放置在作为分组标记的GapMarker列的CASE语句中的各种方案。
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
DROP TABLE #MyTable
CREATE TABLE #MyTable (
Person CHAR(1)
,[Name] VARCHAR(3)
,Country VARCHAR(10)
,add_info VARCHAR(10)
,[Begin] INT
,[End] INT
)
INSERT INTO #MyTable (Person, Name, Country, add_info, [Begin], [End])
VALUES ('A', 'mnb', 'USA', 'prim', 4, 12),
('A', 'mnb', 'USA', 'x', 13, 15),
('A', 'mnb', 'USA', 'un', 16, 25),
('A', 'mnb', 'USA', 'fdfds', 1, 2),
('B', 'ghf', 'CAN', 'sdg', 3, 27),
('B', 'ghf', 'CAN', 'hgh', 28, 29),
('B', 'ghf', 'CAN', 'y', 24, 31),
('B', 'ghf', 'CAN', 'ghf', 38, 42);
WITH CTE
AS
(SELECT
mt.Person
,mt.Name
,mt.Country
,mt.add_info
,mt.[Begin]
,mt.[End]
--,LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [End])
--,CASE WHEN [End] + 1 = LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [End])
-- --AND LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [End]) = LEAD([End], 1) OVER (PARTITION BY mt.Person ORDER BY [End])
-- THEN 1
-- ELSE 0
-- END AS Grp
--,MARKER = COALESCE(LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [End]), LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [End]))
,CASE
WHEN mt.[End] + 1 = COALESCE(LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [End]), LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [End])) OR
1 + COALESCE(LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [End]), LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [End])) = mt.[Begin] OR
COALESCE(LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin]), LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin])) BETWEEN mt.[Begin] AND mt.[End] OR
[End] BETWEEN LAG([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin]) AND LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin]) THEN 1
ELSE 0
END AS GapMarker
,InBetween = COALESCE(LEAD([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin]), LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin]))
,EndInBtw = LAG([Begin], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin])
,LagEndInBtw = LAG([End], 1) OVER (PARTITION BY mt.Person ORDER BY [Begin])
FROM #MyTable mt
--ORDER BY mt.Person, mt.[Begin]
)
SELECT DISTINCT
X.Person
,X.[Name]
,X.Country
,t.add_info
,X.MinBegin
,X.MaxEnd
FROM (SELECT
c.Person
,c.[Name]
,c.Country
,c.add_info
,c.[Begin]
,c.[End]
,c.GapMarker
,c.InBetween
,c.EndInBtw
,c.LagEndInBtw
,MIN(c.[Begin]) OVER (PARTITION BY c.Person, c.GapMarker ORDER BY c.Person) AS MinBegin
,MAX(c.[End]) OVER (PARTITION BY c.Person, c.GapMarker ORDER BY c.Person) AS MaxEnd
--, CASE WHEN c.[End]+1 = c.MARKER
-- OR c.MARKER +1 = c.[Begin]
-- THEN 1
-- ELSE 0
-- END Grp
FROM CTE AS c) X
LEFT JOIN #MyTable AS t
ON t.[Begin] = X.[MinBegin]
AND t.[End] = X.[MaxEnd]
AND t.Person = X.Person
ORDER BY X.Person, X.MinBegin
--ORDER BY Person, [Begin]下一篇: How to plot pie charts as subplots with custom size with Plotly in Python