Consulta SQL para GROUP BY, retornar grupos que correspondem às condições de LEAST, COALESCE
Eu quero escrever uma consulta SQL pedindo várias colunas com condições um pouco complicadas. Estou trabalhando no R Studio usando o pacote RMySQL. Meu servidor é MySQL.
A tabela fica assim.
organisation Tour_ID A B C D
Ikea a 2018-04-01 2018-05-07 2018-05-09 2018-05-01
Ikea a 2018-06-01 2018-05-03 2018-05-29 NA
Ikea a 2018-04-02 2018-05-01 2018-07-08 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b NA 2018-05-05 2018-08-02 2018-06-01
Ikea c 2018-06-01 2018-05-07 2018-05-09 2018-05-01
Ikea c 2018-06-01 2018-05-03 NA NA
Ikea c 2018-08-02 2018-05-09 2018-07-08 2018-05-26
Isto é o que eu quero fazer:
filtrar as linhas ondeorganisation = Ikeaagrupado porTour_ID como isso:
organisation Tour_ID A B C D
Ikea a 2018-04-01 2018-05-07 2018-05-09 2018-05-01
Ikea a 2018-06-01 2018-05-03 2018-05-29 NA
Ikea a 2018-04-02 2018-05-01 2018-07-08 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b NA 2018-05-05 2018-08-02 2018-06-01
Ikea c 2018-06-01 2018-05-07 2018-05-09 2018-05-01
Ikea c 2018-06-01 2018-05-03 NA NA
Ikea c 2018-08-02 2018-05-09 2018-07-08 2018-05-26
em cada grupo deTour_ID, observe a data mais antiga nas colunasA, B, C eD. Se a data mais antiga entre as quatro colunas do grupo estiver entre2018-05-01 e2018-05-31, retorne o grupo inteiro. Se uma linha contiverNA valores, quero ignorar oNAse veja a data mais antiga entre os demais valores. Por exemplo, para o grupo deTour_ID = a, a data mais antiga é2018-04-01 portanto, ele não atende aos critérios.
Em conclusão, apenas os grupos ondeTour_ID = b eTour_ID = c coincidir com as condições. O resultado deve ser:
organisation Tour_ID A B C D
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b 2018-06-02 2018-05-01 NA 2018-05-26
Ikea b NA 2018-05-05 2018-08-02 2018-06-01
Ikea c 2018-06-01 2018-05-07 2018-05-09 2018-05-01
Ikea c 2018-06-01 2018-05-03 NA NA
Ikea c 2018-08-02 2018-05-09 2018-07-08 2018-05-26
Como devo escrever uma consulta SQL? Aqui está minha tentativa, mas simplesmente não sei como agrupar por grupo e como retornar o grupo inteiro, não apenas as linhas com a data mais antiga.
SELECT *
FROM myTable
WHERE organisation LIKE 'Ikea' AND
GROUP BY 'Tour_ID' AND
LEAST(COALESCE(A, '2019-01-01'), COALESCE(B, '2019-01-01'), COALESCE(C, '2019-01-01'), COALESCE(D, '2019-01-01')) >= '2018-05-01' AND
LEAST(COALESCE(A, '2019-01-01'), COALESCE(B, '2019-01-01'), COALESCE(C, '2019-01-01'), COALESCE(D, '2019-01-01')) < '2018-06-01';
('2019-01-01' é substituir NAs)
Obrigado por qualquer tipo de ajuda!
ADICIONADO: Após a resposta deGordon, aqui eu reescrevi a instrução SQL.
"SELECT t.* FROM myTable JOIN (SELECT organisation, Tour_ID
FROM myTable
WHERE organisation LIKE 'Ikea' AND
GROUP BY organisation, Tour_ID
HAVING LEAST(COALESCE(MIN(A), '2119-01-01'),
COALESCE(MIN(B), '2119-01-01'),
COALESCE(MIN(C), '2119-01-01'),
COALESCE(MIN(D), '2119-01-01')) >= '2018-05-01' AND
LEAST(COALESCE(MIN(A), '2119-01-01'),
COALESCE(MIN(B), '2119-01-01'),
COALESCE(MIN(C), '2119-01-01'),
COALESCE(MIN(D), '2119-01-01')) < '2018-06-01'
) tt
ON tt.Tour_ID = t.Tour_ID AND
tt.organisation = t.organisation"
E eu corridbGetQuery do pacote RMySQL. Mas eu recebo o seguinte erro. Eu não entendo porqueGROUP BY parte parece bastante bem. Alguém sabe por que estou recebendo esse erro?
dbGetQuery(connection = connection, statement = condition)
Error in .local(conn, statement, ...) : could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY organisation, Tour_ID HAVING LEAST(COALESCE(A' at line 1