GROUP BY 与日期范围

发布于 2024-10-18 02:23:08 字数 558 浏览 3 评论 0原文

我有一个包含 4 列的表，id、Stream（文本）、Duration (int) 和 Timestamp （日期时间）。每次有人在我的网站上播放特定音频流时都会插入一行。 Stream 是名称，Duration 是它们正在监听的时间（以秒为单位）。我目前正在使用以下查询来计算一年中每周的总收听时间：

SELECT YEARWEEK(`Timestamp`), (SUM(`Duration`)/60/60) FROM logs_main
WHERE `Stream`="asdf" GROUP BY YEARWEEK(`Timestamp`);

这符合我的预期...呈现有数据的一年中每周的总收听时间。

但是，我想构建一个查询，其中的结果行数周内可能没有任何数据。例如，如果 2006 年第 26 周没有属于该周的行，那么我希望 SUM 结果为 0。

可以这样做吗？也许通过某种方式在某个日期范围内进行 JOIN ？

原文

I have a table with 4 columns, id, Stream which is text, Duration (int), and Timestamp (datetime). There is a row inserted for every time someone plays a specific audio stream on my website. Stream is the name, and Duration is the time in seconds that they are listening. I am currently using the following query to figure up total listen hours for each week in a year:

SELECT YEARWEEK(`Timestamp`), (SUM(`Duration`)/60/60) FROM logs_main
WHERE `Stream`="asdf" GROUP BY YEARWEEK(`Timestamp`);

This does what I expect... presenting a total of listen time for each week in the year that there is data.

However, I would like to build a query where I have a result row for weeks that there may not be any data. For example, if the 26th week of 2006 has no rows that fall within that week, then I would like the SUM result to be 0.

Is it possible to do this? Maybe via a JOIN over a date range somehow?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

送你一个梦 2024-10-25 02:23:08

尝试过的真正的老式解决方案是设置另一个包含一堆日期范围的表，您可以将其外部连接以进行分组（就像在另一个表中将包含所有具有开始/结束日期的周）。

在这种情况下，您可以使用一个充满 YEARWEEK 中的值的表：

下面是 sql 语句的草图：

SELECT year_weeks.yearweek , (SUM(`Duration`)/60/60) 

FROM year_weeks LEFT OUTER JOIN logs_main
   ON year_weeks.yearweek = logs_main.YEARWEEK(`Timestamp`)

WHERE `Stream`="asdf" GROUP BY year_weeks.yearweek;

The tried an true old school solution is to set up another table with a bunch of date ranges that you can outer join with for the grouping (as in the other table would have all of the weeks in it with a begin / end date).

In this case, you could just get by with a table full of the values from YEARWEEK:

And here is a sketch of a sql statement:

SELECT year_weeks.yearweek , (SUM(`Duration`)/60/60) 

FROM year_weeks LEFT OUTER JOIN logs_main
   ON year_weeks.yearweek = logs_main.YEARWEEK(`Timestamp`)

WHERE `Stream`="asdf" GROUP BY year_weeks.yearweek;

回复收藏 0 原文

一江春梦 2024-10-25 02:23:08

这是一个建议。可能不正是您正在寻找的东西。

但是假设您有一个简单的表，其中有一列 [year_week]，其中包含 1、2、3、4...52 的值

，那么理论上您可以：

SELECT

A.year_week, 
(SELECT SUM('Duration')/60/00) FROM logs_main WHERE
 stream = 'asdf' AND YEARWEEK('TimeStamp') = A.year_week GROUP BY YEARWEEK('TimeStamp')) 

FROM

tblYearWeeks A

这显然需要一些调整...i在其他项目中已经做了几个类似的查询，并且根据具体情况，这效果足够好。

如果您正在寻找一种基于单表/sql 的解决方案，那么这肯定也是我感兴趣的！

Here is a suggestion. might not be exactly what you are looking for.

But say you had a simple table with one column [year_week] that contained the values of 1, 2, 3, 4... 52

You could then theoretically:

SELECT

A.year_week, 
(SELECT SUM('Duration')/60/00) FROM logs_main WHERE
 stream = 'asdf' AND YEARWEEK('TimeStamp') = A.year_week GROUP BY YEARWEEK('TimeStamp')) 

FROM

tblYearWeeks A

this obviously needs some tweaking... i've done several similar queries in other projects and this works well enough depending on the situation.

If your looking for a one table/sql based solution then that is deffinately something I would be interested in as well!

回复收藏 0 原文

~没有更多了~