代码之家 › 专栏 › 技术社区 › Vémundr

具有聚合和的Mysql注释表

sql mysql

Vémundr · 技术社区 · 7 年前

我有两张桌子

t1
alias_id (string, unique)
finished (datetime)
sum (float)

t2
alias_id (string)
sum (float)

表中包含付款,每个表大约有800k条记录。t1只包含一次付款,而t2可以有多个具有相同别名\u id的记录—因为某些付款可以包含多个事务。

在Excel中这样做是可行的,但是很痛苦,需要4个小时。我尝试将表上传到mysql并对其运行查询,但惊讶地发现,完成这些表大约需要8个小时。

查询如下。

SELECT 
    s.alias_id AS id, 
    s.finished AS finished, 
    s.sum AS sum, 
    Sum(b.sum_aggr) AS b_sum 
FROM report.rep1 s 
LEFT  JOIN 
(    SELECT alias_id, SUM(sum) AS sum_aggr    
    FROM   report.rep2
    GROUP  BY 1    
) b 
ON b.alias_id = s.alias_id 
GROUP BY 1, 2, 3;

第一:

CREATE TABLE `rep1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `corp_client_id` longtext,
  `agr_name` longtext,
  `client_id` longtext,
  `order_id` longtext,
  `alias_id` longtext,
  `due` longtext,
  `finished` longtext,
  `sum` double NOT NULL,
  `currency` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=720886 DEFAULT CHARSET=utf8

第二:

CREATE TABLE `rep2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` longtext,
  `contract` longtext,
  `contract_start_dt` longtext,
  `contract_end_dt` longtext,
  `country` longtext,
  `provider` longtext,
  `date` longtext,
  `alias_id` longtext,
  `transaction_id` longtext,
  `payment_transaction` longtext,
  `transaction_type` longtext,
  `sum` double NOT NULL,
  `transaction_type_name` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=655351 DEFAULT CHARSET=utf8

1 回复 | 直到 7 年前

Madhur Bhaiya 7 年前

如果你想比较和是否匹配,你可以简单的做一个 left join 在桌子之间 alias_id . 现在,只需计算 SUM

请尝试以下操作:

SELECT 
    s.alias_id AS id, 
    s.finished AS finished, 
    s.sum AS sum, 
    SUM(b.sum) AS b_sum 
FROM report.rep1 AS s 
LEFT  JOIN report.rep2 AS s2 ON s2.alias_id = s.alias_id 
GROUP BY s.alias_id, s.finished, s.sum

编辑: 根据观察 OP's comments ,那个 别名\u id 别名\u id 字段为长文本类型;它将需要适当的索引,否则无论发生什么查询都会很慢。现在,田野 longtext 无法索引数据类型;所以你需要先把它们转换成 varchar 数据类型。

ALTER TABLE `rep1` MODIFY COLUMN `alias_id` VARCHAR(255);
ALTER TABLE `rep2` MODIFY COLUMN `alias_id` VARCHAR(255);

您可以按如下方式在两个表上添加索引:

ALTER TABLE `rep1` ADD INDEX alias_id (`alias_id`);
ALTER TABLE `rep2` ADD INDEX alias_id (`alias_id`);

如果 别名\u id 将会是独一无二的 rep1 ,可以使用以下语句(而不是上面的第一个语句):

ALTER TABLE `rep1` ADD UNIQUE alias_id (`alias_id`);