本月每周至少看电影的所有用户的列表 [英] list of all users who watched at least a movie every week in this month

问题描述

create table active_users(
user_id numeric,
movie_streamed date
)


insert into active_users values (1,'2020-01-2'::date);

insert into active_users values (1,'2020-01-9'::date);

insert into active_users values (1,'2020-01-16'::date);

insert into active_users values (1,'2020-01-23'::date);

insert into active_users values (1,'2020-01-30'::date);

insert into active_users values (2,'2020-01-14'::date);

insert into active_users values (2,'2020-01-16'::date);

大家好，

我在寻找进行查询，这将帮助我获得本月每周至少看过一部电影的用户（即测试数据）。给定数据，其中每个记录都有user_id，并且特定人何时观看电影（给定日期）。我想要一个通用的答案。不像每个月都有4周。因为在某些情况下可能还会有5个星期的时间。

I am looking for a query which will help me to get the users who watched at least a movie every week in this month(being the test data). Given the data where every record has the user_id and when that particular person has watched the movie given the date. I want a generic answer. Not like every month has 4 weeks. Because there could be some scenarios where there are 5 weeks in some cases too.

推荐答案

您可以使用 generate_series（1,5）从1到5进行计数，因为应该有5个不同的星期存在，甚至如您已经提到的那样可能还没有完成。

You can use generate_series(1,5) by counting from 1 upto 5, since there should 5 different weeks might exist even uncompleted as you already mentioned.

技巧是比较当月内每周的开始日期的不同计数：

The trick is to compare the distinct count of the beginning dates for each week within the current month :

SELECT u.user_id  
  FROM active_users u 
  JOIN generate_series( 1, 5 ) g  
    ON date_trunc('week', movie_streamed) 
     = date_trunc('week', current_date) + interval '7' day * (g-1) 
 GROUP BY u.user_id
HAVING COUNT(DISTINCT date_trunc('week', movie_streamed))  = 
            (
             SELECT COUNT(*)
               FROM generate_series( 1, 5 ) g
              WHERE to_char(current_date,'yyyymm') 
                  = to_char(date_trunc('week', current_date) 
                    + interval '7' day * (g-1),'yyyymm')                  
            );

演示

这篇关于本月每周至少看电影的所有用户的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！