Início / dba / Perguntas / 227515
Accepted
Eduard Sukharev
Asked: 2019-01-19 12:16:34 +0800 CST

Obter incremento de valor durante uma hora de um dia

  • 772

Eu consulto a API de dados do YouTube para obter uma lista dos vídeos mais populares em um canal e, em seguida, obtenho suas estatísticas, 4 vezes por hora (a cada 15 minutos, por cron). Os dados são armazenados no Postgres, mas despejá-los e carregá-los em outro banco de dados SQL não seria um problema. Agora tenho a seguinte tabela de dados:

 video_id| views_count | likes_count | timestamp 
---------+-------------+-------------+---------------------
     foo | 100         | 1           | 2018-12-01 12:01:03
     foo | 101         | 1           | 2018-12-01 12:16:06
     foo | 105         | 1           | 2018-12-01 12:31:01
     bar | 199         | 0           | 2018-12-01 12:01:02
     bar | 200         | 0           | 2018-12-01 12:16:08
     bar | 301         | 5           | 2018-12-01 12:31:02
     ... | ...

UPD: Aqui está o esquema (colado em sqlfiddle ):

CREATE TABLE video_statistics
(
  video_id TEXT not null,
  views_count INTEGER not null,
  likes_count INTEGER not null,
  timestamp TIMESTAMPTZ not null
);

Como devo consultar esses dados para obter incrementos por hora view_countse likes_countcolunas, agrupados por vídeo? Para esclarecer o que quero obter:

hour_of_day|video_id|views_increment|likes_increment
-----------+--------+---------------+---------------
     ...   | ...
     11    | foo    | 4             | 0
     12    | foo    | 5             | 1
     ...   | ...
     11    | bar    | 73            | 0
     12    | bar    | 102           | 5
     ...   | ...

Em outras palavras, é um "melhor momento para postar vídeo" com base em dados históricos, levando em consideração dados durante muitas semanas e meses. Devo despejar os dados em algum banco de dados de série temporal ou outro, mais apropriado para esses casos, e consultá-lo lá? Ou devo apenas recorrer a calcular isso no código?

postgresql aggregate

2 respostas

  1. Best Answer

    Uma possibilidade é primeiro row_number()os registros para obter o primeiro e o último valor por vídeo, dia e hora. Em seguida, junte os dois conjuntos de primeiro e último valores para obter as respectivas diferenças. Agrupe o resultado em vídeo e hora e obtenha a soma ou a média por vídeo por dia.

    SELECT first.video_id,
       first.timestamp_hour,
       sum(last.views_count - first.views_count) views_count_diff_sum,
       sum(last.likes_count - first.likes_count) likes_count_diff_sum,
       avg(last.views_count - first.views_count) views_count_diff_avg,
       avg(last.likes_count - first.likes_count) likes_count_diff_avg
       FROM (SELECT video_id,
             timestamp_day,
             timestamp_hour,
             views_count,
             likes_count
             FROM (SELECT video_id,
                          timestamp::date timestamp_day,
                          date_part('hour', timestamp) timestamp_hour,
                          views_count,
                          likes_count,
                          row_number() OVER (PARTITION BY video_id,
                                                          timestamp::date,
                                                          date_part('hour', timestamp)
                                             ORDER BY timestamp ASC) rn
                          FROM elbat) first
             WHERE rn = 1) first
            INNER JOIN (SELECT video_id,
                               timestamp_day,
                               timestamp_hour,
                               views_count,
                               likes_count
                               FROM (SELECT video_id,
                                            timestamp::date timestamp_day,
                                            date_part('hour', timestamp) timestamp_hour,
                                            views_count,
                                            likes_count,
                                            row_number() OVER (PARTITION BY video_id,
                                                                            timestamp::date,
                                                                            date_part('hour', timestamp)
                                                               ORDER BY timestamp DESC) rn
                                            FROM elbat) last
                               WHERE rn = 1) last
                       ON last.video_id = first.video_id
                          AND last.timestamp_day = first.timestamp_day
                          AND last.timestamp_hour = first.timestamp_hour
       GROUP BY first.video_id,
                first.timestamp_hour;
    • 2

  2. Esquema:

    create table T 
( video_id char(3) not null
, views_count int not null
, likes_count int not null
, ts timestamp not null
);

    Adivinhe algo como:

    select hr, video_id
     , lag(vc) over (partition by video_id
                     order by hr) - vc as vc_incr
     , lag(lc) over (partition by video_id
                     order by hr) - lc as lc_incr                
from (                          
    select extract(hour from ts) as hr
         , video_id
         , sum(views_count) as vc
         , sum(likes_count) as lc
    from t
    group by extract(hour from ts)
           , video_id
 ) as tt;

    Observe que você terá que decidir o que fazer com as linhas que não possuem uma linha de atraso, ou seja, a primeira linha em cada partição.

    • 1

relate perguntas