从不相关的不同表中选择2个值，并使用结果BigQuery执行操作

Question

我需要一些关于Google Big Query中SQL的帮助。

我有两个表，A和B，我需要从A中选择一个值，从B中选择另一个不相关的值，并执行数学运算。

TABLE A
NAME        |      TIMESTAMP
------------------------------
xyz         |      2013-01-02 
abcd        |      2013-01-02
efgh        |      2012-03-01

TABLE B
CAMPAING    |         DATE      | COST
-------------------------------------------
A          |      2013-01-02    |  10   
B          |      2013-01-02    |  30
C          |      2012-03-01    |  15

我能做的事：

SELECT (COUNT(DISTINCT NAME)
FROM `TABLE A`
WHERE DATE(TIMESTAMP) = '2013-01-02'
UNION DISTINCIT
SELECT (SUM(COST))
FROM `TABLE B`
WHERE date = '2013-01-02'

输出：

ROW |    f0_
----------------
1   |     2
2   |     40

我需要做的是划分这两个价值观。。。40/2

期望输出：

ROW |    f0_
----------------
1   |     20

有人能帮我做到这一点吗？提前感谢！

Answer 1

使用子查询：

SELECT a.cnt, b.cost, b.cost / a.cnt
FROM (SELECT (COUNT(DISTINCT NAME) as cnt
FROM `TABLE A`
WHERE DATE(TIMESTAMP) = '2013-01-02'
) a CROSS JOIN
(SELECT SUM(COST) as cost
FROM `TABLE B`
WHERE date = '2013-01-02'
) b;