主页 / dba / 问题 / 227305
Accepted
usr
Asked: 2019-01-17 09:34:44 +0800 CST

在表格中插入缺失的日期

  • 772

我有一个表格，其中有一列ID，Date和Frequency出现在那个特定的date和ID。

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-20      1
4     2014-05-18      6

我希望为每个ID当前不存在的日期插入日期（假设日期范围从2014-05-18到2014-05-20）并为这些添加一个值 0 Frequency。

例如，您可以看到

ID1 对所有三个日期都有观察；

ID2 缺失2014-05-18和2014-05-19;

ID3 缺失2014-05-19；

ID4 缺失2014-05-19和2014-05-20.

因此，输出应如下所示

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-18      0
2     2014-05-19      0
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-19      0
3     2014-05-20      1
4     2014-05-18      6
4     2014-05-19      0
4     2014-05-20      0

我从这个问题尝试了以下内容。但是，这只会添加Frequency不存在于整个表中而不是在一个ID级别上的 0 日期。

DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT c.date, Frequency = COALESCE(a.Frequency,0)
FROM dbo.Calendar AS c
LEFT OUTER JOIN dbo.table AS a
ON c.date = s.[date]
WHERE c.d >= @s
AND c.d < DATEADD(DAY, 1, @e);
sql-server sql-server-2012

2 个回答

  1. Best Answer

    使用上面的基本示例查询，此代码应该可以满足您的需求。重要变化：

    • CROSS JOIN 到应该在报告中的 ID 列表
    • 使用日期和 ID 加入数据表
    • 从 CROSS JOIN 表中选择 id

    编码：

    DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT 
    i.ID, 
    c.[date], 
    Frequency = COALESCE(a.Frequency,0)
FROM 
    dbo.Calendar AS c
CROSS JOIN (SELECT DISTINCT x.ID FROM dbo.table x WHERE x.date >= @s AND x.date < DATEADD(day, 1, @e)) AS i
LEFT OUTER JOIN 
    dbo.table AS a
ON 
    c.[date] = a.[date]
    AND a.ID = i.ID
WHERE 
    c.[date]>= @s
    AND c.[date] < DATEADD(DAY, 1, @e);
    • 3

  2. 假设此表/数据：

    CREATE TABLE dbo.MyTable
(
  ID int NOT NULL, 
  [date] date NOT NULL, 
  Frequency int,
  CONSTRAINT PK_MyTable PRIMARY KEY (ID,[date])
);

INSERT dbo.MyTable VALUES
(1,'2014-05-18',5 ),
(1,'2014-05-19',4 ),
(1,'2014-05-20',25),
(2,'2014-05-20',7 ),
(3,'2014-05-18',4 ),
(3,'2014-05-20',1 ),
(4,'2014-05-18',6 );

    您需要生成范围内的日期列表，然后为每个日期生成一行，并结合您范围内存在的每个唯一 ID，然后将该输出外部连接到您的实际数据。

    DECLARE @s date = '20140518', @e date = '20140520';

;WITH n AS
(
  SELECT n = 0
  UNION ALL
  SELECT n + 1 FROM n
  WHERE n < DATEDIFF(DAY, @s, @e)
),
range AS
(
  SELECT [date] = DATEADD(DAY, n, @s) FROM n
),
IDs AS
(
  SELECT ID 
    FROM dbo.MyTable AS t
    INNER JOIN range
    ON t.[date] = range.[date]
    GROUP BY ID
)
SELECT 
  IDs.ID,
  range.[date],
  Frequency = COALESCE(t.Frequency, 0)
FROM range CROSS JOIN IDs
LEFT OUTER JOIN dbo.MyTable AS t
ON t.[date] = range.date 
AND IDs.ID = t.ID;

    一个可能稍微更有效的替代方案：

    ;WITH n AS
(
  SELECT n = 0 UNION ALL SELECT n + 1 FROM n
  WHERE n < DATEDIFF(DAY, @s, @e)
),
range AS
(
  SELECT [date] = DATEADD(DAY, n, @s) FROM n
),
src AS
(
  SELECT ID, [date], Frequency 
    FROM dbo.MyTable AS t
    WHERE EXISTS (SELECT 1 FROM range WHERE range.[date] = t.[date])
)
SELECT 
  IDs.ID,
  range.[date],
  Frequency = COALESCE(t.Frequency, 0)
FROM range
CROSS JOIN (SELECT ID FROM src GROUP BY ID) IDs
LEFT OUTER JOIN dbo.MyTable AS t
ON t.[date] = range.date 
AND IDs.ID = t.ID;

    输出：

    ID  date        Frequency
--  ----------  ---------
1   2014-05-18  5
1   2014-05-19  4
1   2014-05-20  25
2   2014-05-18  0
2   2014-05-19  0
2   2014-05-20  7
3   2014-05-18  4
3   2014-05-19  0
3   2014-05-20  1
4   2014-05-18  6
4   2014-05-19  0
4   2014-05-20  0

    递归 CTE 的一个问题是，如果您需要支持大于 100 天的日期范围，则需要添加OPTION (MAXRECURSION n).

    • 2

相关问题