Как исправить sql запрос?

Question

[Mike Diam]

Мне нужно получить данные по каждому пользователю. Все данные прекрасно выводятся кроме последнего значения (s4.date as high_OCI_date).

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

select s.user, u.name, s.OCI, s.OCO, s.CCP, s.PV, us.total_games, s2.total_games as today_games, s3.total_games as yesterday_games, s4.date as high_OCI_date
        from qz_user_stat_date s 
                join qz_users u on u.id=s.user
                join qz_user_stat us on us.user=s.user
                left join qz_user_stat_date s2 on s2.user=s.user and s2.date='.$today.'
                left join qz_user_stat_date s3 on s3.user=s.user and s3.date='.$yest.'
                left join qz_user_stat_date s4 on (
                        s4.user=s.user 
                        and s4.date = (
                                select min(date) from qz_user_stat_date
                                where user = s4.user 
                                and OCI = (
                                        select max(OCI) from qz_user_stat_date
                                        WHERE user = s4.user
                                )
                        )
                )

Данный запрос вешает базу

Comments

[Дмитрий Ковальский]

За 3 уровня вложенности запроса, два из которых внутри JOIN - вас бы расстрелять надо. Он не только базу должен повесить, но и черную дыру создать. В крайнем случае выберите нужные данные во временную таблицу и JOIN-те ее.

[Mike Diam]

Я со сложными sql запросами сталкиваюсь впервые, так что не стреляйте)
GROUP BY здесь может помочь?

Answer 1

[Rsa97]

SELECT `s`.`user`, `u`.`name`, `s`.`OCI`, `s`.`OCO`, `s`.`CCP`, `s`.`PV`, `us`.`total_games`, 
       `s2`.`total_games` AS `today_games`, `s3`.`total_games` AS `yesterday_games`, 
       `s4`.`date` AS `high_OCI_date`
  FROM `qz_user_stat_date` AS `s` 
  JOIN `qz_users` AS `u` ON `u`.`id` = `s`.`user`
  JOIN `qz_user_stat` AS `us` ON `us`.`user` = `s`.`user`
  LEFT JOIN `qz_user_stat_date` AS `s2` ON `s2`.`user` = `s`.`user` AND `s2`.`date` = :today
  LEFT JOIN `qz_user_stat_date` AS `s3` ON `s3`.`user` = `s`.`user` AND `s3`.`date` = :yesterday
  LEFT JOIN (
    SELECT `t`.`user` AS `usr`, min(`t`.`date`) AS `date`
      FROM `qz_user_stat_date` AS `t`
      JOIN (
        SELECT `user`, max(`OCI`)  AS `oci`
          FROM `qz_user_stat_date` 
          GROUP BY `user`
      ) AS `o` ON `o`.`user` = `t`.`user` AND `o`.`oci` = `t`.`OCI`
      GROUP BY `t`.`user`
    ) AS `s4` ON `s4`.`usr` = `s`.`user`

Comments

[Mike Diam]

Возникает ошибка: #1052 - Столбец 'user' в field list задан неоднозначно

[Rsa97]

Mike Diam: Да, во вложенном SELECT'е ошибся. Поправил.

[Mike Diam]

Rsa97: Вы поправили в ответе выше? Почему-то так же ошибка #1052 - Столбец 'user' в group statement задан неоднозначно

[Rsa97]

Mike Diam: Угу, ещё в одном месте пропустил, в GROUP BY. Тоже поправил.

[Mike Diam]

Да, так запрос прошел, все ок! Только в результате показывает очень много дублирующих строк по каждому пользователю prntscr.com/bjfb2e
Видимо из-за того что в таблице много записей (сохраняются каждый день) - как вывести строку с данными только по самому последнему дню (кроме даты для max(OSI) конечно)?

[Rsa97]

Mike Diam: Первой вы берёте таблицу статистики, соответственно для каждой её строки (попадающей в JOIN) будет выдана строка результата. Если надо сделать отбор только по пользователям, то за основу надо брать таблицу пользователей, а из статистики прикреплять только нужные данные. То есть заменить

