Bigquery: объединение 2 таблиц, в одной из которых есть повторяющиеся записи, а в другой - count ()

Я хочу присоединиться к таблицам после невложенных массивов в таблице: 1, но записи дублируются после объединения из-за невложенности.

Таблица 1

| a | d.b | d.c |
-----------------
| 1 |  5  |  2  |
-   -------------
|   |  3  |  1  |
-----------------
| 2 |  2  |  1  |

Таблица 2

| a |  c  |  f  |
-----------------
| 1 |  12 | 13  |
-----------------
| 2 | 14  | 15  |

Я хочу присоединиться к таблице 1 и 2 на a, но мне также нужно получить вывод:

| a | d.b | d.c |  f  |  h  |  Sum(count(a))
---------------------------------------------
| 1 |  5  |  2  | 13  | 12  |
-   -------------     -     -       1
|   |  3  |  1  |     |     |
---------------------------------------------
| 2 |  2  |  1  | 15  | 14  |       1

a можно повторить в таблице 2, для этого мне нужно подсчитать (а), а затем выбрать сумму после соединения.

Моя проблема заключается в том, что когда я присоединяюсь, мне нужно, чтобы вложенная и повторяющаяся запись была такой же, как в первой таблице, но при использовании агрегации для получения суммы я не могу группировать по структуре или массивам, поэтому я сначала использую записи, а затем использую UNNEST функция, но также была проблема в сумме.

SELECT
 t1.a,
 t2.f,
 t2.h,
 ARRAY_AGG(DISTINCT(t1.db)) as db,
 ARRAY_AGG(DISTINCT(t1.dc)) as dc,
 SUM(t2.total) AS total
FROM (
  SELECT
    a,
    d.b as db,
    d.c as dc
  FROM
    `table1`,
    UNNEST(d) AS d,
  ) AS t1
LEFT JOIN (
  SELECT
    a,
    f,
    h,
    COUNT(*) AS total,
  FROM
    `table2`
  GROUP BY
    a,f,h) AS t2
ON
  t1.a = t2.a
GROUP BY
  1,
  2,
  3

Примечание: ошибка в общем количестве, после суммы оно намного выше, чем ожидалось, все остальные данные верны.

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
89
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я предполагаю, что ваша таблица 2 не уникальна для столбца a.

Предположим, что таблица 2 выглядит так:

а с ф 1 12 13 2 14 15 1 100 101

Есть две строки, где a есть 1. Поскольку b и f разные, группировка не решает эту проблему ( GROUP BY a,f,h) AS t2), а counts(*) as total по одному для каждой строки.

а с ф общее 1 12 13 1 2 14 15 1 1 100 101 1

На следующем шаге вы присоединяете эту таблицу к своей таблице 1. Строки таблицы 1 со значением 1 в столбце a дублируются, поскольку в таблице 2 есть две записи. Это приводит к тому, что сумма завышена.

Вместо того, чтобы распаковывать таблицы, я рекомендую следующий подход:

-- Creating of sample data as given:
with tbl_A as (select  1 a, [struct(5 as b,2 as c),struct(3,1)] d union all select 2,[struct(2,1)] union all select null,[struct(50,51)]),
tbl_B as (select 1 as a,12 b, 13 f union all select 2,14,15 union all select 1,100,101 union all select null,500,501)

-- Query:

select *
from tbl_A A
left join
(Select a,array_agg(struct(b,f)) as B, count(1) as counts from tbl_B group by 1) B
on ifnull(A.a,-9)=ifnull(B.a,-9)

Ответ вырван из контекста. Моя точка зрения заключается в том, что количество a в таблице 2 слишком велико, чем фактическое количество. Я предполагаю, что дублирование в счете происходит при соединении, все остальные данные верны.

Mohamed Haydar 12.11.2022 15:11

Как использовать WITH и UNION, если моя таблица содержит миллионы записей? Зная, что записи имеют разный тип в каждом столбце? @Самуэль

Mohamed Haydar 13.11.2022 08:48

@MohamedHaydar обе таблицы в заявлении WITH являются образцами таблиц с предоставленными вами данными.

Samuel 13.11.2022 09:29

Хорошо, я предоставил образцы, но мои данные состоят из миллионов записей, как мне реализовать мой запрос?

Mohamed Haydar 14.11.2022 08:32

Пожалуйста, замените tbl_A на yourdataset.table1 и tbl_B на yourdataset.table2.

Samuel 14.11.2022 12:40

Думаю, я нашел проблему, но другая ошибка, с которой я столкнулся, заключается в том, что когда я UNNEST NULL RECORD, вся объединенная запись удаляется из данных.

Mohamed Haydar 14.11.2022 15:21

Для присоединения я заменяю null на значение -9.

Samuel 16.11.2022 08:37

Другие вопросы по теме