Блог

Главная - Вопросы по программированию - Как получить среднее и последние данные из группы строк в таблице в MySQL

Как получить среднее и последние данные из группы строк в таблице в MySQL

#mysql #sql #database-design #group-by #average

#mysql #sql #база данных-дизайн #группировка по #среднее

Вопрос:

у меня есть таблица с именем tbl_remit

 No.|id|employer|ap_from| ap_to |amount|
1  |1 |    a   |01/2016|01/2016|5.00  |
2  |1 |    a   |02/2016|02/2016|5.00  |
3  |1 |    a   |03/2016|03/2016|2.50  |
4  |2 |    b   |01/2016|01/2016|1.00  |
5  |2 |    b   |02/2016|02/2016|1.00  |
6  |2 |    b   |03/2016|03/2016|1.50  |
7  |2 |    b   |04/2016|04/2016|0.50  |

мне нужно создать представление и получить среднее значение суммы для каждого работодателя.

 id|employer|Average_opt1|LastRemit_opt2|
1 |    a   |    4.16    |     2.50     |
2 |    b   |    1.00    |     0.50     |

Я использовал этот код. но не удалось.

 CREATE OR REPLACE VIEW view_potential_collection AS
SELECT id, Employer, AVG(Amount) as Average_Opt1, Amount as LastRemit_Opt2
FROM tbl_remit
GROUP BY id, Employer

Комментарии:

1. Как может иметь смысл включать неагрегированную сумму в этот результат?

Ответ №1:

 SELECT T.ID,T.EMPLOYER,T.AMOUNT, (SELECT AVG(AMOUNT) FROM TBL_REMIT WHERE EMPLOYER = T.EMPLOYER) AVGAMT
FROM    TBL_REMIT T
WHERE   T.ROWID = (SELECT MAX(ROWID) FROM TBL_REMIT WHERE EMPLOYER = T.EMPLOYER)
;

Результат

  ------ ---------- -------- ---------- 
| ID   | EMPLOYER | AMOUNT | AVGAMT   |
 ------ ---------- -------- ---------- 
|    1 | a        |   2.50 | 4.166667 |
|    2 | b        |   0.50 | 1.000000 |
 ------ ---------- -------- ---------- 
2 rows in set (0.00 sec)

Комментарии:

1. Сэр, могу я спросить, откуда у вас таблица T? :3

2. T — псевдоним для tbl_remit

3. Сэр, какова цель этой строки? «(ВЫБЕРИТЕ СРЕДНЕЕ ЗНАЧЕНИЕ (СУММА) ИЗ TBL_REMIT, ГДЕ РАБОТОДАТЕЛЬ = T.РАБОТОДАТЕЛЬ) СРЕДНЕЕ значение»

4. Это подзапрос корреляции, который вычисляет среднее значение для каждого сотрудника

Ответ №2:

Присоединитесь к результирующему набору, имеющему среднее значение amount, с результатом, имеющим row_number на основе идентификатора, работодателя и порядка убывания без столбцов.

Запрос

 SELECT t1.`id`, t1.`employer`, t1.`Average_opt1`, t2.`amount` FROM
(SELECT `id`, `employer`, AVG(`amount`) as `Average_opt1`
FROM tblEmployee
GROUP BY `id`, `employer`)t1
JOIN 
(SELECT `No`, `id`, `employer`, `ap_from`, `ap_to`, `amount`, 
(CASE `id` WHEN @curA THEN @curRow := @curRow   1 
ELSE @curRow := 1 AND @curA := `id` END) AS rn 
FROM tblEmployee t, 
(SELECT @curRow := 0, @curA := '') r 
ORDER BY `id`, `No` DESC)t2
ON t1.`id` = t2.`id`
AND t2.rn = 1;