如何生成缺失数据的间隙

发布于 2024-12-29 10:21:26 字数 803 浏览 1 评论 0原文

我的数据结构如下：

在此处输入图像描述

使用以下 CTE：

WITH DateRange AS
(
SELECT CAST('2012-01-24 06:00' AS DATETIME) DateValue
UNION ALL
SELECT DATEADD(mi, 1, DateValue)
FROM    DateRange   
WHERE   DATEADD(mi, 1, DateValue) <= '2012-01-24 12:00'
)
SELECT DateValue FROM DateRange
OPTION (MAXRECURSION 0)

我创建了一个“日期集”看起来像这样：

在此处输入图像描述

问题是：原始数据集不具有CTE 中的相应日期（在提供的示例中，数据中不存在 2012-01-24 6:00）。

对于这些缺失的数据点，我希望将“NULL”视为它们的值。我想我也许能够利用我生成的 CTE 中的日期，但我不确定如何做到这一点。

我尝试过类似的事情但没有成功：

SELECT C.DateValue, D.Value 
FROM myCTE C 
LEFT OUTER JOIN myData D ON C.DateValue = D.Date
ORDER BY C.DateValue ASC;

原文

I have data that is structured as follows:

enter image description here

Using the following CTE:

WITH DateRange AS
(
SELECT CAST('2012-01-24 06:00' AS DATETIME) DateValue
UNION ALL
SELECT DATEADD(mi, 1, DateValue)
FROM    DateRange   
WHERE   DATEADD(mi, 1, DateValue) <= '2012-01-24 12:00'
)
SELECT DateValue FROM DateRange
OPTION (MAXRECURSION 0)

I have created a "Date Set" that look like this:

enter image description here

The Issue Is: There is some data in the original data-set that does not have a corresponding date in the CTE (In the provided example, 2012-01-24 6:00 is not present in the data).

For these missing data points, I would like to see "NULL" as their value. I thought I might be able to leverage the dates in the CTE I generated, but I'm not sure how I can do this.

I have tried something like this with no success:

SELECT C.DateValue, D.Value 
FROM myCTE C 
LEFT OUTER JOIN myData D ON C.DateValue = D.Date
ORDER BY C.DateValue ASC;

分享到QQ

分享到微博

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

发布评论

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

謌踐踏愛綪 2025-01-05 10:21:26

我无法复制您所看到的问题。我创建了下面的查询并对其进行了测试，它工作正常 - 您可以在您的环境中测试它吗？

Declare @var as Table (DateValue datetime)

Insert Into @var (DateValue) Values ('2012-01-24 06:01:00.000')
Insert Into @var (DateValue) Values ('2012-01-24 06:02:00.000')
Insert Into @var (DateValue) Values ('2012-01-24 06:03:00.000')

;WITH DateRange AS ( 
    SELECT CAST('2012-01-24 06:00' AS DATETIME) DateValue 
    UNION ALL 
    SELECT DATEADD(mi, 1, DateValue) 
    FROM    DateRange    
    WHERE   DATEADD(mi, 1, DateValue) <= '2012-01-24 12:00' 
) 

SELECT dr.DateValue, v.DateValue
FROM DateRange dr
        Left Outer Join @var v On dr.DateValue = v.DateValue
Order By v.DateValue
OPTION (MAXRECURSION 0)

I can't replicate the issue you're seeing. I've created the query below and tested it and it works fine - can you test this in your environment?

Declare @var as Table (DateValue datetime)

Insert Into @var (DateValue) Values ('2012-01-24 06:01:00.000')
Insert Into @var (DateValue) Values ('2012-01-24 06:02:00.000')
Insert Into @var (DateValue) Values ('2012-01-24 06:03:00.000')

;WITH DateRange AS ( 
    SELECT CAST('2012-01-24 06:00' AS DATETIME) DateValue 
    UNION ALL 
    SELECT DATEADD(mi, 1, DateValue) 
    FROM    DateRange    
    WHERE   DATEADD(mi, 1, DateValue) <= '2012-01-24 12:00' 
) 

SELECT dr.DateValue, v.DateValue
FROM DateRange dr
        Left Outer Join @var v On dr.DateValue = v.DateValue
Order By v.DateValue
OPTION (MAXRECURSION 0)

回复收藏 0 原文