Строки даты SQL для дней бездействия

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

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

Дни без активности не имеют соответствующих строк в таблице.

Например

Day1 - 7
Day2 - 8
Day4 - 7

И сгенерированный график будет неверным. Так как ему нужен 0 для Day3.

Теперь, не используя ничего, кроме SQL, можно ли создать эти значения для дней бездействия?

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

Заранее спасибо.


sql gaps-and-islands
person zz1433    schedule 22.04.2010    source источник
comment
SQL-сервер? MySQL? Что-то другое?   -  person Mark Byers    schedule 22.04.2010
comment
возможный дубликат stackoverflow.com/questions/75752/ - этот вопрос часто задавали на SO. Как правило, ответ состоит в том, чтобы присоединиться к временной таблице, содержащей все ваши даты, или обработать отсутствующие значения в логике вашего приложения.   -  person zombat    schedule 22.04.2010
comment
Другой вопрос, похожий на этот: stackoverflow .com/questions/2670183/   -  person Mark Byers    schedule 22.04.2010
comment
Спасибо всем за ответы, на самом деле я решил это по-другому, с классом словаря, добавил все значения, а затем для каждого дня проверял, существовало ли значение для этой даты, если не значение было принято равным 0. Но это не так. только с sql   -  person zz1433    schedule 24.05.2010

Ответы (4)


arrow_upward
1
arrow_downward

Ваше решение по созданию таблицы с 30 днями — очень простое и практичное решение.

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

person Mark Byers    schedule 22.04.2010

arrow_upward
1
arrow_downward

Использование Sql Server 2005+ и CTE Recursive (Использование общих табличных выражений) ты мог бы попробовать

DECLARE @Table TABLE(
        DateVal DATETIME,
        Visists INT
)

INSERT INTO @Table SELECT '01 Jan 2010', 10
INSERT INTO @Table SELECT '03 Jan 2010', 1
INSERT INTO @Table SELECT '05 Jan 2010', 30
INSERT INTO @Table SELECT '10 Jan 2010', 50

;WITH MinMax AS (
        SELECT  MIN(DateVal) Startdate,
                MAX(DateVal) EndDate
        FROM    @Table
),
DateRange AS(
        SELECT  StartDate DateVal
        FROM    MinMax
        UNION ALL
        SELECT  DateRange.DateVal + 1
        FROM    DateRange,
                MinMax
        WHERE   DateRange.DateVal + 1 <= MinMax.EndDate
)
SELECT  DateRange.DateVal,
        ISNULL(t.Visists,0) TotalVisits
FROM    DateRange LEFT JOIN
        @Table t ON DateRange.DateVal = t.DateVal

С выходом как

DateVal                 TotalVisits
----------------------- -----------
2010-01-01 00:00:00.000 10
2010-01-02 00:00:00.000 0
2010-01-03 00:00:00.000 1
2010-01-04 00:00:00.000 0
2010-01-05 00:00:00.000 30
2010-01-06 00:00:00.000 0
2010-01-07 00:00:00.000 0
2010-01-08 00:00:00.000 0
2010-01-09 00:00:00.000 0
2010-01-10 00:00:00.000 50
person Adriaan Stander    schedule 22.04.2010

arrow_upward
1
arrow_downward

Я бы не назвал этот SQL только, поскольку он использует специфическую функцию PostgreSQL, но в любой базе данных, которую вы используете, может быть что-то подобное.

В PostgreSQL есть замечательная функция: generate_series.

Вы можете использовать эту функцию для создания серии из 30 дней.

select current_date + s.a as dates from generate_series(0,30) as s(a);

 dates    
------------
 2010-04-22
 2010-04-23
 2010-04-24
 (.. etc ..)

Затем вы можете использовать это в запросе, например:

select vpd.visits, temp.dates
  from (select current_date + s.a as dates from generate_series(0,30) as s(a)) as temp
 left outer join visits_per_day vpd on vpd.day = temp.dates

 visits |   dates    
--------+------------
     10 | 2010-04-22
        | 2010-04-23
     20 | 2010-04-24
        | 2010-04-25
        | 2010-04-26
     30 | 2010-04-27
person Steve K    schedule 22.04.2010

arrow_upward
0
arrow_downward

Нет, не существует стандартного способа, использующего только SQL, для добавления неопределенного количества отсутствующих строк в результат SQL-запроса без предварительного сохранения этих строк в таблице.

Либо у вас может быть одна таблица, содержащая все даты, в течение которых будет работать ваше приложение, либо у вас может быть таблица, в которую вы помещаете только те даты, которые будут использоваться вашим текущим запросом. Если вы выберете второе решение, не забудьте запланировать, чтобы разные пользователи выполняли один и тот же запрос с разными диапазонами дат одновременно — вы захотите, чтобы таблица была временной и специфичной для пользователя, если ваша СУБД поддерживает это.

person Larry Lustig    schedule 22.04.2010