00users.sql

CREATE TEMP FUNCTION Now0()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SECONDS(1628038568));

WITH users AS (
    SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
    SELECT 2, "bar", Now0()
)
SELECT
  *
FROM
  users

01users-multi-gen.sql

CREATE TEMP FUNCTION Now0()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SECONDS(1628038568));

CREATE TEMP FUNCTION Now1()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY));


WITH users AS (
    SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
    SELECT 2, "bar", Now0() UNION ALL
    -- これが過去のもの
    SELECT 1 as id, "fo" as name, Now1()
)
SELECT
  *
FROM
  users  

02with-latest-view.sql

CREATE TEMP FUNCTION Now0()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SECONDS(1628038568));

CREATE TEMP FUNCTION Now1()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY));


WITH users AS (
  SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
  SELECT 2, "bar", Now0() UNION ALL
  SELECT 1 as id, "fo" as name, Now1()
),
users_latest AS ( -- 実際はviewなど
  SELECT
   * 
  FROM users as x
  INNER JOIN (SELECT DISTINCT o.id, MAX(o._updatedAt) as _updatedAt FROM users as o GROUP BY id) as y ON x.id = y.id AND x._updatedAt = y._updatedAt
)
SELECT
  *
FROM
  users_latest

03with-latest-view.sql

CREATE TEMP FUNCTION Now0()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SECONDS(1628038568));

CREATE TEMP FUNCTION Now1()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY));

WITH users AS (
  SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
-- 完全に同じ
  SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
  SELECT 2, "bar", Now0() UNION ALL
  SELECT 1, "fo", Now1()
),
users_latest AS (
  SELECT
   x.* 
  FROM users as x
  INNER JOIN (SELECT DISTINCT o.id, MAX(o._updatedAt) as _updatedAt FROM users as o GROUP BY id) as y ON x.id = y.id AND x._updatedAt = y._updatedAt
)
SELECT
  *
FROM
  users_latest

04distinct.sql

CREATE TEMP FUNCTION Now0()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SECONDS(1628038568));

CREATE TEMP FUNCTION Now1()
  RETURNS TIMESTAMP
  AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY));

WITH users AS (
  SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
  SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL
  SELECT 2, "bar", Now0() UNION ALL
  SELECT 1, "fo", Now1()
),
users_latest AS (
  SELECT DISTINCT -- distinct
   x.* 
  FROM users as x
  INNER JOIN (SELECT DISTINCT o.id, MAX(o._updatedAt) as _updatedAt FROM users as o GROUP BY id) as y ON x.id = y.id AND x._updatedAt = y._updatedAt
)
SELECT
  *
FROM
  users_latest

05struct.sql

WITH points AS (
    SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y)
)
SELECT * FROM points

06struct-distinct.sql

WITH points AS (
    SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y)
)
SELECT * FROM points

-- this is error
-- Column p of type STRUCT cannot be used in SELECT DISTINCT at [6:18]

07struct-group-by.sql

WITH points AS (
    SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y)
)
SELECT * FROM points as t
GROUP BY
  t.id, t.p  -- Grouping by expressions of type STRUCT is not allowed at [8:9] 

08struct-group-by.sql

WITH points AS (
    SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y)
)
SELECT
  t.id,
--  t.p, -- SELECT list expression references t.p which is neither grouped nor aggregated at [8:3]
  t.p.x,
  t.p.y
FROM points as t
GROUP BY
  t.id, t.p.x, t.p.y

09struct-group-by.sql

WITH points AS (
    SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL
    SELECT 2, STRUCT(0 as x, 10 as y)
)
SELECT
  t.id,
  STRUCT(t.p.x as x, t.p.y as y) as p
FROM points as t
GROUP BY
  t.id, t.p.x, t.p.y