查询返回最后一个ID和第一个ID的收入值

Question

我被要求报告一家小型卡车运输公司的收入数字。基本数据包括装载ID、司机ID、出行状态、交付日期、起始城市、目的地城市、拆分编号和收入。通常，负载在两个或更多驱动程序之间分配，但收入仅附加到第一个驱动程序。直到最后一次分装完成，我才能报告收入。下面是我编写的查询，但它返回与Load ID的拆分相关的所有行(342135)

select 
    tn.ctripnumber as 'Load ID',
    tn.driverid as 'Driver ID'
    tn.ctripstatus as 'Trip Status',
    tn.ddeliverydate as 'Delivery Date',
    tn.corigcity as 'Origin City',
    tn.cdestcity as 'Destination City',
    tn.csplittripnumber as 'Split Number',
    "Revenue"
from tripnumber tn  
left outer join (
    select ctripnumber, sum(curevenue) as "Revenue" 
    from revenuedtl 
    group by ctripnumber
) r on tn.ctripnumber = r.ctripnumber 
where tn.ctripnumber like ('342135%')  
group by
    tn.ctripnumber,
    tn.driverid,
    tn.ctripstatus,
    tn.ddeliverydate,
    tn.corigcity,
    tn.cdestcity,
    tn.csplittripnumber,
    "Revenue"

下面是结果

​

下面是我想要得到的结果

​

Answer 1

快速回顾一下，我发现有几个挑战。

第一个问题是初始驱动程序(342135)和任何后续驱动程序(34215S)之间的ctripnumber (“加载ID")是不同的。一个快速的解决办法可能是更新您的group by和outer join语句以标准化ctripnumber，例如，剥离尾随的S-or您可能添加到ctripnumber中的任何其他后缀。

第二，有几个group by字段在不同的记录中会有所不同。例如，driverid和deliverydate总是不同的，所以如果你将它们保存在你的group by中，你总是会得到多条记录。为了缓解这种情况，您可能会执行类似MAX(deliverydate) AS 'Delivery Date'的操作，以便只选择最高的值。只选择第一个driverid比较复杂，需要您使用ROW_NUMBER() OVER()函数对记录进行排序等操作。

我说过，这个拆分仍然会给你留下一个问题，那就是确保你的拆分是fulfilled，的，而不是progress.中的<>E127<>。

假设您不需要为任何间隙驱动程序(例如，count(driverid)或sum(revenue))聚合数据，您应该能够执行以下操作：

JOIN        tripnumber tn_finished 
ON          tn_finished.ctripnumber =
  WHEN      tn.csplittripnumber is null
  THEN      tn.ctripnumber
  ELSE      tn_finished.ctripnumber
AND         tn_finished.ctripstatus = 'Completed'

注意：WHEN语句确保在交付未拆分的情况下，初始驱动程序的记录与自身联接，并且初始驱动程序完成了最终交付。

这样可以确保拆分已经完成。但这也解决了前两个问题，因为您现在可以使用tn.tripnumber获取基本加载ID，使用tn.driverid获取初始驱动程序，使用tn_finished.deliverydate获取最终交付日期-而且您根本不需要为group by大惊小怪。

同样，这只有在完全忽略所有间隙记录(即，没有进行初始提取或最终交付的司机)的情况下才有效，所以只有在您为了这份报告而安全地忽略这些记录的情况下，这才有意义。

Answer 2

我认为你的逻辑可以通过使用窗口函数和过滤而不是聚合来简化。考虑一下：

select *
from (
    select
        t.*,
        row_number() over(partition by LoadId order by DeliveryDate desc) rn,
        sum(revenue) over(partition by LoadId) sum_revenue
    from tripnumber t
) t
where rn = 1

内部查询通过降序DeliveryDate对具有相同LoadId的组中的每条记录进行排名，并对每条LoadId的总收入求和。然后，外部查询只过滤每个LoadId的最后一次交付的记录。

Answer 3

为此，我建议使用CTE (公用表表达式)。CTE将给出第一个结果集，然后它下面的查询再次将其过滤到您想要的行。

由于您没有提供示例脚本，因此此代码未经过测试：

;with BaseQuery as 
(
    select 
        tn.ctripnumber as 'Load ID',
        tn.driverid as 'Driver ID'
        tn.ctripstatus as 'Trip Status',
        tn.ddeliverydate as 'Delivery Date',
        tn.corigcity as 'Origin City',
        tn.cdestcity as 'Destination City',
        tn.csplittripnumber as 'Split Number',
        'Revenue'
    from tripnumber tn  
    left outer join (
        select ctripnumber, sum(curevenue) as "Revenue" 
        from revenuedtl 
        group by ctripnumber
    ) r on tn.ctripnumber = r.ctripnumber 
    where tn.ctripnumber like ('342135%')  
    group by
        tn.ctripnumber,
        tn.driverid,
        tn.ctripstatus,
        tn.ddeliverydate,
        tn.corigcity,
        tn.cdestcity,
        tn.csplittripnumber,
        Revenue
)
select 'Load ID', 'Driver ID' 'Trip Status',
       'Delivery Date', 'Origin City', 'Destination City',
       'Split Number', Revenue
from   BaseQuery
where  "Revenue" is not null