英文:
BigQuery WHERE statement filter out pairs of values
问题
以下是您要翻译的内容:
我有一个包含多行和以下字段的表格:
访客 | 主机 | 事件 | 时间 |
---|
这张表格列出了访问建筑物的访客。访客是访客的姓名,主机是他们要访问的公司,事件是'IN'或'OUT',表示访客进入或离开建筑物的时间。
访客 | 主机 | 事件 | 时间 |
---|---|---|---|
Alan | GraphicsMan | IN | 12:34 |
Alan | GraphicsMan | OUT | 13:44 |
Barry | CodeMan | IN | 14:31 |
Carla | CodeMan | IN | 15:10 |
Danielle | SecureMan | IN | 15:13 |
Carla | CodeMan | OUT | 15:31 |
Carla | SecureMan | IN | 15:35 |
我希望我的查询返回当前正在访问的所有访客以及他们要访问的对象。换句话说,我希望返回所有访客-主机对,其具有'IN'事件但没有'OUT'事件。对于上面的示例表格,查询应返回:
访客 | 主机 | 事件 | 时间 |
---|---|---|---|
Barry | CodeMan | IN | 14:31 |
Danielle | SecureMan | IN | 15:13 |
Carla | SecureMan | IN | 15:35 |
我最接近的尝试如下:
with entrances as (SELECT visitor FROM table where event = 'IN'),
exits as (SELECT visitor FROM table WHERE event = 'OUT'),
SELECT * from table
WHERE visitor IN entrances AND visitor NOT IN exits
这返回:
访客 | 主机 | 事件 | 时间 |
---|---|---|---|
Barry | CodeMan | IN | 14:31 |
Danielle | SecureMan | IN | 15:13 |
Carla被排除,因为她在返回访问不同的主机之前进出了建筑物。我正在尝试考虑在where子句中检查值对(即访客-主机对)的方法。
英文:
I have a table with multiple rows and the following fields:
Visitor | Host | Event | Time |
---|
The table lists visitors to a building. Visitor is the name of a visitor, Host is the company they are visiting, and Event is either 'IN' or 'OUT' indicating that the visitor has entered or exited the building at Time
Visitor | Host | Event | Time |
---|---|---|---|
Alan | GraphicsMan | IN | 12:34 |
Alan | GraphicsMan | OUT | 13:44 |
Barry | CodeMan | IN | 14:31 |
Carla | CodeMan | IN | 15:10 |
Danielle | SecureMan | IN | 15:13 |
Carla | CodeMan | OUT | 15:31 |
Carla | SecureMan | IN | 15:35 |
I want my query to return all visitors who are currently visiting and who they are visiting. In other words, I want all visitor-host pairs that have an 'IN' event but do not have an 'OUT' event. For the example table above, the query should return:
Visitor | Host | Event | Time |
---|---|---|---|
Barry | CodeMan | IN | 14:31 |
Danielle | SecureMan | IN | 15:13 |
Carla | SecureMan | IN | 15:35 |
The closest I've gotten is the following:
with entrances as (SELECT visitor FROM table where event = 'IN'),
exits as (SELECT visitor FROM table WHERE event = 'OUT'),
SELECT * from table
WHERE visitor IN entrances AND visitor NOT IN exits
which returns
Visitor | Host | Event | Time |
---|---|---|---|
Barry | CodeMan | IN | 14:31 |
Danielle | SecureMan | IN | 15:13 |
Carla will be excluded because she went in and out of the building before going back in to visit a different host. I'm trying to think of a way to check pairs of values (i.e. a visitor-host pair) in the where clause.
答案1
得分: 1
以下是翻译好的内容:
假设 OUT 始终在 IN 之后打上时间戳,COUNT
可以用于查找 IN 没有对应 OUT 的情况。
SELECT visitor, host, time
FROM (
SELECT
visitor, host,
COUNTIF(event='IN') ins, COUNTIF(event='OUT') outs,
MAX(time) time
FROM table
GROUP BY visitor, host
HAVING ins > outs)
它应该返回:
行 | 访客 | 主机 | 时间 |
---|---|---|---|
1 | Barry | CodeMan | 14:31:00 |
2 | Carla | SecureMan | 15:35:00 |
3 | Danielle | SecureMan | 15:13:00 |
英文:
Assuming OUT is always timestamped after IN, COUNT
can be used to find cases where IN does not have a corresponding OUT.
SELECT visitor, host, time
FROM (
SELECT
visitor, host,
COUNTIF(event='IN') ins, COUNTIF(event='OUT') outs,
MAX(time) time
FROM table
GROUP BY visitor, host
HAVING ins > outs)
It should return:
Row | visitor | host | time |
---|---|---|---|
1 | Barry | CodeMan | 14:31:00 |
2 | Carla | SecureMan | 15:35:00 |
3 | Danielle | SecureMan | 15:13:00 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论