基于表中的多列筛选SQL中的数据

Question

我有一个表geo_loc，它包含每个送货员的地理位置(纬度和经度)以及其他列中的时间戳。

任意一天的GEO_LOC表视图：-

agent_id       date            geo_lat  geo_long
1134    13-02-2021T09:09:54    17.66    89.44
1134    13-02-2021T10:10:47    19.99    76.56
1134    13-02-2021T10:50:47    19.99    76.56
1134    13-02-2021T11:57:47    19.99    33.33
1134    13-02-2021T13:13:23    34.44    89.67
2678    13-02-2021T10:25:11    45.55    34.67
4657    13-02-2021T11:55:33    22.34    66.78
4657    13-02-2021T12:20:27    22.34    66.78
4657    13-02-2021T15:15:13    33.45    45.67
7545    13-02-2021T08:17:55    12.45    56.56
7545    13-02-2021T11:55:23    18.56    87.77
0908    13-02-2021T16:55:56    19.99    79.99
0908    13-02-2021T17:43:12    19.99    79.99
0908    13-02-2021T18:12:34    19.99    79.99

​

GEO_LOC Table每天都有类似于上面的条目，用于多个传递agent_id。

对于任何一天，我都想过滤所有代理的所有记录，这些代理在任何一天都有一个以上的gps条目 (geo_lat和geo_long)。

For ex:

0908在13-02-2021上有相同的geo_lat和geo_long，所以我不想要这一行。

但是1134在13-02-2021上有多个geo_lat和geo_long条目，所以我想要这个代理在这一天的所有行。

2678在13-02-2021有一个条目，所以我不知道这行也是什么。

期望输出：-

agent_id       date           geo_lat   geo_long
1134    13-02-2021T09:09:54    17.66    89.44
1134    13-02-2021T10:10:47    19.99    76.56
1134    13-02-2021T10:50:47    19.99    76.56
1134    13-02-2021T11:57:47    19.99    33.33
1134    13-02-2021T13:13:23    34.44    89.67
4657    13-02-2021T11:55:33    22.34    66.78
4657    13-02-2021T12:20:27    22.34    66.78
4657    13-02-2021T15:15:13    33.45    45.67
7545    13-02-2021T08:17:55    12.45    56.56
7545    13-02-2021T11:55:23    18.56    87.77

​

Answer 1

为了得到你想要的数据，我们需要做几件事。

1. 我们需要将Date列转换为只显示日期而不是时间
2. ，然后根据ID和date对数据进行分组，使用连接的lat/long列
3. 的不同计数，然后可以使用agent_id使用WHERE子句

从原始表中选择

对于1，我们可以使用转换将ISO8601日期时间更改为NVARCHAR日期：

convert(nvarchar,date,103)

对于2，我们使用上面的内容以及COUNT和CONCAT；CONCAT来创建具有lat和long的单个列：

concat(geo_lat, ',', geo_long)

然后计数DISTINCT只返回唯一的lat/long组合：

concat(geo_lat, ',', geo_long)

然后，我们可以将它们与agent_id上的GROUP BY子句以及新的date列放在一起，为您提供一个经过筛选的表。

select 
agent_id
, convert(nvarchar,date,103)
, count(distinct(concat(geo_lat, ',', geo_long))) 
from [71405703] 
GROUP BY agent_id, convert(nvarchar,date,103)

agent_id    date    count
908     13/02/2021  1
1134    13/02/2021  4
2678    13/02/2021  1
4657    13/02/2021  2
7545    13/02/2021  2

然后，我将该查询放入CTE中，以便能够轻松地针对列编写WHERE子句。

最后的脚本如下所示：

WITH TableFilter (agent, date, count) 
AS 
(
select 
agent_id
, convert(nvarchar,date,103)
, count(distinct(concat(geo_lat, ',', geo_long))) 
from [71405703] 
GROUP BY agent_id, convert(nvarchar,date,103))

SELECT * FROM [71405703] 
WHERE agent_id IN (select agent FROM TableFilter WHERE count > 1)