如何在SQL中计算平均日期出现频率

发布于 2024-10-05 09:56:51 字数 266 浏览 0 评论 0原文

我正在尝试对下表生成查询（仅限相关部分）：

Create Table [Order] (
  OrderID int NOT NULL IDENTITY(1,1),
  CreationDate datetime NOT NULL,
  CustomerID int NOT NULL
)

我希望查看 CustomerID 列表，其中包含每个客户的订单之间的平均天数。我很好奇这是否可以通过基于纯集的解决方案来完成，或者是否需要游标/临时表解决方案。

原文

I'm trying to produce a query on the following table (relevant portion only):

Create Table [Order] (
  OrderID int NOT NULL IDENTITY(1,1),
  CreationDate datetime NOT NULL,
  CustomerID int NOT NULL
)

I would like to see a list of CustomerIDs with each customer's average number of days between orders. I'm curious if this can be done with a pure set based solution or if a cursor/temp table solution is necessary.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

过期以后 2024-10-12 09:56:51

;WITH base AS
     ( 
     SELECT  CustomerID,
             ROW_NUMBER() over (partition BY CustomerID ORDER BY CreationDate, OrderID) AS rn
     FROM     [Order]
     )
SELECT   b1.CustomerID,
         AVG(DATEDIFF(DAY,b1.CreationDate, b2.CreationDate) )
FROM     base b1
         JOIN base b2
         ON       b1.CustomerID=b2.CustomerID
         AND      b2.rn        =b1.rn+1
GROUP BY b1.CustomerID

;WITH base AS
     ( 
     SELECT  CustomerID,
             ROW_NUMBER() over (partition BY CustomerID ORDER BY CreationDate, OrderID) AS rn
     FROM     [Order]
     )
SELECT   b1.CustomerID,
         AVG(DATEDIFF(DAY,b1.CreationDate, b2.CreationDate) )
FROM     base b1
         JOIN base b2
         ON       b1.CustomerID=b2.CustomerID
         AND      b2.rn        =b1.rn+1
GROUP BY b1.CustomerID

回复收藏 0 原文

~没有更多了~