Formula Query on PostgreSQL

Question

My input data is like this

wavelength   reflectance
341.6        1.15  
343.1        1.14
344.7        1.13
346.3        1.14
347.9        1.14
349.5        1.12
351.1        1.12
352.6        1.13
354.2        1.13

I am using this formula Query

WITH CTE AS(
    SELECT 
      ROW_NUMBER() OVER(    
        PARTITION BY CAST(wavelength AS INT)
                     -CAST(wavelength AS INT)%5
        ORDER BY wavelength) AS ROW_ID,
      wavelength,
      avg( reflectance ) OVER(
        PARTITION BY CAST(wavelength AS INT)
                     -CAST(wavelength AS INT)%5
        ORDER BY wavelength
        rows BETWEEN 1 FOLLOWING
             and UNBOUNDED FOLLOWING) As reflectance 
      FROM 
      test
    )
    select trunc(wavelength/5)*5 AS wavelengthwavelength, reflectance
    from CTE
    where row_id = 1

In this query it povides the output like this

wavelength  reflectance
340         2.6400000000000000
340         2.5200000000000000
345         2.5200000000000000
355         2.5500000000000000
360         2.4250000000000000
365         2.4650000000000000
365         2.5450000000000000
370         2.4733333333333333
380         2.6600000000000000
385         2.7400000000000000
390         2.7700000000000000
390         2.8833333333333333

In this output 340,365,390 is placed as twice, instead of placed as twice it should be placed as once only based on the min value, how should do this...

score 1 · Answer 1 · edited May 23 '17 at 11:57

WITH cte AS(
   SELECT row_number() OVER(PARTITION BY wavelength::int - wavelength::int%5
                            ORDER BY wavelength) AS row_id,
          wavelength,
          avg(reflectance) OVER(PARTITION BY wavelength::int - wavelength::int%5
                           ORDER BY wavelength
                           ROWS BETWEEN 1 FOLLOWING
                           AND UNBOUNDED FOLLOWING) AS reflectance 
   FROM   test
   )
SELECT DISTINCT ON (1)
       trunc(wavelength/5)*5 AS wavelength, reflectance
FROM   cte
WHERE  row_id = 1
ORDER  BY 1, 2;

DISTINCT ON is a Postgres extension to standard SQL DISITNCT and is particularly useful if you want to add more columns and still pick the row with minimum reflectance. Otherwise GROUP BY can to the job as well.

More details in this related answer:
Select first row in each GROUP BY group?

With DISTINCT ON, you need ORDER BY in the final SELECT. However, you will want to add that either way. Per documentation:

Currently, window functions always require presorted data, and so the query output will be ordered according to one or another of the window functions' PARTITION BY/ORDER BY clauses. It is not recommended to rely on this, however. Use an explicit top-level ORDER BY clause if you want to be sure the results are sorted in a particular way.

If i am giving 5 instead of 10 placed in the query, it is giving same interval as two times, i want to omit the rows that are having same wavelength interval,but it is omitting rows340;1.1375 360;1.168 380;1.302 390;1.405 400;1.456 410;1.53666666666667 420;1.606 430;1.75333333333333 440;1.874 460;2.124 480;2.34 490;2.47 510;2.91833333333333 520;3.19 530;3.404 550;3.90166666666667 560;4.09333333333333 570;4.24833333333333 580;4.356 590;4.475 600;4.43333333333333 610;4.43 620;4.46166666666667 630;4.55333333333333 640;4.53428571428571 — Abishek Arumugam R, Mar 11 '14 at 05:11

score 0 · Answer 2 · answered Mar 10 '14 at 05:22

0

I think that the best way is that you use trunc(wavelength) exchange of trunc(wavelength/5)*5 but if you don't want to do that you can do something like this:

select trunc(trunc(wavelength * 5) / 5) AS wavelengthwavelength, reflectance
from CTE
where row_id = 1

answered Mar 10 '14 at 05:22

Hamidreza

3,038
1
18
15

It is giving the same output i shown above, i want to filter the output as for one wavelength there is one reflectance based on the selection of min wavelength value – Abishek Arumugam R Mar 10 '14 at 06:29

score 0 · Answer 3 · answered Mar 10 '14 at 05:41

0

select wavelength, MIN(reflectance) 
FROM (select trunc(wavelength/5)*5 AS wavelength, reflectance
    from CTE
    where row_id = 1) src
GROUP BY wavelength

answered Mar 10 '14 at 05:41

StanislavL

56,971
9
68
98

It is changing the output value differently, not based on wavelength order – Abishek Arumugam R Mar 10 '14 at 06:33

Formula Query on PostgreSQL

3 Answers3