Поиск информации в хранилище

 

По своей сути таблица фактов представляет собой OLAP-куб (Online Analytical Processing), который позволяет быстро находить интересующую информацию в разных аспектах. Существуют стандартные операции с OLAP-

кубами: срез, вращение, консолидация и детализация. Кратко опишем суть этих операций:

• Операция среза обычно определяет все возможные данные с фиксацией одного или нескольких измерений.

• Операция вращения предполагает переупорядочивание измерений.

• Операция консолидации предполагает выполнение агрегирования данных по отдельным измерениям.

• Операция детализации предполагает получение нового куба, содержащего не все значения измерений.

А теперь продемонстрируем на примере ряда запросов применение этих операций для получения информации. Обычно все запросы к хранилищу данных обращаются к таблице фактов или к естественному соединению таблицы фактов с необходимыми для запроса таблицами измерений:

• Получить все оценки по дисциплине «Математический анализ». В этом случае мы используем операцию среза. Можно сказать, что это одновременно является срезом по измерению «Преподаватель» (так как согласно генерации данных дисциплина ведется только одним преподавателем):

SELECT FIOStudent, Mark FROM Results INNER JOIN Students 

   ON Results.idStudent=Students.idStudent 

WHERE idSubject = 

(SELECT idSubject FROM Subjects 

          WHERE TitleSubject='Математический анализ');

     

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

Рис. 53. Оценки студентов по дисциплине «Математический анализ».

 

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

SELECT FIOStudent, AVG(Balls) AS "Средний балл" FROM 

   Results INNER JOIN Students 

   ON Results.idStudent=Students.idStudent 

   GROUP BY FIOStudent;

 

 

• Получить средние баллы сдачи экзаменов по группам студентов. В данном случае производится консолидация по более крупной группе – номерам групп студентов. Таким образом, результатом является гиперкуб, в котором два измерения – дисциплина и номер учебной группы. В какомто смысле здесь производится переупорядочивание измерений, поэтому можно говорить и о применении операции вращения.

SELECT TitleSubject, NumGroup, AVG(Balls) AS "Средний балл" FROM 

  Results INNER JOIN Subjects 

  ON Results.idSubject=Subjects.idSubject 

  INNER JOIN Students ON Results.idStudent=Students.idStudent 

  GROUP BY TitleSubject, NumGroup ORDER BY TitleSubject;

 

Рис. 55. Средние баллы по предметам и группам.

 

• Получить средние баллы по преподавателям. Какому студенту не хотелось знать перед экзаменом, какой преподаватель более лояльный? Ситуация аналогичная запросу о средних баллах студентов. Как видно, нашелся один заметно более строгий преподаватель.

SELECT FIOTeacher, AVG(Balls) AS "Средний балл" FROM 

       Results INNER JOIN Teachers 

       ON Results.idTeacher=Teachers.idTeacher 

       GROUP BY FIOTeacher;

 

 

Рис. 56. Средние баллы по преподавателям.

 

• Получить количество пятерок, которые поставили преподаватели.

SELECT FIOTeacher, COUNT(Mark) AS "Количество пятерок" FROM 

       Results INNER JOIN Teachers 

       ON Results.idTeacher=Teachers.idTeacher 

       WHERE Mark=5 

       GROUP BY FIOTeacher;

 

Рис. 57. Количество пятерок, которые поставили преподаватели.

• Узнать, в какую сессию (зимнюю или летнюю) студенты сдают экзамены лучше. Для этого мы рассчитаем средние баллы сдачи экзаменов зимой и летом. Здесь мы опять сталкиваемся с операцией консолидации, но по нестандартному измерению – дате сдачи экзамена.

SELECT DATEPART(month,DateExam) AS "Месяц", 

  AVG(Balls) AS "Средний балл" 

     FROM Results GROUP BY DATEPART(month,DateExam);

 

Рис. 58. Средние баллы в зимнюю и летнюю сессии.

 

• Получить количество дисциплин, которые ведет каждый из преподавателей. Здесь в качестве факта выступает измерение таблицы фактов idSubject. В данном случае сначала применяем операцию детализации, выбирая для нового гиперкуба из таблицы фактов только некоторые данные, после чего применяется операция консолидации посредством группировки по измерению «Преподаватель».

SELECT FIOTeacher, COUNT(idSubject) AS "Количество дисциплин" FROM 

   (SELECT DISTINCT FIOTeacher, idSubject FROM Results 

           INNER JOIN Teachers 

           ON Results.idTeacher=Teachers.idTeacher) AS Q1 

   GROUP BY Q1.FIOTeacher;

 

Рис. 59. Количество дисциплин каждого из преподавателей.

 

• Получить количество студентов, сдавших дисциплину «Математический анализ» на каждую дату сдачи этого предмета. Здесь используется операция среза, после чего применяется операция консолидации по дате сдачи экзамена.

SELECT DateExam, COUNT(*) AS "Количество студентов" FROM Results 

        WHERE idSubject=(SELECT idSubject FROM Subjects WHERE                           TitleSubject='Математический анализ') 

        GROUP BY DateExam;

 

Рис. 60. Количество студентов, сдавших математический анализ на разные даты. 

 

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

SELECT TitleSubject, COUNT(DateExam) AS "Количество дат" FROM 

    (SELECT DISTINCT TitleSubject, DateExam FROM Results 

     INNER JOIN Subjects 

     ON Results.idSubject=Subjects.idSubject) AS Q1 

    GROUP BY Q1.TitleSubject;

 

Рис. 61. Количество дат сдачи каждого из экзаменов.

 

• Получить количество оценок каждого типа по дисциплине «Математический анализ». В данном примере применение среза и последующей детализации приведет к построению гиперкуба, в котором можно сделать факт (оценку) измерением. Затем к этому новому гиперкубу применяется операция консолидации.

SELECT Mark, COUNT(*) AS "Количество" FROM Results 

WHERE idSubject = 

           (SELECT idSubject FROM Subjects                     WHERE TitleSubject='Математический анализ')   GROUP BY Mark; 

Рис. 62. Количество оценок каждого типа по математическому анализу.