BigQuery WHERE语句用于筛选数值对。

huangapple go评论54阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年3月21日 00:19:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75792802.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定