检查每个组的不同值的存在 [英] Check existence of distinct values for each group

问题描述

编辑：

假设我在MySQL中有以下表格：

 `pv_name` varchar（60）COLLATE utf8mb4_unicode_ci NOT NULL，
`time_stamp` bigint（20）UNSIGNED NOT NULL，
`value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin，
 PRIMARY KEY（`pv_name`，`time_stamp`）
）ENGINE = InnoDB; 
  

我可以找到每个 pv_name 在这个表格中使用以下查询比一个截然不同的值：

  SELECT events.pv_name 
 FROM events 
 GROUP BY events.pv_name 
 HAVING COUNT（DISTINCT events.value）> 1; 
  

问题是这个查询效率不高。它计算了所有不同的值，而不是在找到多个值后停止。

一个建议如下：

  SELECT events.pv_name 
 FROM events 
 GROUP BY events.pv_name 
 HAVING MIN（events.value）< MAX（events.value）; 
  

如果索引包含 value 。但是， value 是一个文本列，所以它不能。

是否有另一种方法可以使这个搜索更多有效？一些相关的子查询的形式也许？我希望继续使用MySQL，但是如果在另一个数据库服务器中有一个功能可以帮助我做到这一点，那么我可能会考虑迁移它。 解决方案

我相信以下可能工作？可以改进吗？

   - 从每个`pv_name`的`events`表中选择一个非空值` `。 
 CREATE TEMPORARY TABLE single_values（PRIMARY KEY（pv_name））ENGINE =内存AS（
 SELECT events.pv_name，events.value 
 FROM events 
 WHERE events.value IS NOT NULL 
 GROUP BY events.pv_name）; 
 
  - 查找每个`pv_name`的`value`与`single_values`中的值不同。 
  - 这是一个相关的子查询。 
 SELECT single_values.pv_name 
 FROM single_values 
 WHERE 1 =（
 SELECT 1 
 FROM events 
 WHERE events.pv_name = single_values.pv_name 
 AND events.value<> single_values.value 
 AND events.value IS NOT NULL 
 LIMIT 1）; 
  

EDITED:

Suppose I have the following table in MySQL:

CREATE TABLE `events` (
`pv_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
`time_stamp` bigint(20) UNSIGNED NOT NULL,
`value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
PRIMARY KEY (`pv_name`, `time_stamp`)
) ENGINE=InnoDB;

I can find each pv_name that has more than one distinct value in this table using the following query:

SELECT events.pv_name
FROM events
GROUP BY events.pv_name
HAVING COUNT(DISTINCT events.value) > 1;

The issue is that this query is not efficient. It counts all of the distinct values instead of stopping after finding more than one.

One suggestion has been the following:

SELECT events.pv_name
FROM events
GROUP BY events.pv_name
HAVING MIN(events.value) < MAX(events.value);

This is efficient if the index includes value. However, value is a text column so it can not.

Is there another approach that would make this search more efficient? Some form of correlated subquery perhaps? I would like to stay with MySQL, but if there is a feature in another database server that would help this I might consider moving to it.

解决方案

I believe the following may work? Can it be improved upon?

-- Chooses a single non null `value` from the `events` table for each `pv_name`.
CREATE TEMPORARY TABLE single_values ( PRIMARY KEY (pv_name) ) ENGINE=Memory AS (
SELECT events.pv_name, events.value
FROM events
WHERE events.value IS NOT NULL
GROUP BY events.pv_name );

-- Finds each `pv_name` that has a `value` different than the one for it in `single_values`.
-- This is a correlated subquery.
SELECT single_values.pv_name
FROM single_values
WHERE 1 = (
SELECT 1
FROM events
WHERE events.pv_name = single_values.pv_name
AND events.value <> single_values.value
AND events.value IS NOT NULL
LIMIT 1 );

这篇关于检查每个组的不同值的存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！