Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: БД очень долго восстанавливается из инкрементальных бэкапов

Здравствуйте!

Восстанавливаю базу (1ТБ) из 1 полного бэкапа (размер 200ГБ) и 5 инкрементальных (размер каждого ~40ГБ). При бэкапе и восстановлении создаю 8 каналов.


Время восстановления из полного бэкапа ~2 часа
Время восстановления из 5 инкрементальных бэкапов ~40 часов

Восстанавливаю(создание копии базы) следующей командой:
run {
        allocate auxiliary channel c1 type disk;
        allocate auxiliary channel c2 type disk;
        allocate auxiliary channel c3 type disk;
        allocate auxiliary channel c4 type disk;
        allocate auxiliary channel c5 type disk;
        allocate auxiliary channel c6 type disk;
        allocate auxiliary channel c7 type disk;
        allocate auxiliary channel c8 type disk;

                DUPLICATE DATABASE TO newdb
                  BACKUP LOCATION '/mnt/inc_backup/'
                  NOFILENAMECHECK;
}



+ Вывод iostat -mxdt 5 во время восстановления из инкрементальных бэкапов


31.08.2016 16:05:56
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0,00 4,20 0,00 1,00 0,00 0,02 41,60 0,01 10,00 0,00 10,00 10,00 1,00
sdc 0,00 13,20 0,00 197,80 0,00 3,82 39,58 0,44 2,21 0,00 2,21 1,67 33,10
sdb 0,00 13,20 0,00 197,60 0,00 3,78 39,14 0,38 1,91 0,00 1,91 1,56 30,76
sdd 0,00 14,60 0,00 199,80 0,00 3,80 38,91 0,48 2,38 0,00 2,38 1,82 36,38
sdf 0,00 13,60 0,00 196,40 0,00 3,78 39,46 0,42 2,16 0,00 2,16 1,69 33,24
sde 0,00 12,80 0,00 200,40 0,00 3,85 39,37 0,49 2,45 0,00 2,45 1,82 36,50
md126 0,00 0,00 0,00 449,60 0,00 19,03 86,69 0,00 0,00 0,00 0,00 0,00 0,00

31.08.2016 16:06:01
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 2,60 1,40 0,40 17,20 0,01 0,03 4,82 0,05 2,59 19,00 2,21 2,52 4,44
sdc 0,00 11,80 0,20 204,20 0,00 3,77 37,74 0,44 2,18 7,00 2,17 1,73 35,28
sdb 0,00 13,20 0,00 200,40 0,00 3,76 38,45 0,40 1,97 0,00 1,97 1,58 31,58
sdd 0,00 13,60 0,20 206,80 0,00 3,84 38,01 0,43 2,08 9,00 2,07 1,69 34,90
sdf 0,00 12,00 0,00 201,60 0,00 3,73 37,94 0,40 1,98 0,00 1,98 1,58 31,90
sde 0,00 13,00 0,00 201,20 0,00 3,77 38,41 0,44 2,18 0,00 2,18 1,67 33,54
md126 0,00 0,00 0,40 452,80 0,00 18,88 85,31 0,00 0,00 0,00 0,00 0,00 0,00

31.08.2016 16:06:06
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0,00 0,80 0,00 5,80 0,00 0,02 5,79 0,04 7,34 0,00 7,34 4,34 2,52
sdc 0,00 14,80 0,00 200,20 0,00 4,42 45,23 0,46 2,29 0,00 2,29 1,77 35,52
sdb 0,00 15,40 0,00 197,80 0,00 4,40 45,59 0,50 2,55 0,00 2,55 1,93 38,12
sdd 0,00 15,40 0,00 199,60 0,00 4,44 45,60 0,45 2,25 0,00 2,25 1,75 34,94
sdf 0,00 15,80 0,00 201,00 0,00 4,47 45,50 0,44 2,20 0,00 2,20 1,81 36,48
sde 0,00 16,80 0,00 198,00 0,00 4,43 45,85 0,46 2,30 0,00 2,30 1,72 34,04
md126 0,00 0,00 0,00 472,20 0,00 22,17 96,14 0,00 0,00 0,00 0,00 0,00 0,00

Дисковая система состоит из 5 дисков (sdc, sdb, sdd, sdf, sde) WD RAPTOR 10K, объединенных в RAID0
CPU - Intel Core i7
RAM - 32GB

Как можно ускорить восстановление из инкрементальных бэкапов?
Ответ: №1

> 5 дисков (sdc, sdb, sdd, sdf, sde) WD RAPTOR 10K, объединенных в RAID0
поменять вот это на SSD.

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

