08-advanced-operations.Rmd

```{r db-advanced, include = FALSE}
eval_adv <- FALSE
if(Sys.getenv("GLOBAL_EVAL") != "") eval_adv <- Sys.getenv("GLOBAL_EVAL")
```

```{r, eval = eval_adv, include = FALSE}
library(connections)
library(dplyr)
library(rlang)
library(config)
library(dbplyr)
library(purrr)
```

# Advanced Operations

## Simple wrapper function

1. Load the `connections` and `dplyr` libraries
    ```{r, eval = eval_adv}
    library(connections)
    library(dplyr)
    library(dbplyr)
    library(config)
    ```

2. Use `connection_open()` to open a Database connection
    ```{r, eval = eval_adv}
    con <- connection_open(
      RPostgres::Postgres(),
      host =  "localhost",
      user = get("user"),
      password = get("pwd"),
      port = 5432,
      dbname = "postgres",
      bigint = "integer"
    )
    ```

3. Create a variable that points to the **v_orders** table
    ```{r, eval = eval_adv}
    orders <- tbl(con, in_schema("retail", "v_orders"))
    ```


4. Create a simple `dplyr` call that gets the average of all order totals
    ```{r, eval = eval_adv}
    orders %>%
      summarise(mean = mean(order_total, na.rm = TRUE))
    ```

5. Load the `rlang` library
    ```{r, eval = eval_adv}
    library(rlang)
    ```

6. Create a new function call `my_mean()` that will take an argument, `x`, and then returns the results of `enquo(x)`
    ```{r, eval = eval_adv}
    my_mean <- function(x){
      enquo(x)
    }
    ```

7. Test the new function. It should return the same variable name, but inside quosure. Use `order_total` as its argument's value to test
    ```{r, eval = eval_adv}
    
    ```

8. In the function, re-assign `x` to the result of `enquo(x)`, and then return `x`
    ```{r, eval = eval_adv}
    
    ```

9. Test the same way again, the output should match to what it was as before
    ```{r, eval = eval_adv}
    
    ```

10. Remove the last line that has `x`,  add the contents of the function with the initial `dplyr` code from step 3.  Then replace `order_total` with `!! x`
    ```{r, eval = eval_adv}
    
    ```

11. Test the new function by passing `order_total` as `x`
    ```{r, eval = eval_adv}
    
    ```

12. In the `summarise()` step, replace the name `mean`, with `!! as_label(x)`, also replace the `=` sign, with `:=`
    ```{r, eval = eval_adv}
    
    ```

13. Run the function again, the name of the column should match the argument value
    ```{r, eval = eval_adv}
    
    ```

14. Test the function by passing a formula, such as `order_total / order_qty`

    ```{r, eval = eval_adv}
    my_mean(order_total / order_qty)
    ```

15. Make the function generic, add a new argument called: `.data`. Inisde the function, replace `orders` with `.data`
    ```{r, eval = eval_adv}
    
    ```

16. The function now behaves more like a `dplyr` verb. Start with `orders` and then pipe into the function
    ```{r, eval = eval_adv}
    
    ```

17. Clean up the code by removing the pipe that inside the function
    ```{r, eval = eval_adv}
    
    ```

18. Confirm that there is no change in the behavior of the function
    ```{r, eval = eval_adv}
    orders %>% 
      my_mean(order_total)
    ```

19. Add a `show_query()` step to preview the resulting SQL statement
    ```{r, eval = eval_adv}
    
    ```

20. Try the function with a non-DB backed variable, such as `mtcars`. Use `mpg` as the aggregating variable
    ```{r, eval = eval_adv}
    mtcars %>% 
      my_mean(mpg)
    ```

## Multiple variables
*Create functions that handle a variable number of arguments. The goal of the exercise is to create an `anti-select()` function.*

1. Load the `purrr` package
    ```{r, eval = eval_adv}
    library(purrr)
    ```


2. Use *...* as the second argument of a function called `de_select()`.  Inside the function use `enquos()` to parse it
    ```{r, eval = eval_adv}
    de_select <- function(.data, ...){
      vars <- enquos(...)
      vars
    }
    ```

