mysql - 创建一个包含生成列的表

Question

我们尝试创建一个包含生成列的表。

请查看这些查询以了解我们当前的表结构:

表Heepsy:

CREATE TABLE `Heepsy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `photo_url` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
  `followers` int(10) DEFAULT NULL,
  `engagement` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51731 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

表 HypeAuditor:

CREATE TABLE `HypeAuditor` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `engaement` decimal(5,2) DEFAULT NULL,
  `Country` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `Country_percentage` int(3) DEFAULT NULL,
  `Interest1` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `Interest1_percentage` int(3) DEFAULT NULL,
  `Interest2` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `Interest2_percentage` int(3) DEFAULT NULL,
  `Interest3` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `Interest3_percentage` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27123 DEFAULT CHARSET=utf8;

table 上忍者外展:

CREATE TABLE `NinjaOutreach` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `fullname` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `photo_url` varchar(300) CHARACTER SET utf8 DEFAULT NULL,
  `followers` int(10) DEFAULT NULL,
  `engagement` decimal(5,2) DEFAULT NULL,
  `city` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `state` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `country` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `category_1` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `category_2` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `category_3` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `category_4` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `category_5` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `category_6` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=296788 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

表组合:

CREATE TABLE `Kombiniert` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `fullname` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `followers_heepsy` int(11) DEFAULT NULL,
  `followers_ninjaoutreach` int(11) DEFAULT NULL,
  `followers_average` int(11) DEFAULT NULL,
  `engagement_heepsy` decimal(5,4) DEFAULT NULL,
  `engagement_ninjaoutreach` decimal(5,4) DEFAULT NULL,
  `engagement_hypeauditor` decimal(5,4) DEFAULT NULL,
  `engagement_average` decimal(5,4) DEFAULT NULL,
  `city` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `state` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `country` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `category1` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `category2` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `category3` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `category4` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `category5` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `category6` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `follower_interest1` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `follower_interest1_share` decimal(5,4) DEFAULT NULL,
  `follower_interest2` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `follower_interest2_share` decimal(5,4) DEFAULT NULL,
  `follower_interest3` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `follower_interest3_share` decimal(5,4) DEFAULT NULL,
  `follower_country` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `follower_country_share` decimal(5,4) DEFAULT NULL,
  `follower_country_total` int(11) DEFAULT NULL,
  `follower_country_engaged` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 我们需要如何更新 SQL 查询来创建具有以下生成列的表 Kombiniert:

followers_average:“followers_heepsy”和“followers_ninjaoutreach”列的平均值，但前提是两者都大于 0。如果只有一列大于 0，则使用该列的值。

平均参与度:“engagement_heepsy”、“engagement_ninjaoutreach”和“engagement_hypeauditor”列的平均值，但前提是全部都大于 0。如果只有两列大于 0，则使用这两列的平均值。如果只有一列大于 0，则使用该列的值。

follower_country_total:follower_average * follower_country_share

follower_country_engagged:Followers_country_total *engagement_average

所有生成的列都应该存储而不是虚拟的？

我们需要如何创建一个 INSERT 查询，它将执行以下操作:

对于来自 HypeAuditor 的每个用户名，在表 Kombiniert 中创建包含以下数据的行:

https://docs.google.com/spreadsheets/d/1qU0WaExjg8cCsA3cdDvKf3UqdLxqXKw_8QnDRLvJwMA/edit?usp=sharing ？

Answer 1

好的，我假设每个表中的每个用户名都有一个连接和一条记录。

    INSERT INTO Kombiniert
(username, fullname, followers_heepsy, followers_ninjaoutreach, followers_average, engagement_heepsy, engagement_ninjaoutreach, engagement_hypeauditor, engagement_average, city, state, country, category1, category2, category3, category4, category5, category6, follower_interest1, follower_interest1_share, follower_interest2, follower_interest2_share, follower_interest3, follower_interest3_share, follower_country, follower_country_share, follower_country_total, follower_country_engaged)
    Select bb.*, follower_country_total*engagement_average as follower_country_engaged from
        (
            Select aa.*, followers_average*follower_country_share as follower_country_total from 
            (
                select 
                h.username,
                nj.fullname,
                hy.followers as followers_heepsy,
                nj.followers as followers_ninjaoutreach,
                case when (hy.followers=0 or hy.followers is null) and nj.followers>0 then nj.followers
                when hy.followers>0 and (nj.followers=0 or nj.followers is null) then hy.followers
                else (hy.followers+nj.followers)/2 end as followers_average,
                hy.engagement/100 as engagement_heepsy,
                nj.engagement/100 as engagement_ninjaoutreach,
                h.engagement/100 as engagement_hypeauditor,
                case    when (hy.engagement/100=0 or hy.engagement is null) and (nj.engagement/100=0 or nj.engagement is null) and h.engagement/100>0 then h.engagement/100
                        when (hy.engagement/100=0 or hy.engagement is null) and nj.engagement/100>0 and (h.engagement/100=0 or h.engagement is null) then nj.engagement/100
                        when hy.engagement/100>0  and (nj.engagement/100=0  or nj.engagement is null) and (h.engagement/100=0 or h.engagement is null) then hy.engagement/100
                        when (hy.engagement/100=0 or hy.engagement is null) and nj.engagement/100>0 and h.engagement/100>0 then (h.engagement/100+nj.engagement/100)/2
                        when hy.engagement/100>0  and nj.engagement/100>0 and (h.engagement/100=0 or h.engagement is null) then (hy.engagement/100+nj.engagement/100)/2
                        when hy.engagement/100>0  and (nj.engagement/100=0 or nj.engagement is null) and h.engagement/100>0 then (h.engagement/100+nj.engagement/100)/2
                        else (hy.engagement/100+nj.engagement/100+h.engagement/100)/3 end as engagement_average,
                nj.city,
                nj.state,
                nj.country ,
                nj.category_1,
                nj.category_2,
                nj.category_3,
                nj.category_4,
                nj.category_5,
                nj.category_6,
                h.interest1,
                h.Interest1_percentage/100 as follower_interest_share1,
                h.interest2,
                h.Interest2_percentage/100 as follower_interest_share2,
                h.interest3,
                h.Interest3_percentage/100 as follower_interest_share3,
                h.country as follower_country,
                h.country_percentage/100 as follower_country_share


                from HypeAuditor h
                left join Heepsy hy on h.username = hy.username
                left join NinjaOutreach nj on nj.username=h.username
                ) aa 
            ) bb