№2
Убрать инкрементальные бэкапы и делать полные каждый раз. 200ГБ это не много, если кажется много - включить компрессию.
Вопрос: Причина разрастания инкрементальных бэкапов.

1С УПП в связке с MSSQL 2012, пару дней наблюдается такая картина.
Вчера очищал журнал транзакций и инкрементальные бэкапы стали адекватного размера.
Сегодня журнал транзакций маленький, а инкрементальные бэкапы по 30 ГБ каждый.
Как выяснить причину такого поведения MSSQL, куда копать ?
В логах MSSQL ни варнингов ни ошибок, но это не нормальная ситуация.

К сообщению приложен файл. Размер - 120Kb
Ответ: мои претензии не к ответившим, а к вопрошающему.
ибо в MS SQL нет инкрементальных бэкапов.
и понять его каждый может по-своему.
названия бэкапов у него все одинаковые, сам-то он не фигеет различать их по типам,
если тип бэкапа в названии не отражен вообще?
и потом он сам несет околесицу:
автор
Вчера очищал журнал транзакций и инкрементальные бэкапы стали адекватного размера.

как "очищал", руками залез?
бэкап лога наконец сделал?
и еще раз, что у нас после этого "инкрементальные бэкапы"?
из его картинки это вроде диффы, или почему не меняется размер, если это бэкапы лога.
но если это диффы, то при чем тут "очистка лога" и зачем диффы делать каждые 20 минут?
Вопрос: ClickHouse: можно ли ускорить этот запрос?

Таблица:

CREATE TABLE c2.t
(
    date Date,
    datetime DateTime,
    name String,
    street String,
    code UInt32,

    count UInt64

) ENGINE = MergeTree(date, (date, name), 8192);

-- пробовал индекс (date, name, street, code)



Данных - 300 млн строк.

Запрос:
select name, street, code, sum(count) from c2.t
WHERE date >= toDate('2016-01-01') AND date <= toDate('2016-09-10')
GROUP BY name, street, code
ORDER BY sum(count) DESC limit 10;


Работает сканированием всех данных.

Можно ли заставить этот запрос работать гораздо быстрее? Как? Если нет решения с индексами, какие решения есть с агрегатами? Примеры?

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

