Попытка исключить временные таблицы MYSQL и объединить процесс в один оператор, если это возможно
У меня есть приложение, которое отслеживает продажи для 11 продавцов. Это достаточно простой процесс, за исключением того, что продажи могут делиться между продавцами, что снижает их продажную стоимость в два раза. Это означает, что если два продавца делят работу, стоимость которой составляет 100 долларов, то каждый продавец учитывает только 50 долларов продажной стоимости для себя. Ниже приведен код, который я сейчас использую для выполнения этого процесса, но он кажется неуклюжим, а производительность на мой вкус немного вялой. Можно ли объединить это в один процесс и устранить необходимость во Временной таблице (я также видел, где вы не должны использовать Временные таблицы в производстве)
$sql = "DROP TEMPORARY TABLE IF EXISTS newbalancetbl" ;
mysqli_query ($db, $sql ) or ( "Error " . mysqli_error () ) ;
$newBalances = "CREATE TEMPORARY TABLE newbalancetbl (
`custid` int NOT NULL,
`assigned` int NOT NULL,
`newBalance` double,
PRIMARY KEY(custid)
)
";
mysqli_query($db, $newBalances) or die ("Sql error : ".mysqli_error());
$year = date("Y");
$start = "01/01/".$year;
$today = date("Y-m-d");
$first = $year."-01-01";
$assignments = "SELECT leadid, price
FROM jobbooktbl
WHERE convertdate >= '".$first."' AND convertdate<='".$today."' AND (status=4 OR status=6 OR status=7 OR status=8 OR status=11)";
$assignmentsqry = mysqli_query($db,$assignments);
while ($row = mysqli_fetch_array($assignmentsqry)) {
$custid = $row["leadid"];
$price = $row["price"];
$statement = $db->prepare("INSERT INTO newbalancetbl (custid, newBalance) VALUES (?,?)");
$statement->bind_param('id', $custid, $price);
$statement->execute();
}
$sqlnewbal = "SELECT a.custid, COUNT(a.custid) AS assCnt
FROM assignmentstbl a, newbalancetbl b
WHERE a.custid=b.custid
GROUP BY a.custid";
$qrynewbal = mysqli_query($db,$sqlnewbal);
while ($row = mysqli_fetch_array($qrynewbal)) {
$custid = $row['custid'];
// $paid = $row["sumAmnt"];
$assigned = $row['assCnt'];
$usqlUpdate = $db->prepare("UPDATE newbalancetbl SET assigned=? WHERE custid=?");
$usqlUpdate->bind_param('ii',$assigned,$custid);
$usqlUpdate->execute();
}
$sqlnewbal = "SELECT *
FROM newbalancetbl";
$qrynewbal = mysqli_query($db,$sqlnewbal);
while ($row = mysqli_fetch_array($qrynewbal)) {
$custid = $row['custid'];
$assigned = $row['assigned'];
$newBalance = $row['newBalance'];
$newBal = $newBalance/$assigned;
$newBal - number_format($newBal,2);
$usqlUpdate = $db->prepare("UPDATE newbalancetbl SET newBalance=? WHERE custid=?");
$usqlUpdate->bind_param('di',$newBal,$custid);
$usqlUpdate->execute();
}
$salesArray = [];
$tesql = "SELECT SUM(n.newBalance) AS newB, u.username
FROM newbalancetbl n
INNER JOIN assignmentstbl a
ON a.custid=n.custid
INNER JOIN usertbl u
ON a.userid=u.userid
-- WHERE u.salesman=1
GROUP BY a.userid
ORDER BY newB DESC";
$teresult = mysqli_query($db,$tesql);
while ($row = mysqli_fetch_array($teresult)) {
$user = $row['username'];
$sales = $row['newB'];
array_push($salesArray, [$user,floatval($sales)]);
}
$arrayCount = count($salesArray);
$total_sales = 0;
$total_sales = array_sum( array_map(function($element){
return $element[1];
},
$salesArray));
$pretotal_sales = number_format($total_sales, 2);
$total_sales = '$' . number_format($total_sales, 2);
Схема таблицы:
jobbooktbl
assignmentstbl
Решение
Я пойду по коду, чтобы понять, что он делает, и предложу изменения по пути.
CREATE TEMPORARY TABLE newbalancetbl (
custid int NOT NULL,
assigned int NOT NULL,
newBalance double,
PRIMARY KEY(custid)
) ENGINE=Memory
ХОРОШО. Стол кажется маленьким, поэтому вы можете использовать ENGINE = Memory, чтобы сделать его быстрее. Кроме того, рассмотрите возможность использования типа DECIMAL вместо DOUBLE. Это не обязательно, но позволяет избежать ошибок при округлении.
Тем не мение. Ваш первый запрос. Заполнение вашей таблицы с помощью php while () loops выполняется медленно и не нужно. Просто сделайте:
INSERT INTO newbalancetbl (custid, newBalance)
SELECT leadid, price
FROM jobbooktbl
WHERE convertdate BETWEEN '$first' AND '$today'
AND status IN (4,6,7,8,11);
Обратите внимание на использование IN (), которое более читабельно. Кроме того, МЕЖДУ. INSERT INTO SELECT будет намного быстрее, чем зацикливание результатов запроса в php. Кроме того, я не вижу «assign» и «newBalance» во вставленных столбцах, но в таблице не указаны значения по умолчанию. Вы должны сделать значение по умолчанию явным.
Теперь следующий запрос:
SELECT custid, COUNT(*) AS assCnt
FROM assignmentstbl a JOIN newbalancetbl b USING (custid)
GROUP BY custid
Я изменил уродливый старый синтаксис JOIN на правильный (примерно с 1999 года). Кроме того, COUNT (столбец) подсчитывает строки, где «столбец» не является нулевым. Таким образом, COUNT (a.custid) подразумевает, что «a.custid» может фактически быть нулевым. Поскольку это невозможно, этот синтаксис просто сбивает с толку. Я заменяю его на count (*).
Затем вы просматриваете результаты в PHP и выполняете «UPDATE newbalancetbl SET назначено = $ assCnt WHERE custid =?»
Вы должны решить, использовать ли имя «assCnt» или «назначенный». Мне больше нравится первый, так как это число, когда в названии есть «cnt», это делает его менее запутанным. Теперь этот цикл не нужен, мы могли бы использовать одно UPDATE с JOIN или, лучше, встроить значения в rable с самого начала. Таким образом, первый запрос становится:
INSERT INTO newbalancetbl (custid, newBalance, assigned)
SELECT j.leadid, j.price,
(SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS assigned
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '$first' AND '$today'
AND j.status IN (4,6,7,8,11);
Я использовал подвыбор. Не стесняйтесь использовать вместо этого JOIN.
Следующий запрос Я буду игнорировать «$ newBal — number_format ($ newBal, 2);» который ничего не делает, так как вы использовали «-» вместо «=» … это можно решить, используя числовой формат, или просто используйте это:
UPDATE newbalancetbl
SET newBalance=ROUND(newBalance/assigned, 2)
Еще одна петля php устранена. Но мы можем отменить обновление, а также временную таблицу, пока мы на нем.
SELECT
j.leadid AS custid,
ROUND( j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid), 2) AS newBalance
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '$first' AND '$today'
AND j.status IN (4,6,7,8,11);
Это должно дать те же результаты, что и временная таблица, за исключением «назначенного» столбца, который в любом случае не используется в остальной части кода, поэтому мы можем его отбросить. Теперь давайте вставим это в следующий запрос …
SELECT ROUND(SUM(n.newBalance), 2) AS newB, u.username
FROM (
SELECT
j.leadid AS custid,
j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '$first' AND '$today'
AND j.status IN (4,6,7,8,11)
) n
JOIN assignmentstbl a USING (custid)
JOIN usertbl u USING (userid)
-- WHERE u.salesman=1
GROUP BY a.userid
ORDER BY newB DESC
Это должно делать то, что вы хотите. Я переместил КРУГЛЫЙ во внешний запрос.
Другие решения
С предположениями всегда есть кто-то назначен на работу, и многие другие подразумевали в этом вопросе, единственный запрос может быть:
SELECT SUM(n.newBalance) AS newB, u.username, u.userid
FROM
(SELECT
j.leadid as custid,
(j.price / COUNT(*)) as newBalance
FROM jobbooktbl j
INNER JOIN assignmentstbl a
ON a.custid = j.leadid
WHERE j.convertdate >= ?
AND j.convertdate <= ?
GROUP BY 1
) n
INNER JOIN assignmentstbl a
ON a.custid=n.custid
INNER JOIN usertbl u
ON a.userid=u.userid
GROUP BY u.userid
ORDER BY newB DESC;
n подзапрос вычисляет среднюю цену
Если они являются единственным продавцом на работе, и работа составляла 200 долларов, то им будет начислено 200 долларов (200/1 = 200), если они поделятся работой с другим продавцом, то есть назначены два продавца, тогда они будут зачислены только на 100 долларов США (200/2 = 100)
при условии, что если на одну работу назначены 4 продавца, они получат по 50 долларов каждый.
Просто немного ударить в этом
SELECT name,SUM(
SELECT (
SELECT price/
(SELECT COUNT(1) FROM jobs WHERE job.ID = Assignments.jobId)
FROM jobs WHERE
(SELECT COUNT(1) FROM assignment WHERE salesman.ID = assignment.salesmanId) > 0)
)
) AS commission
FROM salesman.
Правильно, так что выбирайте всех продавцов, получайте цену от всех рабочих мест, частью которых он является. Разделите эту цену на количество людей на работе. Таблица могла бы изменить названия, но в целом я думаю, что это то, что вы собираетесь.