Вопрос
запрос
лог-таблица
Технические вопросы
Разработка

Запрос для выбора данных из лог-таблицы

Черных Руслан
20 октября 2009 11:36

Как построить запрос к таблице tbl_Contact и tbl_ContactLog, таким образом, чтоб извлечь информацию в нужном мне разрезе.
Мне необходимо подсчитать, сколько полей каждого типа(Должность, РОль, Средство связи и т.д.), за период заполнил Сотрудник?
1. Если запись в справочнике Сотрудники только создана?
2. Если запись в справочнике Сотрудники была изменена? Ведь в лог-таблице хранятся данные, предыдущих значений основной таблицы, но как из этой таблицы извлечь количество записей, где за заданный период поле изменило свое значение с «нулл» на «ненулл»? И как построить запрос, если мне нужно в одной таблице получить количество таких переходов, по нескольким типам записей, например, Должность, Роль, Департамент и т.д.
Или может быть проще написать свой триггер, для контроля, если извлечение информации из лог-таблицы будет слишком сложным?

Нравится

7 комментариев
Андрей Богданов
20 октября 2009 15:50

Запрос построить можно. В Oracle можно довольно изящно с аналитическими функциями написать, но и без аналитических функций получается. Например, что-то такое (для должности - можно легко добавить и другие колонки):

select "CreatedByID", 
       sum(decode("JobID",null,0,decode("prevJobID",null,1,0))) "JobSetCount" -- считаем количество записей, где предыдущее значение null, а новое не null
  from(select "RecordID", "CreatedOn", "CreatedByID", "JobID", 
              (select min("JobID") from "Log" l2 -- извлекаем предыдущую запись лога
                where l2."RecordID" = l1."RecordID" 
                  and l2."CreatedOn" = (select max("CreatedOn") from "Log" l3 
                                         where l3."RecordID" = l1."RecordID"
                                           and l3."CreatedOn" < l1."CreatedOn")
              ) "prevJobID" 
         from "Log" l1)
group by "CreatedByID";

Только надо учесть, что текущего состояния записи в "tbl_ContactLog" нет, поэтому стоит сделать view:

create view "Log" as select "ID", "RecordID", "CreatedOn", "CreatedByID", "JobID"
     from "tbl_ContactLog"
    union all
   select "ID", "ID", "ModifiedOn", "ModifiedByID", "JobID" 
     from "tbl_Contact";

Ну а сложно это или нет смотрите сами. Также как и быстродействие на ваших объемах.

Агутин Алексей
20 октября 2009 16:04

"Андрей Богданов" написал:Также как и быстродействие на ваших объемах.
Необходимо также учесть что по умолчанию таблица логов создается без индексов, так что если объем данных большой их надо добавить вручную.

Черных Руслан
20 октября 2009 18:21

Прошу прощения сразу не пояснил, что запросы нужны для MS SQL :-(. Функции decode в T-SQL нет, насколько я понял это аналог CASE. А зачем поля брать в кавычки, это так в Оракле положено? Ну и т.к. не указаны алиасы полей, то на GROUP BY тоже вываливается ошибка, если не затруднит пользователей более продвинутых в вопросе особенностей реализации первести этот запрос на MS SQL.

Андрей Богданов
20 октября 2009 18:59

"Черных Руслан" написал:Функции decode в T-SQL нет, насколько я понял это аналог CASE.

Да, это аналог CASE.

"Черных Руслан" написал:А зачем поля брать в кавычки, это так в Оракле положено?

Для работы с Terrasoft (из-за того, что используются "case sensitive" имена) так положено.

"Черных Руслан" написал:если не затруднит пользователей более продвинутых
Я не настолько "продвинут", чтобы написать это вслепую, а mssql, чтобы проверить под рукой нет. Может быть кто-то другой поможет.

Черных Руслан
21 октября 2009 18:31

Эксперементирую с запросом, но никак не пойму странного поведения CASE. Буду благодарен за подсказку, что я делаю не так. См. приложенный рисунок

Андрей Богданов
21 октября 2009 19:05

"Черных Руслан" написал:Эксперементирую с запросом, но никак не пойму странного поведения CASE. Буду благодарен за подсказку, что я делаю не так.

Это типичная ошибка. Дело в том, что прямые сравнения с null никогда не дают true. Для проверку на null надо использовать is null. То есть

CASE 
WHEN "Communication3" IS NULL OR "Communication3"='' THEN 0
ELSE 1
END
Черных Руслан
22 октября 2009 14:42

Спасибо большое, Андрей. Если кому интересно, вот так выглядит запрос для MS SQL:

SELECT "CreatedByID", 
sum(CASE when "JobID" is NULL  then 0 else
 (CASE  when "prevJobID" is NULL then 1 else 0 END) END) "JobSetCount" 
-- считаем количество записей, где предыдущее значение null, а новое не null
FROM
(SELECT "RecordID", "CreatedOn", "CreatedByID", "JobID",
  (SELECT l2.JobID FROM dbo.vw_ContactLog as l2 -- извлекаем предыдущую запись лога
                WHERE l2."RecordID" = l1."RecordID" 
                  AND l2."CreatedOn" = (SELECT max("CreatedOn") FROM dbo.vw_ContactLog as l3 
                                         WHERE l3."RecordID" = l1."RecordID"
                                           AND l3."CreatedOn" < l1."CreatedOn")
              ) "prevJobID"  
 
         FROM dbo.vw_ContactLog as l1) as T
Group By "CreatedByID"

Из характерных особенностей - обязательное указание алиаса для конструкции select ... from(select ...) as T
И так как я буду контролировать не только ключевые поля, на предмет заполнения , но и строковые(средства связи), то в CASE будет еще добавлена проверка на не пустое значение, как показал Андрей выше.

Показать все комментарии