Получение записи SQL
Доброго времени суток сообщество,
Мне нужна помощь, чтобы получить запись из таблицы mysql, как показано ниже. Обратите внимание на мои целевые результаты:
Ниже представлен мой текущий подход, который хорошо работает с небольшим количеством строк, но очень медленный или замедляет работу с тысячами или сотнями строк.
public function usersTopRecruits(){
$tempUID = array();
$mainAssocArr = array();
for ($i=0; $i < User::count(); $i++) {
if ($i == 0){
$offset = 0;
}
else{
$offset = $this->take * $i;
}
$users = User::take($this->take)->offset($offset)->get();
foreach ($users as $value) {
$refCnts = User::where('referral', $value->aff_id)->count();
if ($refCnts > 0){
$tempUID['userAffID'] = $value->aff_id;
$tempUID['userrecruits'] = $refCnts;
$mainAssocArr[] = $tempUID;
}
}
}//End of For loop
//Here, i tried to sort by highest recruits
foreach ($mainAssocArr as $key => $row) {
$desc[$key] = $row['userrecruits'];
$asc[$key] = $row['userAffID'];
}
array_multisort($desc, SORT_DESC, $asc, SORT_ASC, $mainAssocArr);
return $mainAssocArr;
}
Текущий месяц:
public function userTopRecruitsCurrMonth(){
$tempUID = array();
$mainAssocArr = array();
for ($i=0; $i < User::count(); $i++) {
if ($i == 0){
$offset = 0;
}
else{
$offset = $this->take * $i;
}
$users = User::take($this->take)->offset($offset)->get();
foreach ($users as $value) {
$refCnts = User::where('referral', $value->aff_id)->where('signup_date', '>=', 'startDateOfCurrentMonth')->count();
if ($refCnts > 0){
$tempUID['userAffID'] = $value->aff_id;
$tempUID['userRecruits'] = $refCnts;
$mainAssocArr[] = $tempUID;
}
}
}//End of For loop
foreach ($mainAssocArr as $key => $row) {
$volume[$key] = $row['userRecruits'];
$edition[$key] = $row['userAffID'];
}
array_multisort($volume, SORT_DESC, $edition, SORT_ASC, $mainAssocArr);
return $mainAssocArr;
}
Любая помощь или предложение будут оценены.
ПРИМЕЧАНИЕ. Я использую модель Eloquent, но НЕ использую «laravel».
Да, построители запросов. Извините, я их не фанат. Лично я, вероятно, сделал бы (коррелированный) подзапрос, чтобы подсчитать итоги, а затем присоединиться к нему, чтобы выполнить заказ. Это агрегированный запрос, который должен подсчитывать всю БД, и если вы можете запечь это число и поддерживать его, это будет намного быстрее.
@ArtisticPhoenix спасибо за комментарий. любой пример запроса или подзапроса, который, по вашему мнению, может решить или помочь в решении проблемы?
Да, DbFiddle, если вам нужны пользователи без рефералов, вам, возможно, придется выполнить левое присоединение вместо внутреннего (присоединиться)
Подзапрос есть, если вы хотите такие вещи, как имя и т. д. Поскольку группировка на referral вместо этого предоставит вам данные одного из тех, на которые ссылаются пользователи, а не фактические данные пользователей. Лучше сделать это как подзапрос, потому что он создает меньшую временную таблицу, а затем выполняет все это на том же уровне, а также выполняет сортировку по меньшей временной таблице. Вы даже можете переместить ORDER BY в подзапрос, и он ничего не изменит. Например По крайней мере, насколько я понимаю, как это оптимизирует.
@ArtisticPhoenix, это полезно, большое вам спасибо
Ответы 2
Из вашего комментария я понимаю, что предоставление вам SQL-запросов может помочь.
Этот простой расширенный запрос вернет REFERRAL с наибольшим количеством вхождений (за все время), с наибольшим наверху.
SELECT referral, COUNT(*)
FROM table
GROUP BY referral
ORDER BY COUNT(*) DESC
Если вы ищете такие же результаты за текущий месяц, просто добавьте предложение WHERE:
SELECT referral, COUNT(*)
FROM table
WHERE YEAR(sigmup_date) = YEAR(NOW()) AND MONTH(sigmup_date) = MONTH(NOW())
GROUP BY referral
ORDER BY COUNT(*) DESC
ваш ответ выглядит великолепно, но часть текущего месяца, похоже, не работает из условия <where>
@HarrisonOziegbe: правильно, я неправильно расставил пункты WHERE и GROUP BY. Отредактировал мой ответ, теперь он должен работать нормально.
Добро пожаловать, @HarrisonOziegbe, рад, что помог!
Я бы использовал такой запрос:
SELECT
*
FROM
users AS u0
JOIN
(
SELECT
u1.REFERRAL AS agg,
COUNT(u1.REFERRAL) AS total
FROM
users AS u1
GROUP BY
u1.REFERRAL
ORDER BY total DESC
) AS u2
ON
u2.agg = u0.AFF_ID
Подзапрос позволяет вам, снова присоединившись к таблице, извлечь данные о фактических пользователях, по которым сгруппирован AFF_ID. Это даст вам такие вещи, как их FIRST_NAME и т. д. Если вы не присоединитесь снова, вы не получите этого.
Он также создает меньшую временную таблицу (в памяти), тогда, если бы вы сделали это на том же уровне (возможно, мне пришлось бы вникнуть в нее, чтобы объяснить больше).
Если вы хотите сделать это всего за один месяц, мы можем изменить подзапрос, чтобы подсчитывать только пользователей, добавленных в этом месяце:
SELECT
*
FROM
users AS u0
JOIN
(
SELECT
u1.REFERRAL AS agg,
COUNT(u1.REFERRAL) AS total
FROM
users AS u1
WHERE
YEAR(u1.sigmup_date) = YEAR(NOW())
AND
MONTH(u1.sigmup_date) = MONTH(NOW())
GROUP BY
u1.REFERRAL
ORDER BY total DESC
) AS u2
ON
u2.agg = u0.AFF_ID
Наконец, вам может / придется использовать ЛЕВОЕ СОЕДИНЕНИЕ, если вам нужны пользователи, которые никого не рекомендовали.
А если добавить что-то вроде
WHERE
u2.agg IS NULL
В конце внешнего запроса вы можете найти всех пользователей без рефералов.
Помимо любой оптимизации, лучше (для меня) делать это как подзапрос.
И последнее, если вам не нравится NULL в целом, вы можете использовать COALESCE(), чтобы исправить это следующим образом:
SELECT
u0.*, COALESCE(u2.total,0) AS total
FROM
users AS u0
LEFT JOIN
(
SELECT
u1.REFERRAL AS agg,
COUNT(u1.REFERRAL) AS total
FROM
users AS u1
GROUP BY
u1.REFERRAL
ORDER BY total DESC
) AS u2
ON
u2.agg = u0.AFF_ID
А теперь это 0
Я считаю, что COALESCE очень полезен, и чем меньше работы придется выполнять в коде, тем легче будет. Если вы можете отформатировать данные из БД так, как вы хотите, это, как правило, лучше, чем манипулировать ими позже. Это может быть разница между использованием чего-то вроде fetchAll и необходимостью его зацикливать и изменять в PHP, прежде чем вы сможете его использовать.
Ваше здоровье!
Почему тег <sql>? Вам нужен SQL-запрос?