У меня есть таблица table1
, содержащая несколько строк в каждом значении ID
столбца. Я хотел бы, чтобы вычислить такой же весили в среднем с использованием той же колонки продолжительность time
для большого количества столбцов.
Я могу вручную определить каждый столбец:
drop table if exists table2;
create table table2 as
select ID,
sum(var1*time)/sum(time) as var1,
sum(var2*time)/sum(time) as var2,
sum(var3*time)/sum(time) as var3,
sum(var4*time)/sum(time) as var4
from table1 group by ID;
Есть ли способ сделать это программно для большого количества столбцов?
Скажем, я могу использовать регулярное выражение для определения интересующих столбцов. В этом примере мы можем использовать синтаксис var1, var2, var3 и т.д.
Рассмотрим построение вектора суммарных выражений, затем paste(..., collapse)
в более крупный оператор SQL. Отрегулируйте 1:4
до фактического диапазона изменения. Разрывы строк не отображаются в переданном запросе.
sums <- paste0(" sum(var", 1:4, "*time)/sum(time) as var", 1:4)
sql <- paste0('select ID,\n',
paste(sums, collapse = ', \n'), '\n',
'from table1 \n',
'group by ID;')
cat(sql)
# select ID,
# sum(var1*time)/sum(time) as var1,
# sum(var2*time)/sum(time) as var2,
# sum(var3*time)/sum(time) as var3,
# sum(var4*time)/sum(time) as var4
# from table1
# group by ID;
channel <- odbcConnect("redacted",uid="redacted",case="nochange")
x <- sqlQuery(channel, sql)
В некотором роде язык в щеках, но это то, что я буквально должен был делать (в R):
library(RODBC)
library(data.table)
channel <- odbcConnect("redacted",uid="redacted",case="nochange")
x <- as.data.table(sqlQuery(channel, "select * from schema1.table1"))
vars <- grep("var", names(x), v=TRUE)
out <- x[,lapply(.SD, function(v){
sum(v*time)/sum(time)
}),by=ppt_id, .SDcols=vars]
sqlSave(channel, as.data.frame(out), tablename="schema1.table2")
Мне все равно хотелось бы знать, как это сделать в mysql.