代码之家 › 专栏 › 技术社区 › rmeador

用于按日期统计事件的SQL

date logging sql

rmeador · 技术社区 · 16 年前

我觉得我以前见过这个问题,但无论是搜索引擎还是谷歌都帮不了我。。。也许我只是不知道如何表达这个问题。我需要计算在给定的时间跨度内每天的事件数(在本例中为登录数),以便绘制网站使用情况图表。到目前为止,我的问题是:

select 
   count(userid) as numlogins, 
   count(distinct userid) as numusers, 
   convert(varchar, entryts, 101) as date 
from 
   usagelog 
group by 
   convert(varchar, entryts, 101)

这就完成了我所需要的大部分工作(我在每个日期获得一行作为输出,其中包含登录总数和该日期的唯一用户数)。问题是,如果没有人在给定日期登录,则该日期的数据集中将没有一行。我想让它添加一行,表示这些日期的登录次数为零。我可以想出两种方法来解决这个问题,但我觉得这两种方法都不是很优雅。

向结果集中添加一列,列出从时段开始到当前行日期之间的天数。在生成图表输出时,我将跟踪此值,如果下一行不等于当前行加1,则在图表中为缺少的每一天插入0。
创建一个“日期”表,该表包含感兴趣期间的所有日期以及与之对应的外部联接。可悲的是,我正在开发的系统已经有了一个用于此目的的表,其中包含了未来每个日期的一行。。。我不喜欢这样,我更愿意避免使用它,特别是因为该表是用于系统的另一个模块的,因此会引入对我当前开发的内容的依赖。

有没有更好的解决方案或更好的谷歌搜索词提示?谢谢

6 回复 | 直到 16 年前

sleske 16 年前

坦白地说,在构建最终输出时,我会以编程的方式完成这项工作。实际上,您试图从数据库中读取不存在的数据(没有数据的天数的数据)。SQL并不是真的适用于这类事情。

如果你真的想这样做,那么“日期”表似乎是你最好的选择。为了使它更好,您可以使用数据库的日期函数和派生表动态生成它。

Keith Williams 16 年前

最近我不得不做同样的事情。我在T-SQL中就是这样做的( YMMV的速度很快,但我发现它的性能足以覆盖数百万行事件数据):

DECLARE @DaysTable TABLE ( [Year] INT, [Day] INT )

DECLARE @StartDate DATETIME
SET @StartDate = whatever

WHILE (@StartDate <= GETDATE())
BEGIN

  INSERT INTO @DaysTable ( [Year], [Day] )
  SELECT DATEPART(YEAR, @StartDate), DATEPART(DAYOFYEAR, @StartDate)

  SELECT @StartDate = DATEADD(DAY, 1, @StartDate)
END

-- This gives me a table of all days since whenever
-- you could select @StartDate as the minimum date of your usage log)

SELECT days.Year, days.Day, events.NumEvents
FROM @DaysTable AS days
LEFT JOIN (
  SELECT
    COUNT(*) AS NumEvents
    DATEPART(YEAR, LogDate) AS [Year],
    DATEPART(DAYOFYEAR, LogDate) AS [Day]
  FROM LogData
  GROUP BY
    DATEPART(YEAR, LogDate),
    DATEPART(DAYOFYEAR, LogDate)
) AS events ON days.Year = events.Year AND days.Day = events.Day

Adam Robinson 16 年前

创建一个内存表(一个表变量),在其中插入日期范围,然后根据它加入logins表。按开始日期分组,然后可以执行聚合和计算。

David Snabel-Caunt 16 年前

我通常使用的策略是与查询的对立面联合,通常是一个为不存在的行检索数据的查询。

SELECT AVG(mark), course FROM `marks` 
    UNION
SELECT NULL, course FROM courses WHERE course NOT IN
    (SELECT course FROM marks)

您的查询将更加复杂,但同样的原则也应适用。对于第二个查询,您可能确实需要一个日期表

kishore 16 年前

选择1 您可以创建一个临时表,插入具有范围的日期,并使用usagelog进行左外部联接在计算结果集以生成最终输出时,可以通过编程方式插入缺少的日期

Quassnoi 16 年前

WITH q(n) AS
          (
          SELECT  0
          UNION   ALL
          SELECT  n + 1
          FROM    q
          WHERE   n < 99
          ),
    qq(n) AS 
          (
          SELECT  0
          UNION   ALL
          SELECT  n + 1
          FROM    q
          WHERE   n < 99
          ),
    dates AS
          (
          SELECT  q.n * 100 + qq.n AS ndate
          FROM    q, qq
          )
SELECT    COUNT(userid) as numlogins,
          COUNT(DISTINCT userid) as numusers,
          CAST('2000-01-01' + ndate AS DATETIME) as date
FROM      dates
LEFT JOIN
          usagelog
ON        entryts >= CAST('2000-01-01' AS DATETIME) + ndate
          AND entryts < CAST('2000-01-01' AS DATETIME) + ndate + 1
GROUP BY
          ndate

10,000 动态构建的日期,应该足够 30

SQL Server 100 每个的递归数 CTE ,这就是为什么内部查询可以返回到 100 每行。

如果你需要更多 10,000 ,只需添加第三个 CTE qqq(n) dates .