优化SQL查询：统计FuelTankHistory表每日汽油销量 规避循环提升性能

现有业务表结构 #

涉及两张业务表[dbo].[FuelTankHistory]和[dbo].[Product]，建表语句如下：

CREATE TABLE [dbo].[FuelTankHistory](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [FuelLevel] [decimal](18, 2) NULL,
    [FuelLevelTime] [datetime] NULL,
    [TankId] [bigint] NULL,
    [Volume] [decimal](18, 2) NULL,
    [ProductId] [bigint] NOT NULL
)
GO

CREATE TABLE [dbo].[Product](
    [ProductId] [bigint] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NULL,
    [IsActive] [bit] NULL)
GO

统计规则 #

需生成指定日期范围的汽油销售报表，输出ReportDate、sale_petrol两个字段，规则如下：

• 每日统计周期为前一日13:00到当日13:00，TankId为1、2的是汽油罐，对应ProductId=1。每个油罐取周期内最早记录的FuelLevel为期初库存，最晚记录的FuelLevel为期末库存，单罐销量=期初库存-期末库存，求和得到当日汽油总销量。
• 当日期初库存必须等于前一日期末库存，避免跨13点临界点记录漏取导致数据不一致：如2021-08-01 12:59:45油位100、13:00:01油位90时，当日期初需取100。

现有实现问题 #

原有查询使用WHILE循环逐天统计，由于FuelTankHistory表有百万级历史数据，生成月度报表耗时极长，且存在临界点数据漏取问题，需无循环的优化查询方案，保证数据准确性。
原有参考代码如下：

declare @dt1 date='2021-08-01'
declare @dt2 date='2021-08-22'
DECLARE @tbl_temp_max TABLE(max_fuelLevel decimal(18,3),FuelStationTankId bigint,ReportDate date)
DECLARE @tbl_temp_min TABLE(min_fuelLevel decimal(18,3),FuelStationTankId bigint,ReportDate date)
WHILE ( @dt1 <= @dt2)
BEGIN
    DECLARE @date2 datetime= CAST(CAST(@dt1 AS DATE) AS DATETIME)
    set @date2 = DATEADD(HOUR, 13,@date2)
    DECLARE @date1 datetime= DATEADD(DAY, -1, @date2)
    insert into @tbl_temp_min
    select tbl.[FuelLevel],tbl.TankId ,@dt1
    from [dbo].[FuelTankHistory] tbl
    inner join (
    select min(t.[FuelLevelTime]) [date],t.TankId
      from [dbo].[FuelTankHistory] t
      where t.[FuelLevelTime] between @date1 and @date2 and t.TankId in (1,2) and t.ProductId=1
      group by t.TankId
    ) temp on tbl.TankId=temp.TankId and temp.[date]=tbl.[FuelLevelTime]
    insert into @tbl_temp_max
   select tbl.[FuelLevel],tbl.TankId ,@dt1
    from [dbo].[FuelTankHistory] tbl
    inner join (
    select max(t.[FuelLevelTime]) [date],t.TankId
      from [dbo].[FuelTankHistory] t
      where t.[FuelLevelTime] between @date1 and @date2 and t.TankId in (1,2) and t.ProductId=1
      group by t.TankId
    ) temp on tbl.TankId=temp.TankId and temp.[date]=tbl.[FuelLevelTime]
    SET @dt1= DATEADD(DAY, 1, @dt1)
END

select ReportDate,sum(sale) sale_petrol from
(select [max].*,ISNULL([min].min_fuelLevel, 0 ) [min_fuelLevel],(ISNULL([max].max_fuelLevel, 0 )-ISNULL([min].min_fuelLevel, 0 )) sale from @tbl_temp_max [max]
full join @tbl_temp_min [min] on [max].ReportDate=[min].ReportDate and [max].FuelStationTankId=[min].FuelStationTankId) total
group by ReportDate;

优化后无循环查询方案 #

DECLARE @dt1 DATE = '2021-08-01', @dt2 DATE = '2021-08-22';
-- 扩展查询时间范围，覆盖统计周期所需的前一天13点到最后一天13点
DECLARE @startTime DATETIME = DATEADD(HOUR, 13, DATEADD(DAY, -1, CAST(@dt1 AS DATETIME)));
DECLARE @endTime DATETIME = DATEADD(HOUR, 13, CAST(@dt2 AS DATETIME));

WITH FilteredData AS (
    SELECT 
        TankId,
        FuelLevel,
        FuelLevelTime,
        -- 计算每条记录所属的报表日期：13点前属于前一天报表，13点及以后属于当天报表
        ReportDate = CASE 
            WHEN CAST(FuelLevelTime AS TIME) >= '13:00:00' 
            THEN CAST(DATEADD(DAY, 1, FuelLevelTime) AS DATE)
            ELSE CAST(FuelLevelTime AS DATE)
        END
    FROM dbo.FuelTankHistory
    WHERE 
        FuelLevelTime BETWEEN @startTime AND @endTime
        AND TankId IN (1,2)
        AND ProductId = 1
),
RankedData AS (
    SELECT 
        TankId,
        ReportDate,
        FuelLevel,
        -- 按时间升序排序取第一条为期初，降序取第一条为期末
        ROW_NUMBER() OVER (PARTITION BY TankId, ReportDate ORDER BY FuelLevelTime ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY TankId, ReportDate ORDER BY FuelLevelTime DESC) AS rn_desc
    FROM FilteredData
)
SELECT 
    ReportDate,
    SUM(CASE WHEN rn_asc = 1 THEN FuelLevel ELSE 0 END) - SUM(CASE WHEN rn_desc = 1 THEN FuelLevel ELSE 0 END) AS sale_petrol
FROM RankedData
WHERE rn_asc = 1 OR rn_desc = 1
GROUP BY ReportDate
ORDER BY ReportDate;

方案说明 #

• 仅扫描一次符合条件的历史数据，避免循环逐次查询的IO开销，百万级数据下性能提升明显
• 按时间维度直接计算所属报表日期，自动对齐13点临界点规则，保证当期期初和上期期末数据一致
• 配合FuelTankHistory表的(ProductId, TankId, FuelLevelTime)联合索引，查询性能可进一步提升

内容的提问来源于stack exchange，提问作者Zulqarnain Jalil