How can I COUNT the number of rows in a database f

问题:

I've got a table in a Sybase DB with a column createdDateTime. What I want to be able to do is count how many rows were created between specific but accumulating time periods, ie:
7:00 - 7:15
7:00 - 7:30
7:00 - 7:45
7:00 - 8:00 ... and so on until I have the last time group, 7:00 - 18:00.

Is there a nice way to make one query in SQL that will return all the rows for me with all the row counts:
Time              Rows Created
7:00 - 7:15     0
7:00 - 7:30     5
7:00 - 7:45     8
7:00 - 8:00     15
...                    ...

I have a solution at the moment, but it requires me running a parameterised query 44 times to get all the data.

Thanks,

回答1:

I recently blogged about this exact topic, not sure if it works in Sybase though, here's the solution

declare @interval int
set @interval = 5
select datepart(hh, DateTimeColumn)
, datepart(mi, DateTimeColumn)/@interval*@interval
, count(*)
from thetable
group by datepart(hh, DateTimeColumn)
, datepart(mi, DateTimeColumn)/@interval*@interval

and more details http://ebersys.blogspot.com/2010/12/sql-group-datetime-by-arbitrary-time.html

回答2:

try this

 select count(*) from table groupedby createdDateTime where createdDateTime in (
SELECT *
FROM table 
WHERE createdDateTime between createdDateTime ('2011/01/01:07:00', 'yyyy/mm/dd:hh:mm')
AND createdDateTime ('2011/01/01:07:15', 'yyyy/mm/dd:hh:mm')
)

回答3:

Does Sybase have a CASE statement? If so try this:

SELECT SUM(CASE WHEN CreatedTime BETWEEN ('7:00:00' AND '7:14:59') THEN 1 ELSE 0) as '7-7:15',
       SUM(CASE WHEN CreatedTime BETWEEN ('7:15:00' AND '7:29:59') THEN 1 ELSE 0) as '7:15-7:30',
FROM MyTable
Where <conditions>

I use this a LOT in SQL Server.

回答4:

You could determine the quarter of the hour in which a row was created and group by that value. Please note that this is Oracle SQL, but Sybase probably has an equivalent.

select to_char(datetime_created, 'HH24') hour
, floor(to_char(datetime_created, 'MI')/15)+1 quarter
, count(1)
from my_table
group by to_char(datetime_created, 'HH24')
, floor(to_char(datetime_created, 'MI')/15)+1;

回答5:

You have irregular periods (some are 15 min length, others are 1 hour length, others are a few hours length). In that case, the best you can do is running a query with case statements:

with thetable as
(
SELECT  'TM' code, convert(datetime, '2011-04-15 07:01:00 AM') date, 1 id union all
SELECT  'TM', convert(datetime, '2011-04-15 07:05:00 AM'), 2 union all
SELECT  'TM', convert(datetime, '2011-04-15 07:08:00 AM'), 3 union all
SELECT  'TM', convert(datetime, '2011-04-15 07:20:00 AM'), 4 union all
SELECT  'TM', convert(datetime, '2011-04-15 08:25:00 AM'), 5 
)

SELECT '07:00 - 07:15' interval, sum(case when CONVERT(varchar, date, 108) between '07:00:00' AND '07:14:59' then 1 else 0 end) counting
FROM thetable

union 

select '07:15 - 08:00', sum(case when CONVERT(varchar, date, 108) between '07:15:00' AND '07:59:59' then 1 else 0 end)
from thetable

union 

select '08:00 - 09:00', sum(case when CONVERT(varchar, date, 108) between '07:59:59' AND '08:59:59' then 1 else 0 end)
from thetable

Now, if you did have regular intervals, you'd do something like that:

select counting, 
dateadd(ms,500-((datepart(ms,interval)+500)%1000),interval) intini
from
(
SELECT COUNT(1) counting, CONVERT(datetime, round(floor(CONVERT(float, date) * 24 * 4) / (24 * 4), 11)) interval
FROM 
(
SELECT  'TM' code, convert(datetime, '2011-04-15 07:01:00 AM') date, 1 id union all
SELECT  'TM', convert(datetime, '2011-04-15 07:05:00 AM'), 2 union all
SELECT  'TM', convert(datetime, '2011-04-15 07:08:00 AM'), 3 union all
SELECT  'TM', convert(datetime, '2011-04-15 07:20:00 AM'), 4 union all
SELECT  'TM', convert(datetime, '2011-04-15 08:25:00 AM'), 5 
) thetable
group by FLOOR(CONVERT(float, date) * 24 * 4) 
) thetable2

Notice that 24 * 4 is the interval of 15 minutes. If your interval is 1 hour, you should replace that with 24. If you interval is 10 minutes, it should be 24 * 6. I think you got the picture.