我使用以下SQL查询来提取按RowDate分组的表(table1(中的行数:
SELECT RowDate AS Date,
Count(RowDate) as NumberRows FROM [project:dataset.table1] GROUP BY Date ORDER BY Date
这给了我一个表格,显示:
Row Date NumberRows
1 2017-01-01 54
2 2017-01-02 57
3 2017-01-03 46
4 2017-01-04 32
5 2017-01-05 28
是否可以在此查询的数据集中包含多个表(甚至在项目级别更好(,以显示每个 RowDate 的行数的单独列(所有表中的公共列(?
如果是这样,是否可以在不在查询中显式命名表的情况下完成此操作?
任何帮助将不胜感激。
谢谢。
使用标准 SQL,您可以执行以下操作:
SELECT RowDate AS Date,
select row_date, sum(t1_rows) as t1_rows, sum(t2_rows) as t2_rows, . . .
from ((select rowdate, count(*) as t1_rows, 0 as t2_rows, . . .
from table1
group by row_date
) union all
(select rowdate, 0, count(*), . . .
from table2
group by row_date
) union all
. . .
) t
group by rowdate
order by rowdate;
如果由于某种原因您仍在使用 BigQuery Legacy SQL - 您应该为此使用TABLE_QUERY()
- 请参阅下面的示例
#legacySQL
SELECT
RowDate,
COUNT(1) AS NumberRows
FROM TABLE_QUERY([project:dataset], 'true')
GROUP BY RowDate
ORDER BY RowDate
同时,请查看 -Wildcard Tables
- BigQuery Standard SQL 的功能 - 并Migrating to Standard SQL