gpt4 book ai didi

sql - 汇总BigQuery中的重复字段

转载 作者:行者123 更新时间:2023-12-02 09:24:27 24 4
gpt4 key购买 nike

我将尝试尽可能清楚地解释我的问题,如果不是,请告诉我。

我有一个表[MyTable]看起来像这样:

----------------------------------------
|chn:integer | auds:integer (repeated) |
----------------------------------------
|1 |3916 |
|1 |4983 |
|1 |6233 |
|1 |1214 |
|2 |1200 |
|2 |900 |
|2 |2030 |
|2 |2345 |
----------------------------------------
Auds始终重复4次。

如果查询 SELECT chn, auds FROM [MyTable] WHERE chn = 1,则会得到以下结果:
-------------------
|Row | chn | auds |
-------------------
|1 |1 |3916 |
|2 |1 |4983 |
|3 |1 |6233 |
|4 |1 |1214 |
-------------------

如果查询 SELECT chn, auds FROM [MyTable] WHERE (chn = 1 OR chn = 2),则会得到以下结果:
-------------------
|Row | chn | auds |
-------------------
|1 |1 |1200 |
|2 |1 |900 |
|3 |1 |2030 |
|4 |2 |2345 |
-------------------

从逻辑上讲,我得到的结果是原来的两倍,但是我想要得到的是 SUM()auds的重复字段 chn = 1chn = 2,或者在视觉上,类似这样的结果:
-------------------
|Row | chn | auds |
-------------------
|1 |3 |5116 |
|2 |3 |5883 |
|3 |3 |8263 |
|4 |3 |3559 |
-------------------

我试图做些事情:
SELECT a1+a2 FROM

(SELECT auds AS a1 FROM [MyTable] WHERE chn = 1),
(SELECT auds AS a2 FROM [MyTable] WHERE chn = 2)

但是我收到以下错误:
Error: Cannot query the cross product of repeated fields a1 and a2.

最佳答案

standard SQL表示这种逻辑要容易得多(取消选中“显示选项”下的“使用旧版SQL”)。这是一个计算auds数组总和的示例:

WITH MyTable AS (
SELECT
1 AS chn,
[2, 3, 4, 5, 6] AS auds
UNION ALL SELECT
2 AS chn,
[7, 8, 9, 10, 11] AS auds
)
SELECT
chn,
(SELECT SUM(aud) FROM UNNEST(auds) AS aud) AS auds_sum
FROM MyTable;
+-----+----------+
| chn | auds_sum |
+-----+----------+
| 1 | 20 |
| 2 | 45 |
+-----+----------+

另一个计算 chn = 1chn = 2的成对和(我根据您的问题认为这是您想要的):
WITH MyTable AS (
SELECT
1 AS chn,
[2, 3, 4, 5, 6] AS auds
UNION ALL SELECT
2 AS chn,
[7, 8, 9, 10, 11] AS auds
)
SELECT
ARRAY(SELECT first_aud + second_auds[OFFSET(off)]
FROM UNNEST(first_auds) AS first_aud WITH OFFSET off)
AS summed_auds
FROM (
SELECT
(SELECT auds FROM MyTable WHERE chn = 1) AS first_auds,
(SELECT auds FROM MyTable WHERE chn = 2) AS second_auds
);
+---------------------+
| summed_auds |
+---------------------+
| [9, 11, 13, 15, 17] |
+---------------------+

编辑:另一个示例,该示例求和所有行中对应的数组元素。这可能不是特别有效,但是它应该产生预期的结果:
WITH MyTable AS (
SELECT
1 AS chn,
[2, 3, 4, 5, 6] AS auds
UNION ALL SELECT
2 AS chn,
[7, 8, 9, 10, 11] AS auds
UNION ALL SELECT
3 AS chn,
[-1, -6, 2, 3, 2] AS auds
)
SELECT
ARRAY(SELECT
(SELECT SUM(auds[OFFSET(off)]) FROM UNNEST(all_auds))
FROM UNNEST(all_auds[OFFSET(0)].auds) WITH OFFSET off)
AS summed_auds
FROM (
SELECT
ARRAY_AGG(STRUCT(auds)) AS all_auds
FROM MyTable
);
+--------------------+
| summed_auds |
+--------------------+
| [8, 5, 15, 18, 19] |
+--------------------+

关于sql - 汇总BigQuery中的重复字段,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38978805/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com