Есть своё решение с агрегатами, но переложить на SQL не могу в силу пока непонятности их хелпа по части агрегатных функций, состояний и т.п.
Ответ: Попробовал заюзать агрегатные функции и их стейты.
Это то, чего интуитивно хотелось.
Еле понял что в их хелпе написано, тупой наверное (их хелп:

Нужное место:

Добавлю свои комменты к кускам их хелпа, которые лично мне проясняют значение слов в первоисточнике. Возможно мои комменты тупые.

Черновик и треш


AggregatingMergeTree

автор
Отличается от MergeTree тем, что при слиянии, выполняет объединение состояний агрегатных функций, хранимых в таблице, для строчек с одинаковым значением первичного ключа.

Слияние -- это чем занимается их сервер в фоне: когда на диске валяется 2 блока строк по 10К строк, то фоновый процесс сервера сливает эти 2 блока в 1. Ну типа как в LSM. При "обычном" (ENGINE = "MergeTree") слиянии, в НОВОМ блоке (слитом из двух старых) получается 200 строк. Строки в двух блоках могут быть целиком одинаковыми, но останутся раздельными, просто лягут рядом, в соответствии с сортировкой по ключу. А в этом движке, если у строк одинаковый привичный ключ, то останется ОДНА строка, а значения полей с типом "состояние агрегатной функции" будет объединено. Что значит объединено - определяется тем, как конкретный вид "стейта" объединяется с себе подобным. Далее будет понятнее. Состояния - они для того и придуманы, чтобы объединяться.

автор
Чтобы это работало, используются: тип данных AggregateFunction, а также модификаторы -State и -Merge для агрегатных функций. Рассмотрим подробнее.

Дебильная путающая фраза. Чтобы это работало. Блин. Чтобы это работало оно прежде всего существует, а не просто "используется". Возьмите синхрофазотрон. Что это? Ну это такая штука... А-а-а, вот надо было начинать...

AggregateFunction -- это такой тип. Этот тип умеет хранить упомянутое раньше "состояние агрегатной функции".

автор
Существует тип данных AggregateFunction. Это параметрический тип данных. В качестве параметров передаются: имя аргетатной функции, затем типы её аргументов.

Каких параметров? Параметров ТИПА. Это как шаблоны C++ -- у шаблона есть параметры (аргументы). Полный тип известен только когда известны все параметры. Генерики в java.

Т.е. если в качестве параметров этой штуке AggregateFunction передать (uniq, UInt64), то мы только теперь получим законченный ТИП с полным именем AggregateFunction(uniq, UInt64), который по смыслу означает "храню состояние агрегатной функции uniq, которая агрегирует (жрёт) аргументЫ типа UInt64". Как реализовано "состояние агрегатное функции" - отдельная тема гаданий. В C++ "состояние агрегатной функции" думаю можно представить как структуру, которая хранит 2 итератора на начало и конец некого диапазона вещей, которые агрегатная функция проагрегировала и хранит результат применения агрегатной функции на этом диапазоне. Т.е. состояние умеет быстро сказать текущий результат (он лежит в структуре) и умеет объединяться с другими такими состояниями (пересекаются диапазонами - придётся что-то пересчитать, не пересекаются - просто проаггрегировать (например сложить) 2 готовых результата из двух состояний).


CREATE TABLE t
(
    column1 AggregateFunction(uniq, UInt64),
    column2 AggregateFunction(anyIf, String, UInt8),
    column3 AggregateFunction(quantiles(0.5, 0.9), UInt64)
) ENGINE = ...

автор
Столбец такого типа хранит состояние агрегатной функции.

Я много думал. После ENGINE стоит "..."? Я что, могу запилить поле такого типа в мою таблицу на движке "MergeTree"? А как оно будет взаимодействовать с другими полями этой таблицы? Никак? Или как? Сейчас-то понятнее, но когда первый раз читаешь этот кусок шуба заворачивается.

автор
Чтобы получить значение такого типа, следует использовать агрегатные функции с суффиксом State.
Пример: uniqState(UserID), quantilesState(0.5, 0.9)(SendTiming) - в отличие от соответствующих функций uniq, quantiles, такие функции возвращают не готовое значение, а состояние. То есть, значение типа AggregateFunction.

Получить значение этого типа? А зачем мне его получать? Ну я напишу SELECT uniqState(UserID), что я получу? Состояние агрегатной функции uniq? И чё мне с ним делать? А UserD - это что? Имя столбца, ясно, а строки-то какие (какой диапазон строк) в это аггрегатное состояние попадут, значениекоторого я получу? Какой-то треш (у меня в голове).

автор
Значение типа AggregateFunction нельзя вывести в Pretty-форматах. В других форматах, значения такого типа выводятся в виде implementation-specific бинарных данных. То есть, значения типа AggregateFunction не предназначены для вывода, сохранения в дамп.

Короче мой SELECT uniqState(UserID) смысла не имеет, разве что выплюнет кусок бинарных данных (implementation-specific), коим представлено это состояние в памяти движка, что никому не нужно. Ок.

автор
Единственную полезную вещь, которую можно сделать со значениями типа AggregateFunction - это объединить состояния и получить результат, по сути - доагрегировать до конца. Для этого используются агрегатные функции с суффиксом Merge.
Пример: uniqMerge(UserIDState), где UserIDState имеет тип AggregateFunction.

Почти понятно. А до какого конца? А где начало? Нет, интуитивно понятно, но непонятно точно. Понятно, что можно взять 2 стейта агрегатной функции, засунуть их в соответствующий *Merge (uniqMerge) и тут-то получить нечто, выводимое, печатаемое, дампаемое и т.п. Но где начало? Ну это, откуда всё "доагрегируется" до некого "конца". Фигово сформулировано...

автор
То есть, агрегатная функция с суффиксом Merge берёт множество состояний, объединяет их, и возвращает готовый результат.

Понятно.

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

О господи, а раньше вы где были? Я гадал как MergeTree с таблицей напополам из полей типа AggregateFunction работает. Чуть моск не сломал! А оказывается существует специальный движок, мать перемать! Ну дальше фраза перекликается из фразы из самого начала главы. Если у тебя в 2 строках таблицы, созданной НА ДВИЖКЕ AggregatingMergeTree, одинаковый первичный ключ (а чё, там вторичный бывает разве?, ну ладно), то 2 такие строки станут одной строкой, а в её колонках с типом AggregatingMergeTree появится объединённое состояние двух экземпляров этой колонки в двух старых строках. Под "два" тут понимается диапазон строк, ясен пень.

автор
В таблицу, содержащую столбцы типа AggregateFunction невозможно вставить строчку обычным запросом INSERT, так как невозможно явно указать значение типа AggregateFunction. Вместо этого, для вставки данных, следует использовать INSERT SELECT с агрегатными функциями -State.

Где вы раньше были, я уже пытался это сделать и чуть не убился. Дальше интересно "невозможно явно указать". Слава Богу дожили до строки, где это наконец сформулировано. INSERT SELECT -- более менее понятно, но без примера наполовину. Блин, а пример-то выше парой абзацев валяется:
SELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID)

