Как сначала отсортировать и потом сгруgпировать выборку SQL?

Question

[ekaramazov]

Нужно найти по одному последнему действию юзеров, а получаю по первому

SELECT
    *
FROM
    (
    SELECT
        act.*,
        users.name,
        users.lname,
        users.ava_file
    FROM
        act
    INNER JOIN users WHERE act.date > "1517427936" AND act.date < "1517428356" AND act.creator = users.id
    ORDER BY
        act.date
    DESC
) a
GROUP BY
    a.creator

SELECT
    b.*,
    users.name,
    users.lname,
    users.ava_file
FROM
    (
    SELECT
        a.*
    FROM
        (
        SELECT
            act.*
        FROM
            act
        WHERE
            act.date > "1517427936" AND act.date < "1517428356"
        ORDER BY
            act.id
        DESC
    ) a
GROUP BY
    a.creator
) b
INNER JOIN users WHERE b.creator = users.id

и какой из запросов лучше использовать? второй день голову ломаю над задачкой.

Answer 1

[Dimonchik]

сорт в вычислениях до Ж, это всего лишь способ вывода

таблицы нарисуйте

Comments

[longclaps]

Сорт в вычислениях не до Ж при использовании оконных функций.

[ekaramazov]

CREATE TABLE `act` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `date` int(10) unsigned NOT NULL,
 `creator` int(10) unsigned NOT NULL,
 `value` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

CREATE TABLE `users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `invited_by_id` int(11) unsigned DEFAULT NULL,
 `creator_id` int(11) unsigned DEFAULT NULL,
 `name` varchar(128) NOT NULL,
 `lname` varchar(128) NOT NULL,
 `email` varchar(128) NOT NULL,
 `phone` varchar(128) NOT NULL,
 `ava_file` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

Answer 2

[Александр]

Что-то вроде этого?

CREATE TEMPORARY TABLE `act_tmp`

SELECT
    `act`.*
FROM
    `act`
LEFT JOIN(
    SELECT
        `creator`,
        MAX(`date`) AS `date`
    FROM
        `act`
    WHERE
        `act`.`date` > "1517427936" AND `act`.`date` < "1517428356"
    GROUP BY
        `creator`
) AS `act_b`
ON
    `act`.`creator` = `act_b`.`creator` AND `act`.`date` = `act_b`.`date`
WHERE
    `act_b`.`creator` IS NOT NULL
GROUP BY
    `act`.`creator`;

SELECT
    `act_tmp`.*,
    `users`.`name`,
    `users`.`lname`,
    `users`.`ava_file`
FROM
    `users`
LEFT JOIN `act_tmp` ON `users`.`id` = `act_tmp`.`creator`
WHERE
    `act_tmp`.`creator` IS NOT NULL
GROUP BY
    `users`.`id`

Comments

[ekaramazov]

по моему слишком сложно получилось

[Александр]

ekaramazov, было бы легче, если бы при группировке выбирались данные именно с той строки, где max(col), но max() живет своей жизнью. Генерация act_tmp и есть попытка решить эту проблему (вместо этого можно конечно поместить во вложенный select, но запрос меньше не станет).

[ekaramazov]

Александр, и сколько вложенных селектов будет?

[Александр]

ekaramazov,
Два

SELECT
    `act_tmp`.*,
    `users`.`name`,
    `users`.`lname`,
    `users`.`ava_file`
FROM
    `users`
LEFT JOIN(
    SELECT `act`.*
    FROM
        `act`
    LEFT JOIN(
        SELECT
            `creator`,
            MAX(`date`) AS `date`
        FROM
            `act`
        WHERE
            `act`.`date` > "1517427936" AND `act`.`date` < "1517428356"
        GROUP BY
            `creator`
    	) AS `act_b`
	ON
    	`act`.`creator` = `act_b`.`creator` AND `act`.`date` = `act_b`.`date`
	WHERE
    	`act_b`.`creator` IS NOT NULL
	GROUP BY
    	`act`.`creator`
	) AS `act_tmp`
ON
    `users`.`id` = `act_tmp`.`creator`
WHERE
    `act_tmp`.`creator` IS NOT NULL
GROUP BY
    `users`.`id`

Answer 3

[d-stream]

А какой смысл сортировки, которая потом будет "перевычеслена" группировкой?

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

select
user_id,
act_date
from table
group by user_id

не прокатит, а потребуется агрегировать act_date

select
user_id,
min(act_date) -- или max|sum|avg и т.п.
from table
group by user_id

Comments

[ekaramazov]

с max пробовал - выдаёт одну строку с максимальной датой из всех а не для каждого

[d-stream]

ekaramazov, видимо неправильно составлен запрос
ибо max() берет значения в рамках группировки

[ekaramazov]

d-stream, в том то и дело что нужно найти сначала все действия всех юзеров и потом сгрупировать чтоб получить только с максимальной датой, а вот как перевернуть группировку не соображу

[ekaramazov]

даже упростив запрос до

select *,max(date) FROM act WHERE date > "1517427936" and date < "1517428356" GROUP BY creator

мне выдаёт все поля + max(date) но максимальная дата может быть и верна но остальные данные выдаёт не отсортированные - т е получаю первую попавшуюся строку

[d-stream]

ekaramazov,
select * и group by - ОТЛИЧНЫЙ способ нагадить себе на ногу.

пример

1 a 01-01-2017
1 b 02-02-2017

что покажет во второй колонке
select * from ... group by column 1

и почему?

[ekaramazov]

d-stream, первую запись поидее

[ekaramazov]

d-stream, а чем так плох select *?

[d-stream]

ekaramazov,

первую запись поидее

а чего не последнюю? И из какой последовательности: ордерингу, физическом порядке хранения записей, физическом порядке хранения данных в индексе? -)))

а чем так плох select *?

отсутствием однозначности - см. выше.

Именно поэтому нормальная практика - явно указывать агрегацию тех полей, которые не участвуют в группировке. Это дает гарантию однозначности и предсказуемости результата запроса.

[ekaramazov]

d-stream,

select id,date,creator,max(date) as m FROM rooms_bids WHERE date > "1517427936" and date < "1517428356" GROUP BY creator

ничего не дало, хранится всё как и добавляется - со временем и по инкрименту

[d-stream]

ekaramazov, необходимо агрегировать поля не участвующие в группировке.

Иначе это будет как пописать не снимая штанов - иногда на пол, иногда - на ногу...

[ekaramazov]

d-stream, эм а как мне неизменяя агрегировать id и value например? (сейчас нет value тк пока с id разбираюсь)

[d-stream]

ekaramazov, в чем проблема?
или группировать или агрегировать

[ekaramazov]

d-stream, а можно пример хотя бы для этого запроса?

select id,date,creator,max(date) as m FROM rooms_bids WHERE date > "1517427936" and date < "1517428356" GROUP BY creator

[d-stream]

ekaramazov,

select 
first_groupped_field,
second_gropped_field,
sum(other_filed),
min(other_field),
max(other_field)
from table
where ...
group by first_groupped_field, second_gropped_field