- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
Дано:
CREATE TABLE IF NOT EXISTS `statistic` (
`type` tinyint(4) NOT NULL DEFAULT '0',
`date` date NOT NULL DEFAULT '0000-00-00',
`user` int(11) NOT NULL DEFAULT '0',
`count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`user`,`date`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
INSERT INTO `statistic` (`type`, `date`, `user`, `count`) VALUES
(1, '2013-10-14', 1, 6),
(2, '2013-10-14', 1, 6),
(2, '2013-10-16', 1, 11),
(2, '2013-10-15', 1, 1),
(2, '2013-10-16', 26, 4),
(2, '2013-10-16', 25, 1),
(2, '2013-10-16', 29, 3),
(2, '2013-10-16', 27, 1),
(2, '2013-10-17', 22, 1),
(2, '2013-10-21', 1, 2),
(1, '2013-10-21', 1, 1),
(1, '2013-10-22', 1, 1),
(2, '2013-10-22', 1, 1);
Задача: выбрать статистики (2 типа) за 30 дней, для текущего пользователя, в разрезе по дням.
Решение:
SELECT t.dates,
SUM(IF(type=1,count,0)) AS type1,
SUM(IF(type=2,count,0)) AS type2
FROM (
SELECT SUBDATE(DATE(NOW()),INTERVAL `index` DAY) AS dates
FROM
(
SELECT 0 AS `index` UNION
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 11 UNION
SELECT 12 UNION
SELECT 13 UNION
SELECT 14 UNION
SELECT 15 UNION
SELECT 16 UNION
SELECT 17 UNION
SELECT 18 UNION
SELECT 19 UNION
SELECT 20 UNION
SELECT 21 UNION
SELECT 22 UNION
SELECT 23 UNION
SELECT 24 UNION
SELECT 25 UNION
SELECT 26 UNION
SELECT 27 UNION
SELECT 28 UNION
SELECT 29
) AS t
) AS t
LEFT JOIN statistic ON t.dates=date AND user=1
GROUP BY dates
ORDER BY dates
Follow us!
SELECT 0 AS `index` UNION
SELECT 1 UNION
...
Что это, блеа? Таблица со всеми днями с 1900 по 2099 содержит всего-то 73к строк. Неужели так сложно нагенерить и держать в базе? Зато в ту таблицу можно напихать каких угодно атрибутов: и день недели, и признак последнего дня в неделе (для разных колейшенов), и признак последнего дня месяца, и кварталы, и полугодия, и названия дней недели на различных языках. Запилил такую табличку один раз и больше не паришься с запросами от бизнес юзеров.
Но, вспоминая свою бурную молодость - так раньше и делал... Всё, детские объёмы данных остались в прошлом :(
Плюс ко всему ваш подход не решает задачи описанной ТС. Там даже если статистики в какой-то день не было - хотят видеть или пусто или 0 в тот день.
Нельзя предсказать какой диапазон дат выберет юзер.
Если говорить, про предложенное мною решение - то да, мы НЕ показываем конечному пользователю все 200 лет из таблицы времени. Бизнесом было оговорено, что им достаточно двадцатилетнего интервала с 2000 года по 2020. Т.е. вью для пользователя выглядит где-то так:
у вас для дат ID в виде YYYDDMM? если да, то почему день и месяц равны нулю? :)
Таблица со временем ненужная? О_о
Я же написал выше, что в ней кроме самих дат ещё уйма других полезных атрибутов (у меня в базе данных 31 колонка).
> у вас для дат ID в виде YYYDDMM? если да, то почему день и месяц равны нулю? :)
Почти - YYYYMMDD. Месяцы и дни в таблице не равны нулю. Просто в это условие попадают все даты с 2000.01.01 по 2020.12.31 :)
а что вообще можно хранить в 31 колонке? ума не приложу.
>Почти - YYYYMMDD. Месяцы и дни в таблице не равны нулю. Просто в это условие попадают все даты с 2000.01.01 по 2020.12.31 :)
а столбца с типом "дата" нет? у нас работала одна дамочка... лепила бэкэнд с процедурами, кубы.. а когда глянули - у нее для поля int Year и Month.
Сами попросили :)
Переводил вручную со скандинавского на английский - могут быть ошибки.
> а столбца с типом "дата" нет?
Есть см. выше. Там пофиг по какой колонке фильтровать, можно и по дате.
> у нас работала одна дамочка... лепила бэкэнд с процедурами, кубы.. а когда глянули - у нее для поля int Year и Month.
Ну, если не придерживаться концепции построения хранилища (Кимбал или Инмон) данных, то можно различное гавно наворотить.
http://msdn.microsoft.com/ru-ru/library/ms174420.aspx
если говорить о 2012, то там есть и другие функции для дат
причем, как было сказано выше, сджойниться с таблицей dim_date в десяток К строк - особенно, когда речь идёт о детальных таблицах на 3+ порядка больше - для СУБД проще пареной репы
хотя бы потому, что эти функции нативные, и работают быстро.
для примера, я создал временную таблицу на основе представления, и добавил туда 2 столбца (Day - число месяца, и Month - номер месяца) с типом INT.
и написал два довольно простых запроса -
select
pf.RecordId,
t.Date,
t.Day,
t.Month
from Warehouse.Table as pf
join #t as t
on pf.Month = t.Date
select
pf.RecordId,
pf.month,
day(pf.month),
month(pf.Month)
from Warehouse.Table as pf
при построении плана запроса сервер разделил стоимость обоих запросов, и они равнялись - 71% и 29% соответственно.
чтобы было понятнее, для первого запроса была операция hash match (inner join), которая стоила 60% каста + еще была операция чтения 72к записей по 23байта с диска.
во втором запросе 2% на вычисление новых значений, а остальные 98% скан кластерного индекса.
используя таких таблицы вы засоряете свою базу + снижаете производительность сервера. единственный выигрыш от такого подхода - отсутствие в необходимости написания функций для написания каких-то данных, так как они уже просчитаны.
>сджойниться с таблицей dim_date в десяток К строк - особенно, когда речь идёт о детальных таблицах на 3+ порядка больше - для СУБД проще пареной репы
не проще, в данном случае это нативные функции, на выполнение которых ресурсов тратится меньше чем на операции чтения с диска + сравнение значений.
Во-первых, всех функций, покрывающих все 30 атрибутов нет.
Во-вторых, эти функции должны будут выполняться для каждой строки отдельно - это затратно.
> для примера, я создал временную таблицу на основе представления
Нормальную таблицу создайте, по типу того, что я кинул. И кластерный индекс на идентификатор.
> on pf.Month = t.Date
Шта? Месяц равен дате?
> для первого запроса была операция hash match (inner join), которая стоила 60% каста + еще была операция чтения 72к записей по 23байта с диска.
У вас в таблице Warehouse.Table где ключ интовый, который будет связью на таблицу время? И индекс на него. И не будет никак кастов и хэш матчей и полного вычитывания таблицы времени.
> используя таких таблицы вы засоряете свою базу +
Как вас задело-то... Я тоже за чистоту базы данных и за то, чтоб в ней были только нужные таблицы, так вот таблица времени - последнее, что я буду вычищать из БД.
> снижаете производительность сервера.
Табличка лежащая в БД производительность снижает?
> не проще, в данном случае это нативные функции, на выполнение которых ресурсов тратится меньше чем на операции чтения с диска + сравнение значений
Вы мыслите понятиями OLTP базы, в этом ключе - вы совершенно правы. Но в запросе ТС речь уже идёт об отчётной системе (OLAP), тут другие законы и другие представления.
не могу сказать точно про все, но большинство точно в 2012 сервере покрыто
>Во-вторых, эти функции должны будут выполняться для каждой строки отдельно - это затратно.
hash join более затратная операция, чем вычисление этих данных
>Шта? Месяц равен дате?
в данном случае, все данные агрегируются на первое число месяца, и мы оперируем данными за месяц. в поле тип DateTime, а в случае вспомогательной таблицы это дата.
>У вас в таблице Warehouse.Table где ключ интовый, который будет связью на таблицу время? И индекс на него. И не будет никак кастов и хэш матчей и полного вычитывания таблицы времени.
в таблице
в таблице тип datetime, и эти требования предоставили аналатики.
>Табличка лежащая в БД производительность снижает?
не таблица, а работа с этой таблицой, вместо нативных функций
вот такие пироги
в первом случае, у вас происходит вычисление предиката в момент выполнения, а во втором соединение по ключу, что совершенно разные вещи. не говоря уже о том, что Oracle и Sql Server это тоже разные вещи.
на моей практике, мне потребовалось написать определенный запрос, на выборку данных, разбитых на две таблицы.
одна - данные, вторая файлы, из которых она была запущена.
и я написал
select * from data_table where import_id in (select import_id from import_table where filename like '%blabla%')
не дожидаясь его выполнения я ушел на обед, а когда вернулся - запрос не выполнился. обратился к ораклистам, выяснилось, что нужно было писать
select * from data_table d
join import_table t
on d.import_id = t.import_id
where t.filename like '%blablabla%'
и запрос выполнился за пару секунд.
и я еще раз убедился, что oracle и sql server это разные предметные области.
в sql server есть функции типа day(), которые на входе получают datetime значение, а выдают номер дня в месяце.
select count(*)
from #t as t
join Warehouse.Table as pf
on pf.Month = t.Date
where t.day = 1
select count(*)
from Warehouse.Table as pf
where day(pf.month) = 1
и получаем результат
-----------
3853590
(1 row(s) affected)
-----------
3853590
(1 row(s) affected)
в плане выполнения же
если уж говорить о времени, то первый запрос - 1200 мс, а второй 826мс.
наброшу ещё немного магии
ну чтобы уж совсем просто и очевидно
1. сабж по всей видимости был на mysql
2. пример предрасчитанной таблицы у DBdev был на Sql Server
3. функции о которых я писал - на Sql Server
4. я уже писал, что Oracle и Sql Server несмотря на частичное соответствие ANSI являются разными базами данных, и то, что работает на одной базе хорошо, на другой может работать плохо.
например следующие запросы эквивалентны по плану выполнения в Sql Server, но в Oracle все будет иначе
подводя итоги всему вышесказанному - вы сравниваете жопу с пальцем. это разные СУБД со своими особенностями.
ах, везде эти профессионалы, везде эти жопы с пальцем...
Хреновые итоги :-\ Особенности есть, но вас явно заносит в сравнениях.
Нафига временная таблица тут? Заменить её на статическую + кластерный индекс.
Где целочисленный ключ в большой таблице ссылающийся на справочник дат? Добавить колонку dim_date_id и индекс на неё.
Почему не делаете запрос, как у дефекейстры? У него выборка по всем средам, а не номеру дня в месяце. Среда - это какой день в месяце?
Фишка отдельной таблицы со временем и заключается в том, что какую бы агрегацию или фильтрацию не захотели аналитики в контексте временных периодов - она выполнится одинаково быстро.
Вот в нем, походу, и таится все читерство этой схемы :) Перебор таблицы и проверка функции для каждой ее записи (миллионы итераций, если в ней нет индекса по дням недели) превращается в скан жалких 20к записей в таблице с днями и джойн результата с огромной таблицей. При этом срабатывает индекс той самой огромной таблицы по полю, связывающему ее с табличкой дат, и большая ее часть тупо не читается...
Как-то так?
P.S. Кластерный индекс заставляет записи с близким значением ключа лежать рядом?
Угу :) Борманд прохавал тему.
> Кластерный индекс заставляет записи с близким значением ключа лежать рядом?
Сортирует физически данные по нему на диске. В Оракле это называется как-то по другому.
>Нафига временная таблица тут? Заменить её на статическую + кластерный индекс.
что? временная таблица с # она такая же статичная как и все остальные, за исключением того, что создается она на диске в базе tempdb и живет только на время сессии, и по ее окончанию автоматически дропается.
>Где целочисленный ключ в большой таблице ссылающийся на справочник дат? Добавить колонку dim_date_id и индекс на неё.
Foreign Keys are a referential integrity tool, not a performance tool.
>Почему не делаете запрос, как у дефекейстры? У него выборка по всем средам, а не номеру дня в месяце. Среда - это какой день в месяце?
для этого напишите datepart(dw,поле) = желаемое значение. в любом случае работает так же
>Фишка отдельной таблицы со временем и заключается в том, что какую бы агрегацию или фильтрацию не захотели аналитики в контексте временных периодов - она выполнится одинаково быстро.
конечно, к такой таблице проще генерировать запросы, но все же это частности. мне например проще написать where datepart(dw,date) = 3, чем join table on blablabla.
если же использовать ваш подходит, то меня это будет обязывать использовать эту таблицу по всей базе, и использовать ссылку на нее, вместо реального значения. и в таком случае, будет много ненужных зависимостей, без которых можно легко обойтись без ущерба функциональности и производительности.
Капитанить не надо. Откуда я знаю на какой вы жёсткий разместили свою темпдб? Может на помедленнее, чем тот, где ваша база. Чистый эксперимент делайте, не увиливайте. Где кластерный индекс на вашей временной таблице?
> Foreign Keys are a referential integrity tool, not a performance tool.
Капитанить не надо, ч.2. Где у меня хоть слово о внешнем ключе? Если вы нашли это во фразе "ссылающийся на справочник" - то это лично ваша трактовка. Где индекс на колонке?
> для этого напишите datepart(dw,поле) = желаемое значение. в любом случае работает так же
Ну так пишите в своих запросах, вы же отчаянно пытаетесь что-то доказать.
> мне например проще написать where datepart(dw,date) = 3
Задачу ТС это НЕ решает, если в какой-то день не было транзакции - надо показать 0. Как вы это сделаете своим запросом?
> это будет обязывать использовать эту таблицу по всей базе
Ну, единая точка правды, чем плохо?
> будет много ненужных зависимостей
Пффф, вы это серьёзно? Внешние ключи никто не заставляет создавать.
> без ущерба функциональности и производительности
Уже ведь показали, что будет менее производительнее. Что менее функционально - я показал уже (1. Нет всех атрибутов во встроенных функциях 2. Нет возможности построить отчёт с отображением всех дат, даже когда не было транзакции).
вы мне предлагаете создавать отдельный ключ в таблицах для цифровых дат во всех таблицах? 72к строк datetime занимают около 500кб, а int около 250кб, так, что объем не сильно увеличивается, и на производительность это практически не влияет.
для проверки - создаем по 2 таблицы по 72к запией (value int) и (value datetime), и делаем их джоин по value = value. результат - 47% и 53% соответственно.
datetime в sqlserver хранится в виде двух int по 4 байта.
в данном случае, такая таблица обяжет меня использовать внешние ключи со всех таблиц, где у меня она встречается, использовать ее в запросах, поддерживать эти связи с генерацией ключей, а в результате как мне кажется я ничего не выиграю, ни в производительности, ни в удобстве.
что мне вам доказывать? пишите как хотите :) вы же мне отчаянно пытаетесь доказать, что я неправильно пишу.
все очень просто. select isnull(dt.value,0) from vDates as d left join data as dt on d.date = d.date
хорошо, это если вы аналитические кубы делаете, и вам нужно связать все эти данные. в моем же случае, у меня 50 таблиц с датами, которые по бизнес-логике в некоторых случаях пересекаются между собой по дате, а некоторые аж по 5 полям. и использовать одну точку будет неправильно.
кто тогда будет гарантировать целостность базы данных? 100500 миллионов строк в хранимых процедурах, которые будут руками валидировать все эти данные?
каких например? обрезать время можно например cast(cast(cast(date as float) as int) as datetime). первый день месяца dateadd(d,(day(date)-1)*-1,date). с последним посложнее, но все же
DATEADD(day,DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(d,(day(EndDate)-1)*-1,EndDate))+1,0)))-1,DATEADD(d,(day(EndDate)-1)*-1,EndDate))
каких функций не хватает в 2012 сервере?
> каких функций не хватает в 2012 сервере?
Блин, и вы еще спрашиваете? :) Бегом создавать новый тред, посвященный этому коду!
P.S. Мой мозг был сломан где-то на трети этого выражения, после чего я запутался в закрывающих скобках.
в оракле вообще есть last_day и куча других функций с датами, которых таки не хватает в 2012 сервере
В 2012 надеюсь добавили функцию сборки даты из трех компонент?
P.S. Вообще M$SQL сервер мне показался самым унылым из всех остальных по набору функций для работы с датами и строками. Слава богу, что работать с ним пришлось совсем немного.
Не барскоеСУБДшное это дело.
Хотя из версии к версии свистелок и перделок всё больше и больше...
раньше не было
Ой, так функции для сборки даты из компонентов не хватает в MSSQL :) Ну значит не прокатит.