

### やりたいこと

MySQLで学生ごとの出席率を取得したい

### クエリの条件

SELECT文で出席数、欠席数、遅刻数、遅刻分欠席(3回で欠席1回)を取得している  
遅刻分欠席(3回で欠席1回)は月毎に3回遅刻したら1欠席にカウント(例:
7月に2回遅刻、8月に2回遅刻の場合、合計遅刻数は4回なので、1欠席1遅刻になるが実際には月を跨いでいるので欠席は0回とカウントしたい欠席)

### 悩んでいること

日付を指定した際の合計出席率を表示させたい。  
現在の書き方では月毎の出席率は出るが、合計は出ない。  
下記のように条件を絞った場合、7月、8月の出席率は出るが2ヶ月の出席率の合計は出ない

[code]

    where finish_time in 2021-07-01 and 2021-08-31)
    
[/code]

### クエリ

[code]

    select
        lectures.finish_time as "期間",
        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回)",
        ROUND((count(attend_logs.attend_status = "attended" or null ) + (count(attend_logs.attend_status = "late" or null) - count(attend_logs.attend_status = "late" or null) DIV 3) + count(attend_logs.attend_status = "public_absented" or null) + count(attend_logs.attend_status = "closed"or null ))  / count(attend_logs.attend_status),5) * 100 as "出席率合計(%)"
    from
        attend_logs
        join 
        students 
        on 
        attend_logs.student_id = students.id
    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')
    
[/code]

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 |


CC BY-SA 4.0
2021-08-02T07:39:41.937
80539
2021-08-02T22:10:33.543
2021-08-02T07:55:50.883
3060
47556
question
mysql
学生ごとの出席率の表示


質問文のクエリをサブクエリにして、 `学生` で `group by` すればよいかと思います。

* * *
[code]

    create table students(
        id bigint primary key,
        name varchar(10) unique not null
    );
    
    create table lectures(
        id bigint primary key,
        name varchar(10),
        finish_time datetime not null
    );
    
    create table attend_logs(
        id bigint primary key,
        student_id bigint not null,
        lecture_id bigint not null,
        attend_status varchar(255) not null,
        foreign key idx_student(student_id) references students(id),
        foreign key idx_lecture(lecture_id) references lectures(id)
    );
    
    insert into students(id, name) values
    (1, 'alice'), (2, 'bob');
    
    insert into lectures(id, name, finish_time) values
    (1, 'lec-0501', '2021/05/01'), (2, 'lec-0515', '2021/05/15'), (3, 'lec-0530', '2021/05/30'), (4, 'lec-0620', '2021/06/20');
    
    insert into attend_logs(id, student_id, lecture_id, attend_status) values
    (1, 1, 1, 'late'), (2, 1, 2, 'late'), (3, 1, 3, 'absented'), (4, 1, 4, 'late'),
    (5, 2, 1, 'late'), (6, 2, 2, 'late'), (7, 2, 3, 'late'), (8, 2, 4, 'late')
    ;
    
[/code]

なテーブルに対して、質問文(相当)のクエリ

[code]

    select
        date_format(lectures.finish_time, '%Y%M') as '期間',
        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回)`,
        count(attend_logs.attend_status) as `授業数`,
        (count(attend_logs.attend_status = 'attended' or null ) + (count(attend_logs.attend_status = 'late' or null) - count(attend_logs.attend_status = 'late' or null) DIV 3) + count(attend_logs.attend_status = 'public_absented' or null) + count(attend_logs.attend_status = 'closed' or null )) as `非欠席数`,
        ROUND((count(attend_logs.attend_status = 'attended' or null ) + (count(attend_logs.attend_status = 'late' or null) - count(attend_logs.attend_status = 'late' or null) DIV 3) + count(attend_logs.attend_status = 'public_absented' or null) + count(attend_logs.attend_status = 'closed' or null ))  / count(attend_logs.attend_status),5) * 100 as `出席率合計(%)`
    from
        attend_logs
        join 
        students 
        on 
        attend_logs.student_id = students.id
    join
         lectures
         on
         lectures.id = attend_logs.lecture_id
    where
         lectures.finish_time between '2021/05/01' and '2021/06/30'
    group by students.name, date_format(lectures.finish_time, '%Y%M')
    ;
    
[/code]

を実行すると

期間 | 学生 | 出席(授業数) | 欠席(授業数) | 遅刻数 | 遅刻分欠席(3回で欠席1回) | 授業数 | 非欠席数 | 出席率合計(%)  
---|---|---|---|---|---|---|---|---  
2021May | alice | 0 | 1 | 2 | 0 | 3 | 2 | 66.6700  
2021May | bob | 0 | 0 | 3 | 1 | 3 | 2 | 66.6700  
2021June | alice | 0 | 0 | 1 | 0 | 1 | 1 | 100.0000  
2021June | bob | 0 | 0 | 1 | 0 | 1 | 1 | 100.0000  
  
を得ますが、このクエリをサブクエリにして

[code]

    select sum(by_month.`出席(授業数)`) as `出席合計`, sum(by_month.`非欠席数`) as `非欠席合計`, sum(by_month.`授業数`) as `授業合計`, (100 * sum(by_month.`非欠席数`) / sum(by_month.`授業数`) ) as `出席率`
    from (
    
        /* ここから前出の質問文(相当)のクエリ */
        select
            date_format(lectures.finish_time, '%Y%M') as '期間',
            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回)`,
            count(attend_logs.attend_status) as `授業数`,
            (count(attend_logs.attend_status = 'attended' or null ) + (count(attend_logs.attend_status = 'late' or null) - count(attend_logs.attend_status = 'late' or null) DIV 3) + count(attend_logs.attend_status = 'public_absented' or null) + count(attend_logs.attend_status = 'closed' or null )) as `非欠席数`,
            ROUND((count(attend_logs.attend_status = 'attended' or null ) + (count(attend_logs.attend_status = 'late' or null) - count(attend_logs.attend_status = 'late' or null) DIV 3) + count(attend_logs.attend_status = 'public_absented' or null) + count(attend_logs.attend_status = 'closed' or null ))  / count(attend_logs.attend_status),5) * 100 as `出席率合計(%)`
        from
            attend_logs
            join 
            students 
            on 
            attend_logs.student_id = students.id
        join
            lectures
            on
            lectures.id = attend_logs.lecture_id
        where
            lectures.finish_time between '2021/05/01' and '2021/06/30'
        group by students.name, date_format(lectures.finish_time, '%Y%M')
        /* ここまで前出の質問文(相当)のクエリ */
    
    
    ) by_month
    group by `学生`;
    
[/code]

とすると、

学生 | 出席合計 | 非欠席合計 | 授業合計 | 出席率  
---|---|---|---|---  
alice | 0 | 3 | 4 | 75.0000  
bob | 0 | 3 | 4 | 75.0000  
  
が得られます。  
(aliceは欠席x1, 遅刻x3 だが、遅刻の3回は月をまたいでいるので欠席扱いにならない)


CC BY-SA 4.0
2021-08-02T21:52:53.863
80552
2021-08-02T22:10:33.543
2021-08-02T22:10:33.543
2808
2808
80539
answer