Блог

Я готовлюсь к собеседованию на работу, связанную с большим количеством SQL. Я заметил несколько упражнений, которые вращаются вокруг возврата значений на основе последовательности между строками, и я хотел бы знать, есть ли стандартный способ сделать это. Что-то похожее на приведенный ниже подзапрос, который я нашел полезным для выбора максимального / минимального значения:

 ( SELECT column FROM table ... ORDER BY column [DESC] LIMIT 1 )
  

Вот соответствующий пример из LeetCode:

 Table: Stadium

 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| id            | int     |
| visit_date    | date    |
| people        | int     |
 --------------- --------- 
visit_date is the primary key for this table.
  

Каждая строка этой таблицы содержит дату посещения и идентификатор посещения стадиона с количеством людей во время посещения.
Никакие две строки не будут иметь одинаковую дату посещения, и по мере увеличения идентификатора даты также увеличиваются.

Напишите SQL-запрос для отображения записей с тремя или более строками с последовательными идентификаторами, и количество людей больше или равно 100 для каждого.

Возвращает таблицу результатов, упорядоченную по visit_date в порядке возрастания.

Формат результата запроса приведен в следующем примере.

 Stadium table:
 ------ ------------ ----------- 
| id   | visit_date | people    |
 ------ ------------ ----------- 
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
 ------ ------------ ----------- 

Result table:
 ------ ------------ ----------- 
| id   | visit_date | people    |
 ------ ------------ ----------- 
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
 ------ ------------ ----------- 
  

В моей попытке решить проблему использовалась пользовательская переменная. Приведенный ниже код был моим лучшим решением:

 SET @rowIndex = 0;

SELECT s1.id, s1.visit_date, s1.people
FROM ( SELECT @rowIndex:=@rowIndex 1 as rowIndex, s.id, s.visit_date, s.people
    FROM Stadium as s
    WHERE s.people >=100 ) as s1
GROUP BY rowIndex - s1.id, s1.id, s1.visit_date, s1.people
HAVING COUNT(s.id) >= 3
ORDER BY s1.visit_date
  

В приведенном выше запросе где-то есть синтаксическая ошибка.Сообщение:

У вас ошибка в синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, чтобы найти правильный синтаксис для использования рядом с ‘SELECT s1.id , s1.дата посещения, s1.люди ИЗ ( ВЫБЕРИТЕ @RowIndex := @RowIndex 1 как’ в строке 4

У кого-нибудь есть любимый способ выбора строк на основе последовательности? Возможно, что менее важно, может ли кто-нибудь заметить ошибку в моем запросе выше?

Я бы назвал это проблемой пробелов и островов. Вот подход, использующий оконные функции:

 select id, visit_date, people
from (
    select s.*, count(*) over(partition by id - rn) cnt
    from (
        select s.*, row_number() over(order by id) rn
        from stadium s
        where people > 100
    ) s
) s
where cnt >= 3
  

Идея состоит в том, чтобы отфильтровать дни, в которых менее 100 посещений, а затем использовать разницу между id и монотонно возрастающий ранг для идентификации островов (последовательные дни с более чем 100 посещениями). Затем мы можем сохранить группы, которые имеют только более 3 строк.