Блин, мы в какую сторону доку должны читать? Ладно...
В этом примере есть подзапрос "SELECT uniqState(UserID)....". В нём результат будет сгруппирован по RegionID (каждый уникальный RegionID только 1 раз в результате). Что будет сделано с рядом значений колонки UserID для одного уникального RegionID? Они будут ВСЕ засунуты в стейт uniqState(UserID). На один уникальный RegionID получим стейт. Этот стейт может быть засунут в uniqMerge, который скажет число уникальных юзеров в этом регионе.

Дальше идёт основной SELECT, возвращающий колонку uniqMerge(state) (state -- это uniqState(UserID) AS state из подзапроса), забив на регионы. uniqMerge получит весь ряд значений колонки state из подзапроса (для всех регионов), забьёт на RegionID (он же не указан в основном запросе) объединит эти state, засунет объединённое состояние в uniqMerge, выдаст человеко-читаемое нечто -- общее число уникальных юзеров во всех регионов. Зачем тут стейт? Он видимо хранит ВСЁ МНОЖЕСТВО (буквально массив, C++ std::set) уникальных UserID среди попавших в этот стейт. Множества ведь надо сливать. Нельзя сохранить в стейте просто int = "число уникальных", поскольку юзер может захотеть число уникальных среди 2 стейтов.

автор
При SELECT-е из таблицы AggregatingMergeTree, используйте GROUP BY и агрегатные функции с модификатором -Merge, чтобы доагрегировать данные.

Сформулировано абстрактно, но что конкретно хотели тут объяснить мы уже поняли, пол войска правда полегло уже.

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

Опять надо расшифровывать, выдумывая конкретные примеры.

автор
Создаём материализованное представление типа AggregatingMergeTree, следящее за таблицей test.visits:

CREATE MATERIALIZED VIEW test.basic
ENGINE = AggregatingMergeTree(StartDate, (CounterID, StartDate), 8192)
AS SELECT
    CounterID,
    StartDate,
    sumState(Sign)    AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY CounterID, StartDate;

Как-то сурово для новичков создавать таблицу селектом. А где CREATE TABLE и всё такое? Ну ладно. Селект в этом примере служит для установления типов колонок в новой таблице. AS задаёт имена колонок в новой таблице.

автор
Вставляем данные в таблицу test.visits. Данные будут также вставлены в представление, где они будут агрегированы:


INSERT INTO test.visits ...

А чё, нельзя сначала вставить в test.visits, а потом решить "блин, я хочу такие-то агрегаты", test.basic создать позже и чтобы он сам наполнился? Я задолбался передобавлять 200 млн строк в базовую таблицу при замене агрегатов.


автор
Делаем SELECT из представления, используя GROUP BY, чтобы доагрегировать данные:


SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;

Доагрегировать? А чё оно само там не доагрегировалось? А-а-а, всмысле доагрегировать до глаз юзера? Не доагрегировать, а просто проагрегировать тогда.

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

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

Ну незнаю, у меня без агрегатов 30 сек выполнялся запрос "дай топ 10 среди уникальных комбинаций 6 полей", а с агрегатами 0.08 сек из этого материализед вью.
Вопрос: Вывод всех недель конкретного года

Нашёл запрос для вывода порядкового номера недели, даты начала и даты конца:
SQL
1
2
3
4
5
6
7
8
9
10
SELECT Week.Week, 
 
IIf(Week.Week=1,DateSerial(myYear,1,1),DateAdd('ww',Week.Week-7,DateSerial(myYear,1,1)+(7-DatePart('w',DateSerial(myYear,1,1),7))+1)) AS FirstDayWeek, 
 
IIf(YEAR(
IIf(Week.Week=1,DateSerial(myYear,1,1)+(7-DatePart('w',DateSerial(myYear,1,1),7)),DateAdd('ww',Week.Week-1,DateSerial(myYear,1,1)+(7-DatePart('w',DateSerial(myYear,1,1),7)))))>myYear,DateSerial(myYear,12,31),
IIf(Week.Week=1,DateSerial(myYear,1,1)+(7-DatePart('w',DateSerial(myYear,1,1),7)),DateAdd('ww',Week.Week-1,DateSerial(myYear,1,1)+(7-DatePart('w',DateSerial(myYear,1,1),7))))) AS LastDayWeek, 
 
