Preenchendo os espaços em uma consulta SQL

votos
2

Desculpe a vaga assunto, mas eu não conseguia pensar o que colocar.

Aqui está o meu problema, eu estou fazendo uma consulta em uma tabela que me retorna uma contagem de itens relacionados a um dia. Eu quero ter certeza de que se eu fizer uma consulta sobre a DB, eu sempre chegar um determinado número de linhas. Por exemplo, imagine que eu tenho a seguinte tabela que contém um registro de quando log pessoas em um site:

**WebsiteLogin**
id: Integer
login_date: Datetime

Posso, então, começar a contagem dos logins para cada data, fazendo algo parecido:

SELECT DATE(login_date), COUNT(*) FROM WebsiteLogin GROUP BY DATE(login_date)

Que funciona muito bem e vai me devolver os dados que eu quero. Mas imagine o meu site foi bastante impopular nos fins de semana. Os dados retornados seria parecido com:

2008-12-10, 100
2008-12-11, 124
2008-12-12, 151
2008-12-15, 141
2008-12-16, 111

A 13 e 14 estão em falta porque não havia dados para essas datas. Existe alguma maneira eu posso mudar minha consulta para que eu recebo dados que inclui todas as datas I consultar on. Por exemplo

2008-12-10, 100
2008-12-11, 124
2008-12-12, 151
2008-12-13, 0
2008-12-14, 0
2008-12-15, 141
2008-12-16, 111

Eu imagino que eu poderia fazer isso se eu criar uma tabela contendo todas as datas em um ano e, em seguida, usando uma esquerda / direita participar, mas isso é realmente maneira confusa de fazê-lo.

Assim, quaisquer pistas sobre uma maneira agradável de fazer isso em SQL? Ou é programaticamente minha única opção? Felicidades para qualquer entrada.

Publicado 10/12/2008 em 10:30
fonte usuário
Em outras línguas...                            


4 respostas

votos
2

Para fazer isso você precisa escrever um procedimento armazenado que retorna um resultado de mesa.

Ele usaria um loop que iria intervir através de cada dia e obter a contagem e armazená-lo em uma linha de uma tabela temporária, em seguida, retornar essa tabela como o conjunto de resultados.

Aqui está um exemplo de servidor MS SQL de um loop:

http://www.databasejournal.com/features/mssql/article.php/3100621/T-SQL-Programming-Part-2---Building-aT-SQL-Loop.htm

Respondeu 10/12/2008 em 10:38
fonte usuário

votos
1

Você não precisa criar uma tabela temporária, ou similar, você só precisa de uma fonte com linhas suficientes para construir as datas em falta:

Eu não sei mysql, mas se ele suporta "conectar", então você pode fazer o seguinte:

(Isto é, em Oracle)

select d login_date, count(login_date) count
from
    websitelogin wsl
    right outer join (
        select start_date+l-1 d from (select start_date, level l
        from (select min(login_date) start_date, max(login_date)-min(login_date)+1 num_days
        from websitelogin) connect by level <= num_days)) v on d=login_date
group by d
/

Se o MySQL não tem conectar você poderia simplesmente juntar-se em alguma tabela arbitrária com linhas suficientes nele em vez e limitar o resultado para o número de linhas necessárias:

select d login_date, count(login_date) count
from
    websitelogin wsl
    right outer join (select start_date+rownum-1 d from
(
select 
    min(login_date) start_date, 
    max(login_date)-min(login_date)+1 num_days
from websitelogin)v,all_objects
where rownum<=num_days
) v on d=login_date
group by d

não tão puro, porém, e, obviamente, você precisa saber que a tabela de condução tem linhas suficientes nele.

Respondeu 10/12/2008 em 11:25
fonte usuário

votos
1

Eu imagino que eu poderia fazer isso se eu criar uma tabela contendo todas as datas em um ano e, em seguida, usando uma esquerda / direita participar, mas isso é realmente maneira confusa de fazê-lo.

Não. Isso é muito bonito como fazê-lo. Por outro lado, você pode usar uma tabela temporária e preenchê-lo com apenas o intervalo de datas necessário.

Se apenas MS SQL tinha mesas virtuais, onde você forneceu uma função de gerador ...

Respondeu 10/12/2008 em 10:36
fonte usuário

votos
0

Eu sei que não é o MySQL, mas eu uso a seguinte função em MSSQL (veja abaixo a versão do MySQL):

CREATE FUNCTION dbo.DatesBetween (@start_date datetime, @end_date datetime)
RETURNS @DateTable TABLE (gen_date datetime)
AS 
BEGIN
    DECLARE @num_dates int
    DECLARE @tmpVal TABLE (a_count int identity(0,1))

    SELECT @num_dates = datediff(day, @start_date, @end_date)
    WHILE (select isnull(max(a_count), 0) from @tmpVal) < @num_dates
        INSERT IGNORE  @tmpVal DEFAULT VALUES

    INSERT IGNORE  @DateTable (gen_date) 
    SELECT dateadd(day, a_count, @start_date) FROM @tmpVal

    RETURN
END

Assim, para usá-lo no seu exemplo, gostaria de tentar algo como:

DECLARE @min_date datetime, @max_date datetime
SELECT @min_date = min(login_date), @max_date = max(login_date) 
FROM WebsiteLogin

SELECT m.gen_date 'login_date', isnull(l.num_visits, 0) 'num_visits'
FROM dbo.DatesBetween(@min_date, @max_date) as d
LEFT OUTER JOIN (SELECT DATE(login_date) 'login_date', COUNT(*) 'num_visits'
             FROM WebsiteLogin 
             GROUP BY DATE(login_date)) AS l ON d.gen_date = l.login_date

Alternativamente, e com uma enorme melhoria de velocidade na minha consulta, você poderia investigar esta entrada do blog , o que faz o meu código acima faz, mas irá funcionar em todas as versões do SQL.

Ele explica que mais lá, mas o SQL é:

DECLARE @LowDate DATETIME
SET @LowDate = '01-01-2006'

DECLARE @HighDate DATETIME
SET @HighDate = '12-31-2016'

SELECT DISTINCT DATEADD(dd, Days.Row, DATEADD(mm, Months.Row, DATEADD(yy, Years.Row, @LowDate))) AS Date
FROM
(SELECT 0 AS Row UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
 UNION ALL SELECT 30 -- add more years here...
) AS Years
INNER JOIN
(SELECT 0 AS Row UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 UNION ALL SELECT 10 UNION ALL SELECT 11
) AS Months
ON DATEADD(mm, Months.Row,  DATEADD(yy, Years.Row, @LowDate)) <= @HighDate 
INNER JOIN
(SELECT 0 AS Row UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
 UNION ALL SELECT 30
) AS Days
ON DATEADD(dd, Days.Row, DATEADD(mm, Months.Row,  DATEADD(yy, Years.Row, @LowDate))) <= @HighDate
WHERE DATEADD(yy, Years.Row, @LowDate) <= @HighDate
ORDER BY 1
Respondeu 10/12/2008 em 13:11
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more