How to overwrite the data request for postgresql?

Question

I have this query

select count(distinct mac)
from t out
where radar_id = '32'
  and log_time >= '2018-08-18 00:00:00'
  and log_time < '2018-08-22 23:59:59'
  and mac not in
      (select mac from t as inn where out.log_time - interval '2 minutes' > inn.log_time);

I rewrote it to

select count(distinct mac)
from `test-from-frelance.123.sniffer_logs` out
where radar_id = '32'
  and log_time >= '2018-08-18 00:00:00'
  and log_time < '2018-08-22 23:59:59'
  and mac not in
      (select mac 
      from `test-from-frelance.123.sniffer_logs` as inn 
      where TIMESTAMP_SUB(out.log_time, interval 2 minute) > inn.log_time );

But I got error:

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

I don`t understand where I wrong. How to solve this problem?

What to need to get from table? Need to get mac different devices which send information to db since '2018-08-18 00:00:00' to '2018-08-22 23:59:59' (column log_time contain information when device send info) and excluding devices that sent information in interval bigger then 2 minutes.

Your and mac not in (select ...) is being translated by BQ Engine into LEFT JOIN which in BigQuery has some limitations which are reflected in the error you see. It is not big deal to rewrite it such that it will work - but in order to not to waste every-ones time - you should present the logic that you are trying to implement here. Obviously we can reverse-engineer the logic - and I did it -but it makes not much practical sense to me so far - so if you will present clear logic - will be great — Mikhail Berlyant
please clarify and excluding devices that sent information less than 2 minutes — Mikhail Berlyant
By the way, why did the request fail? In postgresql it is correct. — Andru
have you read my first comment above? btw. if my answer helped - consider voting it up and accepting :o) — Mikhail Berlyant
Yes I read and din`t understand why BQ Engine translated my query. What advantages it gives. What to read to understand this topic? — Andru

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-09-07T19:47:35

Below simple example for BigQuery Standard SQL

#standardSQL
SELECT COUNT(1) devices FROM (
  SELECT mac FROM (
    SELECT mac, 
      TIMESTAMP_DIFF(log_time, LAG(log_time) OVER(PARTITION BY mac ORDER BY log_time), MINUTE) delta
    FROM `test-from-frelance.123.sniffer_logs` out
    WHERE radar_id = '32'
      AND log_time >= '2018-08-18 00:00:00'
      AND log_time < '2018-08-22 23:59:59'
  )
  GROUP BY mac
  HAVING MIN(delta) > 2  
)

Here, first we calculate distance/interval between consecutive entries for given mac, then we filter out those with at least one less/equal than 2 minutes and finally we count "survived" macs

You can adjust logic to whatever you need in your particular case

How to overwrite the data request for postgresql?

1 Answers