"2016" AS myYear
FROM Week;
В таблице Week - числа от 1 до 52.
Поскажите, как изменить этот запрос, чтобы недели начинались не с понедельника, а со вторника.

Добавлено через 13 часов 54 минуты
Заменил последнюю переменную 2 (понедельник) на 15 (вторник) в DatePart. Запрос перестал работать.

Добавлено через 14 минут
Методом тыка заменил на 3, заработал. Но в справке тройка не указана:
Константа Значение Описание

vbUseSystem 2460 Используются параметры API многоязыковой поддержки
vbSunday 1 Воскресенье (по умолчанию)
vbMonday 2 Понедельник
vbTuesday 15 Вторник
vbWednesday 16 Среда
vbThursday 17 Четверг
vbFriday 18 Пятница
vbSaturday 7 Суббота
Ответ: Не нужна такая сложная функция. Да и без таблицы можно обойтись. Пример с известным запросом Digits (автор Bonim) и маленькой функцией в ВБА. Запускайте запрос w1 и будет полный список недель с датами начала и конца
Вопрос: Запрос о выведении информации последних месяцев

ДОБРЫЙ ДЕНЬ!!!

Подскажите пожалуйста, как написать запрос выведения информации последних 3 месяцев?

информацию последних 5 дней нашел так
SQL
1
2
3
SELECT     Sales.SalesOrderHeader.OrderDate
FROM       Sales.SalesOrderHeader 
WHERE (Sales.SalesOrderHeader.OrderDate) > (SELECT (MAX(Sales.SalesOrderHeader.OrderDate))-5  FROM Sales.SalesOrderHeader)
Добавлено через 5 часов 12 минут
Нашел расчет начало предыдущего месяца, но как теперь сделать чтоб с этой даты выводились данные?

SQL
1
2
3
4
5
6
7
8
SELECT     Sales.SalesOrderHeader.OrderDate,
           dateadd(MONTH,datediff(MONTH,0,Sales.SalesOrderHeader.OrderDate),0)AS НачалоМесяца,
           EOMONTH(Sales.SalesOrderHeader.OrderDate)AS КонецМесяца,
           dateadd(MONTH,datediff(MONTH,0,EOMONTH(Sales.SalesOrderHeader.OrderDate,-1)),0)AS НачалоПредМесяца,
           EOMONTH(Sales.SalesOrderHeader.OrderDate,-1)AS КонецПредМесяца
           
FROM       Sales.SalesOrderHeader 
WHERE Sales.SalesOrderHeader.OrderDate > (SELECT MAX(Sales.SalesOrderHeader.OrderDate)-1 FROM Sales.SalesOrderHeader)
Добавлено через 4 часа 6 минут
ВСЕМ ГИГАНТСКОЕ СПАСИБО!!! РЕШИЛ САМ!!!

SQL
1
2
3
4
SELECT     Sales.SalesOrderHeader.OrderDate
           
FROM       Sales.SalesOrderHeader
WHERE Sales.SalesOrderHeader.OrderDate > (SELECT MAX(dateadd(MONTH,datediff(MONTH,0,EOMONTH(Sales.SalesOrderHeader.OrderDate,-2)),0))-1 FROM Sales.SalesOrderHeader)
Добавлено через 9 минут
ТЕПЕРЬ ВАЗНИКЛА НОВАЯ ПРОБЛЕМА!!! Как отобразить информацию за последние 3 недели?
Ответ: я решил по аналогии предыдущего месяца (сначала нашел первый день недели, а затем вычел 14 дней)

Теперь пытаюсь найти, первый день 3 предыдущего квартала (в году 4 квартала, 1 квартал с января по март включительно и т.д.), а это уже посложней

Добавлено через 36 минут
ВСЕМ СПАСИБО!!! РЕШИЛ САМ!

вот так нашел информацию с первого дня 3-тьего предыдущего квартала (по аналогии с предыдущими задачами)
SQL
1
2
3
4
SELECT     Sales.SalesOrderHeader.OrderDate
 
FROM       Sales.SalesOrderHeader 
WHERE Sales.SalesOrderHeader.OrderDate > (SELECT MAX(dateadd(QUARTER,datediff(QUARTER,0,Sales.SalesOrderHeader.OrderDate)-2,0))-1 FROM Sales.SalesOrderHeader)
Добавлено через 43 минуты
и по аналогии запрос вывода информации с начала 3-го предыдущего года!

SQL
1
2
3
4
SELECT     Sales.SalesOrderHeader.OrderDate
 
