Moodleでユーザやコース、ファイルを集計する

集計について

Moodleサイトを運用していると色々なデータを出すように依頼されることがある。そんなときに使うクエリ。適宜アップデート予定。

ユーザに関する集計

サイトに登録されている総ユーザ数をカウント

とりあえずアカウントがあるユーザ数を集計。

SELECT COUNT(*) AS total_users
FROM user AS u
WHERE u.id > 1  /* ゲストを除く */

アクティブユーザ数を求める

アクティブを一年以内にログインしたユーザとして集計。

SELECT COUNT(*) AS active_users
FROM user AS u
WHERE u.id > 1  /* ゲストを除く */ 
      AND
      firstaccess > 0 /* ログイン履歴のあるユーザ */
      AND 
      lastaccess > UNIX_TIMESTAMP() - 365 * 24 * 60 * 60 /* 最終ログインが365日以内 */

学生数を求める

「ユーザプロファイルフィールド」を追加して決められた値を設定していることが前提。ここでは省略名にgradeを設定した学年フィールドを追加して学年を設定、教職員には未設定としている。

SELECT COUNT(*) AS students
FROM user AS u
LEFT JOIN user_info_data AS uid ON u.id = uid.userid
LEFT JOIN user_info_field AS uif ON uid.fieldid = uif.id
WHERE u.id > 1 /* ゲストを除く */
      AND
      uif.shortname = 'grade' /* ユーザプロファイルフィールドの短縮名 */
      AND
      uid.data IN (1, 2, 3, 4) /* 指定したユーザプロファイルフィールドの取り得る値 */

余談だがモジュール等の利用制限の条件でユーザプロファイルフィールドを利用できるため、学年や属性として学生、教員、職員などを設定しているなどすると多分便利。

アクティブな学生

前述の集計クエリの組み合わせ

SELECT COUNT(*) AS active_students
FROM user AS u
LEFT JOIN user_info_data AS uid ON u.id = uid.userid
LEFT JOIN user_info_field AS uif ON uid.fieldid = uif.id
WHERE u.id > 1  /* ゲストを除く */ 
      AND
      uif.shortname = 'grade' /* ユーザプロファイルフィールドの短縮名 */
      AND
      uid.data IN (1, 2, 3, 4) /* 指定したユーザプロファイルフィールドの取り得る値 */
      AND
      firstaccess > 0 /* ログイン履歴のあるユーザ */
      AND 
      lastaccess > UNIX_TIMESTAMP() - 365 * 24 * 60 * 60 /* 最終ログインが365日以内 */

アクセス数集計

標準のログストアであるlogstore_standard_logを利用している場合。集計クエリが重い。
集計方法について問題が無いかMoodle標準のレポート機能と比較する必要あり。

一日毎のアクセス数とユニークユーザ数を集計

ざっと見るのには向かないが、ここからソートしてアクセスピークを分析したり。

SELECT FROM_UNIXTIME(l.timecreated, '%Y%m%d') AS datecreated,
       COUNT(*) AS accesscount,
       COUNT(DISTINCT l.userid) AS uniqueusers
FROM logstore_standard_log AS l
WHERE l.origin != 'cli' /* cliによる処理を除く */
      AND
      l.userid > 1 /* ゲストとメッセージング、userid = -1 を除く */
GROUP BY datecreated

一週間毎のアクセス数とユニークユーザ数を集計

2017年～2018年の場合は2017年53週が2017/12/31～2018/01/06、2018年01週が2018/01/07～2018/01/13となる。

SELECT FROM_UNIXTIME(l.timecreated, '%X') AS yearcreated,
       FROM_UNIXTIME(l.timecreated, '%V') AS weekcreated,
       DATE_FORMAT(ADDDATE(FROM_UNIXTIME(MIN(l.timecreated)), -1 * (DAYOFWEEK(FROM_UNIXTIME(MIN(l.timecreated))) - 1)), '%Y%m%d') AS firstdayofthisweek,
       DATE_FORMAT(ADDDATE(FROM_UNIXTIME(MIN(l.timecreated)), -1 * (DAYOFWEEK(FROM_UNIXTIME(MIN(l.timecreated))) - 1) + 6) , '%Y%m%d') AS lastdayofthisweek,
       COUNT(*) AS accesscount,
       COUNT(DISTINCT l.userid) AS uniqueusers
