Generate Postgres output in columns

Navigation
1

I am trying to make a query in a Postgres database where I want to get results in the form of a table as follows. Variables 1, 2 ... n are the names of the sensors that are known (from the list in the query). Then, the result would be for each date, set the value of the sensor whose name is variableN. 

logtime     variable1      variable2        variable3
------------------------------------------------------
fecha1          valor11        valor12          valor13
----------------------------------------------------
fecha2          valor21        valor22          valor23
----------------------------------------------------

For this, I have seen the function crosstab , but the truth is that I do not know very well how to use it. I would appreciate any help to generate this query: 

select * from crosstab(
'SELECT sensorhistdatalog.valuedate as logtime, sensorhistdatalog.value as logvalue, sensor.name FROM public.sensor
INNER JOIN public.sensorhistdatalog
ON sensor.id = sensorhistdatalog.sensor_id
where sensorhistdatalog.valuedate between ''2017-03-06'' and ''2017-03-07''
and sensor.name in (''LHTES_ON','C_VELOCIDAD_V3_MAN','C_VELOCIDAD_V4_MAN','VELOCIDAD_V3','VELOCIDAD_V4','LHTES MODE','TEMP_COMF_W','TEMP_COMF_S',
'TO_EXT','TO_INT','TI_EXT','TI_INT','TEMP_PLATE_1','TEMP_PLATE_2','TEMP_PLATE_3','TEMP_PLATE_4','DUMPER_1','DUMPER_2','REF_V3','REF_V4'')
order by name, valuedate asc')
as (
logtime text,
logvalue text,
name text);

The tables I want to cross are sensorhistdatalog and sensor where sensorhistdatalog has several columns, but we use the sensor_id (which is the foreign key of the id of the sensor table), the date and the value, basically. The sensor table is simply the list of sensors with their id, name and description.

The error I get is the following: 

ERROR: no existe la función crosstab(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown)
    
asked by Jose Luis Hernandez 07.03.2017 в 09:29
source

1 answer

1

According to the POSTGRESQL documentation :

  

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.

That is:

  

In practice, the SQL query must always specify ORDER BY 1,2 to ensure that the input rows are ordered correctly , that is, values with the same row name are grouped together and they are ordered correctly within the row. Note that the cross-reference table pays no attention to the second column of the query result ; it is there only to be sorted by, to control the order in which the values of the third column appear on the page.

Which means that your order by name, valuedate asc is of no use in a query with crosstab . In other words, every query with crosstab must have a ORDER BY 1,2 .

Then: 

SELECT * FROM crosstab(
    'SELECT 
        sensorhistdatalog.valuedate as logtime, 
        sensorhistdatalog.value as logvalue, 
        sensor.name 
    FROM public.sensor
    INNER JOIN public.sensorhistdatalog
        ON sensor.id = sensorhistdatalog.sensor_id
        WHERE 
        sensorhistdatalog.valuedate between ''2017-03-06'' and ''2017-03-07''
        AND sensor.name in 
        (''LHTES_ON','C_VELOCIDAD_V3_MAN','C_VELOCIDAD_V4_MAN','VELOCIDAD_V3',
          'VELOCIDAD_V4','LHTES MODE','TEMP_COMF_W','TEMP_COMF_S',
          'TO_EXT','TO_INT','TI_EXT','TI_INT','TEMP_PLATE_1','TEMP_PLATE_2',
          'TEMP_PLATE_3','TEMP_PLATE_4','DUMPER_1','DUMPER_2','REF_V3','REF_V4'')
    ORDER BY 1, 2')
    AS (logtime text,logvalue text,name text);

It should work ... provided that your SELECT within crosstab with your JOIN and your WHERE is correct , that is up to you.

    
answered by 07.03.2017 в 13:57
Making Arcs in Java Find max and min in Javascript without using array