Início / dba / Perguntas / 333097
Accepted
Zephyrus
Asked: 2023-11-15 00:55:46 +0800 CST

Funções de janela e distintas

  • 772

Eu tenho a seguinte tabela.

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

Preciso usar os dados desta tabela para criar um gráfico de velas que terá velas de 6 horas. Cada vela é representada pelos campos { x, open, close, low, high }onde x é o timestamp UNIX do início do período.

A consulta a seguir funciona, mas usar distinctfaz com que a consulta demore mais. Normalmente, quando as pessoas não querem usar distinto, elas usam group by, mas não posso usar isso com as funções da janela e não tenho certeza se isso ajudaria de qualquer maneira. Existe uma maneira de eliminar o uso de distinto nesta consulta para torná-la mais rápida e ainda retornar os mesmos resultados?

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
postgresql

2 respostas

  1. Best Answer

    Em vez de usar funções de janela, que não reduzem o número de linhas de resultados, e então DISTINCT, o que reduz, seria melhor usar funções agregadas que façam as duas coisas em uma única etapa.

    Precisamos de funções agregadas para o primeiro e o último valor:

    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
);

    Agora você pode escrever

    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;
    • 1

  2. Você pode usar uma combinação de funções de janela e normais group byno mesmo particionamento, em vez de usar distinct. Isso significa que o otimizador pode confiar que a classificação necessária não será alterada.

    Você pode usar minou maxnos valores da janela, não faz diferença.

    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;
    • 0

relate perguntas