FROM logstore_standard_log AS l
WHERE l.origin != 'cli' /* cliによる処理を除く */
      AND
      l.userid > 1 /* ゲストとメッセージング、userid = -1 を除く */
GROUP BY yearcreated, weekcreated

コース集計

考え中。色々まとめて集計。
コース名、セクション数、モジュール数、割り当てユーザ数、作成日、最終更新日などを集計。

SELECT c.id AS courseid,
       c.fullname, c.shortname,
       cc.name AS categoryname,
       IF(c.visible = 1, 'visible', 'hidden') AS status,
       csc.sections AS cousesections,
       csc.visiblesections AS visiblesections,
       cmc.modules AS coursemodules,
       cmc.visiblemodules AS visiblemodules,
       uec.enroledusers AS enroledusers,
       uec.teachers AS teachers,
       uec.students AS students,
       uec.norole AS norole,
       FROM_UNIXTIME(c.timecreated, '%Y/%m/%d') AS datecreated,
       FROM_UNIXTIME(c.timemodified, '%Y/%m/%d') AS datemodified
FROM course AS c
LEFT JOIN course_categories AS cc ON c.category = cc.id
LEFT JOIN 
(
  SELECT course,
         COUNT(*) AS sections,
         COUNT(CASE WHEN visible = 1 THEN 1 ELSE null END) AS visiblesections
  FROM course_sections
  GROUP BY course
) AS csc ON c.id = csc.course
LEFT JOIN
(
  SELECT course,
         COUNT(*) AS modules,
         COUNT(CASE WHEN visible = 1 THEN 1 ELSE null END) AS visiblemodules
  FROM course_modules
  GROUP BY course
) AS cmc ON c.id = cmc.course
LEFT JOIN (
  SELECT e.courseid,
         COUNT(DISTINCT ra.userid) AS enroledusers,
         COUNT(DISTINCT CASE WHEN r.shortname IN ('manager', 'teacher', 'editingteacher') THEN ra.userid ELSE null END) AS teachers,
         COUNT(DISTINCT CASE WHEN r.shortname IN ('student') THEN ra.userid ELSE null END) AS students,
         COUNT(CASE WHEN r.shortname IS null THEN 1 ELSE null END) AS norole
  FROM user_enrolments AS ue
  LEFT JOIN enrol AS e ON ue.enrolid = e.id
  LEFT JOIN context AS cx ON e.courseid = cx.instanceid AND cx.contextlevel = 50 /* CONTEXT_COURSE */
  LEFT JOIN role_assignments AS ra ON cx.id = ra.contextid AND ue.userid = ra.userid
  LEFT JOIN role AS r ON ra.roleid = r.id
  WHERE ue.status = 0 AND e.status = 0 /* 有効なenrolmentのみ */
  GROUP BY e.courseid
) AS uec ON c.id = uec.courseid
WHERE c.format != 'site' /* サイト除く */
GROUP BY c.id
ORDER BY c.sortorder ASC

コースに割り当てられたユーザを集計

上記のコース集計でも使っているクエリ。多分後で見返したときに忘れるのでメモ。
user_enrolmentsテーブルはどのユーザがどのコースに割り当てられているか確認できるが、コースでのロールは分からない…ロールについてはcontextテーブルとrole_assignmentsテーブルを確認する必要がある。

enroledusersはコースにenrolされているユニークユーザ数を集計（複数ロールを無視）。ただし後述のnoroleをカウント除外。
teachersはコースのマネージャ・教師・TAのユニークユーザ数を集計。これも複数ロールを考慮。
studentsはコースの学生数を集計。
noroleはコースに割り当てられているが、有効なロールが設定されていないユーザ数を集計。異常発見用。
教師がテスト用に学生ロールも持っているような場合はenroledusers < teachers + students ( + norole)となる。
また、追加で（編集権限のない）教師の氏名リストを発音順でソートして追加している。