FROM       Sales.SalesOrderHeader 
WHERE Sales.SalesOrderHeader.OrderDate > (SELECT MAX(dateadd(YEAR,datediff(YEAR,0,Sales.SalesOrderHeader.OrderDate)-2,0))-1 FROM Sales.SalesOrderHeader)
Добавлено через 4 минуты
ТЕПЕРЬ ВОЗНИКЛА НОВАЯ ПРОБЛЕМА!!! Необходима получить информацию с 3-го предыдущего года 7 месяца (июля)?

Добавлено через 40 минут
сделал!!!

SQL
1
2
3
4
SELECT     Sales.SalesOrderHeader.OrderDate
 
FROM       Sales.SalesOrderHeader 
WHERE Sales.SalesOrderHeader.OrderDate > (SELECT MAX(DATEADD(MONTH,6,dateadd(YEAR,datediff(YEAR,0,Sales.SalesOrderHeader.OrderDate)-2,0)))-1 FROM Sales.SalesOrderHeader)
Добавлено через 1 час 7 минут
ИТОГОВЫЙ РЕЗУЛЬТАТ ВОПРОСА - на память!!!

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())-1,0) /* определяет начало предыдущего года */
SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,GetDate())-1,0) /* определяет начало предыдущего квартала */
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GetDate())-1,0) /* определяет начало предыдущего месяца */
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,GetDate())-1,0) /* определяет начало предыдущей недели */
SELECT DATEADD(DAY,DATEDIFF(DAY,0,GetDate())-1,0) /* определяет начало предыдущего дня */
SELECT DATEADD(DAY,DATEDIFF(DAY,0,GetDate()),0) /* определяет начало текущего дня */
SELECT DATEADD(DAY,DATEDIFF(DAY,0,GetDate())+1,0) /* определяет начало следующего дня */
/* и т.д. */
SELECT DATEADD(MONTH,6,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())-1,10))/* определяет предыдущий год и фиксирует определенный месяц и день */
SELECT DATEADD(DAY,10,DATEADD(MONTH,6,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())-1,0)))/* аналогично */
SELECT DATEADD(YEAR,-1, DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,10))/* определяет предыдущий год, месяц ... */
 
SELECT EOMONTH ( GETDATE(), -1 )/* определяет конец предыдущего месяца */
SELECT EOMONTH ( GETDATE(), 0 )/* определяет конец текущего месяца */
SELECT EOMONTH ( GETDATE(), +1 )/* определяет конец следующего месяца */
Вопрос: Запрос с DatePart

Здравствуйте, уважаемые.
По-тихоньку с вашей помощью двигается проектик... Жду очередной помощи.
Есть таблица со статистикой с большим кол-вом записей (500-1000 тыс. строк).
IDtestDTEquity
902.01.2015 13:18:00-136.00
902.01.2015 13:19:00-96.00
902.01.2015 13:20:00-136.00
902.01.2015 13:21:008.00
902.01.2015 13:22:000.00

Шаг записей в таблице 1 минута.
Для целей формирования читаемого отчета (графика) мне необходима группировка по полю [DT] (тип Date)/
Жесткие настройки худо-бедно получаются, например для группировки по неделям:
SELECT DatePart("ww",[tEquity]![DT],2) AS Week, Min(tEquity.Equity) AS [MinEquity]
FROM tEquity
GROUP BY DatePart("ww",[tEquity]![DT],2);

Но уже для группировки с шагом в 1 день DatePart у меня не "взлетел". Сделал
SELECT Min(tEquity.Equity) AS [MinEquity], Format([tEquity]![DT],"dd.mm.yyyy") AS DateDay
FROM tEquity
GROUP BY Format([tEquity]![DT],"dd.mm.yyyy");

Но хочется сделать единообразно, без костылей- формировать строку в ВБА и запускать...

Идея в том, чтобы пользователь задавал анализируемый период, а запрос формировался из расчета, допустим, 50 записей.,
т.е. если период=год, то группировка по неделям, период = 2 месяца, то группировка с "шагом" 1 день и т.п.
Спасибо заранее...
Ответ:
Akina
Fora74
может есть некая универсальная функция, на входе- период, на выходе- "градация" (час, день, неделя, месяц).
Нет. Но не вижу особой сложности в создании такой функции. Есть список "градаций", берём ту, которая попадает в диапазон (период / макс.кол.единиц) .. (период / мин.кол.единиц). Если таких несколько - самую близкую к геом. центру.
Вот только слишком большая разница между "градациями" - неплохо бы добавить промежуточных.

