How to compare date between years that stored in one column in Postgresql?

Question

I have this output:

It's data of stock symbol, closing values and the year of the closing of the stock. I make the output from this query:

select symbol, close, extract(year from time) as tahun 
from (select * from (select * from tabel1 where close > (select avg(close) from tabel1)) 
      as result where open < close) as result group by symbol, close, tahun

Now, I want to find the 2016 closing values that has higher value than year 2017 (2016 > 2017). I'm stuck, please anyone can help?

[EDIT]

This is the original data:

How I define closing value for a year:

I extract all the closing values that has higher values than averag closing values. Here's the query:

select * from tabel1 where close > (select avg(close) from tabel1)

Then, from query above I extract the closing values that has higher values than the opening (open < close). Here's the query:

select * from (select * from tabel1 where close > (select avg(close) from tabel1)) as result where open < close

From the second output, I tried to group the data by symbol and find the 2016 closing values that has higher values than 2017 closing values. But I'm stuck with this query, seems that I don't know how to compare between the years that stored in one column:

select symbol, close, extract(year from time) as tahun 
from (select * from (select * from tabel1 where close > (select avg(close) from tabel1)) as result where open < close) as result group by symbol, close, tahun

You might want to show the data in the original table, to begin with. It might be possible to optimize the whole query at once rather than adding to what you have written already. — GMB
@Jericho . . . Is that one table or two? How are you defining the close for a year? — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-09-04T12:59:33

You can use window functions with filtering:

select t.*
from (select t.*,
             max(close) filter (where tahun = 2016) over (partition by symbol) as close_2016
      from table1 t
     ) t
where tahun = 2017 and close < close_2016;

Or, if you prefer, a correlated subquery:

select t.*
from table1 t
where t.tahun = 2017 and
      t.close < (select tt.close from table1 tt where tt.symbol = t.symbol and tt.tahun = 2016);

How to compare date between years that stored in one column in Postgresql?

1 Answers