My如何创建表示动态透视的视图

Question

我有两个数据库表customers其中包含有关具有如下方案的客户的数据：

mysql> SELECT * FROM customers;
customer_id created_at              partner_id
1           "2019-08-20 09:17:58"   cats
2           "2019-09-12 11:46:37"   dogs

以及customers_facts以fact_name和相应fact_value的形式保留客户事实。

mysql> SELECT * FROM customers_facts;
customer_id fact_name   fact_value
1,          name        Milton
1           city        Milan
2           surname     Bloom
2           name        Orlando

我想创建一个数据透视表，该表在每一行中都有一个客户，并且每个事实都作为单独的列。像这样：

mysql> SELECT * FROM pivot_table;
customer_id created_at              partner_id  name    city    surname
1           "2019-08-20 09:17:58"   cats        Milton  Milan   
2           "2019-09-12 11:46:37"   dogs        Orlando         Bloom

我找到了一个脚本，允许我创建这样的表：

SET @sql = '';
SELECT
@sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
SELECT
DISTINCT
CONCAT(
'MAX(IF(cf.fact_name = ''',
fact_name,
''', cf.fact_value, NULL)) AS ''',
fact_name,
''''
) as output
FROM
customers_facts
) as temp;
SET @sql = CONCAT('SELECT c.customer_id, c.created_at, c.partner_id, ', @sql, ' 
FROM customers c
LEFT JOIN customers_facts AS cf 
ON cf.customer_id = c.customer_id
GROUP BY c.customer_id, c.created_at, c.partner_id');

但我有一个如何做到这一点的问题：

a) 我将能够查询数据透视表

b)当我在这两个原始表之一中添加新条目/更新旧条目时，数据透视表将被更新

如何解决？可能吗？

Answer 1

请考虑以下事项：

DROP TABLE IF EXISTS customers;
CREATE TABLE customers
(customer_id SERIAL PRIMARY KEY
,created_at DATETIME NOT NULL
,partner_id INT NOT NULL
);
INSERT INTO customers VALUES
(1,"2019-08-20 09:17:58",108),
(2,"2019-09-12 11:46:37",110);
DROP TABLE IF EXISTS customers_facts ;
CREATE TABLE customers_facts 
(customer_id INT NOT NULL
,fact_name  VARCHAR(20) NOT NULL
,fact_value VARCHaR(20) NOT NULL
,PRIMARY KEY(customer_id,fact_name)
);
INSERT INTO customers_facts VALUES
(1,'name','Milton'),
(1,'city','Milan'),
(2,'surname','Bloom'),
(2,'name','Orlando');

现在我们可以按照您描述的方式创建一个视图...

DROP VIEW IF EXISTS my_pivot;
CREATE VIEW my_pivot AS
SELECT c.customer_id
, c.created_at
, c.partner_id
, MAX(CASE WHEN fact_name = 'name' THEN fact_value END) name
, MAX(CASE WHEN fact_name = 'surname' THEN fact_value END) surname
, MAX(CASE WHEN fact_name = 'city' THEN fact_value END) city
FROM customers c
LEFT 
JOIN customers_facts f
ON f.customer_id = c.customer_id
GROUP
BY c.customer_id;

我们可以通过一个简单的查询来询问这个 VIEW - 例如SELECT customer_id FROM my_pivot WHERE name = 'Milton'，但是，这不能使用索引，因此效率不高。

此外，由于我们创建视图的方式，它无法更新......

UPDATE my_pivot SET name = 'Leonardo' WHERE customer_id = 1;
ERROR 1288 (HY000): The target table my_pivot of the UPDATE is not updatable

但是，如果我们创建视图的方式略有不同，那么它可以更新......

DROP VIEW IF EXISTS my_pivot;
CREATE VIEW my_pivot AS
SELECT c.customer_id
, c.created_at
, c.partner_id
, name.fact_value name
, surname.fact_value surname
, city.fact_value city
FROM customers c
LEFT 
JOIN customers_facts name
ON name.customer_id = c.customer_id 
AND name.fact_name = 'name'
LEFT
JOIN customers_facts surname
ON surname.customer_id = c.customer_id 
AND surname.fact_name = 'surname'
LEFT
JOIN customers_facts city
ON city.customer_id = c.customer_id 
AND city.fact_name = 'city';
UPDATE my_pivot SET name = 'Leonardo' WHERE customer_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
SELECT * FROM customers_facts;
+-------------+-----------+------------+
| customer_id | fact_name | fact_value |
+-------------+-----------+------------+
|           1 | city      | Milan      |
|           1 | name      | Leonardo   |
|           2 | name      | Orlando    |
|           2 | surname   | Bloom      |
+-------------+-----------+------------+

。但这仍然不能使用索引。

编辑：要回答问题下方评论中提出的问题，您可以做...

SELECT customer_id 
FROM customers_facts 
WHERE 
( fact_name,fact_value ) IN (('name','Orlando'),('surname','Bloom')) 
GROUP 
BY customer_id 
HAVING COUNT(*) = 2;

。虽然我认为 MySQL 在这种情况下不能使用索引，所以手写版本可能会更好......

SELECT customer_id 
FROM customers_facts 
WHERE 
( fact_name = 'name'
AND fact_value = 'Orlando'
)
OR 
( fact_name = 'surname'
AND fact_value = 'Bloom'
)
GROUP 
BY customer_id HAVING COUNT(*) = 2;