FROM `qz_user_stat_date` AS `s` 
  JOIN `qz_users` AS `u` ON `u`.`id` = `s`.`user`

на
FROM `qz_users` AS `u`
В выборке убрать поля `s`.*, в условиях ON заменить `s`.`user` на `u`.`id`

[Rsa97]

Mike Diam: А данные по последнему дню у вас в первом LEFT JOIN собираются. Если же нужен последний игровой день, то надо добавить

LEFT JOIN (
  SELECT `st`.`OCI` AS `OCI`, `st`.`OCO` AS `OCO`, `st`.`CCP` AS `CPP`, 
           `st`.`PV` AS `PV`, `st`.`user` AS `user`
    FROM `qz_user_stat_date` AS `st`
    JOIN (
      SELECT `user`, MAX(`date`) AS `date`
        FROM  `qz_user_stat_date`
        GROUP BY `user`
    ) AS `d` ON `d`.`user` = `st`.`user` AND `d`.`date` = `st`.`date`
) AS `s` ON `s`.`user` = `u`.`id`

Но если есть несколько записей за один день, то всё равно будет несколько строк на пользователя.

[Mike Diam]

Первые два LEFT JOIN собирают данные по дате сколько игр пользователь сыграл вчера и сегодня.
А вот выводить только по одной строке на каждого пользователя по вашей инструкции поправить запрос не получилось - вывелся пустой json - видимо ошибка в запросе.

[Mike Diam]

Т.е. нужно вывести все данные кроме тех что добавляются из запросов JOIN за последнюю имеющуюся в базе дату. А из JOIN данные верные добавляются, просто остальные данные выводятся за все дни, а нужен только самый последний

[Rsa97]

Mike Diam:

SELECT `s`.`user`, `u`.`name`, `s`.`OCI`, `s`.`OCO`, `s`.`CCP`, `s`.`PV`, `us`.`total_games`, 
       `s2`.`total_games` AS `today_games`, `s3`.`total_games` AS `yesterday_games`, 
       `s4`.`date` AS `high_OCI_date`
  FROM `qz_users` AS `u`
  JOIN `qz_user_stat` AS `us` ON `us`.`user` = `u`.`id`
  LEFT JOIN `qz_user_stat_date` AS `s2` ON `s2`.`user` = `u`.`id` AND `s2`.`date` = :today
  LEFT JOIN `qz_user_stat_date` AS `s3` ON `s3`.`user` = `u`.`id` AND `s3`.`date` = :yesterday
  LEFT JOIN (
    SELECT `t`.`user` AS `usr`, min(`t`.`date`) AS `date`
      FROM `qz_user_stat_date` AS `t`
      JOIN (
        SELECT `user`, max(`OCI`)  AS `oci`
          FROM `qz_user_stat_date` 
          GROUP BY `user`
      ) AS `o` ON `o`.`user` = `t`.`user` AND `o`.`oci` = `t`.`OCI`
      GROUP BY `t`.`user`
    ) AS `s4` ON `s4`.`usr` = `u`.`id`
    LEFT JOIN (
      SELECT `st`.`OCI` AS `OCI`, `st`.`OCO` AS `OCO`, `st`.`CCP` AS `CPP`, 
               `st`.`PV` AS `PV`, `st`.`user` AS `user`
      FROM `qz_user_stat_date` AS `st`
      JOIN (
        SELECT `user`, MAX(`date`) AS `date`
          FROM  `qz_user_stat_date`
          GROUP BY `user`
      ) AS `d` ON `d`.`user` = `st`.`user` AND `d`.`date` = `st`.`date`
    ) AS `s` ON `s`.`user` = `u`.`id`

[Mike Diam]

#1054 - Неизвестный столбец 's.CCP' в 'field list'

[Mike Diam]

А там опечатка была у вас - все запрос заработал!

Answer 2

[Alex]

Проверьте наличие индексов.
Последний JOIN в таблицу превратите.
И я надеюсь user имеет тип int

Comments

[Mike Diam]

Да user конечно int. Уже ищу по временным таблицам, но пока не пойму, подскажете как?