Заполнение заглушек на SQL-запроса

голоса
2

К сожалению о туманной теме, но я не мог думать, что надеть.

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

**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 и четырнадцатого отсутствуют, потому что не было никаких данных для этих дат. Есть ли способ, что я могу изменить свой запрос так, чтобы получить данные, которые включают в себя все даты я запросана. Например

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 в 10:30
источник пользователем
На других языках...                            


4 ответов

голоса
2

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

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

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

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

Ответил 10/12/2008 в 10:38
источник пользователем

голоса
1

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

Я не знаю, MySQL, но если он поддерживает «соединить путем», то вы можете сделать следующее:

(Это в оракула)

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

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

Ответил 10/12/2008 в 11:25
источник пользователем

голоса
1

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

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

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

Ответил 10/12/2008 в 10:36
источник пользователем

голоса
0

Я знаю, что это не 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 IGNORE  @tmpVal DEFAULT VALUES

    INSERT IGNORE  @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
Ответил 10/12/2008 в 13:11
источник пользователем

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