活动用户的滚动7天不同计数

Question

我正在查找的输出是每个独立日的记录，包括DISTINCT活动成员的计数和DISTINCT滚动7天活动成员的计数。

我正在处理两个表格 - [交易]，它通过我们的系统记录了每笔交易的记录，其中包括交易处理日期的dmn_date_id以及1990年至2020年间每天有day_offset_nbr表示的DATE_DIMENSION从TODAY（）中偏移的天数（此表每晚重建）。

SELECT 
    dd.date_tms 
    , COUNT(DISTINCT(t.user_id)) as Active_Members_Today 
    , COUNT(DISTINCT(t2.user_id)) as Rolling_7Day_Active_Members 
FROM 
    transactions t 
    JOIN DATE_DIMENSION dd 
     ON t.transaction_processed_date_id = dd.date_id 
    JOIN DATE_DIMENSION d2 
     ON d2.day_offset_nbr BETWEEN (dd.day_offset_nbr - 6) AND dd.day_offset_nbr 
    JOIN transactions t2 
     ON d2.dmn_date_id = t2.common_trans_processed_date_id 
WHERE 
    t.user_initiated_ind = 'Y' -- Only User-initiated Transactions 
    AND t2.user_initiated_ind = 'Y' 
    AND t.transaction_state_name = 'Accepted' -- Accepted Transactions Only 
    AND t2.transaction_state_name = 'Accepted' 
    AND dd.day_offset_nbr >= -731 
    AND d2.day_offset_nbr >= -738 
GROUP BY 
    dd.date_tms 

上面的查询让我知道我们需要什么，但需要很长时间（一小时）才能处理。我们在user_initiated_ind和common_trans_processed_date_id上​​创建了包含user_id的索引，但查询仍在旋转。

想我可以通过一个窗口函数解决这个问题，它知道你不能在窗口中使用DISTINCT，这导致我使用带有Row_Number（）的派生表来识别窗口内的DISTINCT用户，这导致我发现Row_Number（）不能与一个窗口中的ROWS BETWEEN子句一起使用...

我读过Calculate running total/running balance，它是类似的，但处理运行平衡而不是滚动不同的计数，而且我并不完全确定如何将我读过的内容应用于我的问题;只有我正在使用的INNER JOIN方法被建议为不使用的方法...

......这激励我发布。

如何提高查询性能？

Answer 1

第二次加入transactions似乎完全没有必要。您可以使用条件聚合来完成计数。所以，这个版本可能会更快：

SELECT d2.date_tms, 
     count(distinct case when d2.day_offset_nbr = dd.day_offset_nbr then t.user_id end) as Active_Members_Today, 
     count(distinct t.user_id) as Rolling_7Day_Active_Members 
FROM transactions t JOIN 
    DATE_DIMENSION dd 
    ON t.transaction_processed_date_id = dd.date_id JOIN 
    DATE_DIMENSION d2 
    ON d2.day_offset_nbr BETWEEN dd.day_offset_nbr AND (dd.day_offset_nbr + 6) AND 
WHERE t.user_initiated_ind = 'Y' AND -- Only User-initiated Transactions 
     t.transaction_state_name = 'Accepted' AND -- Accepted Transactions Only 
     dd.day_offset_nbr >= -731 
GROUP BY d2.date_tms;