The source table is as follows:

CREATE TABLE public.psi_humsensor ( id serial NOT NULL, sensorblock integer NOT NULL, psi_act integer NOT NULL, chamber_hum real, sensor_hum real, /* Keys */ CONSTRAINT psi_humsensor_pkey PRIMARY KEY (id), /* Foreign keys */ CONSTRAINT foreign_key01 FOREIGN KEY (psi_act) REFERENCES public.psi_humsensor_acts(id) ON DELETE RESTRICT, CONSTRAINT foreign_key02 FOREIGN KEY (sensorblock) REFERENCES public.sensorblock(id) ) WITH ( OIDS = FALSE );

The table is filled with the following data:

Data in the table

I am trying to form a report from this table. To do this, you need to make a request that would return the humidity of the sensor in the chamber at 25%, the humidity of the sensor under test at 25%, the humidity of the sensor in the chamber at 95%, the humidity of the sensor under test at 95%. Wrote this query:

 SELECT sb.serial_number ,t.label_number ,round(CAST(psi1.chamber_hum as numeric), 2) AS ch25 ,round(CAST(psi1.sensor_hum as numeric), 2) AS sh25 ,abs(round(CAST((psi1.chamber_hum - psi1.sensor_hum) as numeric), 2)) as delta25 ,round(CAST(psi2.chamber_hum as numeric), 2) AS ch95 ,round(CAST(psi2.sensor_hum as numeric), 2) AS sh95 ,abs(round(CAST((psi2.chamber_hum - psi2.sensor_hum) as numeric), 2)) as delta95 FROM psi_humsensor psi1 JOIN psi_humsensor psi2 ON (psi1.psi_act = psi2.psi_act AND psi1.sensorblock = psi2.sensorblock) JOIN sensorblock sb ON (sb.id = psi1.sensorblock) JOIN termistors t ON (t.id = sb.termistor) WHERE psi1.id <> psi2.id AND psi1.psi_act = 3

When executing this query, duplicate entries are obtained, for example:

1 | 1 | 25.2 | 25.3 | 0.1 | 95.3 | 95.5 | 0.2

2 | 2 | 95.3 | 95.5 | 0.2 | 25.2 | 25.3 | 0.1

If you add a SELECT DISTINCT(psi1.chamber_hum + psi2.chamber_hum) to the query SELECT DISTINCT(psi1.chamber_hum + psi2.chamber_hum) , the duplication disappears, but the records are sometimes confused: the data on the left side of the table is at 95%, and the right at 25%. How to make such a request?

  • 2
    I do not see where you are selected for the given parameters (25% and 95%). The only selection in the request is psi1.psi_act = 3. And in order to avoid "swapping places" add the corresponding condition to the binding condition. selections (for example AND psi1.sensor_hum <30 AND psi2.sensor_hum> 90). - Akina

1 answer 1

You don't need samojoyn at all. All the necessary information is already there, you just need to add grouping:

 SELECT sb.serial_number ,t.label_number ,MAX(CASE WHEN psi.sensor_hum < 30 THEN round(CAST(psi.chamber_hum as numeric), 2) END) AS ch25 ,MAX(CASE WHEN psi.sensor_hum < 30 THEN round(CAST(psi.sensor_hum as numeric), 2) END) as sh25 ,MAX(CASE WHEN psi.sensor_hum < 30 THEN abs(round(CAST((psi.chamber_hum - psi.sensor_hum) as numeric), 2)) END) as delta25 ,MAX(CASE WHEN psi.sensor_hum > 90 THEN round(CAST(psi.chamber_hum as numeric), 2) END) AS ch95 ,MAX(CASE WHEN psi.sensor_hum > 90 THEN round(CAST(psi.sensor_hum as numeric), 2) END) as sh95 ,MAX(CASE WHEN psi.sensor_hum > 90 THEN abs(round(CAST((psi.chamber_hum - psi.sensor_hum) as numeric), 2)) END) as delta95 FROM psi_humsensor psi JOIN sensorblock sb ON (sb.id = psi.sensorblock) JOIN termistors t ON (t.id = sb.termistor) WHERE psi.psi_act = 3 GROUP BY sb.serial_number, t.label_number /*тут возможно надо добавить группировку по psi.sensorblock*/