Сводная таблица в Google Big Query

мы также можем использовать

(.*?\n)*?

для соответствия всем, включая новую строку без жадного

. Это сделает новую строку опцией

(.*?|\n)*?
0
задан Pranav 13 July 2018 в 19:18
поделиться

2 ответа

Я думаю, вы в основном хотите:

with t as (
      select t.*, row_number() over (order by id) as seqnum
      from t
     )
select t.place,
       max(case when t2.place = 'A' then 1 else 0 end) as A,
       max(case when t2.place = 'B' then 1 else 0 end) as B,
       max(case when t2.place = 'C' then 1 else 0 end) as C,
       max(case when t2.place = 'D' then 1 else 0 end) as D
from t join
     t t2
     on t.id = t2.id and t.seqnum <> t2.seqnum
group by t.place
order by t.place;

Это не совсем тот результат, который у вас есть в вопросе, но он, похоже, логически перекрывает перекрытия. Я не вижу, как у вас есть «A» / «A» с 1, но «C» / «C» с 0.

0
ответ дан Gordon Linoff 17 August 2018 в 12:23
поделиться
  • 1
    Спасибо за этот ответ. Я попробую. & Quot, A & Quot; / & Quot, A & Quot; равен нулю, потому что существует только один идентификатор, уникальный для A i.e - ID = 5, другой идентификатор - совместно с C. Также «C» / «C» равен нулю, потому что нет идентификатора, уникального только для C, он делится с A и B. – Pranav 13 July 2018 в 19:10
  • 2
    Гордон, я только что обновил свой вопрос. – Pranav 13 July 2018 в 19:24

Ниже для стандартного SQL-запроса BigQuery

#standardSQL
WITH self AS (
  SELECT arr[OFFSET(0)] place, COUNT(1) cnt
  FROM (
    SELECT ARRAY_AGG(place) arr, id
    FROM `project.dataset.table`
    GROUP BY id
    HAVING ARRAY_LENGTH(arr) = 1
  )
  GROUP BY place
), pairs AS (
  SELECT id, ARRAY_AGG(place) arr
  FROM `project.dataset.table` 
  GROUP BY id
), flat_matrix AS (
  SELECT place1, place2, COUNT(DISTINCT id) cnt
  FROM pairs, UNNEST(arr) place1, UNNEST(arr) place2
  WHERE place1 <> place2
  GROUP BY 1, 2
  UNION ALL
  SELECT place, place, cnt
  FROM self
)
SELECT place1 place,
  MAX(IF(place2 = 'A', cnt, 0)) AS A,
  MAX(IF(place2 = 'B', cnt, 0)) AS B,
  MAX(IF(place2 = 'C', cnt, 0)) AS C,
  MAX(IF(place2 = 'D', cnt, 0)) AS D 
FROM flat_matrix    

Вы можете протестировать и воспроизвести с использованием фиктивных данных из вашего вопроса, как показано ниже

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'A' place UNION ALL
  SELECT 2, 'B' UNION ALL
  SELECT 1, 'C' UNION ALL
  SELECT 6, 'B' UNION ALL
  SELECT 4, 'D' UNION ALL
  SELECT 5, 'A' UNION ALL
  SELECT 6, 'C' UNION ALL
  SELECT 7, 'A' UNION ALL
  SELECT 8, 'A' UNION ALL
  SELECT 8, 'C'   
), self AS (
  SELECT arr[OFFSET(0)] place, COUNT(1) cnt
  FROM (
    SELECT ARRAY_AGG(place) arr, id
    FROM `project.dataset.table`
    GROUP BY id
    HAVING ARRAY_LENGTH(arr) = 1
  )
  GROUP BY place
), pairs AS (
  SELECT id, ARRAY_AGG(place) arr
  FROM `project.dataset.table` 
  GROUP BY id
), flat_matrix AS (
  SELECT place1, place2, COUNT(DISTINCT id) cnt
  FROM pairs, UNNEST(arr) place1, UNNEST(arr) place2
  WHERE place1 <> place2
  GROUP BY 1, 2
  UNION ALL
  SELECT place, place, cnt
  FROM self
)
SELECT place1 place,
  MAX(IF(place2 = 'A', cnt, 0)) AS A,
  MAX(IF(place2 = 'B', cnt, 0)) AS B,
  MAX(IF(place2 = 'C', cnt, 0)) AS C,
  MAX(IF(place2 = 'D', cnt, 0)) AS D 
FROM flat_matrix
GROUP BY place1
-- ORDER BY place

, с результатом как

Row place   A   B   C   D    
1   A       2   0   2   0    
2   B       0   1   1   0    
3   C       2   1   0   0    
4   D       0   0   0   1    
2
ответ дан Mikhail Berlyant 17 August 2018 в 12:23
поделиться
Другие вопросы по тегам:

Похожие вопросы: