根据表中的多列过滤 SQL 中的数据

发布于 2025-01-12 18:58:53 字数 1775 浏览 2 评论 0原文

我有一个表 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 表包含如上所述的每天和多个投递 agent_id 的条目。

对于任何一天，我都想过滤所有在任何一天拥有多个 GPS 条目（geo_lat 和 geo_long）的所有记录。

例如：< /强>
0908 在 2021 年 2 月 13 日具有相同的 geo_lat 和 geo_long，所以我不想要这一行。
但是 1134 在 2021 年 2 月 13 日有多个 geo_lat 和 geo_long 条目，所以我想要此代理的所有行日。
2678 在 2021 年 2 月 13 日有一个条目，所以我也不知道这一行是什么。

所需输出：-

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

原文

I have a table geo_loc which contains the geo location (latitude and longitude) of each delivery guy along with time stamps in other columns.

GEO_LOC Table view on any day: -

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 has entries like above for each day and for multiple delivery agent_id.

For any day I want to filter all the records for all those agents who have more than one gps entry (geo_lat and geo_long) on any day.

For ex:
0908 has same geo_lat and geo_long on 13-02-2021, so I don’t want this row.
But 1134 has multiple geo_lat and geo_long entry on 13-02-2021 so I want all the rows for this agent on this day.
2678 has single entry on 13-02-2021 so I don’t what this row also.

Desired output: -

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

平安喜乐 2025-01-19 18:58:53

我们需要做一些事情来获取您想要的数据

我们需要转换日期列以仅显示日期而不显示时间
然后我们需要按 ID 和日期对数据进行分组，并使用串联的不同计数lat/long 列
然后我们可以使用带有 WHERE IN 子句的 agent_id 从原始表中进行选择

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

convert(nvarchar,date,103)

对于 2，我们使用上述以及计数不同和连接； CONCAT 创建包含纬度和经度的单个列：

concat(geo_lat, ',', geo_long)

然后 COUNT DISTINCT 仅返回唯一的纬度/经度组合：

concat(geo_lat, ',', geo_long)

然后我们可以将它们与 agent_id 上的 GROUP BY 子句和新日期列放在一起，为您提供过滤后的结果然后

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)

There are a couple of things we need to do to get the data you want

We need to convert the Date column to show only the date and not the time
We need to then group the data by ID and Date with a distinct count of a concatenated lat/long column
We can then select from the original table using the agent_id with a WHERE IN clause

For 1, we can use CONVERT to change the ISO8601 datetime into an NVARCHAR date:

convert(nvarchar,date,103)

For 2, we use the above as well as COUNT DISTINCT and CONCAT; CONCAT to create a single column with both the lat and long:

concat(geo_lat, ',', geo_long)

And then COUNT DISTINCT to only return unique lat/long combinations:

concat(geo_lat, ',', geo_long)

We can then put these together along with the GROUP BY clause on agent_id and the new date column to give you a filtered table

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

I then put that query into a CTE so that I can easily write WHERE clauses against the columns.

The final script would look like:

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)

回复收藏 0 原文

~没有更多了~