bq.md

BigQueryで論理削除&論理更新

スキーマ

name	type
user_id	INTEGER
name	STRING
is_active	BOOLEAN
created_at	TIMESTAMP

name が初音 && user_id でユニーク && user_id が同じなら最新を採用

SELECT
  a.user_id as user_id,
  a.name as name,
  a.is_active as is_active,
  a.max_created_at as created_at
FROM [sandbox.users] a
JOIN EACH
  (
    SELECT
      MAX(created_at) as max_created,
      user_id
    FROM [sandbox.users]
    WHERE
      (REGEXP_MATCH(name, r'初音'))
    GROUP EACH BY user_id
  ) b
  ON
  b.max_created = a.created_at AND
  b.user_id = a.user_id

name が初音 && user_id でユニーク && user_id が同じなら最新を採用 && is_active = true でフィルター

SELECT *
FROM (
  SELECT a.store_id AS store_id,
  a.name AS name,
  a.created_at AS created_at,
  a.is_active AS is_active
  FROM sandbox.users a
  JOIN (
    SELECT
      MAX(created_at) as created_at,
      name,
      user_id
    FROM [sandbox.users]
    WHERE
      (REGEXP_MATCH(name, r'初音'))
    GROUP EACH BY user_id
   ) b
  ON a.user_id = b.user_id
  AND a.created_at = b.created_at
) c
WHERE a.is_active = true