文本挖掘与词提取

Question

我试图从R中的SQL语句中提取表名--例如，我将SQL查询导入到R中，一行将包含：

SELECT A , B
FROM Table.1 p
JOIN Table.2 pv
ON p.ProdID.1 = ProdID.1
JOIN Table.3 v
ON pv.BusID.1 = v.BusID
WHERE SubID = 15
ORDER BY v.Name;

在R中，我一直试图对SQL语句使用str拆分，该语句将每个单词拆分成一个列，创建一个数据框架，然后找到与单词"from“的匹配，并提取下一个单词，即表1。

我在如何从多个连接中提取其他表方面遇到了困难，或者如果有一种更有效的方法或包，在我的研究中我还没有遇到过。任何帮助都将不胜感激！

Answer 1

这里有一种使用正则表达式的方法：

lines <- strsplit("SELECT A, B
                   FROM Table.1 p
                   JOIN Table.2 pv
                   ON p.ProdID.1 = ProdID.1
                   JOIN Table.3 v
                   ON pv.BusID.1 = v.BusID
                   WHERE SubID = 15
                   ORDER BY v.Name;", split = "\\n")[[1]]

sub(".*(FROM|JOIN) ([^ ]+).*", "\\2", lines[grep("(FROM|JOIN)", lines)]) # "Table.1" "Table.2" "Table.3"

分解：

# Use grep to find the indeces of any line containing 'FROM' or 'JOIN'
keywords_regex <- "(FROM|JOIN)"
line_indeces <- grep(keywords_regex, lines) # gives: 2 3 5
table_lines <- lines[line_indeces] # get just the lines that have table names

# Build regular expression to capture the next word after either keyword
table_name_regex <- paste0(".*", keywords_regex, " ([^ ]+).*")

# The "\\2" means to replace each match with the contents of the second capture 
# group, where a capture group is defined by parentheses in the regex
sub(table_name_regex, "\\2", table_lines)