Как составить сложный SQL запрос?

Question

[Angel1]

Привет, помогите составить сложный (для меня) запрос!

В общем составляю график для аналитики странички из соц сети, хочу вывести количество "подписчиков" по дням.
Есть скрипт который собирает кол-во подписчиков и при каждом сборе записывает общее число подписчиков аккаунта. В день происходит несколько тысяч таких сборов для каждого аккаунта.

И так, есть таблица account_analytics с полями
id (int)
account_id (int)
count (int)
created_at (int)

В ней следующие записи: prnt.sc/dmsawa
-----------------------------------------------------------------------
1. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для всех аккаунтов.
Пример: prnt.sc/dmsbzd

2. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для определённого аккаунта.
Пример: prnt.sc/dmscev
-----------------------------------------------------------------------

Внимание: очень важный момент, необходимо учитывать только самые последние данные, т.е. собрать id уникальных аккаунтов и выбрать для каждого запись отсортированную created_at по убыванию (DESC). Ну в общем брать цифры из самых последних проверок, а не наоборот.

Данные по всем аккаунтам я получаю так:

SELECT 
    * 
FROM (
        SELECT
            DATE(FROM_UNIXTIME(`created_at`)) AS `date`, `count`
        FROM
            `account_analytics`
        ORDER BY
            `id` DESC
        WHERE
            `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY))
    ) `t`
GROUP BY 
    `date` DESC

Для первой задачи не получается составить запрос((

Answer 1

[Сергей Еремин]

Все такие селекты за отдельные периоды собираешь юнионами... Все...

Comments

[Артём Каретников]

Класс решение, замечательно! За 12 месяцев, например - 12 сканов таблицы вместо один раз сесть и решить задачу. Гениально!

[Сергей Еремин]

С текущей структурой базы, никак... А если нужно отсчитывать периоды от текущего момента (а он плавающий) то и подавно.

Answer 2

[AlikDex]

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

SELECT DATE(FROM_UNIXTIME(`created_at`)) AS `dt`, COUNT(`id`) AS `cnt`
FROM `account_analytics`
WHERE `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY))
GROUP BY DATE(FROM_UNIXTIME(`created_at`));

Не проверял, не знаю работает или нет, но думаю ход мысли понятен.
Либо может GROUP BY `dt` сработает, чтоб не считать снова.
Для аккаунта тож самое будет, тока с указанием ида аккаунта во WHERE,

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

Answer 3

[mletov]

Для первой задачи не получается составить запрос((

1. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для всех аккаунтов.

Не совсем понял условие
count - это общее количество подписчиков на этот день (с учетом подписавшихся в предыдущие дни) или количество новых подписчиков, которые подписались в этот день?

Если второе

SELECT DATE(FROM_UNIXTIME(`created_at`)), SUM(count) AS cnt
FROM account_analytics
GROUP BY DATE(FROM_UNIXTIME(`created_at`))
ORDER BY DATE(FROM_UNIXTIME(`created_at`)) DESC
LIMIT 0, 7

Учитываются ли отписавшиеся?

================================================================================
UPDATE 1

SELECT DATE(FROM_UNIXTIME(account_analytics.created_at)) AS dt,
	   SUM(account_analytics.count) AS sumAll
FROM account_analytics
INNER JOIN
(
	-- Берем последний обход по каждому аккаунту за каждый день
	SELECT account_id,
		   DATE(FROM_UNIXTIME(`created_at`)) AS dt,
		   MAX(created_at) AS dtMax
	FROM account_analytics 
	GROUP BY account_id,
			 DATE(FROM_UNIXTIME(`created_at`))
) AS t1		 
ON account_analytics.account_id = t1.account_id
AND account_analytics.created_at = t1.dtMax
GROUP BY DATE(FROM_UNIXTIME(account_analytics.created_at))
ORDER BY  DATE(FROM_UNIXTIME(account_analytics.created_at)) DESC
LIMIT 0, 7

Comments

[Angel1]

Дело в том, что в каждой записи хранится общее кол-во подписчиков, а не новых. И получается, что запрос выше подсчитывает по схеме: кол-во запросов мониторинга * кол-во подписчиков каждого ответа запроса. А мне необходимо считать только последние записи в БД, для каждого аккаунта.

[mletov]

Angel1: Проапдейтил ответ. Как я понимаю, требуется что-то наподобие PARTITION BY из MS SQL.
Т е найти последний обход за каждый день по каждому аккаунту (как я понимаю, таких обходов за день по этому аккаунту м б много), а потом присоединить по TIMESTAMP и account_id (На том допущении, что запросы по одному аккаунту в один день отличаются по времени хотя бы на 1 секунду)

Answer 4

[Angel1]

Готово, запрос не очень приятный, но работает. Далее timestamp заменю на date. Запрос занял 0.0806 сек.

1. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для всех аккаунтов.

SELECT
        `date`,
        SUM(`count`) AS `count`
FROM (
        SELECT * FROM (
            SELECT
                DATE(FROM_UNIXTIME(`created_at`)) AS `date`, 
                CONCAT(`account_id`, DATE(FROM_UNIXTIME(`created_at`))) AS `a`,
                `count_followers`
            FROM 
                `account_analytics`
            WHERE 
                `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY))
            ORDER BY `id` DESC
    	) `t1` GROUP BY `a` 
) `t2` 
GROUP BY `date` DESC

2. Необходимо получить 7 записей (неделя) из БД, в каждой записи должна быть дата и кол-во подписчиков для определённого аккаунта.

SELECT
        `date`,
        SUM(`count`) AS `count`
FROM (
        SELECT * FROM (
            SELECT
                DATE(FROM_UNIXTIME(`created_at`)) AS `date`, 
                CONCAT(`account_id`, DATE(FROM_UNIXTIME(`created_at`))) AS `a`,
                `count_followers`
            FROM 
                `account_analytics`
            WHERE 
                `account_id` = 3 AND `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY))
            ORDER BY `id` DESC
    	) `t1` GROUP BY `a` 
) `t2` 
GROUP BY `date` DESC

Алгоритм такой:
1. Собираются все записи за 7 дней, колонка с датой и id аккаунта сливаются в одну строку. Все записи сортируются по убыванию
2. Затем строки группируются по колонке в которой соеденена дата и id аккаунта.
3. Результат двух действий выше группируется по дате (по убыванию)

Теперь стоит вопрос об оптимизации. Получается, что первый запрос делает выборку на ~8к записей, затем вторая и третья выборка оставляет только 7 записей.

SELECT
    	DATE(FROM_UNIXTIME(`created_at`)) AS `date`, 
    	CONCAT(`account_id`, DATE(FROM_UNIXTIME(`created_at`))) AS `a`,
    	`count_followers`
FROM 
    	`account_analytics`
WHERE 
    	`account_id` = 3 AND `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY))
ORDER BY `id` DESC

Я так предполагаю, что оптимизировать этот запрос нельзя, не учитывая назначение индексов и изменение типа поля created_at с int в datetime?