Capacytron's questions

Estou usando o jooq para gerar consulta SQL e estou com um pouco de preguiça de gerar seleção aninhada de another_select de bottom_select. Minha consulta gerada contém expressões repetidas. O postgres 13+ aplica algumas técnicas de otimização nos bastidores?

amostra simplificada de parte da consulta

SELECT
case
           when b.count is null then 100.00
           when c.count is null then -100.00
           else round(
                   cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
                   2
               )
           end as expression_01,

case
           when b.count is null then 100.00
           when c.count is null then -100.00
           else round(
                   cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
                   2
               )
           end as expression_02
from some_table

Digamos

round(
                   cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
                   2
               )

foi repetido várias vezes, o Postgres calculará isso uma vez ou várias vezes?

Eu tenho uma função agg que soma decimais. Lógica de negócios omitida por questões de brevidade

CREATE OR REPLACE FUNCTION calculate_snapshot_internal_decimal(_this_value DECIMAL,
                                                               _next_value DECIMAL
                                                               OUT _code DECIMAL)
    LANGUAGE plpgsql AS
$func$
BEGIN

    _code = _this_value + _next_value;

END
$func$;

Agora preciso do mesmo para JSONB onde a chave é string e o valor é sempre BigDecimal

_este_valor:

{"weight": "50", "height":"170", "size": "2"}

_próximo_valor

{"weight": "40", "height":"20"}

resultado agregado:

{"weight": "90", "height":"190", "size": "2"}

O que eu poderia tentar?

Eu tenho uma tabela com séries temporais. Ele contém snapshote incrementvalor

• snapshotsignifica valor concreto
• incrementsignifica pegar o instantâneo anterior e adicionar valor de incremento para obter o valor do instantâneo

sensor_id - id of sensor
ts - timestamp
temp - temperature at a given ts
value_type - 0 means snapshot, 1 means increment

sensor_id | ts | temp | value_type
sensor_X  |  1 |  100 | 0
sensor_X  |  2 |    5 | 1
sensor_X  |  3 |   -2 | 1
sensor_X  |  4 |   95 | 0

sensor_Y  |  4 |   90 | 0
sensor_Y  |  5 |   -5 | 1

Meu objetivo é criar uma exibição de série temporal a partir da tabela

           1|   2|   3|   4|    5| 
sensor_X  100| 105| 103|  95|    |     
sensor_Y     |    |    |  90|  85|  

o usuário consulta dados por intervalo ts e sensor_id:

show me sensor temperature where sensor_id starts with sensor_0* and ts in range (2023-01-20, 2023-01-23)

Eu tento implementar a função agregada

-- thisValue, thisValueType, nextValue, nextValueType
CREATE OR REPLACE FUNCTION calculate_snapshot_internal(bigint, int, bigint, int)
    returns bigint
    language sql
as
$$
select case
           when $3 is null then null -- metric value explicitly set to null, means it removed
           when $4 = 1 or $4 = 2 then $3 -- next value is SNAPSHOT or FULL_SNAPSHOT, just set it
           when $4 = 0 and $1 is not null then $1 + $3 -- next value is INCREMENTAL, add it to current SNAPSHOT
           end
$$;

CREATE aggregate calculate_snapshot (bigint, int) (
    sfunc = calculate_snapshot_internal,
    stype = bigint
    -- initcond = null We can do this simply by omitting the initcond phrase, so that the initial state condition is null
    );

e consulta

select sensor_id,
       calculate_snapshot(value, valueType) OVER (PARTITION BY sensor_id ORDER BY ts) as snapshot_value
from metric_table;

Não consigo executar CREATE aggregate calculate_snapshot o erro:

[42883] ERROR: function calculate_snapshot_internal(bigint, bigint, integer) does not exist

Não entendo como é suposto funcionar.

UPD: a explicação do erro é marcada como resposta correta. Aqui está a solução final que funcionou para mim

REATE OR REPLACE FUNCTION calculate_snapshot_internal(_this_value bigint,
                                                              _next_value bigint,
                                                              _next_value_type int,
                                                              OUT _code bigint)
    LANGUAGE plpgsql AS
$func$
BEGIN


    if _next_value is null then
        _code = _this_value;    -- just pass by whatever it has, null means missing value
    elseif _next_value_type = 1 or _next_value_type = 2 then -- SNAPSHOT value
        _code = _next_value;
    elseif _next_value_type = 0 and _this_value is not null then  -- Add INCREMENT value to accumulated value
        _code = _this_value + _next_value;
    end if;

END
$func$;

drop aggregate if exists calculate_snapshot(bigint, int);
CREATE aggregate calculate_snapshot(bigint, int) (
    sfunc = calculate_snapshot_internal,
    stype = bigint
    );

Estou usando o Hive atualmente e ele tem tabelas particionadas. Digamos que estes são meus dados:

Country City DailySale
USA     NY   200.2
USA     SF   190.5
CAN     TR   390.4

O posso definir minha tabela

create table SALES_DATA(
Country    STRING,
City       STRING,
DailySale  BIGDECIMAL
) PARTITIONED BY (Country, City)

Então eu crio a estrutura do catálogo no HDFS:

SALES_DATA/
          Country=USA/
                      City=NY/
                              data-file-0001
                              data-file-0002
... the rest omitted for brevity...

Em seguida, a poda de partição pode ser aplicada para consultas como

SELECT SUM(DailySale) FROM SALES_DATA
WHERE  Country='USA' and City='SF'

O mecanismo Hive removerá todas as partições, exceto uma. Super eficaz e ideal.

Eu gostaria de implementar algo semelhante no Postgres. eu vou ter tabelas imutáveis ​​com dados agregados e gostaria de aplicar algo semelhante para eles. Estou tentando passar por isso https://www.postgresql.org/docs/13/ddl-partitioning.html mas realmente não siga se for o que eu preciso.