CODEWITHSUNDEEP
postgresqltimescaledb
punkuotukas
punkuotukas

Reputation: 107

postgresql group by datetime in join query

I have 2 tables in my postgresql timescaledb database (version 12.06) that I try to query through inner join. Tables' structure:

CREATE TABLE currency(
    id serial PRIMARY KEY,
    symbol TEXT NOT NULL,
    name TEXT NOT NULL,
    quote_asset TEXT
); 

CREATE TABLE currency_price (
    currency_id integer NOT NULL,
    dt timestamp WITHOUT time ZONE NOT NULL,
    open NUMERIC NOT NULL,
    high NUMERIC NOT NULL,
    low NUMERIC NOT NULL,
    close NUMERIC,
    volume NUMERIC NOT NULL,
    PRIMARY KEY (
        currency_id,
        dt
    ),
    CONSTRAINT fk_currency FOREIGN KEY (currency_id) REFERENCES currency(id)
);

The query I'm trying to make is:

SELECT currency_id AS id, symbol, MAX(close) AS close, DATE(dt) AS date
FROM currency_price
JOIN currency ON
currency.id = currency_price.currency_id
GROUP BY currency_id, symbol, date
LIMIT 100;

Basically, it returns all the rows that exist in currency_price table. I know that postgres doesn't allow select columns without an aggregate function or including them in "group by" clause. So, if I don't include dt column in my select query, i receive expected results, but if I include it, the output shows rows of every single day of each currency while I only want to have the max value of every currency and filter them out based on various dates afterwards.

I'm very inexperienced with SQL in general. Any suggestions to solve this would be very appreciated.

Upvotes: 0

Views: 400

Answers (1)

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

There are several ways to do it, easiest one comes to mind is using window functions.

select * 
  from (
     SELECT currency_id,symbol,close,dt
           ,row_number() over(partition by currency_id,symbol
                                  order by close desc,dt desc) as rr
       FROM currency_price
       JOIN currency ON currency.id = currency_price.currency_id
      where dt::date = '2021-06-07'
       )q1 
 where rr=1

General window functions: https://www.postgresql.org/docs/9.5/functions-window.html works also with standard aggregate functions like SUM,AVG,MAX,MIN and others.

Some examples: https://www.postgresqltutorial.com/postgresql-window-function/

Upvotes: 1

Related Questions