1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
# pmin() and pmax() respect na.rm
Code
test_translate_sql(pmin(x, y, z, na.rm = TRUE))
Output
<SQL> COALESCE(IFF(COALESCE(IFF(`x` <= `y`, `x`, `y`), `x`, `y`) <= `z`, COALESCE(IFF(`x` <= `y`, `x`, `y`), `x`, `y`), `z`), COALESCE(IFF(`x` <= `y`, `x`, `y`), `x`, `y`), `z`)
---
Code
test_translate_sql(pmax(x, y, z, na.rm = TRUE))
Output
<SQL> COALESCE(IFF(COALESCE(IFF(`x` >= `y`, `x`, `y`), `x`, `y`) >= `z`, COALESCE(IFF(`x` >= `y`, `x`, `y`), `x`, `y`), `z`), COALESCE(IFF(`x` >= `y`, `x`, `y`), `x`, `y`), `z`)
# row_number() with and without group_by() and arrange(): unordered defaults to Ordering by NULL (per empty_order)
Code
mf %>% mutate(rown = row_number())
Output
<SQL>
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS `rown`
FROM `df`
---
Code
mf %>% group_by(y) %>% mutate(rown = row_number())
Output
<SQL>
SELECT
`df`.*,
ROW_NUMBER() OVER (PARTITION BY `y` ORDER BY (SELECT NULL)) AS `rown`
FROM `df`
---
Code
mf %>% arrange(y) %>% mutate(rown = row_number())
Output
<SQL>
SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY `y`) AS `rown`
FROM `df`
ORDER BY `y`
|
