Início / dba / Perguntas / 227305
Accepted
usr
Asked: 2019-01-17 09:34:44 +0800 CST

Inserir datas ausentes na tabela

  • 772

Eu tenho uma tabela com uma coluna para ID, Datee Frequencyde ocorrência para esse particular datee 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

Estou procurando inserir as datas para cada um IDque não está lá no momento (supondo que a data varie de 2014-05-18até 2014-05-20) e adicionar um valor de 0 Frequencypara eles.

Por exemplo, você pode ver que

ID1 tem uma observação para as três datas;

ID2 está faltando 2014-05-18e 2014-05-19;

ID3 está faltando 2014-05-19;

ID4 está faltando 2014-05-19e 2014-05-20.

Portanto, a saída deve ficar assim

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

Eu tentei o seguinte a partir desta pergunta . No entanto, isso só adicionará datas com 0 Frequencyque não estão presentes em toda a tabela, em vez de em um IDnível.

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 respostas

  1. Best Answer

    Usando sua consulta de exemplo base acima, esse código deve fazer o que você está procurando. As mudanças importantes:

    • CROSS JOIN para uma lista de IDs que devem estar no relatório
    • JUNTE-SE à tabela de dados usando Data e ID
    • SELECIONE o id da tabela CROSS JOIN

    O código:

    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. Assumindo esta tabela/dados:

    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 );

    Você precisa gerar a lista de datas no intervalo e, em seguida, produzir uma linha para cada data combinada com cada ID exclusivo que existe em seu intervalo e, em seguida, deixar a saída externa para seus dados reais.

    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;

    Uma alternativa potencialmente um pouco mais eficiente:

    ;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;

    Resultado:

    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

    Um problema com uma CTE recursiva é que, se você precisar dar suporte a um intervalo de datas > 100 dias, precisará adicionar OPTION (MAXRECURSION n).

    • 2

relate perguntas