MySQLで月ごとの欠席数を取得する方法

Question

やりたいこと
MySQLで学生ごとの出席数、欠席数、遅刻数、遅刻分欠席(3回で欠席1回)を取得したい
遅刻分欠席(3回で欠席1回)は月毎に3回遅刻したら1欠席にカウントしたい

悩んでいること
期間ごとに上記それぞれの出席状況を取得したいが、「遅刻分欠席(3回で欠席1回)」のみ書き方がわからない
lectures.finish_timeで日付を絞っても過去全ての欠席数から計算された値が表示されてしまう

考えたこと
DIVを使って遅刻/3としているため、全体の値から計算されてしまう
(例: 5月に2回遅刻、6月に2回遅刻の場合、合計遅刻数は4回なので、1欠席1遅刻になるが実際には月を跨いでいるので欠席は0回とカウントしたい欠席)

select
    students.name as "学生",
    count(attend_logs.attend_status = "attended" or null) as "出席(授業数)",
    count(attend_logs.attend_status = "absented" or null) as "欠席(授業数)",
    count(attend_logs.attend_status = "late" or null) as "遅刻数",
    count(attend_logs.attend_status = "late" or null) DIV 3 as "遅刻分欠席(3回で欠席1回)"
from
    attend_logs
    join 
    students 
    on 
    attend_logs.student_id = students.id
join
     lectures
     on
     lectures.id = lecture_attend_logs.lecture_id
where
     lectures.finish_time in (2021/05/01 and 2021/06/30)
group by students.name
   

attend_logsテーブル

NAME	SQL Type	Limit
id	bigint(20)	8
student_id	bigint(20)	8
lecture_id	bigint(20)	8
attend_status	varchar(255)	255
updated_at	datetime
created_at	datetime
deleted_at	datetime

studentsテーブル

NAME	SQL Type	Limit
id	bigint(20)	8
name	varchar(10)	10
updated_at	datetime
created_at	datetime
deleted_at	datetime

lecturesテーブル

NAME	SQL Type	Limit
id	bigint(20)	8
name	varchar(10)	10
start_time	datetime
finish_time	datetime
updated_at	datetime
created_at	datetime
deleted_at	datetime

拙い説明で申し訳ありませんが、ご教授いただけないでしょうか。
よろしくお願いします。

Comments

[mdj]

期間は「複数月（2021/05/01 and 2021/06/30）」だけど、遅刻欠席は「1か月単位」ということですか？

[rat_mouse]

コメントありがとうございます。
そうです、1ヶ月単位です。

例えば5月から7月の範囲で絞った際に
5月遅刻3回 = 1欠席
6月遅刻2回 = 0欠席
7月遅刻5回 = 1欠席
となり、2欠席として取得したいのですが、
現状の書き方では10遅刻 = 3欠席になります。

実際にはMetabaseでフィルターを作成して期間を絞れるようにしたいと考えています。

[mdj]

in句を使って期間ごとに更にwhereで処理するくらいしか思いつかないです。すいません～。

[sazi]

累計という訳ではないですから、月ごとの集計は必須だと思います。
ネストで月毎の集計を行ったものを、再度集計でしょうね。

[rat_mouse]

>ネストで月毎の集計を行ったものを、再度集計でしょうね。

回答ありがとうございます。
count(attend_logs.attend_status = "late" or null) DIV 3 as "遅刻分欠席(3回で欠席1回)"
サブクエリでこちらに月毎の集計追記するということでしょうか・・・？

[yambejp]

サンプルデータがあれば例示下さい

Answer 1

where
     lectures.finish_time in (2021/05/01 and 2021/06/30)
は
where
     lectures.finish_time BETWEEN 2021/05/01 and 2021/06/30
ではないですか？

Answer 2

ネストで月毎の集計を行ったものを、再度集計

select  name as 学生
      , sum(出席_授業数) as 出席_授業数
      , sum(欠席_授業数) as 欠席_授業数
      , sum(遅刻数) as 遅刻数
      , sum(遅刻分欠席) as 遅刻分欠席
from (
    select  students.name
          , count(attend_logs.attend_status = 'attended' or null) as 出席_授業数
          , count(attend_logs.attend_status = 'absented' or null) as 欠席_授業数
          , count(attend_logs.attend_status = 'late' or null) as 遅刻数
          , count(attend_logs.attend_status = 'late' or null) as DIV 3 as 遅刻分欠席
    from  attend_logs
          inner join students 
          on  attend_logs.student_id = students.id
          inner join lectures
          on  lectures.id = attend_logs.lecture_id
    where lectures.finish_time in (2021/05/01 and 2021/06/30)
    group by students.name, date_format(lectures.finish_time, '%Y%m')
) mnth
group by name

Comments

[rat_mouse]

回答ありがとうございます。
ご提示いただいた構文だとサブクエリに主テーブルが結合されていないのでエラーになります。
念の為attend_logsとstudentsに結合してみたのですがエラーのようです。

[sazi]

質問の内容を基本転記しただけですけど、「サブクエリに主テーブルが結合されていない」とは何のことですか？
内容的には、質問のSQLのgroup byに年月(date_format(lectures.finish_time, '%Y%M'))を追加して、その外側では、年月を外した集計をするという事です。
※質問のSQLにある「lecture_attend_logs」は誤記だと思って修正していますが。

[rat_mouse]

申し訳ありません、見間違えていました。
と提示いただいた方法で正常に出席日数を集計できました。
ありがとうございます。