PostgreSQL转BigQuery查询报错求助：未分组/聚合引用字段问题

问题背景 #

我使用谷歌转换工具将PostgreSQL查询转换为BigQuery查询时遇到错误，原查询来自基于MIMIC-IV数据集的论文（作者使用本地PostgreSQL数据库），现需迁移至BigQuery环境运行。

原PostgreSQL查询 #

create table ld_commonlabs as
  -- extracting the itemids for all the labevents that occur within the time bounds for our cohort
  with labsstay as (
    select l.itemid, la.stay_id
    from labevents as l
    inner join ld_labels as la
      on la.hadm_id = l.hadm_id
    where l.valuenum is not null  -- stick to the numerical data
      -- epoch extracts the number of seconds since 1970-01-01 00:00:00-00, we want to extract measurements between
      -- admission and the end of the patients' stay
      and (date_part('epoch', l.charttime) - date_part('epoch', la.intime))/(60*60*24) between -1 and la.los),
  -- getting the average number of times each itemid appears in an icustay (filtering only those that are more than 2)
  avg_obs_per_stay as (
    select itemid, avg(count) as avg_obs
    from (select itemid, count(*) from labsstay group by itemid, stay_id) as obs_per_stay
    group by itemid
    having avg(count) > 3)  -- we want the features to have at least 3 values entered for the average patient
  select d.label, count(distinct labsstay.stay_id) as count, a.avg_obs
    from labsstay
    inner join d_labitems as d
      on d.itemid = labsstay.itemid
    inner join avg_obs_per_stay as a
      on a.itemid = labsstay.itemid
    group by d.label, a.avg_obs
    -- only keep data that is present at some point for at least 25% of the patients, this gives us 45 lab features
    having count(distinct labsstay.stay_id) > (select count(distinct stay_id) from ld_labels)*0.25
    order by count desc;

转换后的BigQuery查询 #

CREATE TABLE mimic_iv.ld_commonlabs
  AS
    WITH labsstay AS (
      SELECT
          --  extracting the itemids for all the labevents that occur within the time bounds for our cohort
          l.itemid,
          la.stay_id
        FROM
          physionet-data.mimiciv_hosp.labevents AS l
          INNER JOIN mimic_iv.ld_labels AS la ON la.hadm_id = l.hadm_id
        WHERE l.valuenum IS NOT NULL
         AND (UNIX_SECONDS(CAST(CAST(l.charttime as DATE) AS TIMESTAMP)) - CAST(UNIX_SECONDS(CAST(CAST(la.intime as DATE) AS TIMESTAMP)) as FLOAT64)) / (60 * 60 * 24) BETWEEN -1 AND la.los
    ), avg_obs_per_stay AS (
      SELECT
          --  stick to the numerical data
          --  epoch extracts the number of seconds since 1970-01-01 00:00:00-00, we want to extract measurements between
          --  admission and the end of the patients' stay
          --  getting the average number of times each itemid appears in an icustay (filtering only those that are more than 2)
          obs_per_stay.itemid,
          avg(CAST(obs_per_stay.count as BIGNUMERIC)) AS avg_obs
        FROM
          (
            SELECT
                labsstay.itemid,
                count(*) AS count
              FROM
                labsstay
              GROUP BY 1, labsstay.stay_id
          ) AS obs_per_stay
        GROUP BY 1
        HAVING avg(CAST(obs_per_stay.count as BIGNUMERIC)) > 3
    )
    SELECT
        --  we want the features to have at least 3 values entered for the average patient
        d.label,
        count(DISTINCT labsstay.stay_id) AS count,
        a.avg_obs
      FROM
        labsstay
        INNER JOIN physionet-data.mimiciv_hosp.d_labitems AS d ON d.itemid = labsstay.itemid
        INNER JOIN avg_obs_per_stay AS a ON a.itemid = labsstay.itemid
      GROUP BY 1, 3
      HAVING count(DISTINCT labsstay.stay_id) > (
        SELECT
            --  only keep data that is present at some point for at least 25% of the patients, this gives us 45 lab features
            count(DISTINCT labsstay.stay_id) AS count
          FROM
            mimic_iv.ld_labels
      ) * NUMERIC '0.25'

报错信息 #

An expression references labsstay.stay_id which is neither grouped nor aggregated at [46:28]

报错原因与解决方案 #

1. 核心报错原因 #

报错出现在HAVING子句的子查询中：SELECT count(DISTINCT labsstay.stay_id) FROM mimic_iv.ld_labels。这里错误地引用了labsstay.stay_id，但子查询仅从ld_labels表读取数据，没有关联labsstay表，BigQuery无法识别该字段，因此抛出错误。

2. 额外优化点 #

• 时间转换冗余：原转换后的代码将charttime和intime先转DATE再转TIMESTAMP，会丢失时分秒信息，导致时间差计算不准确。直接使用UNIX_SECONDS(l.charttime)即可（MIMIC-IV的对应字段默认是TIMESTAMP类型）。
• 不必要的类型转换：avg(CAST(obs_per_stay.count as BIGNUMERIC))可以简化为avg(obs_per_stay.count)，count(*)的结果是整数，BigQuery的AVG函数可直接处理。

修正后的BigQuery查询 #

CREATE TABLE mimic_iv.ld_commonlabs
AS
WITH labsstay AS (
  SELECT
    l.itemid,
    la.stay_id
  FROM
    physionet-data.mimiciv_hosp.labevents AS l
    INNER JOIN mimic_iv.ld_labels AS la ON la.hadm_id = l.hadm_id
  WHERE 
    l.valuenum IS NOT NULL
    -- 直接用UNIX_SECONDS处理TIMESTAMP，保留时分秒
    AND (UNIX_SECONDS(l.charttime) - UNIX_SECONDS(la.intime)) / (60 * 60 * 24) BETWEEN -1 AND la.los
), avg_obs_per_stay AS (
  SELECT
    obs_per_stay.itemid,
    avg(obs_per_stay.count) AS avg_obs
  FROM (
    SELECT
      labsstay.itemid,
      count(*) AS count
    FROM labsstay
    GROUP BY 1, labsstay.stay_id
  ) AS obs_per_stay
  GROUP BY 1
  HAVING avg(obs_per_stay.count) > 3
)
SELECT
  d.label,
  count(DISTINCT labsstay.stay_id) AS count,
  a.avg_obs
FROM
  labsstay
  INNER JOIN physionet-data.mimiciv_hosp.d_labitems AS d ON d.itemid = labsstay.itemid
  INNER JOIN avg_obs_per_stay AS a ON a.itemid = labsstay.itemid
GROUP BY 1, 3
HAVING count(DISTINCT labsstay.stay_id) > (
  -- 修正为ld_labels.stay_id
  SELECT count(DISTINCT stay_id) FROM mimic_iv.ld_labels
) * 0.25
ORDER BY count DESC;

内容的提问来源于stack exchange，提问作者igodfried