主页 / dba / 问题 / 295139
Accepted
jsf80238
Asked: 2021-07-02 14:57:33 +0800 CST

计算给定时间的累积金额

  • 772

我想找出在 P 小时内花费超过 D 美元的客户。假设 D=10 美元，P=48。然后在下面的交易数据中：

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

客户 B 符合标准，客户 A 和 C 不符合（即使他们的总支出 > 10 美元）。

我知道如何使用窗口函数，但在这种情况下，我事先不知道窗口应该有多大。

我认为答案是这样的：

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

但我的数据库是雪花，它似乎不支持这种语法。该页面说：

对于累积窗口框架： ... RANGE 类似于 ROWS，除了它只计算与当前行具有相同值的行的结果（根据指定的 ORDER BY 子句）。

window-functions snowflake

1 个回答

  1. Best Answer

    Snowflake 确实支持执行您的要求，但语法不同：

    这是脚本：

    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;

    你得到的输出是这样的：在此处输入图像描述

    • 1

相关问题