1

I have the following table.

  Column  |            Type             | Collation | Nullable | Default 
----------+-----------------------------+-----------+----------+---------
 code     | text                        |           | not null | 
 price_at | timestamp without time zone |           | not null | 
 price    | double precision            |           | not null | 

I need to use the data in this table to create a candlestick chart that will have 6-hour candlesticks. Each candlestick is represented by the fields { x, open, close, low, high } where x is the UNIX timestamp of the beginning of the period.

The following query works, but using distinct causes the query to take longer. Usually, when people don't want to use distinct, they use group by instead, but I can't use that with the window functions and I'm not sure it would help anyway. Is there a way to eliminate the use of distinct in this query to make it faster and still return the same results?

with price_quotes as (
        select
            extract (epoch from price_at) - (extract (epoch from price_at) % extract (epoch from '6 hours'::interval)) as period_begin,
            extract (epoch from price_at) as quote_time,
            price
        from quote)
select distinct
    period_begin as x,
    first_value (price) over (partition by period_begin order by quote_time asc) as open,
    last_value (price) over (partition by period_begin order by quote_time asc rows between current row and unbounded following) as close,
    min (price) over (partition by period_begin) as low,
    max (price) over (partition by period_begin) as high
from price_quotes
order by x asc

2 Answers 2

1

Instead of using window functions, which don't reduce the number of result rows, and then DISTINCT, which does, it would be better to use aggregate functions that do both in a single step.

We need aggregate functions for the first and last value:

CREATE FUNCTION first_not_null(anyelement, anyelement) RETURNS anyelement
   LANGUAGE sql AS 'SELECT coalesce($1, $2)';

CREATE AGGREGATE first(anyelement) (
   SFUNC = first_not_null,
   STYPE = anyelement
);

CREATE FUNCTION second_not_null(anyelement, anyelement) RETURNS anyelement
   LANGUAGE sql AS 'SELECT coalesce($2, $1)';

CREATE AGGREGATE last(anyelement) (
   SFUNC = second_not_null,
   STYPE = anyelement
);

Now you can write

WITH price_quotes AS (
   SELECT date_bin('6 hours', price_at, '1970-01-01 00:00:00') AS period_begin,
          EXTRACT (epoch FROM price_at) AS quote_time,
          price
   FROM quote
)
SELECT period_begin,
       first(price ORDER BY quote_time) AS open,
       last(price ORDER BY quote_time) AS close,
       min(price) AS low,
       max(price) AS high
FROM price_quotes
GROUP BY period_begin;
0

You can use a combination of window functions and normal group by on the same partitioning, rather than using distinct. This means the optimizer can rely on the required sort not changing.

You can use either min or max on the windowed values, it makes no difference.

with price_quotes as (
        select
            extract (epoch from price_at) - (extract (epoch from price_at) % extract (epoch from '6 hours'::interval)) as period_begin,
            extract (epoch from price_at) as quote_time,
            price
        from quote
),
windowed as (
    select
        period_begin,
        price
        first_value(price) over (partition by period_begin order by quote_time asc) as open,
        last_value (price) over (partition by period_begin order by quote_time asc rows between current row and unbounded following) as close,
    from price_quotes
)
select
    period_begin as x,
    min(open) as open,
    min(close) as close,
    min(price) as low,
    max(price) as high
from windowed
group by
  period_begin
order by
  x;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.