Подсчет общего числа последовательных отсутствий и процентного расчета с помощью запроса MySQL
У меня есть две таблицы; student а также attendance:
Студенческий стол:
sid name
----- --------
s1 nam1
s2 nam2
s3 nam3
s4 nam4
s5 nam5
Таблица посещаемости:
sid status date sub_id
----- -------- --------- ------
s1 present 2017-05-16 ms100
s2 present 2017-05-16 ms100
s3 absent 2017-05-16 ms100
s4 present 2017-05-16 ms100
s5 present 2017-05-16 ms100
s1 present 2017-05-17 ms100
s2 present 2017-05-17 ms100
s3 absent 2017-05-17 ms100
s4 present 2017-05-17 ms100
s5 absent 2017-05-17 ms100
s1 present 2017-05-16 ms101
s2 present 2017-05-16 ms101
s3 absent 2017-05-16 ms101
s4 present 2017-05-16 ms101
s5 absent 2017-05-16 ms101
Теперь я хочу показать, в какую дату учащиеся присутствовали или отсутствовали, а также подсчитать общее количество посещенных занятий, последовательное отсутствие и процент посещаемости для каждого учащегося для идентификатора предмета ms100.
Для последовательного отсутствия / отсутствия я хочу рассмотреть только последнее последовательное отсутствие / отсутствие. Например, если среди 10 дней s1 присутствовал в 1, 6, 7 день, тогда его con_missing будет 3 не 5, Если s1 присутствовал в день 9, тогда его con_missing будет 0, так как я хочу считать последовательные пропуски только тогда, когда ученик отсутствует более 1 дня.
Например, студенты с sid s1,s2,s4 присутствовали во всех 2 классах subject ms100 так их total number of attended class будет 2, как присутствовали во всех классах, так что их total number of consecutive absent будет 0 а также percentage будет {(total attended class/total class)*100} который 100 % в этом случае. С другой стороны s3 отсутствовал во всех классах, поэтому его total number of attended class а также percentage будет 0 а также total number of consecutive absent будет 2.
Для студента ID S5 consecutive absent будет 0 как он отсутствует только на один день.
Я ожидаю результата, подобного следующему шаблону, где каждая отдельная дата класса для предмета ms100 будет отображаться в виде столбца, а статус посещаемости (присутствующий / отсутствующий) отдельного студента в эту конкретную дату будет отображаться как значение этого столбца:
sid name 2017-05-16 2017-05-17 consecutive_absnt total_atn %
----- ----- ---------- --------- ---------------- --------- ----
s1 nam1 present present 0 2 100
s2 nam2 present present 0 2 100
s3 nam3 absent absent 2 0 0
s4 nam4 present present 0 2 100
s5 nam5 present absent 0 1 50
я использую Angularjs как конец шрифта и php как конец. Это то, что я пробовал до сих пор
PHP
$query="SELECT atn.sid
, atn.date
, atn.status
, s.name
FROM attendance atn
join student s
on atn.sid = s.sid
where atn.sub_id = 'ms100'
ORDER
BY atn.date
, atn.sid
";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
$arr = array();
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$arr[] = $row;
}
}
# JSON-encode the response
$json_response = json_encode($arr);
// # Return the response
echo $json_response;
angularjs
<table class="table table-striped table-bordered">
<thead>
<th>sid</th>
<th>name</th>
<th ng-repeat="data in list | unique: 'date'">{{data.date}}</th>
<th>consecutive missing</th>
<th>total attended </th>
<th>%</th>
</thead>
<tbody>
<tr ng-repeat="data in filtered = (list | filter:search | orderBy : predicate :reverse) | startFrom:(currentPage-1)*entryLimit | limitTo:entryLimit">
<td>{{data.sid}}</td>
<td>{{data.name}}</td>
<td>{{data.status}}</td>
<td>{{data.consecutive}}</td>
<td>{{data.total_atn}}</td>
<td>{{data.percentage}}</td>
</tbody>
</table>
Получать такой результат
sid name 2017-05-16 2017-05-17 con_missing totl_atend %
----- -------- --------- ---------- -------- -------- ---
s1 nam1 present
s2 nam2 present
s3 nam3 absent
s4 nam4 present
s5 nam5 present
s1 nam1 present
s2 nam2 present
s3 nam3 absent
s4 nam4 present
s5 nam5 absent
Так как я могу достичь ожидаемого результата с помощью запроса MySQL?
Решение
Хорошо, это заняло некоторое время, чтобы собраться вместе 🙂
Прежде всего, мы устанавливаем функцию в базе данных mysql, чтобы получить последовательное количество дней:
CREATE FUNCTION `getConsecutive`( _subid varchar(45), _sid varchar(45) ) RETURNS int(11)
BEGIN
declare ret int;
select max(consecutive) into ret from (
select q.date,
q.status,
@consecutive := CASE WHEN @stop = 1 THEN 0 WHEN q.status = 'absent' THEN @consecutive +1 ELSE 0 END as consecutive,
@started := CASE WHEN @consecutive > 0 OR @started > 0 THEN 1 ELSE 0 END as started,
@stop := CASE WHEN @consecutive = 0 AND @started > 0 THEN 1 ELSE @stop END as stop
from (
select date, status from Attendance where sub_id = _subid and sid = _sid order by date desc
) q,
(select @consecutive := 0) r,
(select @started := 0) r2,
(select @stop := 0) r3
) as z;
RETURN CASE WHEN ret = 1 THEN 0 ELSE ret END;
END
Затем мы создаем sql, но жестко запрограммирован для проверки на 2 столбца:
SELECT atn.sid, s.name,
MAX(IF(atn.date = '2017-05-16', atn.date, null)) `2017-05-16`,
MAX(IF(atn.date = '2017-05-17', atn.date, null)) `2017-05-17`,
getConsecutive(atn.sub_id, atn.sid) consecutive_absnt,
SUM(CASE WHEN atn.status = 'present' THEN 1 ELSE 0 END) total_atn,
ROUND(100*(SUM(CASE WHEN atn.status = 'present' THEN 1 ELSE 0 END)/count(1)), 2) '%'
FROM Attendance atn
join Student s
on atn.sid = s.sid
where atn.sub_id = 'ms100'
GROUP BY atn.sid, s.name;
Тогда мы знаем, что это работает, но главная проблема заключается в том, что это динамическое количество столбцов .. поэтому вам нужно будет разделить это на части.
Нам нужна хранимая процедура, которая будет строить и выполнять наш динамический SQL.
CREATE PROCEDURE `getData`(_subId VARCHAR(45))
BEGIN
select GROUP_CONCAT(CAST(CONCAT('MAX(IF(atn.date = \'', dd, '\', atn.date, null)) `', dd, '`\n') AS CHAR)) INTO @builtSql
from (
select distinct str_to_date(date, '%Y-%m-%d') dd from Attendance where sub_id = _subId
) q
;SET @builtSql = CONCAT('SELECT atn.sid,
s.name,', @builtSql, ',
getConsecutive(atn.sub_id, atn.sid) consecutive_absnt,
SUM(CASE WHEN atn.status = \'present\' THEN 1 ELSE 0 END) total_atn,
ROUND(100*(SUM(CASE WHEN atn.status = \'present\' THEN 1 ELSE 0 END)/count(1)), 2) \'%\'
FROM Attendance atn
join Student s on atn.sid = s.sid
where atn.sub_id = \'', _subId,'\'
GROUP BY atn.sid, s.name');
PREPARE stmt FROM @builtSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
Создание динамического sql обычно плохая идея из-за риска внедрения sql, поэтому я советую вам сделать некоторые проверки данных, отправляемых в хранимую процедуру.
Затем вы можете просто вызвать хранимую процедуру, чтобы получить желаемый результат выше.
call getData('ms100')
Другие решения
Других решений пока нет …
detector