引用SQL SELECT中的其他列 [英] Referencing other columns in a SQL SELECT

问题描述

我在BigQuery中有一个SQL查询:

I have a SQL query in BigQuery:

SELECT
  creator.country,
  (SUM(length) / 60) AS total_minutes,
  COUNT(DISTINCT creator.id) AS total_users,
  (SUM(length) / 60 / COUNT(DISTINCT creator.id)) AS minutes_per_user
FROM
  ...

您可能已经注意到，最后一列等于 total_minutes/total_users .

You may have noticed that the last column is equivalent to total_minutes / total_users.

我尝试了此操作，但没有用:

I tried this, but it doesn't work:

SELECT
  creator.country,
  (SUM(length) / 60) AS total_minutes,
  COUNT(DISTINCT creator.id) AS total_users,
  (total_minutes / total_users) AS minutes_per_user
FROM
  ...

有什么方法可以使它更简单吗?

Is there any way to make this simpler?

推荐答案

不是.也就是说，您不能在同一 SELECT 中的表达式中重复使用列别名.如果确实需要，可以使用子查询或CTE:

Not really. That is, you cannot re-use column aliases in expressions in the same SELECT. If you really want, you can use a subquery or CTE:

SELECT c.*,
       total_minutes / total_users
FROM (SELECT creator.country,
             (SUM(length) / 60) AS total_minutes,
              COUNT(DISTINCT creator.id) AS total_users
      FROM
     ) c;

这篇关于引用SQL SELECT中的其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！