sql查询groupby，返回符合条件最少的组，coalesce

我想编写一个sql查询，调用几个条件有点复杂的列。我正在使用rmysql包开发r studio。我的服务器是mysql。
这张table看起来像这样。

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

这就是我想做的：
过滤其中的行 organisation = Ikea 分组方式 Tour_ID 这样地：

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

每组 Tour_ID ，在列中查看最早的日期 A , B , C 以及 D . 如果组中四列中最早的日期介于 2018-05-01 以及 2018-05-31 ，返回整个组。如果一行包含 NA 值，我想忽略 NA 然后看看其他值中最早的日期是什么。例如，对于 Tour_ID = a ，最早的日期是 2018-04-01 因此不符合标准。
总之，只有 Tour_ID = b 以及 Tour_ID = c 符合条件。结果应该是：

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

如何编写sql查询？这是我的尝试，但我只是不知道如何执行groupby，以及如何返回整个组，而不仅仅是具有最早日期的行。

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’将取代nas）
谢谢你的帮助！
补充：根据gordon的回答，这里我重写了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"

我跑了 dbGetQuery 来自rmysql包。但我得到以下错误。我不明白因为 GROUP BY 部分似乎很好。有人知道我为什么会犯这个错误吗？

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

先去拿那个 tour_id 符合条件的：

SELECT Tour_ID
FROM myTable 
WHERE organisation LIKE 'Ikea'
GROUP BY Tour_ID
HAVING LEAST(COALESCE(MIN(A), '2019-01-01'), COALESCE(MIN(B), '2019-01-01'), COALESCE(MIN(C), '2019-01-01'), COALESCE(MIN(D), '2019-01-01')) >= '2018-05-01' AND
       LEAST(COALESCE(MIN(A), '2019-01-01'), COALESCE(MIN(B), '2019-01-01'), COALESCE(MIN(C), '2019-01-01'), COALESCE(MIN(D), '2019-01-01')) < '2018-06-01';

然后将其放入查询以获取原始行。有一种方法：

select t.*
from mytable t join
     (SELECT organisation, Tour_ID
      FROM myTable 
      WHERE organisation LIKE 'Ikea'
      GROUP BY organisation, Tour_ID
      HAVING LEAST(COALESCE(MIN(A), '2019-01-01'), COALESCE(MIN(B), '2019-01-01'), COALESCE(MIN(C), '2019-01-01'), COALESCE(MIN(D), '2019-01-01')) >= '2018-05-01' AND
             LEAST(COALESCE(MIN(A), '2019-01-01'), COALESCE(MIN(B), '2019-01-01'), COALESCE(MIN(C), '2019-01-01'), COALESCE(MIN(D), '2019-01-01')) < '2018-06-01'
     ) tt
     ON tt.tour_id = t.tour_id AND
        tt.organisation = t.organisation;

sql查询groupby，返回符合条件最少的组，coalesce

1条答案

相关问题

热门标签

最新问答