3. Test the function using *orders*
    ```{r, eval = eval_adv}
    orders %>%
      de_select(order_id, date)
    ```

4. Add a step to the function that iterates through each quosure and prefixes a minus sign to tell `select()` to drop that specific field.  Use `map()` for the iteration, and `quo()` to create the prefixed expression.
    ```{r, eval = eval_adv}

    ```

5. Run the same test to view the new results
    ```{r, eval = eval_adv}

    ```

6. Add the `select()` step.  Use *!!!* to parse the *vars* variable inside `select()`
    ```{r, eval = eval_adv}

    ```

7. Run the test again, this time the operation will take place.  
    ```{r, eval = eval_adv}
    
    ```

8. Add a `show_query()` step to see the resulting SQL
    ```{r, eval = eval_adv}
    
    ```

9. Test the function with a different data set, such as `mtcars`
    ```{r, eval = eval_adv}
    mtcars %>%
      de_select(mpg, wt, am)
    ```

## Multiple queries
*Suggested approach to avoid passing multiple, and similar, queries to the database*

1. Create a simple `dplyr` piped operation that returns the mean of *order_total* for the months of January, February and March as a group
    ```{r, eval = eval_adv}
    orders %>%
      filter(date_month %in% c(1,2,3)) %>%
      summarise(mean = mean(order_total, na.rm = TRUE)) 
    ```

2. Assign the first operation to a variable called *a*, and create copy of the operation but changing the selected months to January, March and April.  Assign the second one to a variable called *b*.
    ```{r, eval = eval_adv}
    a <- 
    
    b <- 
    ```

3. Use *union()* to pass *a* and *b* at the same time to the database
    ```{r, eval = eval_adv}
    
    ```

4. Pipe the previous instruction to `show_query()` to confirm that the resulting query is a single one
    ```{r, eval = eval_adv}

    ```


5. Assign to a new variable called *months* an overlapping set of months
    ```{r, eval = eval_adv}
    months <- list(
      c(1,2,3),
      c(1,3,4),
      c(2,4,6)
    )
    ```

6. Use `map()` to cycle through each set of overlapping months.  Notice that it returns three separate results, meaning that it went to the database three times
    ```{r, eval = eval_adv}
    
    ```

7. Add a `reduce()` operation and use `union()` command to create a single query
    ```{r, eval = eval_adv}
    
    ```

8. Use `show_query()` to see the resulting single query sent to the database
    ```{r, eval = eval_adv}

    ```


## Multiple queries with an overlapping range

1. Create a table with a *from* and *to* ranges
    ```{r, eval = eval_adv}
    ranges <- tribble(
      ~ from, ~to, 
           1,   4,
           2,   5,
           3,   7
    )
    ```

2. See how `map2()` works by passing the two variables as the *x* and *y* arguments, and adding them as the function
    ```{r, eval = eval_adv}
    
    ```

3. Replace *x + y* with the `dplyr` operation from the previous exercise.  In it, re-write the filter to use *x* and *y* as the month ranges 
    ```{r, eval = eval_adv}
    
    ```

4. Add the `reduce()` operation
    ```{r, eval = eval_adv}
    
    ```

5. Add a `show_query()` step to see how the final query was constructed.
    ```{r, eval = eval_adv}
    
    ```


## Characters to field names

1. Create two character variables. One with the name of a field in *flights* and another with a new name to be given to the field
    ```{r, eval = eval_adv}
    my_field <- "new"
    orders_field <- "order_total"
    ```

2. Add a `mutate()` step that adds the new field. And then another step selecting just the new field
    ```{r, eval = eval_adv}
    
    ```

3. Add a `mutate()` step that adds the new field. And then another step selecting just the new field
    ```{r, eval = eval_adv}
    
    ```

4. Wrap `orders_field` inside a `sym()` function
    ```{r, eval = eval_adv}

    ```

5. Pipe the code into `show_query()`
    ```{r, eval = eval_adv}

    ```

```{r, eval = eval_adv, include = FALSE}
connection_close(con)
```