mysql - BigQuery 和 NOT IN

Question

抱歉我的英语不好。希望你明白，我想要什么。

我想要像数据透视表这样的东西(希望它是正确的词)

例如我有一个包含两列的表:userid 和 domain

UserID      Domain
1        |   A
1        |   B
1        |   C
2        |   A
2        |   B
3        |   A
2        |   C

我想要的。我想要一个如下表，它按行提取差异

    A    B     C  
A   0    1     1 
B   0    0     0   
C   0    0     0

如何读取输出？

例如第一行(0,1,1)想象一下所有访问域 A 的用户(在我们的例子中是用户 1、用户 2 和用户 3)......所有域 A 访问者都在域 A 上(我想这很清楚)。也访问过域B？不，一个用户(在我们的例子中是用户 3)不在域 B 上。所以我们有一个 1。现在我们检查是否所有域 A 访问者都在域 C 上!在这里，我们还有不在域 C 上的用户。用户 1 和 2 在域 C 上，但用户 3 不在域 C 上，而是在域 A 上。所以我们必须再次写一个 1....

第二行 - 检查域 B 上的哪些用户。用户 1 和用户 2 在域 B 上。他们在哪里也在域 A 上？是的...两者...所以我们必须记下一个 0。用户 1 和用户 2 在域 B 上？是的...所以 0。在域 C 上？是的......两者......所以我们必须再次写一个零。

第三行 - 检查域 C在域 C 上，我们有访问者 1 和 2。他们都访问了域 A，所以我们有一个零...都访问了域 B？是的，也是零，最后一个条目很清楚，因为它们来自域 C.....

长话短说:我想提取每个域与其他域相比的所有独占访问者...

自从 2 天以来，我一直在为左连接和 case when 等等而苦苦挣扎......没有任何效果。

有没有人提出他们的建议？真的很有帮助。是的，我有 3 个以上的域。我有大约 200 个!

Answer 1

非常非常大的查询 :)，但它正在工作

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;