SQL查询以汇总最近7天的数据

ryandawkins 发表于 Dev

瑞安德金斯

假设我有一个像这样的表结构。

CheckIn
- int checkInId pk
- int companyPositionId
- Date checkInDate

假设我要获取给定日期以来最近7天的所有签到次数。最好的方法是什么？现在，我要查询7个日期和公司职位。目前这太慢了，因为可能有很多companyPositions * 7天。如何将其汇总为一个查询？

生成最近7天的日期并构造一个长查询最简单吗？然后可以按7天中的每一天的日期范围进行分组计数吗？

理想的结果可能看起来像：

companyPositionId, date1Count, date2Count, date3Count, date4Count, date5Count, date6Count.

示例数据：

checkInId | companyPositionId | checkInDate
1         | 1                 | 1970-01-01
2         | 1                 | 1970-01-02
3         | 1                 | 1970-01-03
4         | 1                 | 1970-01-04
5         | 1                 | 1970-01-05
6         | 1                 | 1970-01-06
7         | 1                 | 1970-01-07
8         | 2                 | 1970-01-01
9         | 2                 | 1970-01-02
10        | 2                 | 1970-01-03
11        | 2                 | 1970-01-04
12        | 2                 | 1970-01-05
13        | 2                 | 1970-01-06
14        | 2                 | 1970-01-07*
15        | 2                 | 1970-01-07*

我当前的查询是这样的：

SELECT * FROM CheckIn
  WHERE (startDate) <= (inputDate) 
    AND (inputDate) <= (endDate)
    AND companyPositionId = (companyPositionId);

然后，我遍历从一天的开始到该天结束之间生成的每个startDate / endDate。然后是每个companyPositionId。

理想结果：

companyPositionId | date1Count | date2Count | date3Count | date4Count | date5Count | date6Count | date7Count
1                 | 1          | 1          | 1          | 1          | 1          | 1          | 1
2                 | 1          | 1          | 1          | 1          | 1          | 1          | 2

汤姆·H

您可以使用PIVOT命令或条件SUMs来执行此操作：

DECLARE @my_date DATE = CAST(GETDATE() AS DATE)

SELECT
    companyPositionId,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -7, @my_date) THEN 1 ELSE 0 END) AS date1Count,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -6, @my_date) THEN 1 ELSE 0 END) AS date2Count,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -5, @my_date) THEN 1 ELSE 0 END) AS date3Count,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -4, @my_date) THEN 1 ELSE 0 END) AS date4Count,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -3, @my_date) THEN 1 ELSE 0 END) AS date5Count,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -2, @my_date) THEN 1 ELSE 0 END) AS date6Count,
    SUM(CASE WHEN CAST(checkInDate AS DATE) = DATEADD(DAY, -1, @my_date) THEN 1 ELSE 0 END) AS date7Count
FROM
    CheckIn
WHERE
    checkInDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND DATEADD(DAY, -1, GETDATE())
GROUP BY
    companyPositionId