sql—从不相关的不同表中选择2个值,并使用结果bigquery执行操作

hsgswve4  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(229)

我需要一些帮助与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

有人能帮我做到吗?提前谢谢!

w7t8yxp5

w7t8yxp51#

使用子查询:

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;

相关问题