SQL多行作为列(优化) [英] SQL multiple rows as columns (optimizing)
问题描述
我有一个SQL查询,可以给出正确的结果,但执行速度太慢.
I have a SQL query which gives the correct result, but performs too slow.
查询对以下三个表进行操作:
The query operates on the following three tables:
-
customers
包含许多客户数据,例如姓名,地址,电话 等等.为简化表格,我仅使用名称.
customers
contains lots of customer data like name, address, phone etc. To simplify the table i am only using the name.
customdatas
包含某些自定义(非客户)数据. (这
表格是用软件创建的,这就是为什么复数形式是错误的
此表)
customdatas
contains certain custom (not customer) data. (The
tables are created in software, which is why the plural form is wrong
for this table)
customercustomdatarels
将自定义数据与客户相关联.
customercustomdatarels
associates custom data with a customer.
客户
Id Name (many more columns)
-----------------------------------------------------------------------
8053c6f4c5c5c631054ddb13d9186117 MyCustomer ...
2efd2aa5711ddfade1f829b12dd88cf3 CheeseFactory ...
自定义数据
id key
-------------------------------------------------
22deb172c1af6e8e245634a751871564 favoritsport
86eea84d296df9309ad6ff36fd7f856e favoritcheese
customercustomdatarels (客户数据与自定义数据之间的关系-具有相应的值)
customercustomdatarels (relation between customer and custom data - with corresponding value)
customer customdata value
-------------------------------------------------------------------------------------
8053c6f4c5c5c631054ddb13d9186117 22deb172c1af6e8e245634a751871564 cycling
8053c6f4c5c5c631054ddb13d9186117 86eea84d296df9309ad6ff36fd7f856e cheddar
2efd2aa5711ddfade1f829b12dd88cf3 22deb172c1af6e8e245634a751871564 football
2efd2aa5711ddfade1f829b12dd88cf3 86eea84d296df9309ad6ff36fd7f856e mouldy
我想要的是一个基本上由customers
中的所有数据以及可变数量的额外列组成的表,该表对应于customercustomdatarels
中指定的自定义数据.
这些列应在某处定义,因此我创建了下表,该表定义了这些额外的列并将它们映射到customdata
表中的键:
What i want is a table basically consisting of all data in customers
with an variable amount of extra columns, corresponding to the custom data specified in customercustomdatarels
.
These columns should be defined somewhere and I have therefore created the following table which defines such extra columns and maps them to a key in the customdata
table:
test_customkeymapping
colkey customkey
---------------------
1 favoritsport
2 favoritcheese
结果应为:
Name ExtraColumn_1 ExtraColumn_2
---------------------------------------------
CheeseFactory football mouldy
MyCustomer cycling cheddar
(因此 ExtraColumn_1 是客户最喜欢的运动的代名词,而 ExtraColumn_2 是客户最喜欢的奶酪的代名词.)
(ExtraColumn_1 is therefore synonym for a customers' favorite sport and ExtraColumn_2 is a synonym for a customers' favorit cheese.)
通过执行以下查询可获得此结果:
This result is achieved by executing the following query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE
WHEN ckm.colkey = ', colkey, ' THEN
(SELECT value FROM customercustomdatarels ccdr2
LEFT JOIN customdatas cd2
ON cd2.id = ccdr2.customdata
WHERE cd2.key = ckm.customkey AND c.Id = ccdr2.customer)
END) AS ', CONCAT('`ExtraColumn_', colkey, '`'))
) INTO @sql
FROM test_customkeymapping;
SET @sql = CONCAT('SELECT c.Name, ', @sql, '
FROM customers c
LEFT JOIN customercustomdatarels ccdr
ON c.Id = ccdr.customer
LEFT JOIN customdatas cd
ON cd.Id = ccdr.customdata
LEFT JOIN test_customkeymapping ckm
ON cd.key = ckm.customkey
GROUP BY c.Id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
这有效.但是太慢了(对于7000个客户,它需要约10秒). 该查询受此问题中的解决方案的影响很大: MySQL将多行作为列联接
This works. But is too slow (for 7000 customers it takes ~10 seconds). The query was greatly influenced by the solution in this question: MySQL Join Multiple Rows as Columns
如何优化此查询?
推荐答案
我不明白为什么您在group_concat()
语句中使用子查询.这样不会生成您真正想要运行的代码吗?
I don't understand why you are using a subquery in the group_concat()
statement. Wouldn't this generate the code that you really want to run?
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN ckm.colkey = ', colkey, ' THEN ccd.value END) AS ',
CONCAT('ExtraColumn_', colkey, ''))
) INTO @sql
FROM test_customkeymapping;
SET @sql = CONCAT('SELECT c.Name, ', @sql, '
FROM customers c
LEFT JOIN customercustomdatarels ccdr
ON c.Id = ccdr.customer
LEFT JOIN customdatas cd
ON cd.Id = ccdr.customdata
LEFT JOIN test_customkeymapping ckm
ON cd.key = ckm.customkey
GROUP BY c.Id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
注意:这未经测试,但是想法是相同的.在您的工作中使用主from
语句中的值,而不要使用一些多余的,不必要的子查询中的值.
Note: This is untested, but the idea is the same. Use the values from the main from
statement for your work rather than the values from some extra, unnecessary subquery.
这篇关于SQL多行作为列(优化)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!