Выбирать только строки с максимальной датой

В таблице clickhouse у меня есть несколько строк для одного _id. Я хочу получить только одну строку для каждого _id, где столбец _status_set_at имеет максимальное значение. Вот чем я сейчас занимаюсь:

SELECT _id, max(_status_set_at), count(_id)
FROM pikta.candidates_states
GROUP BY _id

output

Поскольку я не могу использовать функцию max() в предложении WHERE, как решить эту проблему? count(_id) показывает, сколько строк есть для каждого _id, если запрос правильный, он должен показать 1. Также, насколько я понимаю, в базе данных Clickhouse нет предложения ON.

UPD: в Clickhouse есть пункт ON


sql database clickhouse
person Graygood    schedule 16.08.2018    source источник

Ответы (4)


arrow_upward
2
arrow_downward

Ваш запрос возвращает то, что вам нужно - только одну строку для каждого _id, где столбец _status_set_at имеет максимальное значение. Вам не нужно ничего менять в исходном запросе.

count (_id) показывает, сколько строк для каждого _id в исходной таблице, но не в результате запроса. Результат запроса содержит только одну строку для каждого _id, потому что вы группируете по _id.

Этот запрос показывает, что в результате вашего запроса есть только одна строка для каждого _id.

SELECT _id, max_status_set_at, count(_id) FROM (
SELECT _id, max(_status_set_at) max_status_set_at
FROM pikta.candidates_states
GROUP BY _id) t
GROUP BY _id

Если вам нужно применить условие к max (_status_set_at), вы можете использовать HAVING

person Mikhail    schedule 17.08.2018

arrow_upward
2
arrow_downward

Решение - 1:

SELECT Z._id,
       Z._status_set_at
  FROM 
(
SELECT _id, 
       _status_set_at, 
       max(_status_set_at) OVER ( PARTITION BY _id ORDER BY _status_set_at DESC ) AS rnk
FROM pikta.candidates_states
) Z
WHERE Z.rnk = 1;

Решение - 2:

SELECT A._id,
       A._status_set_at  
  FROM pikta.candidates_states A
CROSS JOIN
       ( 
         SELECT _id, 
                MAX(_status_set_at) AS max_status_set_dt         
           FROM pikta.candidates_states
         GROUP BY _id
        ) B
WHERE A._id = B._id
  AND A._status_set_at = B.max_status_set_dt;
person Teja    schedule 16.08.2018
comment
Думаю, в Clickhouse тоже не реализовано разделение и более. - person Graygood; 16.08.2018
comment
Он говорит, что ›в базе данных Clickhouse нет пункта ON. - person Harun KARATAŞ; 16.08.2018
comment
@Teja no. Есть только ВСЕ / ЛЮБОЙ ВНЕШНИЙ / ВНУТРЕННИЙ СОЕДИНЕНИЕ - person Graygood; 16.08.2018
comment
Второе решение довольно близко к тому, что я хочу, но некоторые _id по-прежнему получают несколько значений. imgur.com/a/Ofm6rGq - person Graygood; 16.08.2018
comment
Перекрестное соединение возвращает какие-либо результаты или выдает ошибку? - person Teja; 16.08.2018
comment
@Teja Error running query: ('Connection aborted.', BadStatusLine("''",)) - person Graygood; 16.08.2018
comment
@Teja обнаружила, что возвращается несколько строк, потому что некоторые из его _status_id также могут повторяться. Проблема его clickhouse, потому что он не поддерживает миллисекунды. Большое спасибо - person Graygood; 16.08.2018
comment
Хороший. Значит, проблема не в перекрестном соединении. - person Teja; 16.08.2018

arrow_upward
1
arrow_downward

Если вы хотите, чтобы вы указали максимальное предложение для оператора where, возможно, это сработает

SELECT * from (SELECT _id, max(_status_set_at) as [MaxDate], count(_id) as [RepeatCount]
    FROM pikta.candidates_states
    GROUP BY _id) t WHERE t.MaxDate = '@parameter'
person Harun KARATAŞ    schedule 16.08.2018

arrow_upward
0
arrow_downward

Замените <other columns> списком других столбцов, которые необходимо выбрать.

SELECT _id, _status_set_at, <other columns>
FROM pikta.candidates_states
WHERE (_id, _status_set_at) in (
    SELECT _id, max(_status_set_at)
    FROM pikta.candidates_states
    GROUP BY _id
)

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

Я отправил свой ответ, потому что ни один из предыдущих ответов не подходил для первоначального вопроса, насколько я понял. Рассматриваемый запрос должен иметь возможность возвращать другие столбцы, а не только _id и _status_set_at, иначе это бесполезно, вы можете использовать для него базовый выбор, который уже упоминался в вопросе. Ответ Майка нельзя изменить таким образом. Решения Teja не будут работать в Clickhouse.

person sdrenn00    schedule 13.05.2020