Заполнение пробелов в запросе SQL

Извините за смутную тему, но я не мог думать, что поставить.

Вот моя проблема, я делаю запрос к таблице, которая возвращает мне количество элементов, связанных с днем. Я хочу убедиться, что если я делаю запрос к БД, я всегда получаю заданное количество строк. Например, представьте, что у меня есть следующая таблица, которая содержит журнал, когда люди заходят на сайт:

**WebsiteLogin**
id: Integer
login_date: Datetime

Затем я могу получить количество логинов для каждой даты, выполнив что-то вроде:

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

Что прекрасно работает и вернет мне данные, которые я хочу. Но представьте, мой сайт был довольно непопулярным по выходным. Возвращенные данные будут выглядеть так:

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

13 и 14 числа отсутствуют, потому что не было данных на эти даты. Можно ли как-нибудь изменить свой запрос, чтобы получить данные, содержащие все даты, по которым я запрашиваю. Например

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

Я думаю, я мог бы сделать это, если бы я создал таблицу, содержащую все даты за год, а затем использовал левое / правое соединение, но это действительно грязный способ сделать это.

Таким образом, есть какие-нибудь подсказки о том, как сделать это в SQL? Или программно это мой единственный выбор? Приветствия за любой вклад.

10.12.2008 09:30:19
4 ОТВЕТА
РЕШЕНИЕ

Я думаю, я мог бы сделать это, если бы я создал таблицу, содержащую все даты за год, а затем использовал левое / правое соединение, но это действительно грязный способ сделать это.

Нет. Это довольно много, как это сделать. С другой стороны, вы можете использовать временную таблицу и заполнить ее только необходимым диапазоном дат.

Если бы только MS SQL имел виртуальные таблицы, где вы предоставили функцию генератора ...

1
10.12.2008 09:36:18

Для этого вам нужно написать хранимую процедуру, которая возвращает результат таблицы.

Он будет использовать цикл, который будет проходить каждый день, получать счетчик и сохранять его в строке временной таблицы, а затем возвращать эту таблицу в качестве набора результатов.

Вот пример цикла сервера MS SQL:

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

2
10.12.2008 09:38:20

Вам не нужно создавать временную таблицу или подобное, вам просто нужен источник с достаточным количеством строк для построения отсутствующих дат:

Я не знаю mysql, но если он поддерживает "connect by", вы можете сделать следующее:

(это в оракуле)

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
/

если у mysql нет соединения, вы можете просто присоединиться к некоторой произвольной таблице с достаточным количеством строк в ней и ограничить результат количеством необходимых строк:

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

хотя и не так аккуратно, и, очевидно, вы должны знать, что в таблице водителей достаточно строк.

1
10.12.2008 10:53:10
Отличный способ сделать это. Мне это нравится.
Jonathan 10.12.2008 14:02:32

Я знаю, что это не MySQL, но я использую следующую функцию в MSSQL (см. Ниже для версии 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 @tmpVal DEFAULT VALUES

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

    RETURN
END

Итак, чтобы использовать его в вашем примере, я бы попробовал что-то вроде:

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

В качестве альтернативы, и с огромным повышением скорости моего запроса, вы можете исследовать эту запись в блоге , которая делает то, что делает мой код выше, но будет работать во всех версиях SQL.

Он объясняет это больше там, но 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
0
10.12.2008 14:32:02