Skip to content

Instantly share code, notes, and snippets.

@podhmo
Created Aug 4, 2021
Embed
What would you like to do?
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
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
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
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
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
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
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]
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]
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment