时间戳

首页 > 解决方案 > SQL (Snowflake) – 30 天前瞻/滚动平均值

sql - SQL (Snowflake) – 30 天前瞻/滚动平均值

问题描述

我有两张桌子 -purchases和activity.

该purchase表的结构如下：

|----------|----------------|----------|
|  user_id |  purchase_date |  status  |
|----------|----------------|----------|
|    1234  |   2020-01-01   |  active  |
|----------|----------------|----------|
|    2345  |   2020-01-10   | cancelled|

该activity表的结构如下：

|----------|----------------|-----------------|
|  user_id |      date      |  videos_viewed  |
|----------|----------------|-----------------|
|    1234  |   2020-01-02   |       4         |
|----------|----------------|-----------------|
|    2345  |   2020-01-03   |       3         |
|----------|----------------|-----------------|
|    2345  |   2020-01-10   |       10        |
|----------|----------------|-----------------|
|    2345  |   2020-01-11   |       7         |

我希望根据设定的购买期查询每个用户前 30 天的前 30 天活动平均值。

到目前为止我写的查询是这样的：

SELECT avg(t3.viewsperday)
FROM
 (SELECT 
    date
   ,sum(t1.videos_viewed)/count(t1.user_id) as viewsperday
   FROM activity t1 
       INNER JOIN (SELECT * FROM purchase c
       WHERE status = 'active'
       AND purchase_date BETWEEN '2020-01-01' and '2020-02-01') t2 
   ON t1.user_id = t2.user_id
   where date between '2020-01-01' and '2020-02-01'
   group by 1
   order by 1 asc) as t3;

但是，这里的问题是，如果用户在2020-01-31I 上购买，则只能获得第一天的活动。我需要帮助来弄清楚如何从每个购买日期起 30 天获得滚动平均值/展望 - 并获得这 30 天的平均活动。

我怀疑窗口函数在这里是合适的，但我不确定如何制定它，因为它有点超出我的知识范围。任何帮助将不胜感激。

标签： sqlsnowflake-cloud-data-platform

解决方案

以下应该工作。我假设您希望平均超过 30 天，即使其中某些天的观看次数可能为零？您可能还需要稍微调整它，具体取决于您定义 30 天日期范围的确切方式，即是否包括第 30 天、是否包括购买日期等。我将其编写为外部连接，这样即使没有用户将包括视图

SELECT
P.USER_ID,
SUM(A.VIDEOS_VIEWED)/30
FROM PURCHASE P
LEFT OUTER JOIN ACTIVITY A ON P.USER_ID = A.USER_ID AND
A.DATE >= P.PURCHASE_DATE AND A.DATE <= dateadd(DAY, 30, P.PURCHASE_DATE)
GROUP BY P.USER_ID;

更新...要获得每日平均值，请尝试以下操作（购买日期的视图显示为第 0 天，如果这应该是第 1 天，则将 1 添加到 Day_after_Purchase 公式）：

SELECT
(a.date - p.purchase_date) as Day_after_Purchase,
avg(A.VIDEOS_VIEWED)
FROM PURCHASE P
LEFT OUTER JOIN ACTIVITY A ON P.USER_ID = A.USER_ID AND
    A.DATE >= P.PURCHASE_DATE AND A.DATE <= dateadd(DAY, 30, P.PURCHASE_DATE)
GROUP BY 1;

推荐阅读