sql - 以向量为输入查询R中不同时间序列的函数
问题描述
我正在获取数据库事实表中特定时间序列的最小和最大日期,如下所示:
auxiliar.dates <- function(machine, signal) {
q.Aux1 <- paste("SELECT
t1.machine,
t1.signal,
t2.signal_name,
t1.min_snsr_dt,
t1.max_snsr_dt,
t1.min_snsr_ts,
t1.max_snsr_ts,
t1.min_etl_dt,
t1.max_etl_dt,
t1.rec_cnt
FROM ", config$SF_CONFIG$my_schema_name1, ".mytable1 AS t1
LEFT JOIN ", config$SF_CONFIG$my_schema_name1, ".mytable2", "AS t2
ON t1.signal=t2.signal
WHERE t1.unit_key=")
q.Aux2 <- " AND t1.signal="
q.Aux.final <- str_c(q.Aux1, machine, q.Aux2, signal)
res <- dbSendQuery(myConn, q.Aux.final)
df <- as.data.table(dbFetch(res, n=-1))
dbClearResult(res)
return(df)
}
dates <-auxiliar.dates("machine", "signal")
该函数的输出是一个数据表,如下所示:
然后我使用输出来查询 min 和 max ts 之间的特定信号,如下所示:
signalQuery <- function(machine, signal, min_ts, max_ts) {
q1.aux1 <- paste("SELECT snsr_val,
snsr_ts,
snsr_dt,
signal,
qual,
machine
FROM ", config$SF_CONFIG$schema_name1,
".mytable1 AS v
WHERE machine=", sep="")
q3.aux1 <-paste(" AND signal=", signal, " AND snsr_ts BETWEEN ", "'", min_ts, "'",
" AND ", "'", max_ts, "'", " ORDER BY v.snsr_ts", sep = "")
qt.auxtotal <- str_c(q1.aux1,
machine,
q3.aux1) #we join que full query with stringr library
res <- dbSendQuery(myConn, qt.auxtotal)
df <- as.data.table(dbFetch(res,n=-1))
dbClearResult(res) #cleaning memory
return(df)
}
例如,我正在调用信号 71:
signal71.dates <- auxiliar.dates(machine, 71)
df <- signalQuery(machine, 71, signal71.dates$min_snsr_dt, signal71.dates$max_snsr_dt)
如果我需要查询更多信号,我正在执行完全相同的过程,但我正在获取我的数据帧调用 signal_number.dates 的 max_snsr_dt 的最小值和我的数据帧 signal_number.dates 的 min_snsr_dt 的最大值。
我想知道改变一下过程并能够在 auxiliar.dates 和 signalQuery 函数中输入我想要的信号的向量。
我的第一次尝试是修改auxiliar.dates:
q.Aux2 <- " AND t1.signal="
到:
q.Aux2 <- " AND t1.signal IN ("
q.Aux.final <- str_c(q.Aux1, machine, q.Aux2, paste(signal, ")", sep = ""))
但是,当我将函数称为:
test <- auxiliar.dates(984, c(70,71))
我收到以下错误:
new_result(connection@ptr, statement) 中的错误:需要单个字符串值:[type=character; 范围=2]。
有人能支持吗?
BR
解决方案
考虑以下变化:
参数化:避免过多的字符串连接会损害可读性和可维护性。而是使用
DBI
+odbc
with支持的参数化sqlInterpolate
。理想情况下,您将在 SQL 字符串语句中对表名进行硬编码,但由于标识符无法参数化,paste
(或paste0
之间没有空格)仍然必须使用。单一 SQL 查询:使用Snowflake 支持的公用表表达式 (CTE)组合两个 SQL 查询。具体来说,第一个查询通过机器、信号和日期
BETWEEN
间隔连接到最后一个查询。反过来,您可以组合这两个函数,减少数据库访问次数,并避免中间的辅助对象。使用
dbGetQuery
:如果数据加载不是需要按块获取大型结果集的问题,请使用和步骤dbGetQuery
组合以进行简洁。dbSendQuery
dbFetch
函数输入:正如@r2evans 评论,避免依赖未知父源的环境变量位于本地函数内。相反,为局部范围变量传递所有需要的输入参数。
迭代:因为这些函数使用标量参数,所以您必须迭代诸如 with 之类的值
lapply
以多次运行函数,然后对最终数据表进行行绑定结果。
单一功能
signalQuery <- function(my_schema, machine, signal) {
# PREPARED STATEMENT
sql <- paste0("WITH sub AS
(SELECT t1.machine, t1.signal, t2.signal_name,
t1.min_snsr_dt, t1.max_snsr_dt,
t1.min_snsr_ts, t1.max_snsr_ts,
t1.min_etl_dt, t1.max_etl_dt, t1.rec_cnt
FROM ", my_schema, ".mytable1 AS t1
LEFT JOIN ", my_schema, ".mytable2", "AS t2
ON t1.signal = t2.signal
WHERE t1.unit_key = ?m_param AND t1.signal= ?s_param)
SELECT v.snsr_val, v.snsr_ts, v.snsr_dt, v.signal,
v.qual, v.machine
FROM ", my_schema, ".mytable1 AS v
INNER JOIN sub
ON v.machine = sub.machine
AND v.signal = sub.signal
AND v.snsr_ts BETWEEN sub.min_snsr_dt AND sub.max_snsr_dt
ORDER BY v.snsr_ts")
# BIND PARAMS TO ?MARK PLACEHOLDERS
query <- sqlInterpolate(conn, sql, m_param = machine, s_param = signal)
# RUN QUERY
dt <- as.data.table(dbGetQuery(myConn, query))
return(dt)
}
函数调用
# SINGLE SIGNAL VALUE
q.Aux.final <- signalQuery(myschema = config$SF_CONFIG$my_schema_name1,
machine = 984, signal = 70)
# MULTIPLE SIGNAL VALUES
dt_list <- lapply(c(70,71), function(i)
signalQuery(myschema = config$SF_CONFIG$my_schema_name1,
machine = 984, signal = i)
)
q.Aux.final <- data.table::rbindlist(dt_list)
多功能
如果您确实需要第一个结果集来满足分析需求,请在没有 CTE 的情况下继续执行相同的过程:
auxiliar.dates <- function(my_schema, machine, signal) {
sql <- paste0("SELECT t1.machine, t1.signal, t2.signal_name,
t1.min_snsr_dt, t1.max_snsr_dt,
t1.min_snsr_ts, t1.max_snsr_ts,
t1.min_etl_dt, t1.max_etl_dt, t1.rec_cnt
FROM ", my_schema, ".mytable1 AS t1
LEFT JOIN ", my_schema, ".mytable2", "AS t2
ON t1.signal=t2.signal
WHERE t1.unit_key = ?m_param AND t1.signal= ?s_param")
query <- sqlInterpolate(conn, sql, m_param = machine, s_param = signal)
dt <- as.data.table(dbGetQuery(myConn, query))
return(dt)
}
signalQuery <- function(my_schema, machine, signal, min_ts, max_ts) {
sql <- paste0("SELECT v.snsr_val, v.snsr_ts, v.snsr_dt, v.signal,
v.qual, v.machine
FROM ", my_schema, ".mytable1 AS v
WHERE v.machine = ?m_param
AND v.signal = ?s_param
AND v.snsr_ts BETWEEN ?min_ts_prm AND ?max_ts_prm
ORDER BY v.snsr_ts")
query <- sqlInterpolate(conn, sql, m_param = machine, s_param = signal,
min_ts_prm = min_ts, max_ts_prm = max_ts)
dt <- as.data.table(dbGetQuery(myConn, query))
return(dt)
}
函数调用
# SINGLE SIGNAL VALUE
signal71.dates <- auxiliar.dates(config$SF_CONFIG$my_schema_name1, 984, 71)
q.Aux.final <- signalQuery(config$SF_CONFIG$my_schema_name1, 984, 71,
signal71.dates$min_snsr_dt, signal71.dates$max_snsr_dt)
# MULTIPLE SIGNAL VALUES
dt_list <- lapply(c(70,71), function(i)
signalQuery(myschema = config$SF_CONFIG$my_schema_name1,
machine = 984, signal = i)
)
signal.dates_dt <- data.table::rbindlist(dt_list)
dt_list <- lapply(1:nrow(signal.dates_dt), function(i)
signalQuery(myschema = config$SF_CONFIG$my_schema_name1,
machine = signal.dates_dt$machine[i],
signal = signal.dates_dt$signal[i],
min_ts = signal.dates$min_snsr_dt[i],
max_ts = signal.dates$max_snsr_dt[i])
)
q.Aux.final <- data.table::rbindlist(dt_list)
推荐阅读
- tensorflow - Keras 的 LSTM 层中的时间步长是多少?以及如何选择此参数的值?
- r - 使用上一行的计算值来计算当前
- excel - 使用“插入超链接”对话框向单元格添加链接
- security - 如何在不停机的情况下在 Kafka 集群中启用安全性
- vue.js - 复杂的预期 v-bind 指令 / v-for
I'm running into a strange issue with a complex loop I'm attempting within my component.
<template v-for="(scorecard, scorecardIndex) in scorecards"> <template v-for="(proper
- xcode - 一次只显示一个视图,以便用户可以在视图之间切换
- javascript - PHP 和 Javascript,两个工作代码 - 组合无法显示
- c# - 修改 Selenium 测试框架中的标头
- oop - 面向对象编程理论
- python - 如何重命名 pytest Allure 报告标题