如何将where子句与日期结合起来

qlvxas9a 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(319)

我必须从下表中获得在给定的一天中至少观看一个视频的用户数和第二天返回以观看至少一个视频的用户数。我已经能够通过使用count语句获得计数，但是由于某些原因，它只显示第一个日期？到目前为止，我掌握的代码是：

SELECT date, COUNT(user_id) from clickstream_videos WHERE event_name = 'video_play'

更新---
我可以让下面的查询在microsoftsqlserver中工作，有什么建议可以让它在myphp上工作吗？

WITH uservideoviewvideo (date, (user_id) AS 
    (SELECT  DISTINCT date, user_id 
    FROM clickstream_videos
    WHERE event_name ='video_play' and user_id IS NOT NULL) 
SELECT currentday.date AS date, COUNT(currentday.user_id) AS users_view_videos, COUNT(nextday.user_id) AS users_view_next_day
FROM userviewvideo currentday
LEFT JOIN userviewvideo nextday ON currentday.user_id = nextday.user_id AND DATEADD(DAY, 1, currentday.date) = nextday.date
GROUP BY currentday.date

sql where-clause

来源：https://stackoverflow.com/questions/63045078/how-to-combine-where-clauses-with-dates

1条答案

按热度按时间

unguejic1#

你好像想要 lag() 以及 group by :

select cv.date, count(distinct user_id) as users_on_day,
       count(distinct case when prev_date = date - interval '1 day' then user_id end) as returning_users
from (select cv.*, lag(date) over (partition by user_id) as prev_date
      from clickstream_videos cv
      where event_name = 'video_play'
     ) cv
group by cv.date;

注意，日期函数高度依赖于数据库。因此，在数据库中比较日期的确切语法可能不同。

赞(0）回复(0）举报 2021-07-24

我来回答

如何将where子句与日期结合起来

1条答案

相关问题

热门标签

最新问答