mysql - 过滤掉非零值，除非该值是其组中唯一的值(SQL 或 R)

Question

我有一个记录编号的数据集，每条记录在一列中有多个值。我想生成一个表，在每个组中它返回最小的非负、非 NA 值。 但是如果负数或 NA 对应于该记录的唯一行，那么我希望它返回 NA。

data <- data.frame(record = c(1, 1, 1, 2, 3, 3, 4), value = c(-1, 2, 40, -10, 3, 4, NA))

| record | value |
------------------
|   1    |  -1   |
|   1    |   2   |
|   1    |   40  |
|   2    |  -10  |
|   3    |   3   |
|   3    |   4   |
|   4    |   NA  |
------------------

我想得到

| record |  out  |
------------------
|   1    |   2   |
|   2    |  NA   |
|   3    |   3   |
|   4    |  NA   |
------------------

在 R 中我尝试过:

filter(data, value >= 0 | is.na(value))
data %>% group_by(record) %>% summarize(out = min(value))

在MySQL中我试过

select min(value) as out
from data
where (value >= 0 or value is null))

但是，这两种解决方案都会从表中删除记录 #2。

我如何包含一个子句来检测每条记录的行数并且不消除单个记录？

Answer 1

一个选项是替换那些小于0的'value'到NA，按'record'分组，切片我们所在的行获取 'value' 的 min 或者如果所有都是 NA，获取第一行

data %>%
   mutate(value = replace(value, value < 0, NA)) %>% 
   group_by(record) %>% 
   slice(if(all(is.na(value))) 1 else which.min(value))
# A tibble: 4 x 2
# Groups:   record [4]
#  record value
#   <dbl> <dbl>
#1      1     2
#2      2    NA
#3      3     3
#4      4    NA