在SQL中添加包含前7个事务的新列

Question

对于客户细分，我需要分析在给定交易日期的基础上，客户在前7天、前14天等时间内进行了多少交易。已复制输入和所需的输出表

我试过用这个，但逻辑似乎不正确

UPDATE WU5
SET txnLast7Days = subquery.txnLast7Days
FROM
    (       
        SELECT  Transaction_Date, sum(dateCounts.transactionCount) OVER (ORDER BY Transaction_Date RANGE BETWEEN 7 PRECEDING AND CURRENT ROW) as txnLast7Days
        FROM (SELECT count(*) transactionCount, Transaction_Date FROM WU5 GROUP BY Transaction_Date) as dateCounts
    ) subquery
WHERE WU5.Transaction_Date = subquery.Transaction_Date

​

​

​

​

Answer 1

您可以使用窗口函数和范围规范：

select w.*,
    count(*) over(
        partition by customer_id 
        order by transaction_date 
        range between interval 7 day preceding and interval 1 day preceding
    ) txnLast7Days,
    count(*) over(
        partition by customer_id 
        order by transaction_date 
        range between interval 14 day preceding and interval 1 day preceding
    ) txnLast14Days
from wu5 w

如果您需要一条update语句：

update wu5 w
inner join (
    select transaction_id,
        count(*) over(
            partition by customer_id 
            order by transaction_date 
            range between interval 7 day preceding and interval 1 day preceding
        ) txnLast7Days,
        count(*) over(
            partition by customer_id 
            order by transaction_date 
            range between interval 14 day preceding and interval 1 day preceding
        ) txnLast14Days
    from wu5 w
) w1 on w1.transaction_id = w.transaction_id
set w.txnLast7Days = w1.txnLast7Days, w.txnLast14Days = w1.txnLast7Days