PhP/MySQL: как динамически изменять мою (большую и постоянно меняющуюся) базу данных

Сценарий

У меня есть база данных MySQL с 10 000 строк. Настройка базы данных:

ID   UniqueKey   Name     Url           Score   ItemValue
1    5Zvr3       Google   google.com    13      X
2    46cfG       Radio    radio.com     -20     X
3    2fg64       Yahoo    yahoo.com     5       X
.... etc etc etc

Как видите, у каждого элемента есть оценка. Счет постоянно меняется. Сейчас у Google может быть 13 баллов, а завтра может быть 80 или -50.

Что я хочу:

Я хочу создать систему, которая создает иерархию в моей текущей базе данных на основе оценки элементов. Прямо сейчас я думаю о процентильном ранге, что означает, что элементы с наивысшей оценкой будут близки к 100 %, а элементы с наименьшей оценкой будут близки к 0 %. Для этого я создал код, который попытается добиться того, что показано здесь: http://www.psychstat.missouristate.edu/introbook/sbk14m.htm

введите здесь описание изображения

Это мой код:

$sql = "SELECT * FROM database order by Score";
$result = $conn->query($sql);
$count = 0;
while ($row = $result->fetch_assoc()) {
    $woow = $row['Score'];
    $sql = "SELECT * FROM database WHERE Score = $woow";
    $resultnew = $conn->query($sql);
    $somanythesame = $resultnew->num_rows;

    $itemPercentile = ( ($count/$result->num_rows + 0.5*$somanythesame/$result->num_rows) * 100 );

    $rowID = $row['ID'];
    $sql2 = "UPDATE database SET itemValue = $itemPercentile WHERE ID = $rowID";
    $conn->query($sql2);
    $count++;
}

Это работает, но есть одна проблема: в моей базе данных есть много элементов, многие из которых имеют одинаковую оценку. Чтобы проиллюстрировать мою проблему, вот очень простая база данных из 10 строк, содержащая только баллы:

Оценки

-10
0
0
0
10
20
20
30
40
50

Проблема с моим кодом заключается в том, что он не дает один и тот же процентиль для элементов с одной и той же оценкой, потому что он принимает во внимание все предыдущие строки для расчета, включая те, у кого одинаковый счет.

Итак, для 2-го, 3-го и 4-го элемента с Score of 0 должно быть так: (1/10 + 0.5*1/10) * 100. Проблема в том, что для третьего элемента будет выполнено (2/10 + 0.5*1/10) * 100, а для четвертого элемента будет выполнено (3/10 + 0.5*1/10) * 100.

Затем для 5-го элемента с оценкой 10 он должен выполнить (4/10 + 0.5*1/10) * 100. Это идет хорошо; только не для предметов с одинаковым счетом.

Я не уверен, что объяснил это хорошо, мне трудно выразить свою проблему правильными словами. Если у вас есть какие-либо вопросы, дайте мне знать! Спасибо за уделенное время :)


mysql php database while-loop percentile
person JuliusSecret    schedule 08.02.2017    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Вам необходимо поддерживать переменную «идентичный счет» ($icount), которая отслеживает количество элементов с одинаковым счетом, и «текущий счет» ($score), который отслеживает текущий счет.

$icount = 0;
$score = null;

Увеличение $icount вместо $count при $woow == $score (проверка идентичного значения). В противном случае добавьте его к вашему $count и увеличьте, а затем сбросьте значение $icount до 0.

if ($woow == $score) {
    $icount++;
} else {
    $count += $icount + 1;
    $icount = 0;
}

Наконец, установите значение $score в самое последнее значение $woow для тестирования в следующей итерации цикла:

$score = $woow;

Это позволит элементам с одинаковым счетом иметь одинаковое значение $count, увеличивая при этом дополнительные $icount раз, когда будет найдено новое $score.

Ваш окончательный код будет выглядеть так:

$sql = "SELECT * FROM database order by Score";
$result = $conn->query($sql);
$count = 0;
$icount = 0;
$score = null;
while ($row = $result->fetch_assoc()) {
    $woow = $row['Score'];
    $sql = "SELECT * FROM database WHERE Score = $woow";
    $resultnew = $conn->query($sql);
    $somanythesame = $resultnew->num_rows;

    $itemPercentile = ( ($count/$result->num_rows + 0.5*$somanythesame/$result->num_rows) * 100 );

    $rowID = $row['ID'];
    $sql2 = "UPDATE database SET itemValue = $itemPercentile WHERE ID = $rowID";
    $conn->query($sql2);
    if ($woow == $score) {
        $icount++;
    } else {
        $count += $icount + 1;
        $icount = 0;
    }
    $score = $woow;
}
person Steven Moseley    schedule 08.02.2017
comment
Обновлено, чтобы исправить сохранение оценки при итерации цикла. - person Steven Moseley; 09.02.2017
comment
Спасибо @StevenMoseley! Извините за поздний ответ, но этот ответ действительно хорош! Хотя это работает только частично. Прямо сейчас первый элемент с score 0 имеет значение count = 2, а 2-й, 3-й и 4-й элементы с score 0 имеют значение count = 3. Есть ли шанс, что вы можете помочь мне с этим? Сейчас это становится слишком сложным для меня... Заранее спасибо! Ты спасатель - person JuliusSecret; 07.03.2017

arrow_upward
0
arrow_downward

Вы можете изменить запрос $sql:

 $sql = "SELECT *,count(*) FROM database group by Score order by Score";

В этом случае вы получаете счет с подсчетом, и больше не требуется выбор в цикле while.

Даже вы можете выбрать Percentile в запросе MySQL:

 Select t2.* , @fb as N , ((t2.fb1 + 0.5 * t2.fw)/@fb*100) as percentile from (
      Select t1.* , (@fb := @fb + t1.fw) as fb1 from (
           Select score,count(*) as fw From tablename group by score order by score ASC
           ) as t1
      ) as t2

Я думаю, что этот запрос возвращает большинство столбцов, которые могут вам понадобиться для проверки результатов.

person MohaMad    schedule 09.02.2017