计算列的中位数并将其存储在BQ表中

SELECT column_name
FROM table_name
ORDER BY column_name ASC

SELECT COUNT(column_name) AS total_count, 
       (COUNT(column_name) + 1) / 2 AS median_position
FROM table_name

SELECT column_name
FROM table_name
ORDER BY column_name ASC
LIMIT 1
OFFSET median_position - 1

SELECT AVG(column_name)
FROM (
  SELECT column_name
  FROM table_name
  ORDER BY column_name ASC
  LIMIT 2
  OFFSET median_position - 2
)

CREATE TABLE new_table_name AS
SELECT *, calculated_median AS new_column_name
FROM (
  SELECT *, (
    CASE
      WHEN total_count % 2 = 1 THEN (
        SELECT column_name
        FROM table_name
        ORDER BY column_name ASC
        LIMIT 1
        OFFSET median_position - 1
      )
      ELSE (
        SELECT AVG(column_name)
        FROM (
          SELECT column_name
          FROM table_name
          ORDER BY column_name ASC
          LIMIT 2
          OFFSET median_position - 2
        )
      )
    END
  ) AS calculated_median
  FROM table_name
)