时间戳

首页 > 解决方案 > 计算订单之间的平均天数 最后三个记录 tsql

sql - 计算订单之间的平均天数 最后三个记录 tsql

问题描述

我试图取每个客户的平均值，但你没有按客户分组。我想从名为发票的表格中计算几个订单日期之间的平均天数。对于每个 BusinessPartnerID，订单之间的平均天数是多少，我想要最后三个记录订单的平均天数。我得到了每个用户的所有订单的平均值，但需要最后三个记录订单 在此处输入图像描述 的天数示例表如下

   ;WITH temp (avg,invoiceid,carname,carid,fullname,mobail)
AS
(
    SELECT AvgLag = AVG(Lag)  , Lagged.idinvoice,
     Lagged.carname ,
     Lagged.carid ,Lagged.fullname,Lagged.mobail
FROM 
(
 SELECT   
  
  (car2.Name) as carname ,
    (car2.id) as carid ,( busin.Name) as fullname, ( busin.Mobile) as mobail , INV.Id as idinvoice , Lag = CONVERT(int, DATEDIFF(DAY, LAG(Date,1) 
    OVER (PARTITION BY car2.Id ORDER BY Date ), Date))
     FROM  [dbo].[Invoice]    AS    INV
    JOIN  [dbo].[InvoiceItem] AS INITEM  on INV.Id=INITEM.Invoiceid
    JOIN [dbo].[BusinessPartner]  as busin on busin.Id=INV.BuyerId and Type=5
    JOIN [dbo].[Product] as pt on pt.Id=INITEM.ProductId and  INITEM.ProductId is not null and  pt.ProductTypeId=3  
    JOIN [dbo].[Car] as car2 on car2.id=INv.BusinessPartnerCarId 
    
    
) AS Lagged 
 GROUP BY
  Lagged.carname,
 Lagged.carid,Lagged.fullname,Lagged.mobail, Lagged.idinvoice

 -- order by Lagged.fullname
  
  )
 SELECT  *  FROM temp  where avg is not null   order by avg

标签： sqlsql-serverdatetimegreatest-n-per-groupdate-arithmetic

解决方案

我真的不明白您的查询与您的问题有何关系。从invoice具有列businesspartnerid和的名为的表开始date，您将如何获取每个业务伙伴的最后 3 张发票之间的日差平均值：

select businesspartnerid, 
    avg(1.0 * datediff(
        day, 
        lag(date) over(partition by businesspartnerid order by date),
        date
    ) avg_diff_day
from (
    select i.*,
        row_number() over(partiton by businesspartnerid order by date desc) rn
    from invoice i
) i
where rn <= 3
group by businesspartnerid

请注意，3 行仅给您 2 个间隔，这将被平均。

推荐阅读