获取价格历史数据的非重叠日期范围

Question

获取价格历史数据的非重叠日期范围

3

假设我有以下表格：

CREATE TABLE [dbo].[PricesHist]
(
   [Product] [varchar](6) NOT NULL,
   [Price] [float] NOT NULL,
   [StartDate] [datetime] NOT NULL,
   [EndDate] [datetime] NOT NULL
)


INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2C00000000 AS DateTime), CAST(0x00009D2C00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2D00000000 AS DateTime), CAST(0x00009D2D00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D2E00000000 AS DateTime), CAST(0x00009D2E00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3000000000 AS DateTime), CAST(0x00009D3000000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3100000000 AS DateTime), CAST(0x00009D3100000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3400000000 AS DateTime), CAST(0x00009D3400000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3500000000 AS DateTime), CAST(0x00009D3500000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3600000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3700000000 AS DateTime), CAST(0x00009D3700000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3800000000 AS DateTime), CAST(0x00009D3800000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3A00000000 AS DateTime), CAST(0x00009D3A00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3B00000000 AS DateTime), CAST(0x00009D3B00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3C00000000 AS DateTime), CAST(0x00009D3C00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3D00000000 AS DateTime), CAST(0x00009D3D00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3E00000000 AS DateTime), CAST(0x00009D3E00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3F00000000 AS DateTime), CAST(0x00009D3F00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4100000000 AS DateTime), CAST(0x00009D4100000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4200000000 AS DateTime), CAST(0x00009D4200000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4300000000 AS DateTime), CAST(0x00009D4300000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4400000000 AS DateTime), CAST(0x00009D4400000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4500000000 AS DateTime), CAST(0x00009D4500000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4600000000 AS DateTime), CAST(0x00009D4600000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4800000000 AS DateTime), CAST(0x00009D4800000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4A00000000 AS DateTime), CAST(0x00009D4A00000000 AS DateTime))

正如您所看到的，该月份的苹果有两个价格。4.90和2.50。为了整理这张表格，我需要将此信息作为日期范围而不是按天的行。

我可以很容易地使用Min和Max聚合来完成此任务，但这些范围会重叠，其他业务代码期望不重叠的范围。我还尝试使用自连接和row_number()来实现这一点，但没有取得太大的成功...

以下是我想要实现的输出结果：

Product | StartDate   |  EndDate    | Price
-------------------------------------------
Apples  | 01 Mar 2010 | 02 Mar 2010 | 4.90
Apples  | 03 Mar 2010 | 03 Mar 2010 | 2.50
Apples  | 05 Mar 2010 | 09 Mar 2010 | 4.90
Apples  | 10 Mar 2010 | 10 Mar 2010 | 2.50
Apples  | 11 Mar 2010 | 16 Mar 2010 | 4.90
Apples  | 17 Mar 2010 | 17 Mar 2010 | 2.50
Apples  | 18 Mar 2010 | 23 Mar 2010 | 4.90
Apples  | 24 Mar 2010 | 24 Mar 2010 | 2.50
Apples  | 25 Mar 2010 | 30 Mar 2010 | 4.90
Apples  | 31 Mar 2010 | 31 Mar 2010 | 2.50

请问最好的方法是什么？

非常感谢您的提前帮助，

- Anonymouse

2

最简单的方法是将其作为存储过程或将逻辑放入您的应用程序中来完成。 - James Black

这是来自以前软件的上次导入的旧数据。我只需要整理一下这个表格，因为它包含太多记录（每天和产品一行）。 - Anonymouse

为什么没有3月4日的行，但是7日和8日都有呢？ - Tom H

抱歉，这是因为商店在4号关闭了。 - Anonymouse

好的，但是为什么你还要把其他不存在的日期包含在内呢？这看起来不太一致。 - Tom H

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tom H · Accepted Answer

这应该可以让您接近答案。一旦确定了如何处理缺失的日期，您就可以相应地进行调整：

SELECT
    SD.Product,
    SD.Price,
    SD.StartDate,
    MAX(ED.EndDate) AS EndDate
FROM
    dbo.PricesHist SD
LEFT OUTER JOIN dbo.PricesHist ED ON
    ED.Product = SD.Product AND
    ED.Price = SD.Price
LEFT OUTER JOIN dbo.PricesHist LD ON
    LD.Product = SD.Product AND
    LD.Price <> SD.Price AND
    LD.EndDate < SD.StartDate
LEFT OUTER JOIN dbo.PricesHist LMD ON
    LMD.Product = SD.Product AND
    LMD.Price = SD.Price AND
    LMD.StartDate > ISNULL(LD.EndDate, '1900-01-01') AND
    LMD.StartDate < SD.StartDate
WHERE
    NOT EXISTS (SELECT * FROM dbo.PricesHist MD WHERE MD.Product = SD.Product AND MD.Price <> SD.Price AND MD.StartDate BETWEEN SD.StartDate AND ED.EndDate) AND
    LMD.Product IS NULL
GROUP BY
    SD.Product,
    SD.Price,
    SD.StartDate
ORDER BY
    SD.StartDate