mysql - 从已过滤的查询中删除 mysql 行

Question

我需要从包含 100,000 行的 mysql 数据库表中执行 3 个不同的查询。用例将是“显示所有内容”、“显示所有内容并删除重复的“值””以及“显示在时间范围内仅与查询的第一个值相比发生更改的值”

“显示所有内容”

mysql> select param_id,time,value  
       from PARAMETERS 
       where param_id like 'point_%' and 
       time >= '2015-352T01:56:52.270' 
       and time <= '2015-360T07:20:32.010' 
       order by param_id,time; 
+----------+-----------------------+------------+
| param_id | time                  | value      |
+----------+-----------------------+------------+
| point_x  | 2015-352T01:56:52.270 | 0.0        |
| point_x  | 2015-360T07:20:32.010 | 1.57       |
| point_x  | 2015-360T07:20:32.010 | 1.57       |
| point_y  | 2015-352T01:56:52.270 | -0.5       |
| point_y  | 2015-352T01:56:55.857 | -0.5       |
| point_y  | 2015-360T07:20:32.010 | -0.5       |
| point_y  | 2015-360T07:20:32.010 | -0.5       |
| point_z  | 2015-352T01:56:52.270 | -9.703     |
| point_z  | 2015-360T07:20:32.010 | 0.0        |
| point_z  | 2015-360T07:20:32.010 | 0.0        |
+----------+-----------------------+------------+

“显示所有内容，删除每个 param_id 的重复‘值’”

mysql> select param_id,time,value,count(*) as replicates  
       from PARAMETERS where 
       param_id like 'point_%' and 
       time >= '2015-352T01:56:52.270' and 
       time <= '2015-360T07:20:32.010' 
       group by value,param_id order by param_id,time;
+----------+-----------------------+--------+------------+
| param_id | time                  | value  | replicates |
+----------+-----------------------+--------+------------+
| point_x  | 2015-352T01:56:52.270 | 0.0    |          1 |
| point_x  | 2015-360T07:20:32.010 | 1.57   |          2 |
| point_y  | 2015-352T01:56:55.857 | -0.5   |          4 |
| point_z  | 2015-352T01:56:52.270 | -9.703 |          1 |
| point_z  | 2015-360T07:20:32.010 | 0.0    |          2 |
+----------+-----------------------+--------+------------+

上述查询效果很好

我遇到问题的最后一个查询是“显示所有内容，删除每个 param_id 的重复“值”，除了省略第一个“值”(该值将被视为默认值，不需要显示)”

问题:由于在此时间范围内只有 1 个“值”(-0.5)，因此仅从该查询中省略“point_y”行的查询是什么？

更新

拥有 count(param_id)>1 不起作用...

mysql> select param_id,time,value,count(*) as replicates  
       from PARAMETERS where  
       param_id like 'point_%' and 
       time >= '2015-352T01:56:52.270' and 
       time <= '2015-360T07:20:32.010' 
       group by value,param_id having count(param_id)>1  
       order by param_id,time;
+----------+-----------------------+-------+------------+
| param_id | time                  | value | replicates |
+----------+-----------------------+-------+------------+
| point_x  | 2015-360T07:20:32.010 | 1.57  |          2 |
| point_y  | 2015-352T01:56:55.857 | -0.5  |          4 |
| point_z  | 2015-360T07:20:32.010 | 0.0   |          2 |
+----------+-----------------------+-------+------------+

子选择中“有计数”？也不好...

mysql>  select * from (select param_id,time,value  from PARAMETERS where 
                 param_id like 'point_%' and 
                 time >= '2015-352T01:56:52.270' and 
                 time <= '2015-360T07:20:32.010' 
                 group by value,param_id order by param_id,time) as t1 
        having (count(t1.param_id)>1);
+----------+-----------------------+-------+
| param_id | time                  | value |
+----------+-----------------------+-------+
| point_x  | 2015-352T01:56:52.270 | 0.0   |
+----------+-----------------------+-------+

可能的解决方案(需要优化)

以下联接查询可以工作，因为我可以使用一些服务器端代码，但它会使查询时间加倍。有没有办法优化这个？

mysql> select t1.param_id,t1.time,t1.value,t2.param_id_cnt 
       from (
          select param_id,time,value 
          from PARAMETERS 
               where param_id like 'point_%' and 
               time >= '2015-352T01:56:52.270' and 
               time <= '2015-360T07:20:32.010' 
               group by param_id, value 
               order by param_id,time ) t1 
       join (
          select *,count(X.param_id) as param_id_cnt 
          from (
               select id,param_id,time,value  
               from PARAMETERS 
                    where param_id like 'point_%' and 
                    time >= '2015-352T01:56:52.270' and 
                    time <= '2015-360T07:20:32.010' 
                    group by param_id, 
                    value order by param_id,time ) X 
               group by X.param_id) t2 
        on t1.param_id=t2.param_id;


+----------+-----------------------+--------+--------------+
| param_id | time                  | value  | param_id_cnt |
+----------+-----------------------+--------+--------------+
| point_x  | 2015-352T01:56:52.270 | 0.0    |            2 |
| point_x  | 2015-360T07:20:32.010 | 1.57   |            2 |
| point_y  | 2015-352T01:56:55.857 | -0.5   |            1 |
| point_z  | 2015-352T01:56:52.270 | -9.703 |            2 |
| point_z  | 2015-360T07:20:32.010 | 0.0    |            2 |
+----------+-----------------------+--------+--------------+

Answer 1

首先，您仅从“显示所有内容，删除每个 param_id 的重复“值””查询中获取超过 1 个不同值的 param_id，然后将其与该查询结合起来再次查询过滤后的param_id。

SELECT 
    a.param_id, b.time, b.value
FROM (
    select param_id
    from PARAMETERS 
    where 
       param_id like 'point_%' and 
       time >= '2015-352T01:56:52.270' and 
       time <= '2015-360T07:20:32.010' 
    group by param_id
    having count(distinct value) > 1
    ) a
JOIN (
    select param_id,time,value
    from PARAMETERS 
    where 
       param_id like 'point_%' and 
       time >= '2015-352T01:56:52.270' and 
       time <= '2015-360T07:20:32.010' 
    group by value,param_id order by param_id, time
    ) b ON  
        a.param_id = b.param_id
ORDER BY a.param_id, b.time

评论后编辑:

不幸的是，MySQL 不支持用于定义公共(public)表表达式的 WITH 子句。防止执行同一查询两次的一种方法是创建一个临时表并在该查询中使用它。您可以阅读有关 MySQL 临时表的更多信息 here .

CREATE TEMPORARY TABLE tmp_parameters AS (
    select param_id,time,value
    from PARAMETERS 
    where 
       param_id like 'point_%' and 
       time >= '2015-352T01:56:52.270' and 
       time <= '2015-360T07:20:32.010' 
    group by value,param_id order by param_id, time
);

调整后的查询如下所示:

SELECT 
    a.param_id, b.time, b.value
FROM (
    select param_id
    from tmp_parameters
    group by param_id
    having count(distinct value) > 1
    ) a
JOIN tmp_parameters b ON a.param_id = b.param_id
ORDER BY a.param_id, b.time