找到你要的答案

Q:Comparing values between rows

Q:行间值比较

I have a table of objects that has values associated to it. The value depends on what model is being used to calculate it.

To model this relation I created a connection table with the following schema:

CREATE TABLE object_value
(
  object_id integer NOT NULL,
  model character varying(20) NOT NULL,
  value integer,
  CONSTRAINT object_value_pkey PRIMARY KEY (object_id, model),
  CONSTRAINT object_value_object_id_fkey FOREIGN KEY (leg_id)
  REFERENCES object (id) MATCH SIMPLE
)

Some example data:

object_id; model; value
116288;"model_2.2.3";7028
116288;"model_2.2.4";7028
116289;"model_2.2.3";5535
116289;"model_2.2.4";5530
116290;"model_2.2.3";3529
116290;"model_2.2.4";3530

I have been querying this table to look for differences in model calculations as follow:

SELECT DISTINCT ON (object_id) *
          FROM object_value AS a INNER JOIN object_value AS b USING(object_id)
          WHERE a.value!=b.value;   

This has been working fine since I have only two different models but when adding data from a third model version the query will not work as intended. And joing the the table to itself again will leave me with the same issue when adding a fourth model.

Is there a different concept of comparing values between rows that I'm not thinking of?

我有一个有关联的值的对象表。该值取决于使用什么模型计算它。

为了模拟这种关系,我创建了一个连接表:

CREATE TABLE object_value
(
  object_id integer NOT NULL,
  model character varying(20) NOT NULL,
  value integer,
  CONSTRAINT object_value_pkey PRIMARY KEY (object_id, model),
  CONSTRAINT object_value_object_id_fkey FOREIGN KEY (leg_id)
  REFERENCES object (id) MATCH SIMPLE
)

一些例子数据:

object_id; model; value
116288;"model_2.2.3";7028
116288;"model_2.2.4";7028
116289;"model_2.2.3";5535
116289;"model_2.2.4";5530
116290;"model_2.2.3";3529
116290;"model_2.2.4";3530

我一直在查询这个表,以寻找模型计算中的差异如下:

SELECT DISTINCT ON (object_id) *
          FROM object_value AS a INNER JOIN object_value AS b USING(object_id)
          WHERE a.value!=b.value;   

这一直很好,因为我只有两个不同的模式,但当添加数据从三分之一模型版本的查询将无法工作的预期。并加入表本身又会给我同样的问题时,添加四分之一的模型。

是否有一个不同的概念比较行之间的价值,我不想?

answer1: 回答1:

You can select the differences in one row using aggregates, e.g.:

insert into object_value values -- third model added
(116290, 'model_2.2.5', 3600);

select 
    object_id, 
    string_agg(model, ' : ') as model, 
    string_agg(value::text, ' : ') as value
from object_value
group by 1
order by 1

 object_id |                  model                  |       value        
-----------+-----------------------------------------+--------------------
    116288 | model_2.2.3 : model_2.2.4               | 7028 : 7028
    116289 | model_2.2.3 : model_2.2.4               | 5535 : 5530
    116290 | model_2.2.3 : model_2.2.4 : model_2.2.5 | 3529 : 3530 : 3600
(3 rows)

您可以使用聚合来选择一行中的差异,例如:

insert into object_value values -- third model added
(116290, 'model_2.2.5', 3600);

select 
    object_id, 
    string_agg(model, ' : ') as model, 
    string_agg(value::text, ' : ') as value
from object_value
group by 1
order by 1

 object_id |                  model                  |       value        
-----------+-----------------------------------------+--------------------
    116288 | model_2.2.3 : model_2.2.4               | 7028 : 7028
    116289 | model_2.2.3 : model_2.2.4               | 5535 : 5530
    116290 | model_2.2.3 : model_2.2.4 : model_2.2.5 | 3529 : 3530 : 3600
(3 rows)
answer2: 回答2:

If you want to find objects where the model values differ, then use aggregation, not joins:

select ov.object_id
from object_value ov
group by ov.object_id
having min(ov.value) <> max(ov.value);

If you want to see the list of models and/or values, then you can use string_agg(). If you want to see all the details, then a similar query using window functions should work:

select ov.*
from (select ov.*,
             min(ov.value) over (partition by object_id) as minvalue,
             max(ov.value) over (partition by object_id) as maxvalue
      from object_value ov
     ) ov
where minvalue <> maxvalue;

如果要查找模型值不同的对象,则使用聚合,而不是连接:

select ov.object_id
from object_value ov
group by ov.object_id
having min(ov.value) <> max(ov.value);

如果你想看到模型和/或值的列表,然后你可以用string_agg()。如果你想看到所有的细节,那么一个类似的查询窗口功能应该工作:

select ov.*
from (select ov.*,
             min(ov.value) over (partition by object_id) as minvalue,
             max(ov.value) over (partition by object_id) as maxvalue
      from object_value ov
     ) ov
where minvalue <> maxvalue;
sql  postgresql