Топорное мое видение:
Допустим, пользователь задает период 3 мес (62 рабочих дня, 92 календарных).
Нам необходимо привести к 50 значениям на выходе.
На входе: 62(дня)*24(часа)*60(мин)=89 280 мин.
K=89 280:50=1785
На выходе: Запрос, где производится группировка каждые 1785 минут (записей).
Вот..., с вами поговорил, легче стало:)

авторAkina
Вот только слишком большая разница между "градациями" - неплохо бы добавить промежуточных.
Для начала не надо промежуточных, дайте базовые..
Думаю, мою функцию можно реализовать, но не хотелось бы, чтоб время исполнения было неразумно большим...
Вопрос: Создание запросов по базе данных

Имеется база данных Учебная. В ней по готовым таблицам нужно составить запросы (ЗапрЗадача1-4). В них нужно соответственно выполнить следующие запросы:
1. Список заказов за средние 3 дня недели (дни недели представлять конкретными датами на предоставляемые услуги)
2. Список наименования услуг, даты их предоставления с категорией абонентов «родственники» и «деловые партнеры»
3. Средняя стоимость оказанных услуг для каждой группы кодов услуг по исходящим вызовам
4. Количество оказанных услуг, тариф которых превышает 5 руб/мин
Надеюсь кто-нибудь может помочь или подсказать что писать в запросе, т.к. самостоятельные попытки составить запросы к успеху не привели.
Ответ: А по поводу остальных запросов что можете подсказать
Вопрос: Выбрать данные с группировкой по неделям.

У меня задача показать пользователю статистику прибыли сгруппированную по неделям (последние 7 недель например).
Есть такая таблица

profit
id | user_id | date | profit
1 1 2009-06-01 100.55
2 1 2009-06-03 110.55
3 1 2009-06-04 120.55
4 1 2009-06-07 140.55
...
5 1 2009-06-12 130.55
6 1 2009-06-13 150.55
7 1 2009-06-14 105.55

В итоге должны получить что то типа 2009-06-01 по 2009-06-07 472$
Еще заморочка в том, что надо все делать относительно дней недели т.е. считать начиная с воскресенья за неделю (в примере выше 2009-06-01 - воскресенье), диапазон дат мне надо выводить.

Заранее спасибо!
Ответ: Спасибо progi2007
progi2007
Написал запрос

SELECT
DAYOFWEEK( d_date ) ,
iF( DAYOFWEEK( d_date ) =1, ADDDATE( d_date, INTERVAL 1 DAY ) , d_date ) AS startInt,
ADDDATE( d_date, INTERVAL( 7 - DAYOFWEEK( d_date ) +1 ) DAY ) AS endInt,
SUM( profit )
FROM `stat_profit`
GROUP BY d_date
ORDER BY d_date DESC


То, что мне нужно)
Вопрос: Про группировку событий по неделям

Добрый день господа,

Довелось столкнуться со следующей задачкой:
Имеется табличка примерно такого вида

Дата Событие Человек
19.05.2015 А0 В0
24.05.2015 А1 В1
25.05.2015 А2 В2
28.05.2015 А3 В3

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

Дата Пн. Вт. Ср. Чт. Пт. Суб. Вск.
18.05.2015-24.05.2015 А0 В0 А1 В1
25.05.2015-31.05.2015 А2 В2 А3 В3

Т.е. получается понедельное расписание.
Теоретически я понимаю, что для вставки события и человека нужно взять дату события, искать в какой интервал попадает и если найден интервал, то привести дату к названию дня и вставить в столбец соответствующий названию дня в строке соответствующий интервалу даты.
Проблема возникает на самом первом этапе, я не понимаю, как построить эти самые недельные интервалы 18.05.2015-24.05.2015.
Буду очень благодарен за помощь.
Ответ: ROUND(d, [, формат])
Округляет дату d, до единицы указанной форматом. Формат соответствует следующей таблице (она же действует и для функции TRUNC):
Формат Единица округления или усечения
СС, SCC Век
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Год (округляется до 1 июля)
IYYY, IYY, IY, I Год ISO
Q Квартал (округляется до шестнадцатого дня второго месяца квартала)
MONTH, MON, MM, RM Месяц (округляется до шестнадцатого дня)
WW То же день недели, что и первый день года
IW То же день недели, что и первый день года ISO
W То же день недели, что и первый день месяца
DDD, DD, J День
Day, DY, D Первый день недели !!!
HH, HH12, HH24 Час
MI Минута
Вопрос: Выборка по неделе

