Блог

У меня есть список, содержащий клиентов и их ID. Он выглядит так, как показано ниже:

 customers_id <- list(x = John(1,2,3), Rick = c(4), Sam = c(5,6))
  

и база данных, которая выглядит как приведенная ниже и вызывает ‘db’

 date        id   value
2017-05-12  1      51 
2017-05-13  2      3  
2017-05-14  3      217
2017-05-15  1      12
2017-05-16  2      98
2017-05-17  3      123
2017-05-18  1      78
2017-05-19  2      36
2017-05-20  4      178
2017-05-18  5      728
2017-05-19  6      336
2017-05-20  4      718
2017-05-18  5      758
2017-05-19  6      366
2017-05-20  4      787
  

Я пытался создать цикл for, но не смог найти правильное решение. Я думаю, что в цикле должен быть запрос, который
примет правильный идентификатор и сумму значений для идентификатора

 corect_values <- paste(" SELECT date, id, SUM(value) FROM db WHERE id = '", id, "' ")
  

Итак, у меня есть две проблемы: как поместить запрос в цикл for и как написать sql-запрос, который будет учитывать все идентификаторы для customer.

Результат должен быть похож на приведенный ниже:

 John  618
Rick  1683
Sam   2188
  

У вас есть какие-либо идеи, как это можно решить?
Спасибо за любую помощь!

Предполагая, что у вас есть data.frame с именами клиентов и идентификаторами, подобными этому…

 customers_id <- data.frame(Names = c("John", "John", "John", "Rick", "Sam", "Sam"),
                           id = c(1:6))
  

Запросите весь идентификатор и значение из базы данных … допустим, теперь это присвоено df

 SELECT id, value FROM db
  

Теперь вы можете использовать left_join для своих имен, сгруппировать по этому значению и суммировать значение.

 library(dplyr)

df <- left_join (df, customers_id, by = "id")

result <- df %>% 
  group_by(Names) %>% 
  summarise(value = sum(value))
  

Этот код для суммирования

ВЫБЕРИТЕ СУММУ (значения) total Из базы данных, ГДЕ customers_id = идентификатор;

Я постараюсь описать как можно лучше: когда у вас есть 2 таблицы, например: (давайте возьмем ваши таблицы)

Table_A) ID, user_name

Table_B) ID, date, user_id(matched to the table above as a foreign key), value

вы можете выполнить запрос, подобный этому:

 SELECT Table_A.user_name, sum(Table_B.value)
FROM Table_A join Table_B on Table_a.ID = Table_B.user_id
GROUP BY Table_A.ID  
  

Этот запрос просуммирует все значения для всех пользователей, которые у вас есть.
Он покажет только одну запись для каждого пользователя со всеми суммированными значениями

Обеспечивает ли этот выбор желаемые результаты?

 customer_id = sample(c(1:6), 10, replace = T) # just some dummy-user ids as an example

customer_id = paste0("'", customer_id, "'")
customer_id = paste(customer_id, collapse =  ", ")

corect_values = paste0("SELECT sum(value) FROM db where id IN (", customer_id, ") GROUP BY id")
  

ofc это не так, поскольку вы не хотите группировать по идентификатору, но по имени, а имя охватывает не только один идентификатор.

возможно, это может решить проблему. Недостаток в том, что вам все равно придется делать запрос для каждого имени. Итак, даже если следующий код работает, я думаю, все равно было бы лучше создать справочную таблицу в вашей базе данных, чтобы объединить name и id, как предложил @Andrey Fiordean в комментариях.

 select_this <- function(temp_ids)
{

  temp_ids = paste0("'", temp_ids, "'")
  temp_ids = paste(temp_ids, collapse =  ", ")
  corect_values = paste0("SELECT sum(value) FROM db where id IN (", temp_ids, ")")
  # results = request select from db here
  return(results)
}

customers_id <- list("John" = c(1,2,3), "Rick" = c(4), "Sam" = c(5,6))
sapply(customers_id, select_this)
  

Если вам действительно нужно использовать цикл for только для выполнения запроса для конкретных клиентов, тогда вы можете сделать:

 for (i in 1:length(customers_id)) {

  sql_q <- paste0("SELECT '", names(customers_id)[i], "' AS customer, sum(value) AS value FROM db WHERE id IN (", paste(customers_id[[i]], collapse = ", "), ")")

}
  

sql_q ВОЗВРАТ

 [1] "SELECT 'John' AS customer, sum(value) AS value FROM db WHERE id IN (1, 2, 3)"
[1] "SELECT 'Rick' AS customer, sum(value) AS value FROM db WHERE id IN (4)"
[1] "SELECT 'Sam' AS customer, sum(value) AS value FROM db WHERE id IN (5, 6)"
  

Затем просто добавьте данные, чтобы получить свой результат, например

 # Example Data given
customers_id <- list(John = c(1,2,3), Rick = c(4), Sam = c(5,6))

# Assuming you have your db connection setup in 'con'
sql_list <- list()
for (i in 1:length(customers_id)) {

  sql_q <- paste0("SELECT '", names(customers_id)[i], "' AS customer, sum(value) AS value FROM db WHERE id IN (", paste(customers_id[[i]], collapse = ", "), ")")
  sql_d <- sqlQuery(con, sql_q)
  sql_list[[i]] <- sql_d

}

combined_d <- do.call("rbind", sql_list)