在没有日期的查询结果中插入日期

Question

在没有日期的查询结果中插入日期

8

我们正在构建一个查询，以每小时、每天计算事件的数量。大多数情况下，某些小时没有任何活动，因此当运行查询时，每小时的活动计数会显示出来，但是存在间隙，查询会排除这些间隙。我们仍然希望显示没有活动的小时，并显示零，以便可以绘制零值图表。我们使用的查询如下...

select datepart(Year, dev_time) as Year,
    datepart(Month, dev_time) as Month,
    datepart(Day, dev_time) as Day,
    datepart(Hour, dev_time) as Hour,
    count(tdm_msg) as Total_ACTIVITES
from TCKT_ACT
where tdm_msg = ‘4162′ and dev_time >= DATEADD(day, - 1, GETDATE())
group by datepart(Year, dev_time) ,
    datepart(Month, dev_time) ,
    datepart(Day, dev_time),
    datepart(Hour, dev_time)
order by datepart(Year, dev_time) asc,
    datepart(Month, dev_time) asc,
    datepart(Day, dev_time) asc,
    datepart(Hour, dev_time) asc

- Fred Gordy

点赞，因为这个问题至少影响了全球 60 亿人口中的 2 人 :-)。 - paxdiablo

5个回答

3

首先，我基于Dave Markle描述的递归公共表查询创建了一个表函数（感谢Dave向我展示这个）。这非常方便，因为我只需要创建一次函数，就可以用它来分析任何区间。

if exists (select * from dbo.sysobjects where name = 'fn_daterange') drop function fn_daterange;
go

create function fn_daterange
   (
   @MinDate as datetime,
   @MaxDate as datetime,
   @intval  as datetime
   )
returns table
--**************************************************************************
-- Procedure: fn_daterange()
--    Author: Ron Savage
--      Date: 12/16/2008
--
-- Description:
-- This function takes a starting and ending date and an interval, then
-- returns a table of all the dates in that range at the specified interval.
--
-- Change History:
-- Date        Init. Description
-- 12/16/2008  RS    Created.
-- **************************************************************************
as
return
   WITH times (startdate, enddate, intervl) AS
      (
      SELECT @MinDate as startdate, @MinDate + @intval - .0000001 as enddate, @intval as intervl
         UNION ALL
      SELECT startdate + intervl as startdate, enddate + intervl as enddate, intervl as intervl
      FROM times
      WHERE startdate + intervl <= @MaxDate
      )
   select startdate, enddate from times;

go

如果你仅从该函数中进行选择，你将得到一个时间间隔表格，如下所示：

fn_daterange('12/14/2008 10:00:00', '12/14/2008 20:00:00', '01:00:00' )

返回结果如下：

startdate               enddate                 intervl                 
----------------------- ----------------------- ----------------------- 
2008-12-14 10:00:00.000 2008-12-14 10:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 11:00:00.000 2008-12-14 11:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 12:00:00.000 2008-12-14 12:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 13:00:00.000 2008-12-14 13:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 14:00:00.000 2008-12-14 14:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 15:00:00.000 2008-12-14 15:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 16:00:00.000 2008-12-14 16:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 17:00:00.000 2008-12-14 17:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 18:00:00.000 2008-12-14 18:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 19:00:00.000 2008-12-14 19:59:59.997 1900-01-01 01:00:00.000 
2008-12-14 20:00:00.000 2008-12-14 20:59:59.997 1900-01-01 01:00:00.000

然后我制作了一个事件数据的样本表：

    eventdate               eventnote            
    ----------------------- -------------------- 
    2008-12-14 10:01:00.000 oo! an event!        
    2008-12-14 10:01:00.000 oo! an event!        
    2008-12-14 10:01:00.000 oo! an event!        
    2008-12-14 10:01:00.000 oo! an event!        
    2008-12-14 10:23:00.000 oo! an event!        
    2008-12-14 10:23:00.000 oo! an event!        
    2008-12-14 10:23:00.000 oo! an event!        
    2008-12-14 11:23:00.000 oo! an event!        
    2008-12-14 11:23:00.000 oo! an event!        
    2008-12-14 11:23:00.000 oo! an event!        
    2008-12-14 11:23:00.000 oo! an event!        
    2008-12-14 11:23:00.000 oo! an event!        
    2008-12-14 14:23:00.000 oo! an event!        
    2008-12-14 14:23:00.000 oo! an event!        
    2008-12-14 14:23:00.000 oo! an event!        
    2008-12-14 19:23:00.000 oo! an event!        
    2008-12-14 19:23:00.000 oo! an event!        
    2008-12-14 19:23:00.000 oo! an event!        
    2008-12-14 19:23:00.000 oo! an event!        
    2008-12-14 19:00:00.000 oo! an event!        
    2008-12-14 19:00:00.000 oo! an event!        
    2008-12-14 19:00:00.000 oo! an event!        

    22 Row(s) affected

然后我用LEFT OUTER JOIN将它们连接在一起，像这样：

select
   dr.startdate,
   dr.enddate,
   count(me.eventdate) as eventcount
from
   fn_daterange('12/14/2008 10:00:00', '12/14/2008 20:00:00', '01:00:00' ) dr

   LEFT OUTER JOIN myevents me
      on ( me.eventdate between dr.startdate and dr.enddate)
group by
   dr.startdate,
   dr.enddate


