sql - 如何在 SQL Server 中创建包含计算字段的分组每日、每周和每月报告

Question

我正在使用 SQL Server (2012)，并使用下面的两个(简化的)表，如何创建 3 个单独的报告(每日、每周和每月)并包含以下计算字段:

1. new users created in this period
2. total number of users at this time


**Users**
userID          int
name            varchar(80)
userCreated     datetime


**Orders**
orderID         int
userID          int
orderCreated    datetime

我一直在摆弄这段代码:

SELECT CAST(DATEPART(dd,userCreated) as VARCHAR(2)) + '/' + CAST(DATEPART(mm,userCreated) AS VARCHAR(2)) + '/' + CAST(DATEPART(yyyy,userCreated) AS VARCHAR(4)) [Date],
    count(*) newSignUps,
    (select count(*) from users u2 WHERE u2.userCreated < u1.userCreated)
FROM users u1
WHERE userCreated BETWEEN '05/01/2014 00:00:00.000' and '05/31/2014 23:59:59.000'
GROUP BY DATEPART(dd,userCreated), DATEPART(mm,userCreated), DATEPART(yyyy,userCreated),userCreated

但要显示任何内容，它需要将“userCreated”字段添加到分组中...

对于我需要显示的报告:

每日:

date          new sign ups        users in system
17/03/2013    10                  100
18/03/2013    4                   104
19/03/2013    8                   112

每周:

week
13             8                   40
14             2                   42
15             5                   47

每月:

Jan            3                   54
Feb            9                   63
Mar            2                   65

我希望这是有道理的？谢谢...

Answer 1

我不确定我是否正确理解了您的问题，但这为您提供了每天创建的所有用户:

SELECT year(userCreated), month(userCreated), day(userCreated), count(*)
FROM Users
GROUP BY year(userCreated), month(userCreated), day(userCreated)

按月计算:

SELECT year(userCreated), month(userCreated), count(*)
FROM Users
GROUP BY year(userCreated), month(userCreated)

这是按周计算的:

SELECT year(userCreated), datepart(week, userCreated), count(*)
FROM Users
GROUP BY year(userCreated), datepart(week, userCreated)

根据您编辑缺少的总计字段我在此处为您提供月份查询的示例:

SELECT year(userCreated), month(userCreated), count(*) AS NewCount,
(SELECT COUNT(*) FROM Users u2 WHERE 
    CAST(CAST(year(u1.userCreated) AS VARCHAR(4)) + RIGHT('0' + CAST(month(u1.userCreated) AS VARCHAR(2)), 2) + '01' AS DATETIME) > u2.userCreated) AS TotalCount
FROM Users u1
GROUP BY year(userCreated), month(userCreated)

希望这对其他两个查询有所帮助。