如何修改Cube.js Schema解决GROUP BY相关SQL错误？

问题场景 #

使用Cube.js定义avg度量时，当查询包含Comments.type维度，Cube.js生成的SQL会抛出GROUP BY缺失错误，具体信息、原Schema及查询如下：

原Cube Schema代码 #

const schemaName = 'my_schema'

cube('Posts', {
    sql: `SELECT * FROM ${schemaName}.posts`,
    joins: {
        Comments: {
            relationship: 'hasMany',
            sql: `${CUBE}.id = ${Comments}.post_id`,
        },
    },
    measures: {
        count: {
            sql: 'id',
            type: 'count',
        },
        avgCommentsCount: {
            sql: `${Comments.count} / ${CUBE.count}`,
            type: 'avg',
        },
    },
    dimensions: {
        id: {
            sql: 'id',
            type: 'string',
            shown: true,
            primaryKey: true,
        },
    },
})

cube('Comments', {
    sql: `SELECT * FROM ${schemaName}.comments`,
    joins: {
        Posts: {
            relationship: 'belongsTo',
            sql: `${CUBE}.post_id = ${Posts}.id`,
        },
    },
    measures: {
        count: {
            sql: 'id',
            type: 'count',
        },
    },
    dimensions: {
        id: {
            sql: 'id',
            type: 'string',
            shown: true,
            primaryKey: true,
        },
        type: {
            sql: 'type',
            type: 'string',
        },
    },
})

执行的JSON查询 #

{
  "dimensions": [
    "Comments.type"
  ],
  "order": {
    "Posts.avgCommentsCount": "desc"
  },
  "measures": [
    "Posts.avgCommentsCount"
  ]
}

抛出的错误 #

column "q_0.comments__type" must appear in the GROUP BY clause or be used in an aggregate function

生成的SQL（缺失GROUP BY） #

SELECT
  q_0."comments__type",
  avg(
    "comments__count" / "posts__count"
  ) "posts__avg_powers_count"
FROM
  (
    SELECT
      "main__comments".type "comments__type",
      count("main__comments".id) "comments__count"
    FROM
      my_schema.users AS "main__posts"
      LEFT JOIN my_schema.powers AS "main__comments" ON "main__posts".id = "main__comments".userid
    GROUP BY
      1
  ) as q_0
  INNER JOIN (
    SELECT
      "keys"."comments__type",
      count(
        "posts_key__posts".id
      ) "posts__count"
    FROM
      (
        SELECT
          DISTINCT "posts_key__comments".type "comments__type",
          "posts_key__posts".id "posts__id"
        FROM
          my_schema.users AS "posts_key__posts"
          LEFT JOIN my_schema.powers AS "posts_key__comments" ON "posts_key__posts".id = "posts_key__comments".userid
      ) AS "keys"
      LEFT JOIN my_schema.users AS "posts_key__posts" ON "keys"."posts__id" = "posts_key__posts".id
    GROUP BY
      1
  ) as q_1 ON (
    q_0."comments__type" = q_1."comments__type"
    OR (
      q_0."comments__type" IS NULL
      AND q_1."comments__type" IS NULL
    )
  )
-- 缺失的GROUP BY：
-- GROUP BY
--  1
ORDER BY
  2 DESC
LIMIT
  10000

修复方案 #

问题根源在于原avgCommentsCount度量是将两个聚合结果相除后再取avg，Cube.js无法自动推断顶层需要按Comments.type分组。以下是三种可行修复方式：

方案1：明确指定度量关联的维度 #

修改Posts中的avgCommentsCount度量，添加dimensions字段明确关联Comments.type，让Cube.js自动生成对应的GROUP BY：

avgCommentsCount: {
    sql: `${Comments.count}`,
    type: 'avg',
    dimensions: [Comments.type]
}

如果需要更精准的单帖评论数计算，也可以用子查询实现：

avgCommentsCount: {
    sql: `(SELECT COUNT(c.id) FROM ${schemaName}.comments c WHERE c.post_id = ${CUBE}.id)`,
    type: 'avg',
    dimensions: [Comments.type]
}

方案2：将度量移至Comments Cube #

若需求是按评论类型分组，计算该类型评论对应帖子的平均评论数，可将度量定义在Comments Cube中：

cube('Comments', {
    // ... 原有代码
    measures: {
        // ... 原有count度量
        avgCommentsPerPost: {
            sql: `${count} / ${Posts.count}`,
            type: 'number',
            dimensions: [type]
        }
    }
})

查询时改用Comments.avgCommentsPerPost替代原Posts.avgCommentsCount。

方案3：使用drillMembers指定分组维度 #

在Posts的avgCommentsCount度量中添加drillMembers，告诉Cube.js该度量需要按指定维度分组：

avgCommentsCount: {
    sql: `${Comments.count} / ${CUBE.count}`,
    type: 'avg',
    drillMembers: [Comments.type]
}

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