Добрый день!
Есть запрос.
select fio, to_char(dt_input, 'WW') || '-я ' AS "WEEK", ((dt_output-dt_input) * 24 - 1) AS "Часы" from inputs_outputs;


Выводит:
FIO WEEK Часы
Иванов И.А. 07-я 8
Иванов И.А. 07-я 8
Иванов И.А. 07-я 8
Иванов И.А. 08-я 8
Семенова А.О. 07-я 7.5
Иванов И.А. 08-я 7
Иванов И.А. 10-я 7.1
Семенова А.О. 07-я 8
Семенова А.О. 08-я 8


В исходной таблице есть ФИО сотрудника, дата и время прихода, дата и время ухода.
Пытаюсь подсчитать сколько часов отработал каждый сотрудник по неделям. Т.е. 7 неделю Иванов отработал столько-то часов и т.д.
Пытаюсь применить GROUP BY и не совсем получается.
Подскажите в каком направлении двигаться, какую конструкцию лучше использовать?
Ответ:
Bugrimov_A
Как можно модернизировать мой запрос?

примерно так
я сильно не проверял, мож пропустил кокой-то интервал, тогда добавте в case

with t as (
select 'Stax' fio,to_date('01.01.2016 08:00','dd.mm.yyyy hh24:mi') dt_input,to_date('01.01.2016 20:00','dd.mm.yyyy hh24:mi') dt_output from dual union all
select 'Stax',to_date('02.01.2016 09:00','dd.mm.yyyy hh24:mi'),to_date('02.01.2016 16:00','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('01.02.2016 13:30','dd.mm.yyyy hh24:mi'),to_date('01.02.2016 21:00','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('02.02.2016 15:00','dd.mm.yyyy hh24:mi'),to_date('02.02.2016 16:00','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('03.02.2016 09:00','dd.mm.yyyy hh24:mi'),to_date('03.02.2016 13:40','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('04.02.2016 08:00','dd.mm.yyyy hh24:mi'),to_date('04.02.2016 08:20','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('05.02.2016 13:10','dd.mm.yyyy hh24:mi'),to_date('05.02.2016 13:40','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('06.02.2016 10:10','dd.mm.yyyy hh24:mi'),to_date('06.02.2016 11:00','dd.mm.yyyy hh24:mi') from dual union all
select 'Stax',to_date('06.02.2016 18:10','dd.mm.yyyy hh24:mi'),to_date('06.02.2016 21:00','dd.mm.yyyy hh24:mi') from dual union all
select 'Bugr',to_date('01.02.2016 08:30','dd.mm.yyyy hh24:mi'),to_date('01.02.2016 18:00','dd.mm.yyyy hh24:mi') from dual 
)
, tt as(
select 
  fio,dt_input,dt_output
 ,case
   when to_char(dt_input,'hh24miss')<'090000' then trunc(dt_input)+9/24                      --прийшов зарано
   when to_char(dt_input,'hh24miss') between'130000' and '140000' then trunc(dt_input)+13/24 --прийшов в обід
   when to_char(dt_input,'hh24miss') between'140000' and '180000' then dt_input-1/24         --прийшов після обіду
   when to_char(dt_input,'hh24miss') > '180000' then trunc(dt_input)+17/24                   --прийшов після роботи
   else dt_input
   end dt_i
 ,case
   when to_char(dt_output,'hh24miss')<'090000' then trunc(dt_output)+10/24                     --пішов зарано
   when to_char(dt_output,'hh24miss') between'130000' and '140000' then trunc(dt_output)+14/24 --пішов в обід
   when to_char(dt_output,'hh24miss') between'090000' and '130000' then dt_output+1/24         --пішов до обіду
   when to_char(dt_output,'hh24miss') > '180000' then trunc(dt_output)+18/24                   --пішов після роботи
   else dt_output
   end dt_o
 , 1 / (case when dt_input<dt_output and trunc(dt_input)=trunc(dt_output) then 1 else 0 end) err
 from t
)
select 
  fio,to_char(dt_input,'yyyy') yy,to_char(dt_input,'WW') ww 
--,tt.*
 ,sum(dt_o-dt_i-1/24) work
 ,max(err) eff
from tt
group by 
 fio,to_char(dt_input,'yyyy'),to_char(dt_input,'WW')
order by 1,2,3
SQL> /

FIO  YY   WW       WORK        EFF
---- ---- -- ---------- ----------
Bugr 2016 05     0.3333          1
Stax 2016 01     0.5833          1
Stax 2016 05     0.3750          1
Stax 2016 06     0.0347          1


......
stax