英文:
how do you determine the total time spend using an ID (that is not unique) and timestamp column?
问题
我尝试了以下方法来获取该访问者的总停留时间，但我无法使其工作。 event_time是时间戳，visit_id是ID（不是唯一的，会多次出现）。
SELECT
visit_id,
SUM(DATEDIFF(second, LAG(event_time) OVER (PARTITION BY visit_id ORDER BY event_time), event_time)) AS total_time_spent
FROM events_over
GROUP BY visit_id;
当我尝试运行此查询时，我看到以下错误：
窗口函数 [LAG(EVENTS.EVENT_TIME) OVER (PARTITION BY EVENTS.VISIT_ID ORDER BY EVENTS.EVENT_TIME ASC NULLS LAST)] 不能出现在聚合函数内部。
英文:
I've tried the following to get the total time spent for that visitor, but I'm unable to get this to work. Event_time is the timestamp and visit_id is the ID (which is not unique and will populate multiple times).
SELECT
visit_id,
SUM(DATEDIFF(second, LAG(event_time) OVER (PARTITION BY visit_id ORDER BY event_time), event_time)) AS total_time_spent
FROM events_over;
GROUP BY visit_id;
when I try to run this, I see the following error:
> Window function [LAG(EVENTS.EVENT_TIME) OVER (PARTITION BY
> EVENTS.VISIT_ID ORDER BY EVENTS.EVENT_TIME ASC NULLS LAST)] may not
> appear inside an aggregate function.
答案1
得分: 0
你不能在聚合函数内使用窗口函数，正如消息所示。以下的查询应该可以工作：
SELECT visit_id, SUM(time_diff) as total_time_spent FROM (
SELECT
visit_id,
DATEDIFF(second, LAG(event_time) OVER (PARTITION BY visit_id ORDER BY event_time), event_time) time_diff
FROM events_over )
GROUP BY visit_id;
英文:
You can't use a window function inside an aggregate function as the message says. This one should work:
SELECT visit_id, SUM(time_diff) as total_time_spent FROM (
SELECT
visit_id,
DATEDIFF(second, LAG(event_time) OVER (PARTITION BY visit_id ORDER BY event_time), event_time) time_diff
FROM events_over )
GROUP BY visit_id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论