мы также можем использовать
(.*?\n)*?
для соответствия всем, включая новую строку без жадного
. Это сделает новую строку опцией
(.*?|\n)*?
Я думаю, вы в основном хотите:
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.
Ниже для стандартного 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