获取每年两个日期之间的天数的平均值、最大值和最小值的查询

Question

我有一桌租来的船：

租借：

• RentDate PK
• ReturnDate，
• BoatId

和其他不需要的字段

我需要执行一个查询，该查询将返回四列：

今年的租赁天数

• 今年的最大租赁天数

• 今年的最小租赁天数

现在，我有这样的查询：

SELECT 
YEAR(RentDate), 
AVG(DATEDIFF(DD, RentDate, ReturnDate))  AS AVERAGE, 
MAX(DATEDIFF(DD, RentDate, ReturnDate)) AS MAXIMUM, 
MIN(DATEDIFF(DD, RentDate, ReturnDate)) AS MINIMUM  
FROM RENTING
WHERE YEAR(RentDate) = YEAR(ReturnDate)
GROUP BY YEAR(RentDate)

问题是，我在想年底开始交房租的可能性，又过了一年就结束了- RentDate year != ReturnDate。我认为这个查询不包括这种可能性。

Answer 1

我认为这个查询不包括这种可能性

不，它没有，但它很容易修复-只需删除您添加的WHERE子句：

SELECT 
YEAR(RentDate), 
AVG(DATEDIFF(DD, RentDate, ReturnDate))  AS AVERAGE, 
MAX(DATEDIFF(DD, RentDate, ReturnDate)) AS MAXIMUM, 
MIN(DATEDIFF(DD, RentDate, ReturnDate)) AS MINIMUM  
FROM RENTING
/*WHERE YEAR(RentDate) = YEAR(ReturnDate)*/
GROUP BY YEAR(RentDate)

DATEDIFF运行得很好，即使在年份发生变化时也是如此。

Answer 2

从你对D Stanley的回答的评论中，你想要拆分任何跨越两年(或更长时间)的租金，将部分租金归因于每年。

为了做到这一点，你需要计算出每年有多少天的租期。据我所知，最简单的方法是使用Calendar Table。在您的情况下，您最感兴趣的是租赁的每一天的年份。

给定一个日历表：

CREATE TABLE Calendar
    ([CalendarDate] date, [CalendarYear] char(4))

您将租用表连接到日历中，日历将每个租期扩展为与租赁天数相等的行数。您可以按年对天数进行分组，以便按日历年拆分租金。

SELECT RentDate, COUNT(*) AS DayCount, CalendarYear
FROM Renting INNER JOIN Calendar ON CalendarDate >= RentDate 
  AND CalendarDate < ReturnDate
GROUP BY RentDate, CalendarYear

包括PK RentDate，以区分每个不同的租赁。否则，你最终会得到每年的一个数字，而不能计算出你的最小、最大和平均值。

要获得聚合值，请将第一个查询包装在另一个查询中：

SELECT CalendarYear,
       AVG(DayCount) AS AVERAGE,
       MAX(DayCount) AS MAXIMUM, 
       MIN(DayCount) AS MINIMUM  
FROM (
    SELECT RentDate, COUNT(*) AS DayCount, CalendarYear
    FROM Renting INNER JOIN Calendar ON CalendarDate >= RentDate 
      AND CalendarDate < ReturnDate
    GROUP BY RentDate, CalendarYear
) AS T

这是一个SQL Fiddle，您可以在操作中看到它。

Answer 3

从表面上看，你需要在年底拆分涵盖年终的租金。我将使用我喜欢称为测试驱动的查询设计(TDQD)来构建分段查询。

一年内的租金

这可能涵盖了大部分数据：

SELECT YEAR(RentalDate) AS RentalYear,
       DATEDIFF(dd, RentalDate, ReturnDate) AS RentalDays
  FROM Renting
 WHERE YEAR(RentalDate) = YEAR(ReturnDate)

跨越年终的租金

此查询处理起始年份的部分租金：

SELECT YEAR(RentalDate) AS RentalYear,
       DATEDIFF(dd, RentalDate, DATEFROMPARTS(YEAR(RentalDate), 12, 31)) AS RentalDays
  FROM Renting
 WHERE YEAR(RentalDate) + 1 = YEAR(ReturnDate)

此查询处理下一年的租赁部分：

SELECT YEAR(ReturnDate) AS RentalYear,
       DATEDIFF(dd, DATEFROMPARTS(YEAR(ReturnDate), 1, 1), ReturnDate) AS RentalDays
  FROM Renting
 WHERE YEAR(RentalDate) + 1 = YEAR(ReturnDate)

超过两年的租赁

这就更棘手了。我要指出的是，从理论上讲，一次租赁可以从2011-04-14开始，到2014-09-30结束(为了便于论证)，在这种情况下，2011年有部分年租金，2012和2013年有两次全年租赁(但一个有366天租赁，另一个有365天租赁)，然后在2014年有部分租赁。但我不打算解决这部分问题。

不带聚合的查询

前三个查询需要与UNION ALL组合，以创建运行聚合的原始数据：

SELECT YEAR(RentalDate) AS RentalYear,
       DATEDIFF(dd, RentalDate, ReturnDate) AS RentalDays
  FROM Renting
 WHERE YEAR(RentalDate) = YEAR(ReturnDate)
UNION ALL
SELECT YEAR(RentalDate) AS RentalYear,
       DATEDIFF(dd, RentalDate, DATEFROMPARTS(YEAR(RentalDate), 12, 31)) AS RentalDays
  FROM Renting
 WHERE YEAR(RentalDate) + 1 = YEAR(ReturnDate)
UNION ALL
SELECT YEAR(ReturnDate) AS RentalYear,
       DATEDIFF(dd, DATEFROMPARTS(YEAR(ReturnDate), 1, 1), ReturnDate) AS RentalDays
  FROM Renting
 WHERE YEAR(RentalDate) + 1 = YEAR(ReturnDate)

聚合数据

SELECT RentalYear,
       AVG(RentalDays) AS Average,
       MIN(RentalDays) AS Minimum,
       MAX(RentalDays) AS Maximum
  FROM (SELECT YEAR(RentalDate) AS RentalYear,
               DATEDIFF(dd, RentalDate, ReturnDate) AS RentalDays
          FROM Renting
         WHERE YEAR(RentalDate) = YEAR(ReturnDate)
        UNION ALL
        SELECT YEAR(RentalDate) AS RentalYear,
               DATEDIFF(dd, RentalDate, DATEFROMPARTS(YEAR(RentalDate), 12, 31)) AS RentalDays
          FROM Renting
         WHERE YEAR(RentalDate) + 1 = YEAR(ReturnDate)
        UNION ALL
        SELECT YEAR(ReturnDate) AS RentalYear,
               DATEDIFF(dd, DATEFROMPARTS(YEAR(ReturnDate), 1, 1), ReturnDate) AS RentalDays
          FROM Renting
         WHERE YEAR(RentalDate) + 1 = YEAR(ReturnDate)
       ) AS Rentals
 GROUP BY RentalYear