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屋！