

### やりたいこと

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

### わからないこと

下記の条件のもと出席率を計算したいが書き方がわからない

### 出席率条件

  * HR~4限全て出席したら出席率100%
  * HRのみ欠席したら91.6%
  * HRと1つの授業を欠席もしくは1つの授業を欠席したら75%
  * HRと2つの授業を欠席もしくは2つの授業を欠席したら50%
  * HRと3つの授業を欠席もしくは3つの授業を欠席したら25%
  * 全て欠席もしくはHRのみ出席したら0%

### 考えたこと

下記出席率を集計するクエリを作成したが「HRのみ欠席の場合91.6%」という条件を組み込む記述がわからない

[code]

    select
        students.name as "学生",
        klasses.name as "クラス",
        count(distinct lectures.id) as "授業数",
        count(lecture_attend_logs.attend_status = "attended" or null) as "出席(授業数)",
        count(lecture_attend_logs.attend_status = "absented" or null) as "欠席(授業数)",
        count(lecture_attend_logs.attend_status = "late" or null) as "遅刻数",
        count(lecture_attend_logs.attend_status = "late" or null) DIV 3 as "遅刻分欠席(3回で欠席1回)",
        count(lecture_attend_logs.attend_status = "public_absented" or null) as "公欠(授業数)",
        count(lecture_attend_logs.attend_status = "closed" or null) as "休校",
        ROUND((count(lecture_attend_logs.attend_status = "attended" or null ) + (count(lecture_attend_logs.attend_status = "late" or null) - count(lecture_attend_logs.attend_status = "late" or null) DIV 3) + count(lecture_attend_logs.attend_status = "public_absented" or null) + count(lecture_attend_logs.attend_status = "closed"or null ))  / count(lecture_attend_logs.attend_status),5) * 100 as "出席率合計(%)"
    from
        lecture_attend_logs
        join lectures on lecture_attend_logs.lecture_id = lectures.id
        join students on lecture_attend_logs.student_id = students.id
        join klass_students on klass_students.student_id = students.id
        join klasses on klasses.id = klass_students.klass_id
    where
        and lecture_attend_logs.deleted_at is null
    group by students.name
    order by lectures.id
    
[/code]

lecture_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 |  
fiish_time | datetime |  
updated_at | datetime |  
created_at | datetime |  
deleted_at | datetime |  
  
klassesテーブル

NAME | SQL Type | Limit  
---|---|---  
id | bigint(20) | 8  
name | varchar(10) | 10  
code | varchar(6) | 6  
start_time | datetime |  
fiish_time | datetime |  
updated_at | datetime |  
created_at | datetime |  
deleted_at | datetime |  
  
klass_studentsテーブル

NAME | SQL Type | Limit  
---|---|---  
id | bigint(20) | 8  
klass_id | bigint(20) | 8  
student_id | bigint(20) | 8  
updated_at | datetime |  
created_at | datetime |  
deleted_at | datetime |


CC BY-SA 4.0
2021-09-03T10:58:17.530
82214
2021-09-04T02:24:17.373
2021-09-03T16:26:06.427
3060
48033
question
mysql
MySQLで出席率の集計を行いたい


ご質問の **わからないこと** への回答です。  
出席率条件の計算においてHRの扱いが特殊なので、case文などで条件分岐することになります。

**やりたいこと**
の月次処理を1つのSQLで実現すると副問い合わせが複雑すぎて保守しにくい状況になりがちですので、月次処理は一時表やプログラムを利用して計算する方法をお勧めします。

下記はSQLで出席率計算を行う例です。  
with句の中でHRの出席と通常授業の出席数をカウントし、selectのcase文で条件分岐して出席率を求めます。

`lectures`テーブルには任意日付1日分の講義が入り、`lectures.name='HR'`がHRであると仮定しています。  
with句を使っているので`MySQL 8.0`以上で動作します。

**SQL** [DB Fiddle](https://www.db-fiddle.com/f/x9tTZNnXJ5apgEexaw9oVx/2)

[code]

    with attend_count
    as  (select a.student_id,
                count(l.name = 'HR' and a.attend_status <> "absented" or null) as hp_count,
                count(l.name <> 'HR' and a.attend_status <> "absented" or null) as normal_count
         from   lecture_attend_logs a
                join lectures l on a.lecture_id = l.id
         group by a.student_id)
    select s.name,
           c.normal_count,
           case c.hp_count
           when 0 then case c.normal_count
                       when 4 then 91.6
                              else c.normal_count * 25
                       end
                  else c.normal_count * 25
           end `出席率合計(%)`
    from   students s
           join attend_count c on s.id = c.student_id
    
[/code]

**DDLなど**

[code]

    create table students (
      id bigint(20),
      name varchar(10)
    );
    
    insert into students values (1, "Aさん");
    insert into students values (2, "Bさん");
    insert into students values (3, "Cさん");
    
    create table lectures (
      id bigint(20),
      name varchar(10)
    );
    
    insert into lectures values (0, "HR");
    insert into lectures values (1, "1限");
    insert into lectures values (2, "2限");
    insert into lectures values (3, "3限");
    insert into lectures values (4, "4限");
    
    create table lecture_attend_logs (
      student_id bigint(20),
      lecture_id bigint(20),
      attend_status varchar(255)
    );
    
    -- 全出席(100%)
    insert into lecture_attend_logs values (1, 0, "attended");
    insert into lecture_attend_logs values (1, 1, "attended");
    insert into lecture_attend_logs values (1, 2, "attended");
    insert into lecture_attend_logs values (1, 3, "attended");
    insert into lecture_attend_logs values (1, 4, "attended");
    
    -- HRと1,2限出席(50%)
    insert into lecture_attend_logs values (2, 0, "attended");
    insert into lecture_attend_logs values (2, 1, "attended");
    insert into lecture_attend_logs values (2, 2, "attended");
    insert into lecture_attend_logs values (2, 3, "absented");
    insert into lecture_attend_logs values (2, 4, "absented");
    
    -- 1-4限出席(91.6%)
    insert into lecture_attend_logs values (3, 0, "absented");
    insert into lecture_attend_logs values (3, 1, "attended");
    insert into lecture_attend_logs values (3, 2, "attended");
    insert into lecture_attend_logs values (3, 3, "attended");
    insert into lecture_attend_logs values (3, 4, "attended");
    
[/code]

**結果**

name | 出席率合計(%)  
---|---  
Aさん | 100  
Bさん | 50  
Cさん | 91.6


CC BY-SA 4.0
2021-09-04T02:24:17.373
82227
2021-09-04T02:24:17.373
9820
82214
answer