非常非常大的查询：），但它正在工作

DROP PROCEDURE IF EXISTS dowhile;
CREATE PROCEDURE dowhile()
BEGIN
    SELECT @domain_arr := CONCAT(GROUP_CONCAT(domain SEPARATOR ','),',') AS domain_arr FROM ( SELECT t1.domain FROM user_domain t1 WHERE 1 GROUP BY t1.domain ) AS tt;
    DROP table IF EXISTS temp_table;
    create temporary table temp_table (
        domain VARCHAR(100) not NULL
    );
    SET @domain_arr_table= @domain_arr;
    WHILE LOCATE(',', @domain_arr_table) > 0 DO
        SET @domain = SUBSTRING(@domain_arr_table,1,LOCATE(',',@domain_arr_table) - 1);
        SET @domain_arr_table= SUBSTRING(@domain_arr_table, LOCATE(',',@domain_arr_table) + 1);
        SET @s= CONCAT('ALTER TABLE temp_table ADD COLUMN ',@domain,' TINYINT DEFAULT 0');
        PREPARE stmt3 FROM @s;
        EXECUTE stmt3;
    END WHILE;
    WHILE LOCATE(',', @domain_arr) > 0 DO
        SET @domain = SUBSTRING(@domain_arr,1,LOCATE(',',@domain_arr) - 1);
        SET @domain_arr= SUBSTRING(@domain_arr, LOCATE(',',@domain_arr) + 1);
        SELECT @user_count := COUNT(*) FROM user_domain WHERE domain=@domain;
        INSERT INTO temp_table (domain) VALUES (@domain);

        SELECT @domains_should_be_1 := CONCAT(GROUP_CONCAT(domain SEPARATOR ','),',') FROM (SELECT domain FROM user_domain WHERE user_id IN (SELECT user_id FROM user_domain WHERE domain=@domain) GROUP BY domain HAVING COUNT(*) < @user_count) AS tt2;
        WHILE LOCATE(',', @domains_should_be_1) > 0 DO
            SET @domain_sb_1 = SUBSTRING(@domains_should_be_1,1,LOCATE(',',@domains_should_be_1) - 1);
            SET @domains_should_be_1= SUBSTRING(@domains_should_be_1, LOCATE(',',@domains_should_be_1) + 1);
            SET @s= CONCAT("UPDATE temp_table SET ",@domain_sb_1,"='1' WHERE domain='",@domain,"'");
            SELECT @s;
            PREPARE stmt3 FROM @s;
            EXECUTE stmt3;
        END WHILE;
    END WHILE;
END;

call dowhile();
SELECT * FROM temp_table;

这里有两个问题
我想提取每个域与其他域相比的所有独占访问者。。。
我想要一张透视表之类的
让我逐一回答你的问题
所以，
如何提取每个域与其他域相比的所有独占访问者。。。
下面是bigquery标准sql，并生成矩阵的展开版本

# standardSQL

WITH `project.dataset.your_table` AS (
  SELECT 1 userid, 'A' domain UNION ALL
  SELECT 1, 'B' UNION ALL
  SELECT 1, 'C' UNION ALL
  SELECT 2, 'A' UNION ALL
  SELECT 2, 'B' UNION ALL
  SELECT 3, 'A' UNION ALL
  SELECT 2, 'C' 
), temp AS (
  SELECT DISTINCT userid, domain
  FROM `project.dataset.your_table`
)
SELECT 
  a.domain domain_a, 
  b.domain domain_b, 
  COUNT(DISTINCT a.userid) - COUNTIF(a.userid = b.userid) count_of_not_in
FROM temp a
CROSS JOIN temp b
GROUP BY a.domain, b.domain
-- HAVING count_of_not_in > 0

这将导致

Row domain_a    domain_b    count_of_not_in  
1   A           A           0    
2   A           B           1    
3   A           C           1    
4   B           A           0    
5   B           B           0    
6   B           C           0    
7   C           A           0    
8   C           B           0    
9   C           C           0

我认为在现实生活中，这个数据中不会有太多的零，所以如果你想压缩这个扁平的版本，只需取消对行的注解 HAVING ... ，因此您将获得“紧凑”版本

Row domain_a    domain_b    count_of_not_in  
1   A           B           1    
2   A           C           1

为了锻炼和享受乐趣，看看下面的另一种方法，它产生完全相同的结果，但方式完全不同

# standardSQL

WITH `project.dataset.your_table` AS (
  SELECT 1 userid, 'A' domain UNION ALL
  SELECT 1, 'B' UNION ALL
  SELECT 1, 'C' UNION ALL
  SELECT 2, 'A' UNION ALL
  SELECT 2, 'B' UNION ALL
  SELECT 3, 'A' UNION ALL
  SELECT 2, 'C' 
), domains AS ( 
  SELECT domain, ARRAY_AGG(DISTINCT userid) users
  FROM `project.dataset.your_table`
  GROUP BY domain
)
SELECT 
  a.domain domain_a, b.domain domain_b, 
  ARRAY_LENGTH(a.users) -
  (SELECT COUNT(1) 
    FROM UNNEST(a.users) user_a 
    JOIN UNNEST(b.users) user_b 
    ON user_a = user_b
  ) count_of_not_in
FROM domains a
CROSS JOIN domains b 
-- ORDER BY a.domain, b.domain

现在，
如何透视以上结果，生成实际矩阵？
理想情况下，无论您通常使用什么可视化工具，都应该在bigquery之外进行数据透视。但是，如果出于任何原因，您希望在bigquery中完成它，那么它是可行的，而且这里有大量的问题与此相关。我最近发布的答案之一是-https://stackoverflow.com/a/50300387/5221944 .
它展示了如何生成/产生pivot查询以获得所需的矩阵
它相对简单，可以作为两个步骤（步骤1-生成透视查询和步骤2-运行生成的查询）手动完成，也可以使用任何 client 你的选择

你不能（轻易地）用矩阵来表示。但您可以将其表示为一个包含三列的表：，count。

with t as (  -- may not be necessary if the rows are already unique
      select distinct userid, domain
      from tab
     )
select t1.domain as domain1, t2.domain as domain2, count(*)
from t t1 join
     t t2
     on t1.userid = t2.userid
group by t1.domain, t2.domain;

除非明确知道所关心的域，否则无法将bigquery中的结果透视到列中。如果愿意，可以将它们聚合到列中。
对于作为列的给定域集，可以使用条件聚合：

with t as (  -- may not be necessary if the rows are already unique
      select distinct userid, domain
      from tab
     )
select t1.domain as domain1,
       sum(case when t2.domain = 'amazon.com' then 1 else 0 end) as amazon,
       sum(case when t2.domain = 'ebay.com' then 1 else 0 end) as ebay,
       sum(case when t2.domain = 'yahoo.com' then 1 else 0 end) as yahoo
from t t1 join
     t t2
     on t1.userid = t2.userid
group by t1.domain, t2.domain;