MYSQL:每周返回的视频中，广告数量最多

Question

我有两个表，用户和事件：

**Users:**
usersid
age
geo_country
gender


**adbreaks:**
ts
videoid
ads_watched
geo_country
userid

我的问题是:在一月份的每周里，都会返回10个广告数量最多的视频。

这段代码做得对吗？

    SELECT video_id, COUNT(ads_watched) AS ad_watched
    FROM (SELECT video_id, MAX(ads_watched) FROM adbreaks) adw
    WHERE WEEK(ts) < WEEK('2017-02-01')
    GROUP BY video_id
    ORDER BY ad_watched DESC
    LIMIT 10;

任何提示都将不胜感激。

谢谢,

克劳迪娅

Answer 1

“一月的每周”是指每周1次查询，还是整个月都必须在结果中显示？如果postgresql中每周预期有一个查询：

SELECT video_id, COUNT(ads_watched) AS ad_watched
    FROM adbreaks
    WHERE EXTRACT(WEEK FROM ts) = 1 AND EXTRACT(YEAR FROM ts) = 2017
    GROUP BY video_id
    ORDER BY ad_watched DESC
    LIMIT 10;

然后使用WHERE EXTRACT(WEEK FROM ts) = 2,3, and 4运行查询，它将为您提供2017年1月的统计数据

如果您不关心年份，并且希望累积到所有可用数据，则删除该年约束。

在mysql中：

SELECT video_id, COUNT(ads_watched) AS ad_watched
    FROM adbreaks
    WHERE WEEK(ts) = 1 AND YEAR(ts) = 2017
    GROUP BY video_id
    ORDER BY ad_watched DESC
    LIMIT 10;