通过不在 GROUP 中的过滤器过滤 SQL GROUP [英] Filter SQL GROUP by a filter that is not in GROUP

问题描述

我正在开发类似基于 C# NET.Core 的应用程序的流服务.我在ping"服务器每分钟从客户端创建一个新的 SQL 条目，其中包含此查看器的时间和 ID.

以下是 5 分钟观看的示例:

StreamViewerId |跟踪日期1 |2020-09-25 05:05:001 |2020-09-25 05:06:001 |2020-09-25 05:07:001 |2020-09-25 05:08:001 |2020-09-25 05:09:001 |2020-09-25 05:10:00...

我需要制作一个图表条"基于该数据，我无法制作 1 分钟作为 xAxis 的图形，因此我尝试使每个图形点至少 5 分钟或更长时间.问题是，如果我只使用下面的查询，所有的观众都被乘以 x5，因为他们每分钟 ping 服务器，所以一组中有 5 行:

SELECT计数(LiveStreamViewerId)[计数]，dateadd(minute,(datediff(minute,0,TrackDate)/5)*5,0) [TrackDate]来自 LiveStreamViewerTracks哪里 LiveStreamId = 1GROUP BY dateadd(minute,(datediff(minute,0,TrackDate)/5)*5,0)

我实际上得到了这个结果:

计数 |跟踪日期5 |2020-09-25 05:05:00...

我想将此查看器视为只有一个寄存器(因为只有一个查看器，而不是 5 个).以下是预期结果:

计数 |跟踪日期1 |2020-09-25 05:05:00

我不能使用这两个 SQL 分区，因为我不能按查看者 ID 分组，因为我已经按日期时间分组，所以我不能过滤它.有人可以帮忙吗?

提前致谢！

解决方案

代替 COUNT(LiveStreamViewerID)，您可以使用 COUNT(DISTINCT LiveStreamViewerID) 删除重复项.>

Im working on a stream service like app based on C# NET.Core. Im "pinging" the server from client every minute and then create a new SQL entry with time and ID of this viewer.

Here is a example of 5 minutes of view:

StreamViewerId | TrackDate
             1 | 2020-09-25 05:05:00
             1 | 2020-09-25 05:06:00
             1 | 2020-09-25 05:07:00
             1 | 2020-09-25 05:08:00
             1 | 2020-09-25 05:09:00
             1 | 2020-09-25 05:10:00
             ...

I need to make a "chart bars" based on that data and i cant make the graphic with 1 minute as xAxis, so i trying to make each graphic point at least 5 minutes or more. The problem is that if i just use below query, all the viewers are been multiplicated x5, because they ping the server every minute, so there ill be 5 rows in a group:

SELECT 
   COUNT(LiveStreamViewerId) [Count], 
   dateadd(minute,(datediff(minute,0,TrackDate)/5)*5,0) [TrackDate] 
   FROM LiveStreamViewerTracks 
   WHERE LiveStreamId = 1
GROUP BY dateadd(minute,(datediff(minute,0,TrackDate)/5)*5,0)

Im actually getting this result:

Count | TrackDate
    5 | 2020-09-25 05:05:00
    ...

And i want to consider this viewer as only one register (because is only one viewer, not 5). Below is expected result:

Count | TrackDate
    1 | 2020-09-25 05:05:00

I can't use neither SQL partition, because i cant group by viewerid because im already grouping by datetime, so i cant filter it. Someone can help?

Thanks in advance!

解决方案

Instead of COUNT(LiveStreamViewerID), you can use COUNT(DISTINCT LiveStreamViewerID) which removes duplicates.

这篇关于通过不在 GROUP 中的过滤器过滤 SQL GROUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！