Query to count within intervals in a table

I have this table:

我有这张桌子:

CREATE TABLE pgoals (
gname varchar(100) REFERENCES pplayers(name),
minute integer,
date date,
PRIMARY KEY (gname,minute,date) ) ;

The table is obviously filled with a number of goals.

该表显然充满了许多目标。

Out of this I want to get a query that shows the number of goals scored within intervals of ten minutes (1-10, 11-20, 21-30 etc.). However, I have no idea how I could do this, except that I should probably use the COUNT command.

除此之外,我想得到一个查询,显示在十分钟(1-10,11-20,21-30等)间隔内得分的目标数。但是,我不知道我怎么能这样做,除了我应该使用COUNT命令。

Any ideas?

Thanks in advance!

提前致谢!

5 个解决方案

#1

drop table pgoals;

CREATE TABLE pgoals (
gname varchar(100),
minute integer,
date date,
PRIMARY KEY (gname,minute,date) ) ;

insert into pgoals (gname  ,[minute] , [date])values ('A',1,GETDATE())      
         insert into pgoals (gname  ,[minute] , [date])values ('B',3,GETDATE());
         insert into pgoals (gname  ,[minute] , [date])values ('B',4,GETDATE()) ;
         insert into pgoals (gname  ,[minute] , [date])values ('B',5,GETDATE())   ;       
         insert into pgoals (gname  ,[minute] , [date])values ('A',11,GETDATE()) ;        
         insert into pgoals (gname  ,[minute] , [date])values ('A',13,GETDATE());
         insert into pgoals (gname  ,[minute] , [date])values ('B',14,GETDATE());
         insert into pgoals (gname  ,[minute] , [date])values ('B',15,GETDATE());
         insert into pgoals (gname  ,[minute] , [date])values ('A',23,GETDATE());
         insert into pgoals (gname  ,[minute] , [date])values ('A',33,GETDATE());

select date,
       minute/10*10 at,
       count(*)
  from pgoals
 group by date,
       minute/10;

date    at  goals
2017-01-30  0   4
2017-01-30  10  4
2017-01-30  20  1
2017-01-30  30  1

#2

This is what you need, small and elegant:

这就是你所需要的,小巧而优雅:

SELECT gname, date, CEILING(minute/10) AS minuteInterval
FROM     pgoals
GROUP BY minuteInterval;

#3

Here is a option with a Left Join with an ad-hoc tally table

这是一个带有左连接和ad-hoc计数表的选项

Declare @pgoals  table (gname varchar(100) , minute integer,date date)
Insert Into @pgoals values
 ('aa',4,'2016-01-01')
,('aa',10,'2016-01-01')
,('aa',15,'2016-01-01')
,('aa',25,'2016-01-01')

Select Interval = concat(N-9,' - ',N)
      ,Goals = count(minute)
 From (Select Top 10  N=10*Row_Number() Over (Order By Number) From master..spt_values ) A
 Left Join @pgoals B
   on minute between N-9 and N
 Group By N

Returns

Interval    Goals
1 - 10      2
11 - 20     1
21 - 30     1
31 - 40     0
41 - 50     0
51 - 60     0
61 - 70     0
71 - 80     0
81 - 90     0
91 - 100    0

#4

Based on the current information I came up with this please have a look if it is helpful

根据我提出的当前信息,请看看它是否有用

    declare  @goal as table (Id int ,name nvarchar(5) , [minute] int, [date] datetime )


         insert into @goal (Id ,name  ,[minute] , [date])values (1,'A',1,GETDATE())      
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'B',3,GETDATE())
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'B',4,GETDATE()) 
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'B',5,GETDATE())          
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'A',11,GETDATE())         
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'A',13,GETDATE())
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'B',14,GETDATE())
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'B',15,GETDATE())
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'A',23,GETDATE())
         insert into @goal (Id ,name  ,[minute] , [date])values (1,'A',33,GETDATE())



         select count(*)as goals,name,
        case when ([minute]/10) = 0 then 10 else (minute/10)*10 end as mints,

         date from @goal group by minute,name,date

#5

rextester link: http://rextester.com/MSW83979

rextester链接:http://rextester.com/MSW83979

create table pgoals (
    gname varchar(100)
  , minute integer
  , date date
  , PRIMARY KEY (gname,minute,date) 
);
insert into pgoals values
 ('a',1,'20170130')
,('a',9,'20170130')
,('a',12,'20170130')
,('a',14,'20170130')
,('a',34,'20170130')
,('a',54,'20170130');

query

with m as (
  select nf, nt 
    from (values (1,10),(11,20),(21,30),(31,40),(41,50),(51,60)) t(nf, nt)
)
select 
    [interval] ='('+convert(varchar(3),m.nf)+ '-' +convert(varchar(3),m.nt)+')'
  , goals      = count(g.minute)
  from m
    left join pgoals g on g.minute between m.nf and m.nt
  group by m.nf, m.nt
  order by m.nf

results:

+----------+-------+
| interval | goals |
+----------+-------+
| (1-10)   |     2 |
| (11-20)  |     2 |
| (21-30)  |     0 |
| (31-40)  |     1 |
| (41-50)  |     0 |
| (51-60)  |     1 |
+----------+-------+