[问题类型]:
程式咨询(我想用R 做某件事情，但是我不知道要怎么用R 写出来)
[软件熟悉度]:
新手(没写过程式，R 是我的第一次)
[问题叙述]:
R与SQL在整理/查询资料上有些类似的用法，
现在想要用R做SQL做的事，
中间卡住，来请教各位大大了~
[程式范例]:
SQL：
SELECT
A.DBName
,A.TableName
,B.Count
,C.LADate
,C.LATime #最后要的5个字段
FROM
Table_A A #主要的表，join最后结果row n不变
LEFT JOIN
( #子查询1 取 count
SELECT A.DBName,
A.TableName,
sum(A.Count) as Count
FROM Count A
LEFT JOIN
User B
ON A.UserName=B.UserName
GROUP BY A.DBName,A.TableName
WHERE B.User_Type IN ('A','B')
) B
ON A.DBName = B.DBName
AND A.TableName = B.TableName
LEFT JOIN
( #子查询2 取 LADate,LAtime
select DBName,
TableName,
LADate,
LATime
from
LA
QUALIFY ROW_NUMBER() OVER (
PARTITION BY DBName,TableName
ORDER BY LADate DESC, LATime DESC)=1
#以LADate DESC,LaTime DESC排序后，取第一列
) C
ON B.DBName = C.DBName
AND B.TableName = C.TableName
R写的想法也是和SQL类似，
先写子查询，最后再3个表left join，
只写了2个子查询，第2段卡住(红字部分)
若用group_by取top1不太对，
应该要取LADate同一列的LATime
sub_q1<-left_join(x=count,y=user,by="UserName") %>%
group_by(DBName,TableName) %>%
summarise(sum(Count))
sub_q2<-select(LA,DBName,TableName,
LADate,LATime) %>%
order_by LADate desc,LATime desc %>%
group_by(DBName,TableName) %>% ??
[环境叙述]:
R version 3.3.2 (2016-10-31)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows >= 8 (build 9200)
[关键字]:
SQL
选择性，也许未来有用