以下参考リンク
https://docs.moodle.org/dev/New_enrolments_in_2.0
https://docs.moodle.org/dev/Database_schema_introduction
https://docs.moodle.org/dev/Roles_and_modules#Context

SELECT e.courseid,
       COUNT(DISTINCT ra.userid) AS enroledusers,
       COUNT(DISTINCT CASE WHEN r.shortname IN ('manager', 'teacher', 'editingteacher') THEN ra.userid ELSE null END) AS teachers,
       COUNT(DISTINCT CASE WHEN r.shortname IN ('student') THEN ra.userid ELSE null END) AS students,
       COUNT(CASE WHEN r.shortname IS null THEN 1 ELSE null END) AS norole,
       GROUP_CONCAT(DISTINCT CASE WHEN  r.shortname IN ('manager', 'teacher', 'editingteacher') THEN CONCAT(u.lastname, ' ', u.firstname) ELSE null END ORDER BY u.lastnamephonetic, u.firstnamephonetic) AS teacherlist
FROM user_enrolments AS ue
LEFT JOIN user AS u ON ue.userid = u.id
LEFT JOIN enrol AS e ON ue.enrolid = e.id
LEFT JOIN context AS cx ON e.courseid = cx.instanceid AND cx.contextlevel = 50 /* CONTEXT_COURSE */
LEFT JOIN role_assignments AS ra ON cx.id = ra.contextid AND ue.userid = ra.userid
LEFT JOIN role AS r ON ra.roleid = r.id
WHERE ue.status = 0 AND e.status = 0 /* 有効なenrolmentのみ */
GROUP BY e.courseid

ファイル集計

コース毎に集計したくなるが、同一のファイルを複数コース、リソースで使い回せるために単純に集計するととんでもない値が出てくることがある。

ユーザID毎のファイルサイズ合計を集計

誰がどれくらいのディスク容量を使っているか調べられるが全体を見づらい。Moodleの総ファイルサイズが異常に増えたときの確認用くらい？

SELECT f.userid, COUNT(*) AS filecount, SUM(f.filesize) AS totalfilesize
FROM files AS f
GROUP BY f.userid

コンポーネント毎に集計

モジュールやリソース毎にファイルサイズを確認する。

SELECT f.component, f.filearea,
       COUNT(*) AS filecount, SUM(f.filesize) AS totalfilesize
FROM files AS f
GROUP BY f.component, f.filearea

MIME type毎に集計

ファイルのメディアタイプ毎に集計。平均ファイルサイズのカラムを追加。

SELECT f.mimetype, COUNT(*) AS filecount, SUM(f.filesize) AS totalfilesize,
       FLOOR(AVG(f.filesize)) AS averagefilesize
FROM files AS f
GROUP BY f.mimetype

週毎に追加されたファイルのサイズを集計

ディスク領域がどれくらいの勢いで消費されていくか予測するために使えそう。

SELECT FROM_UNIXTIME(f.timecreated, '%X') AS yearcreated,
       FROM_UNIXTIME(f.timecreated, '%V') AS weekcreated,
       DATE_FORMAT(ADDDATE(FROM_UNIXTIME(MIN(f.timecreated)), -1 * (DAYOFWEEK(FROM_UNIXTIME(MIN(f.timecreated))) - 1)), '%Y%m%d') AS firstdayofthisweek,
       DATE_FORMAT(ADDDATE(FROM_UNIXTIME(MIN(f.timecreated)), -1 * (DAYOFWEEK(FROM_UNIXTIME(MIN(f.timecreated))) - 1) + 6) , '%Y%m%d') AS lastdayofthisweek,
       COUNT(*) AS filecount,
       SUM(f.filesize) AS totalfilesize
FROM files AS f
GROUP BY yearcreated, weekcreated