如何在Google-Bigquery中添加基于条件和条件重置的自动递增计数器

CREATE TABLE counter (
  counter_value INT64,
  condition_value INT64
);

CREATE TEMPORARY FUNCTION increment_counter(condition INT64) AS (
  (
    SELECT counter_value + 1
    FROM counter
    WHERE condition_value = condition
  )
  UNION ALL
  (
    SELECT 1
    FROM counter
    WHERE NOT EXISTS (
      SELECT 1
      FROM counter
      WHERE condition_value = condition
    )
  )
);

CREATE TEMPORARY FUNCTION reset_counter(condition INT64) AS (
  (
    SELECT 0
    FROM counter
    WHERE condition_value = condition
  )
  UNION ALL
  (
    SELECT 1
    FROM counter
    WHERE NOT EXISTS (
      SELECT 1
      FROM counter
      WHERE condition_value = condition
    )
  )
);

UPDATE counter
SET counter_value = increment_counter(condition_value)
WHERE condition_value = <条件>;

UPDATE counter
SET counter_value = reset_counter(condition_value)
WHERE condition_value = <条件重置的条件>;