我在一个SQL数据库中有两个表。第一个是path，用于保持路径(或轨迹(上的点。每个点都有一行。
第二个表"intervals"列出了第一个表上表示的路径上的间隔。这些在某种程度上被认为是特殊的，例如物体移动不多的路径的一部分。
我们期望在路径上识别多个间隔。
我想在path表中添加一个新列，其值表示路径的该部分是否在其中一个间隔内。我在下面的示例中给出了这个附加列的一个示例。
如果只有一个间隔，我会使用
CASE WHEN p.time BETWEEN i.Start_Time AND i.End_Time THEN True ELSE False END
我可以做些什么来处理任意数量的间隔？
间隔：
| Interval ID | Start_Time | End_Time |
|-------------|------------|----------|
| 1 | 5 | 36 |
| 2 | 71 | 78 |
| 3 | 206 | 308 |
| ... | | |
以下是成功添加了所需"at_rest"列的path表的示例。
| time | x | y | at_rest |
|------|---|----|---------|
| 0 | 5 | 9 | 0 |
| 1 | 6 | 10 | 0 |
| 2 | 7 | 31 | 1 |
| 3 | 9 | 49 | 1 |
| ... | | | |
不同的DBMS的答案可能不同。我认为这种逻辑是可行的，但实现取决于您的环境。我假设间隔没有重叠。
您可以在CASE:中使用WHEN EXISTS和SELECT语句的组合
CASE WHEN EXISTS(
SELECT Interval_ID
FROM intervals
WHERE p.time BETWEEN Start_Time AND End_Time
) THEN (
SELECT Interval_ID
FROM intervals
WHERE p.time BETWEEN Start_Time AND End_Time
)
ELSE 0
如果只需要一行true/false，则使用exists。许多数据库直接支持布尔类型，因此您可以使用：
select . . .,
(exists (select 1
from intervals i
where p.time bertween i.start_time and i.end_time
)
) as flag
在其他数据库中，您需要case表达式：
select . . .,
(case when exists (select 1
from intervals i
where p.time bertween i.start_time and i.end_time
)
then 1 else 0
) as flag
我认为拥有Interval_ID比仅仅是一个"是/否"指示符更有意义。Rest栏保留了您最初的是/否概念。此外，我在路径表中添加了更多的数据来获得我的结果——
SELECT
[Path_time]
,[x]
,[y]
,[at_rest]
,ki.[Interval ID]
,Case When ki.[Interval ID] IS NULL Then 0 Else 1 End as atRest
FROM [StackOver].[dbo].[Keith_path] as kp
Left Join [StackOver].[dbo].[Keith_interval] as ki
On kp.Path_time BETWEEN ki.Start_Time AND ki.End_Time
Order By kp.Path_time
这将需要转换为更新sql来替换现有值。结果是——
Path_time x y at_rest IntervalID atRest
0 5 9 0 NULL 0
1 6 10 0 NULL 0
2 7 31 1 NULL 0
3 7 33 0 NULL 0
4 7 32 0 NULL 0
5 8 31 0 1 1
6 9 30 0 1 1
我和同事提出的解决方案使用了可怕的CROSS JOIN，但我们可以预先将表筛选到几千行，这样就不会有什么问题了。
WITH interval_path AS (SELECT path.*, intervals.*
FROM path
CROSS JOIN intervals
WHERE path.time BETWEEN intervals.Start_Time AND intervals.End_Time)
SELECT path.time, path.x, path.y,
COALESCE(interval_path.in_cluster, 0) AS in_cluster
FROM path
LEFT JOIN interval_path ON interval_path.time = path.time
ORDER BY path.time
正如donPablo所指出的，这可以通过连接来解决(正如您所描述的(：
WITH intervals AS (
SELECT *
FROM VALUES
(1,1,5,36)
,(1,2,71,78)
,(1,3,206,308)
v(unit_id, interval_id, start_time, end_time)
), paths AS (
SELECT *
FROM VALUES
(1,0,5,9 )
,(1,1,6,10)
,(1,2,7,31)
,(1,3,9,49)
,(1,4,9,48)
,(1,5,9,47)
,(1,6,9,46)
p(unit_id, time, x, y)
)
SELECT p.unit_id
,p.time
,p.x
,p.y
,i.unit_id is not null as atRest
FROM paths p
LEFT JOIN intervals i
ON p.unit_id = i.unit_id and p.time between i.start_time and i.end_time
order by 1,2;
给予：
UNIT_ID TIME X Y ATREST
1 0 5 9 FALSE
1 1 6 10 FALSE
1 2 7 31 FALSE
1 3 9 49 FALSE
1 4 9 48 FALSE
1 5 9 47 TRUE
1 6 9 46 TRUE
值得注意的是，你给出的示例间隔并不意味着"；静止时"；你给的第一分，因为这些不是在一个区间内。
我还包括一个"；unit_id"；因为只为一个实体拥有大量数据是非常不可能的。
现在，如果您的间隔数据重叠，那么您需要更改计数：
WITH intervals AS (
SELECT *
FROM VALUES
(1,1,2,4)
,(1,2,4,6)
,(1,3,206,308)
v(unit_id, interval_id, start_time, end_time)
), paths AS (
SELECT *
FROM VALUES
(1,0,5,9 )
,(1,1,6,10)
,(1,2,7,31)
,(1,3,9,49)
,(1,4,9,48)
,(1,5,9,47)
,(1,6,9,46)
p(unit_id, time, x, y)
)
SELECT p.unit_id
,p.time
,p.x
,p.y
,count(i.unit_id) > 0 as atRest
FROM paths p
LEFT JOIN intervals i
ON p.unit_id = i.unit_id and p.time between i.start_time and i.end_time
group by 1,2,3,4
order by 1,2;
给予：
UNIT_ID TIME X Y ATREST
1 0 5 9 FALSE
1 1 6 10 FALSE
1 2 7 31 TRUE
1 3 9 49 TRUE
1 4 9 48 TRUE
1 5 9 47 TRUE
1 6 9 46 TRUE
如果你想要0或1作为雪花中的at_rest值，那么交换到内联IFF是最紧凑的。
,iff(count(i.unit_id) > 0,1,0) as atRest