startdate               enddate                 eventcount 
----------------------- ----------------------- ---------- 
2008-12-14 10:00:00.000 2008-12-14 10:59:59.993 7          
2008-12-14 11:00:00.000 2008-12-14 11:59:59.993 5          
2008-12-14 12:00:00.000 2008-12-14 12:59:59.993 0          
2008-12-14 13:00:00.000 2008-12-14 13:59:59.993 0          
2008-12-14 14:00:00.000 2008-12-14 14:59:59.993 3          
2008-12-14 15:00:00.000 2008-12-14 15:59:59.993 0          
2008-12-14 16:00:00.000 2008-12-14 16:59:59.993 0          
2008-12-14 17:00:00.000 2008-12-14 17:59:59.993 0          
2008-12-14 18:00:00.000 2008-12-14 18:59:59.993 0          
2008-12-14 19:00:00.000 2008-12-14 19:59:59.993 7          
2008-12-14 20:00:00.000 2008-12-14 20:59:59.993 0          

11 Row(s) affected

哇塞，这太棒了 - 我可以在工作中用它进行各种分析！ :-)

感谢 Fred 的问题和 Dave 关于常见表查询信息的提供！

Ron

- Ron Savage

2

我们遇到了类似的性能监控软件问题，但由于我们是DB2/z主机店，我们坚决反对使用SQL技巧来获得这些结果。对每个检索行执行“函数”的SQL查询在扩展性方面非常糟糕，如果我们试图使用它们，数据库管理员会开心地嘲笑我们。

相反，我们发现将数据库模式重构以包括每行中事件计数更容易（显然，我们的数据库管理员不介意使用更多的磁盘空间，只需要更多的CPU grunt）。对于您的情况，这将添加一个名为tdm_quant的列，您将为插入的每一行（即每个事件）设置为1。

然后，查询的第五个字段从count(tdm_msg)更改为sum(tdm_quant)，这将实现相同的结果。

除此之外，您可以插入一个特殊记录（每小时一次，或者在每天开始时插入24个记录，或者在1月1日填充整年的记录），其中tdm_quant字段设置为零。由于为零，这些记录不会影响sum(tdm_quant)，但您将获得所需的行为，即返回一天中每个小时的行，其中未发生任何事件的Total_ACTIVITIES为零。

您的查询的其余部分将不需要更改。

- Pax

1

在一次性扩展表格的成本上，这被认为是很不错的选择。一年一次的工作中，向容纳500,000多个500八位字节行的表格中添加8860（365 * 24）行，所以对于我们来说，额外的磁盘并不是非常重要的。我们可以获得更快的查询和报告，而它们每天需要执行数十次。 - paxdiablo

1

而且最好的部分是，我的奖金因为创新思维而增加了，我还因为让数据库管理员的工作更轻松而获得了奖励。不过，这对我来说是最好的 :-) - paxdiablo

0

听起来你可以使用一个“左连接”，使用另一个包含1到24个数字的表...

- eric

我认为至少需要日期和时间的组合 - 尽管可以肯定地说这些时间会“整点”出现。而在平均数据库管理系统中生成足够的正确范围内的值并不是一件容易的事情。 - Jonathan Leffler

0

基本的答案涉及到左外连接（LOJ）和显式的COUNT(column)，因为它不计算空值，但是COUNT(*)会计算所有行。难点在于生成一个表来执行LOJ。WITH子句和递归解决方案可以在许多DBMS中使用（如MS SQL Server，显然还有几乎肯定的DB2 - 可能还有其他DBMS）。

许多DBMS支持临时表和存储过程；这种组合可以用于使用适当的日期/时间字段值集合填充表，然后对该表执行LOJ（或更精确地说，从temp_table LEFT OUTER JOIN main_table ...）。虽然不太整洁，但在大多数地方都可以工作。

- Jonathan Leffler

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dave Markle · Accepted Answer

你需要创建一个包含日期和时间的表格，并且需要在这个表格和你的查询之间进行外连接。以下是我的解决方案。请注意，该解决方案仅适用于SQL Server 2005和2008。如果你没有这些平台，你需要实际上在你的数据库中创建一个时间表格以便进行连接：

DECLARE @MinDate DATETIME;
SET @MinDate =  CONVERT(varchar, GETDATE(), 101);

WITH times AS (
    SELECT @MinDate as dt, 1 as depth
    UNION ALL
    SELECT DATEADD(hh, depth, @MinDate), 1 + depth as depth
    FROM times
    WHERE DATEADD(hh, depth, @MinDate) <= GETDATE())
SELECT DATEPART(YEAR, t.dt) as [Year],
    DATEPART(MONTH, t.dt) as [Month],
    DATEPART(DAY, t.dt) as [Day],
    DATEPART(HOUR, t.dt) as [Hour],
    COUNT(tdm_msg) as Total_ACTIVITES
FROM times t
LEFT JOIN (SELECT * FROM TCKT_ACT WHERE tdm_msg = '4162' and dev_time >= @MinDate) a
    ON  DATEPART(HOUR, t.dt)  = DATEPART(HOUR, a.dev_time)
    AND MONTH(t.dt) = MONTH(a.dev_time)
    AND DAY(t.dt)   = DAY(a.dev_time)
    AND YEAR(t.dt)  = YEAR(a.dev_time)
GROUP BY DATEPART(YEAR, t.dt) ,
    DATEPART(MONTH, t.dt) ,
    DATEPART(DAY, t.dt),
    DATEPART(HOUR, t.dt)
ORDER BY DATEPART(YEAR, t.dt) asc,
    DATEPART(MONTH, t.dt) asc,
    DATEPART(DAY, t.dt) asc,
    DATEPART(HOUR, t.dt) asc
OPTION (MAXRECURSION 0); /* Just in case you want a longer timespan later on... */

请注意，顶部的WITH语句被称为递归公共表达式，是一种生成具有相对较少元素的连续表格的好方法，就像您在此处所拥有的一样。