sql - 计算平均租用时间的 SQL 查询是什么？

Question

我需要计算按品牌分组的汽车的平均租赁时间。

为简单起见，我的数据库中有以下 LEASE 表:

carMake | startDateTime | endDateTime

我未完成的 SQL 查询如下所示:

SELECT lease.carMake 
FROM lease
WHERE lease.startDateTime <= {?endDate} AND lease.endDateTime >= {?startDate}
SUM ... ???
GROUP BY lease.carMake ;

例子:

carMake |   startDateTime   | endDateTime
MB        01.01.2018 00:00     01.01.2019 00:00
BMW       07.06.2018 21:00     01.01.2019 00:00
MB        06.06.2018 00:00     07.06.2018 00:00 

查询范围在 06.06.2018 和 07.06.2018 之间的结果:

carMake | avgTime
MB        36 hours
BMW       3 hours

数据库是PostgreSQL

Answer 1

我不确定你使用的是什么 sql，但在 SQL Server 中你可以这样做

DECLARE @startdate DATETIME= '2018-06-06 00:00:00'
DECLARE @endDate DATETIME= '2018-06-07 00:00:00'


SELECT LeaseTable.CarMake, AVG(LeaseTable.LeaseHours)
FROM
(SELECT lease.carmake, 
    DATEDIFF(HH, case when lease.startDate <@startdate then @startdate else lease.startDate end, dateadd(DD, 1 ,case when lease.endDate > @endDate then @endDate else lease.endDate end)) as LeaseHours
    FROM lease
where 
    lease.startDate <= @endDate AND lease.endDate >= @startdate) as LeaseTable
GROUP BY LeaseTable.carMake 

让我把它分解一下:

SELECT carmake, AVG(LeaseHours) -- know the lease time avr by car manufacture
FROM
(Select
    LeaseInPeriod.carmake, 
    DATEDIFF(HH, LeaseInPeriod.PeriodStartDate, LeaseInPeriod.PeriodEndDate) as LeaseHours -- Know the lease time in hour
FROM
(SELECT lease.carmake, 
    case when lease.startDate <@startdate then @startdate else lease.startDate end as PeriodStartDate, -- Know the actual lease start date between the perid limits
    dateadd(DD, 1 ,case when lease.endDate > @endDate then @endDate else lease.endDate end) as PeriodEndDate -- Know the actual lease end date between the perid limits
    FROM AS lease
where 
    lease.startDate <= @endDate AND lease.endDate >= @startdate) AS LeaseInPeriod
) Leases
GROUP BY Leases.carMake