在Snowflake中使用PARTITION BY获取Datediff函数的平均值 [英] Get the Average of a Datediff function using a partition by in Snowflake

问题描述

我希望了解我的数据库中使用Snowflake的每个客户的平均事务间隔天数。

select Customer_ID,Day_ID, datediff(Day,lag(Day_ID) over (Partition by Customer_ID ORDER BY DAY_ID), DAY_ID) as Time_Since from Table order by Customer_ID, Day_ID

上面的代码可以让我得到time_elapsed，但是当我尝试添加一个平均函数时，我得到一个错误：

select Customer_ID avg(datediff(Day,lag(Day_ID) over (Partition by Customer_ID ORDER BY DAY_ID), DAY_ID)) as AVG_Time_Since from Table order by Customer_ID group by Customer_ID

错误为： SQL编译错误：聚合函数内不能出现窗口函数[LAG(TABLE.DAY_ID)OVER(PARTITION BY TABLE.CUSTOMER_ID ORDER BY TABLE.DAY_ID ASC NULLS LAST)]。

有什么想法吗？

推荐答案

您可以嵌套它们并获得您要查找的答案。

注意：您只需删除此开头的CTE，并将from cte替换为from YourTable

WITH cte as
  (SELECT column1 customer_id, column2::date day_id
   FROM
   VALUES (1, '2019-01-01'), (1, '2019-01-06'), (1, '2019-01-15'), (1, '2019-01-25'), (1, '2019-01-27'), (1, '2019-01-31'), (2, '2019-01-01'), (2, '2019-01-08'), (2, '2019-01-13'), (2, '2019-01-17'), (2, '2019-01-21'), (2, '2019-01-25'), (2, '2019-02-02'), (3, '2019-02-12'), (3, '2019-02-14'), (3, '2019-02-18'), (3, '2019-02-23'), (3, '2019-03-04'), (3, '2019-03-10'))
SELECT customer_id,
       avg(time_since) AVG_Time_Since
FROM
  (SELECT Customer_ID,
          Day_ID,
          datediff(DAY, lag(Day_ID) OVER (PARTITION BY Customer_ID
                                          ORDER BY DAY_ID), DAY_ID) AS Time_Since
   FROM cte
   ORDER BY Customer_ID,
            Day_ID)
GROUP BY customer_id ;

这篇关于在Snowflake中使用PARTITION BY获取Datediff函数的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！