Given a table like this: ID A B C D 01 3 2 1 0 01 5 2 1 0 01 0 2 1 0 00 4 8 1 1 00 4 8 1 1 00 4 8 1 1 03 6 4 0 0 03 0 2 0 0 03 6 4 0 0 How could I use SQL
Tag: r
Select Rows That Does Not Contain any Negative Or Missing Value
Assume a database table has a few hundred columns. In SQL statements, how would you select rows/records that do not contain any negative or missing value? Can you do it using the sqldf package for R users? Here is an example of data frame with 6 rows and 2 columns: The SQL statement(s) should only return a ta…
Sort by one variable, group by another, and select first row in SQL Query in R
I need to apply a procedure in SQL that is easy for me since R, but has been really tortuous in SQL. I need to sort the data from highest to lowest by two variables, group based on another variable, and select the first item in each group. I leave the code that I am trying to pass from R
R: “Fuzzy Match” and “Between” Statements
I am working with the R Programming Language. I have the following tables (note: all variables appear as “Factors”): I am trying to “join” (e.g. inner join) this tables on the following conditions: 1) if table_1$id “fuzzy equal” table_2$id AND 2) if table_1$date BETWEEN(tab…
Creating a cumulative sum column with_order in R
I’m working alongside a SQL tutorial using queryparser and tidyquery in R. This has been going well until I was asked to do: Tidyquery reported that it did not support OVER functions so I am trying to replicate the OVER (PARTITION BY…) function with dplyr. This led me to with_order(order_by =̷…
How to append two dataframes when column number differ in PostgreSQL in R
What I try to do is that bind rows in my PostgreSQL databes in matched columns like rbindlist’s (from data.table) fill argument. In short, the table I’d like to see in my database is like this; I tried it in RPostgres in this way; But it doesn’t work and fields an error because the second ta…
Permission denied when using file.remove in R after updating to 4.1.1
After updating from 3.6.3 to 4.1.1 a weekly script I run broke. Specifically it triggers an error: To manually delete temp.csv I get a file in use by rsession.exe error from windows. If I restart R in Rstudio, then both file.remove() and manually deleting work. It might be that I’m using a bad stratgy i…
Is there a way to update ONLY field.type data onto SQL using dbWriteTable? Without having to pass the whole table’s value
So pretty much I am pulling a whole table from my database using: So this pulls my data as I need. After that perform some tests and to check the size of each column. I reassign field types based on my test. After that I would send the data back to the database using: Because I am not manipulating any
R – get a vector that tells me if a value of another vector is the first appearence or not
I have a data frame of sales with three columns: the code of the customer, the month the customer bought that item, and the year. A customer can buy something in september and then in december make another purchase, so appear two times. But I’m interested in knowing the absolutely new customoers by mont…
Find the latest version of a dataset available in SQL Server on specified dates, using RStudio
Each month I run a script in RStudio, on the 9th of the month. I need to manually append the latest DATAyymm sql data set onto the my_data vector, before running the script. Is there a way I can have the program automatically create this vector? Maybe it could connect to SQL Server and find what the latest av…