Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #standardSQL
- CREATE TEMPORARY FUNCTION lab_TIME(x ARRAY<TIMESTAMP>)
- RETURNS INT64
- LANGUAGE js AS """
- var total_time = 0;
- //loop through
- for (var i = 0; i < x.length -1; i+=2)
- { total_time += x[i+1] - x[i]; }
- return total_time/1000;
- """;
- SELECT
- lab_member AS user,
- lab_TIME(access_timestamps) AS total_lab_time
- FROM (
- SELECT
- Name AS lab_member,
- ARRAY_AGG(LabAccessTS) AS access_timestamps
- FROM (
- SELECT
- Name,
- LabAccessTS
- FROM
- `secret_lab.door_scans_20140214`
- GROUP BY
- Name,
- LabAccessTS
- ORDER BY
- Name,
- LabAccessTS ASC)
- GROUP BY
- lab_member);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement