Início / dba / Perguntas / 295139
Accepted
jsf80238
Asked: 2021-07-02 14:57:33 +0800 CST

Calcular um valor cumulativo com um determinado tempo

  • 772

Quero identificar clientes que gastaram mais de D dólares em um período de P horas. Suponha que D=$10 e P=48. Em seguida, nos dados da transação abaixo:

CUSTOMER         STAMP        AMOUNT
--------         -----        ------
   A     2021-06-02 00:22:53    1.44
   A     2021-06-02 06:24:17    1.51
   A     2021-06-03 07:09:45    2.73
   A     2021-06-03 15:57:30    3.92
   A     2021-06-04 06:41:21    1.83
   B     2021-06-01 02:50:22    2.65
   B     2021-06-03 07:01:36    4.05
   B     2021-06-04 05:20:10    3.30
   B     2021-06-04 09:53:53    2.64
   B     2021-06-04 14:54:00    2.26
   C     2021-06-01 16:01:38    2.61
   C     2021-06-01 23:38:25    1.16
   C     2021-06-02 14:41:02    2.82
   C     2021-06-03 00:28:37    1.54
   C     2021-06-03 02:06:46    1.19
   C     2021-06-04 17:16:29    2.05

o cliente B atende aos critérios e os clientes A e C não (mesmo que seus gastos totais > $ 10).

Eu sei como usar funções de janelas, mas neste caso não sei de antemão o tamanho da janela.

Acho que a resposta seria algo assim:

select
  customer
, stamp
, amount
, amount > coalesce(sum(amount) over (order by extract('epoch_second', stamp)
range between 2*24*60*60 preceding and current row), 0) as is_alert
from t
where amount is not null
order by stamp

mas meu banco de dados é Snowflake e não parece oferecer suporte a essa sintaxe . Essa página diz:

Para frames de janela cumulativos: ... RANGE é semelhante a ROWS, exceto que apenas calcula o resultado para linhas que têm o mesmo valor que a linha atual (de acordo com a subcláusula ORDER BY especificada).

window-functions snowflake

1 respostas

  1. Best Answer

    O Snowflake suporta fazer o que você está pedindo, mas com sintaxe diferente:

    Aqui está o roteiro :

    CREATE SCHEMA IF NOT EXISTS demo;

create or replace table demo.customer_transactions(
  transaction_id integer identity
,customer varchar
,txn_date timestamp_ltz
,amount float
);

//
//CUSTOMER         STAMP        AMOUNT
//--------         -----        ------
insert into demo.customer_transactions (customer, txn_date, amount)
values
('A',     '2021-06-02 00:22:53',    1.44)
,('A',     '2021-06-02 06:24:17',    1.51)
,('A',     '2021-06-03 07:09:45',    2.73)
,('A',     '2021-06-03 15:57:30',    3.92)
,('A',     '2021-06-04 06:41:21',    1.83)
,('B',     '2021-06-01 02:50:22',    2.65)
,('B',     '2021-06-03 07:01:36',    4.05)
,('B',     '2021-06-04 05:20:10',   3.30)
,('B',     '2021-06-04 09:53:53',    2.64)
,('B',     '2021-06-04 14:54:00',    2.26)
,('C',    '2021-06-01 16:01:38',    2.61)
,('C',     '2021-06-01 23:38:25',    1.16)
,('C',     '2021-06-02 14:41:02',    2.82)
,('C',     '2021-06-03 00:28:37',    1.54)
,('C',     '2021-06-03 02:06:46',    1.19)
,('C',     '2021-06-04 17:16:29',    2.05)
;


select
*
,case when window_transaction_amount > 10.00 then 'warn' else 'ok' end as is_alert
from
(
select 
 a.transaction_id
,a.customer
,a.txn_date
,a.txn_date as window_start_date
,dateadd(hour,48, a.txn_date) as window_end_date
,a.amount
//,array_construct(b.transaction_id) as b_transactions
,listagg(distinct b.transaction_id,',') within group (order by b.transaction_id) as window_transaction_ids
,sum(b.amount) as window_transaction_amount
from demo.customer_transactions a left outer join demo.customer_transactions b on a.customer = b.customer and b.txn_date between a.txn_date and dateadd(hour,48, a.txn_date)
group by 
  a.transaction_id
,a.customer
,a.txn_date
,dateadd(hour,48, a.txn_date)
,a.amount
) q
order by 1,2,3;

    A saída que você obtém é assim:insira a descrição da imagem aqui

    • 1

relate perguntas